应收单应付单未开票核销但未开票数量未开票金额为0

适用场景：应收单应付单未开票核销但未开票数量未开票金额为0或未收付款核销但未核销金额为0

查看是否有停用BOS元数据中保存插件，插件位置如下：

建议启用。

已有异常数据修复SQL：

涉及数据表和字段：

应收单单据头：T_AR_RECEIVABLE / 应付单单据头：T_AP_PAYABLE

应收单收款计划：T_AR_RECEIVABLEPLAN / 应付单付款计划：T_AP_PAYABLEPLAN

    应收金额：FPAYAMOUNTFOR

    已核销金额：FWRITTENOFFAMOUNTFOR

    未核销金额：FNOTVERIFICATEAMOUNT（低版本没有这个字段）

应收单明细：T_AR_RECEIVABLEENTRY / 应付单明细：T_AP_PAYABLEENTRY

    价税合计：FALLAMOUNTFOR

    已开票金额：FOPENAMOUNTFOR

    未开票金额：FNOINVOICEAMOUNT

    计价基本数量：FBASICUNITQTY

    已开票数量：FOPENQTY

    未开票数量：FNOINVOICEQTY

--修复应收单明细的未开票数量与未开票金额
MERGE INTO T_AR_RECEIVABLEENTRY T1 USING(
	SELECT A.FBILLNO,A.FDATE,B.FENTRYID,B.FNOINVOICEAMOUNT,B.FALLAMOUNTFOR,B.FOPENAMOUNTFOR,B.FNOINVOICEQTY,B.FBASICUNITQTY,B.FOPENQTY 
	FROM T_AR_RECEIVABLE A
	LEFT JOIN T_AR_RECEIVABLEENTRY B ON A.FID=B.FID
	WHERE (B.FNOINVOICEAMOUNT<>B.FALLAMOUNTFOR-B.FOPENAMOUNTFOR OR B.FNOINVOICEQTY<>B.FBASICUNITQTY-B.FOPENQTY) AND A.FDOCUMENTSTATUS<>'Z'
) T2 ON (T1.FENTRYID=T2.FENTRYID)
WHEN MATCHED THEN UPDATE SET T1.FNOINVOICEAMOUNT=T1.FALLAMOUNTFOR-T1.FOPENAMOUNTFOR,T1.FNOINVOICEQTY=T1.FBASICUNITQTY-T1.FOPENQTY;

--修复应付单明细的未开票数量和未开票金额
MERGE INTO T_AP_PAYABLEENTRY T1 USING(
    SELECT A.FBILLNO,A.FDATE,B.FENTRYID,B.FNOINVOICEAMOUNT,B.FALLAMOUNTFOR,B.FOPENAMOUNTFOR,B.FNOINVOICEQTY,B.FBASICUNITQTY,B.FOPENQTY 
	--UPDATE B SET B.FNOINVOICEAMOUNT=B.FALLAMOUNTFOR-B.FOPENAMOUNTFOR , B.FNOINVOICEQTY=B.FBASICUNITQTY-B.FOPENQTY
	FROM T_AP_PAYABLE A
	LEFT JOIN T_AP_PAYABLEENTRY B ON A.FID=B.FID
	WHERE ( B.FNOINVOICEAMOUNT<>B.FALLAMOUNTFOR-B.FOPENAMOUNTFOR OR B.FNOINVOICEQTY<>B.FBASICUNITQTY-B.FOPENQTY ) AND A.FDOCUMENTSTATUS<>'Z'
)T2 ON (T1.FENTRYID=T2.FENTRYID)
WHEN MATCHED THEN UPDATE SET T1.FNOINVOICEAMOUNT=T1.FALLAMOUNTFOR-T1.FOPENAMOUNTFOR,T1.FNOINVOICEQTY=T1.FBASICUNITQTY-T1.FOPENQTY;

------------------------------------------------------------------------------

--修复应收单收款计划的未核销金额
MERGE INTO T_AR_RECEIVABLEPLAN T1 USING(
	SELECT A.FBILLNO,A.FDATE,A.FWRITTENOFFSTATUS,B.FPAYAMOUNTFOR 应付金额,B.FWRITTENOFFAMOUNTFOR 已核销金额,B.FNOTVERIFICATEAMOUNT 未核销金额,B.FENTRYID
	FROM T_AR_RECEIVABLE A
	LEFT JOIN T_AR_RECEIVABLEPLAN B ON A.FID=B.FID
	WHERE A.FDOCUMENTSTATUS<>'Z' AND B.FNOTVERIFICATEAMOUNT<>B.FPAYAMOUNTFOR-B.FWRITTENOFFAMOUNTFOR
) T2 ON (T1.FENTRYID=T2.FENTRYID)
WHEN MATCHED THEN UPDATE SET T1.FNOTVERIFICATEAMOUNT=T1.FPAYAMOUNTFOR-T1.FWRITTENOFFAMOUNTFOR;


--修复应付单付款计划的未核销金额
MERGE INTO T_AP_PAYABLEPLAN T1 USING(
	SELECT A.FBILLNO,A.FDATE,A.FWRITTENOFFSTATUS,B.FPAYAMOUNTFOR 应付金额,B.FWRITTENOFFAMOUNTFOR 已核销金额,B.FNOTVERIFICATEAMOUNT 未核销金额,B.FENTRYID
	FROM T_AP_PAYABLE A
	LEFT JOIN T_AP_PAYABLEPLAN B ON A.FID=B.FID
	WHERE A.FDOCUMENTSTATUS<>'Z' AND B.FNOTVERIFICATEAMOUNT<>B.FPAYAMOUNTFOR-B.FWRITTENOFFAMOUNTFOR
) T2 ON (T1.FENTRYID=T2.FENTRYID)
WHEN MATCHED THEN UPDATE SET T1.FNOTVERIFICATEAMOUNT=T1.FPAYAMOUNTFOR-T1.FWRITTENOFFAMOUNTFOR;