应收单应付单未开票核销但未开票数量未开票金额为0原创
4人赞赏了该文章
242次浏览
编辑于2024年01月29日 23:35:03
适用场景:应收单应付单未开票核销但未开票数量未开票金额为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;
推荐阅读