币别为人民币,应付款账龄表尚未收款金额原币和本位币不一致 sql语句原创
4人赞赏了该文章
119次浏览
编辑于2024年04月23日 18:13:18
历史版本6.多或者7.多的版本,核销记录没有关于核销金额本位币的字段,升级版本后会发现历史单据的核销记录本次核销本位币金额为空,导致账龄表尚未付款金额本位币和原币不一致。
需要修复核销记录上的本次核销金额本位币以及单据上的已核销金额本位币
可参考以下语句,注意修复前一定要进行全表备份!!!
涉及到的表: T_AP_PAYBILLENTRY_o T_AP_PAYMATCHLOGENTRY T_AP_REFUNDBILLENTRY T_AP_PAYABLEPLAN T_AP_OTHERPAYABLEENTRY T_AR_RECEIVABLEPLAN 查询应付付款核销记录,汇率为1,本次核销金额原币和本位币不一致的单据类型,根据单据类型备份需要更新的表 例如查询: select distinct FSOURCEFROMID from T_AP_PAYMATCHLOGENTRY where FEXCHANGERATE=1 and FCURWRITTENOFFAMOUNTFOR<>FCURWRITTENOFFAMOUNT 结果: AP_Match AP_OtherPayable AP_Payable AP_PAYBILL AP_REFUNDBILL AR_receivable 根据涉及到的单据类型进行数据修复(汇率为1,已核销金额原币和本位币不一致的单据) 应付付款核销记录 update T_AP_PAYMATCHLOGENTRY set FCURWRITTENOFFAMOUNT=FCURWRITTENOFFAMOUNTFOR where FEXCHANGERATE=1 and FCURWRITTENOFFAMOUNTFOR<>FCURWRITTENOFFAMOUNT 付款单 update c set c.FWRITTENOFFAMOUNT=b.FWRITTENOFFAMOUNTFOR from t_ap_paybill a left join T_AP_PAYBILLENTRY b on a.fid=b.fid left join T_AP_PAYBILLENTRY_o c on c.FENTRYID=b.FENTRYID where a.FEXCHANGERATE=1 and c.FWRITTENOFFAMOUNT<>b.FWRITTENOFFAMOUNTFOR 付款退款单 update b set b.FWRITTENOFFAMOUNT=b.FWRITTENOFFAMOUNTFOR from T_AP_REFUNDBILL a left join T_AP_REFUNDBILLENTRY b on a.fid=b.fid where a.FEXCHANGERATE=1 and b.FWRITTENOFFAMOUNTFOR<>b.FWRITTENOFFAMOUNT 应付单 update c set c.FWRITTENOFFAMOUNT=c.FWRITTENOFFAMOUNTFOR from t_ap_payable a left join t_ap_payablefin b on a.fid=b.fid left join t_ap_payableplan c on a.fid=c.fid where b.FEXCHANGERATE=1 and c.FWRITTENOFFAMOUNTFOR<>c.FWRITTENOFFAMOUNT 应付核销单 update b set b.FMATCHAMOUNT=b.FMATCHAMOUNTFOR from T_AP_MATCK a left join T_AP_MATCKentry b on a.fid=b.fid where b.FEXCHANGERATE=1 and FMATCHAMOUNTFOR<>b.FMATCHAMOUNT 其他应付单 update b set b.FWRITTENOFFAMOUNT=b.FWRITTENOFFAMOUNTFOR from T_AP_OTHERPAYABLE a left join T_AP_OTHERPAYABLEENTRY b on a.fid=b.fid where a.FEXCHANGERATE=1 and b.FWRITTENOFFAMOUNTFOR<>b.FWRITTENOFFAMOUNT 应收单 update c set c.FWRITTENOFFAMOUNT=c.FWRITTENOFFAMOUNTFOR from T_AR_RECEIVABLE a left join T_AR_RECEIVABLEFIN b on a.fid=b.fid left join T_AR_RECEIVABLEPLAN c on a.fid=c.fid where b.FEXCHANGERATE=1 and c.FWRITTENOFFAMOUNTFOR<>c.FWRITTENOFFAMOUNT
推荐阅读