修复应收应付单的已结算金额 sql语句原创
8人赞赏了该文章
261次浏览
编辑于2024年04月28日 11:14:54
业务场景:
存在单据已经完全核销了,但是已结算金额为还为0的情况
以应收单为例,可以根据以下两点可以进行排查:
1、查看应收单的付款条件跟付款计划是否匹配。
例如:
收付款条件按物料明细付款,那么计划页签上需要有物料,销售订单号,销售订单明细内码以及物料行号等字段。
如果是按订单收款,那么计划页签需要有销售订单号。
2、查看应收单明细行是否是正负数相等的金额。
例如:
应收单多行明细,其中一行价税合计为100,另一行为-100,收款计划页签合并到一行,总金额为0,就不会显示这两行数据,核销后这两行明细的已结算金额还是为0(该问题正在优化中,后续版本会解决)
注意:修复已结算金额只能修复完全收款或者完全没有收款的情况,部分收款的由于结算金额是会分摊到每一行明细的,无法直接数据库进行修复,需要等到完全收款之后再进行批量修复。
修复语句:
修改前一定要全表进行备份!!!
应收单: 修复应收单完全收款的异常单据(核销状态为完全) merge into t_ar_receivableentry t1 using( select a.fbillno,b.fentryid, b.FRECEIVEAMOUNT,b.FNORECEIVEAMOUNT,b.FALLAMOUNTFOR from t_ar_receivable a left join t_ar_receivableentry b on a.fid=b.fid where a.fdocumentstatus<>'Z' and a.FWRITTENOFFSTATUS='C' and b.FALLAMOUNTFOR<>0 and( b.FRECEIVEAMOUNT<>b.FALLAMOUNTFOR or b.FNORECEIVEAMOUNT<>0) )t2 on (t1.fentryid=t2.fentryid) when matched then update set t1.FRECEIVEAMOUNT=t1.fallamountfor , t1.FNORECEIVEAMOUNT=0; 修复应收单完全没有收款的异常单据(核销状态为空) merge into t_ar_receivableentry t1 using( select a.fbillno,b.fentryid, b.FRECEIVEAMOUNT,b.FNORECEIVEAMOUNT,b.FALLAMOUNTFOR from t_ar_receivable a left join t_ar_receivableentry b on a.fid=b.fid where a.fdocumentstatus<>'Z' and a.FWRITTENOFFSTATUS in ('','A') and b.FALLAMOUNTFOR<>0 and (b.FNORECEIVEAMOUNT<>b.FALLAMOUNTFOR or b.FRECEIVEAMOUNT<>0) )t2 on (t1.fentryid=t2.fentryid) when matched then update set t1.FRECEIVEAMOUNT=0 , t1.FNORECEIVEAMOUNT=t1.FALLAMOUNTFOR; 应付单: 修复应付单完全付款的异常单据(核销状态为完全) merge into t_ap_payableentry t1 using( select a.fbillno,b.fentryid, b.FPAYMENTAMOUNT,b.FNORECEIVEAMOUNT,b.FALLAMOUNTFOR from t_ap_payable a left join t_ap_payableentry b on a.fid=b.fid where a.fdocumentstatus<>'Z' and a.FWRITTENOFFSTATUS='C' and b.FALLAMOUNTFOR<>0 and( b.FPAYMENTAMOUNT<>b.FALLAMOUNTFOR or b.FNORECEIVEAMOUNT<>0) )t2 on (t1.fentryid=t2.fentryid) when matched then update set t1.FPAYMENTAMOUNT=0 , t1.FNORECEIVEAMOUNT=t1.FALLAMOUNTFOR; 修复应付单完全没有付款的异常单据(核销状态为空) merge into t_ap_payableentry t1 using( select a.fbillno,b.fentryid, b.FPAYMENTAMOUNT,b.FNORECEIVEAMOUNT,b.FALLAMOUNTFOR from t_ap_payable a left join t_ap_payableentry b on a.fid=b.fid where a.fdocumentstatus<>'Z' and a.FWRITTENOFFSTATUS in ('','A') and b.FALLAMOUNTFOR<>0 and (b.FNORECEIVEAMOUNT<>b.FALLAMOUNTFOR or b.FPAYMENTAMOUNT<>0) )t2 on (t1.fentryid=t2.fentryid) when matched then update set t1.FPAYMENTAMOUNT=0 , t1.FNORECEIVEAMOUNT=t1.FALLAMOUNTFOR;
推荐阅读