收付款核销记录中单据编号和单据的单据编号不一致修复SQL参考原创
30人赞赏了该文章
186次浏览
编辑于2024年05月29日 11:33:27
业务场景:
存在单据核销后修改过单据编号的情况,导致单据的单据编号和核销记录的不匹配,需要修复核销记录的单据编号字段。
转销核销记录和收付款核销记录在同一个数据表,可适用。
如下SQL语句仅供参考!!!更新前建议做全表备份!!!
应付付款核销记录:
--应付单 MERGE INTO T_AP_PAYMATCHLOGENTRY T1 USING ( SELECT A.FENTRYID,A.FSRCBILLID,A.FSRCBILLNO,B.FID,B.FBILLNO,A.FSOURCEFROMID FROM T_AP_PAYMATCHLOGENTRY A LEFT JOIN T_AP_PAYABLE B ON A.FSRCBILLID=B.FID WHERE UPPER(A.FSOURCEFROMID)='AP_PAYABLE' AND A.FSRCBILLNO<>B.FBILLNO ) T2 ON (T1.FENTRYID=T2.FENTRYID) WHEN MATCHED THEN UPDATE SET T1.FSRCBILLNO=T2.FBILLNO; --付款单 MERGE INTO T_AP_PAYMATCHLOGENTRY T1 USING ( SELECT A.FENTRYID,A.FSRCBILLID,A.FSRCBILLNO,B.FID,B.FBILLNO,A.FSOURCEFROMID FROM T_AP_PAYMATCHLOGENTRY A LEFT JOIN T_AP_PAYBILL B ON A.FSRCBILLID=B.FID WHERE UPPER(A.FSOURCEFROMID)='AP_PAYBILL' AND A.FSRCBILLNO<>B.FBILLNO ) T2 ON (T1.FENTRYID=T2.FENTRYID) WHEN MATCHED THEN UPDATE SET T1.FSRCBILLNO=T2.FBILLNO; --付款退款单 MERGE INTO T_AP_PAYMATCHLOGENTRY T1 USING ( SELECT A.FENTRYID,A.FSRCBILLID,A.FSRCBILLNO,B.FID,B.FBILLNO,A.FSOURCEFROMID FROM T_AP_PAYMATCHLOGENTRY A LEFT JOIN T_AP_REFUNDBILL B ON A.FSRCBILLID=B.FID WHERE UPPER(A.FSOURCEFROMID)='AP_REFUNDBILL' AND A.FSRCBILLNO<>B.FBILLNO ) T2 ON (T1.FENTRYID=T2.FENTRYID) WHEN MATCHED THEN UPDATE SET T1.FSRCBILLNO=T2.FBILLNO;
--应收收款核销记录
--应收单 MERGE INTO T_AR_RECMACTHLOGENTRY T1 USING ( SELECT A.FENTRYID,A.FSRCBILLID,A.FSRCBILLNO,B.FID,B.FBILLNO,A.FSOURCEFROMID FROM T_AR_RECMACTHLOGENTRY A LEFT JOIN T_AR_RECEIVABLE B ON A.FSRCBILLID=B.FID WHERE UPPER(A.FSOURCEFROMID)='AR_RECEIVABLE' AND A.FSRCBILLNO<>B.FBILLNO ) T2 ON (T1.FENTRYID=T2.FENTRYID) WHEN MATCHED THEN UPDATE SET T1.FSRCBILLNO=T2.FBILLNO; --收款单 MERGE INTO T_AR_RECMACTHLOGENTRY T1 USING ( SELECT A.FENTRYID,A.FSRCBILLID,A.FSRCBILLNO,B.FID,B.FBILLNO,A.FSOURCEFROMID FROM T_AR_RECMACTHLOGENTRY A LEFT JOIN T_AR_RECEIVEBILL B ON A.FSRCBILLID=B.FID WHERE UPPER(A.FSOURCEFROMID)='AR_RECEIVEBILL' AND A.FSRCBILLNO<>B.FBILLNO ) T2 ON (T1.FENTRYID=T2.FENTRYID) WHEN MATCHED THEN UPDATE SET T1.FSRCBILLNO=T2.FBILLNO; --收款退款单 MERGE INTO T_AR_RECMACTHLOGENTRY T1 USING ( SELECT A.FENTRYID,A.FSRCBILLID,A.FSRCBILLNO,B.FID,B.FBILLNO,A.FSOURCEFROMID FROM T_AR_RECMACTHLOGENTRY A LEFT JOIN T_AR_REFUNDBILL B ON A.FSRCBILLID=B.FID WHERE UPPER(A.FSOURCEFROMID)='AR_REFUNDBILL' AND A.FSRCBILLNO<>B.FBILLNO ) T2 ON (T1.FENTRYID=T2.FENTRYID) WHEN MATCHED THEN UPDATE SET T1.FSRCBILLNO=T2.FBILLNO;
推荐阅读