财务应付单删除提示反写暂估应付单采购发票基本单位数量超额修复SQL参考原创
3人赞赏了该文章
156次浏览
编辑于2024年07月02日 17:39:39
业务场景:
标准采购的暂估应付下推财务应付后,财务应付删除提示反写暂估应付单采购发票基本单位数量超出可用数额,是因为暂估应付单上的采购发票数量(基本单位)字段异常导致,需要做数据修复。
报错截图:
应收应付核销字段介绍:应收应付核销字段简介及常见问题分析
如下SQL语句仅供参考!!!更新前建议做全表备份!!!
注意事项:
适用于SQLSERVER数据库且物料无多单位转换(计价数量和基本数量一致)
修复语句示例:通过财务应付单号找到上游暂估应付单且采购发票基本数量异常的明细行进行数据修复
SELECT A.FBILLNO 单据编号,A.FID 单据头内码,A.FDATE 业务日期,A.FISINIT 是否期初,A.FREDBLUE 红蓝字,A.FBILLTYPEID 单据类型,A.FSETACCOUNTTYPE 立账类型,A.FDOCUMENTSTATUS 单据状态,A.FWRITTENOFFSTATUS 付款核销状态,B.FENTRYID, B.FBASICUNITQTY 计价基本数量,B.FOPENSTATUS 明细开票状态,B.FALLAMOUNTFOR 价税合计,B.FOPENAMOUNTFOR 已开票核销金额,B.FOPENAMOUNT 已开票核销金额本位币,B.FOPENQTY 已开票核销数量,B.FPUSHREDQTY 已下推红字发票数量,B.FNOTAXAMOUNTFOR 不含税金额,B.FHADMATCHAMOUNTFOR 已开票金额,B.FMATCHNOTTAXAMTFOR 已开票不含税金额原,B.FMATCHNOTTAXAMT 已开票不含税金额本,B.FPAYMENTAMOUNT 已结算金额,B.FSOURCEBILLNO 源单编号, B.FBUYIVQTY 采购发票数量,B.FBUYIVBASICQTY 采购发票基本数量,B.FIVALLAMOUNTFOR 采购发票价税合计,ISNULL(C.FTHEMATCHNOTAXAMOUNT,0) 核销记录已核销不含税金额,ISNULL(C.FCUROPENQTY,0) 核销记录开票核销数量,ISNULL(D.FBASICUNITQTY_D,0)+ISNULL(C.FCUROPENQTY,0),ISNULL(D.FALLAMOUNTFOR_D,0)+ISNULL(C.FCUROPENAMOUNTFOR,0),D.*,C.* --UPDATE B SET B.FBUYIVBASICQTY=ISNULL(D.FBASICUNITQTY_D,0)+ISNULL(C.FCUROPENQTY,0),B.FBUYIVQTY=ISNULL(D.FBASICUNITQTY_D,0)+ISNULL(C.FCUROPENQTY,0),B.FIVALLAMOUNTFOR=ISNULL(D.FALLAMOUNTFOR_D,0)+ISNULL(C.FCUROPENAMOUNTFOR,0) FROM T_AP_PAYABLE A LEFT JOIN T_AP_PAYABLEENTRY B ON A.FID=B.FID LEFT JOIN ( SELECT FSRCBILLNO,FSRCBILLID,FSRCROWID,SUM(FCUROPENQTY) FCUROPENQTY,SUM(FCUROPENAMOUNTFOR) FCUROPENAMOUNTFOR,SUM(FCUROPENAMOUNT) FCUROPENAMOUNT,SUM(CASE WHEN FCURMATCHNOTTAXAMOUNT=0 THEN FTHEMATCHNOTAXAMOUNT ELSE FCURMATCHNOTTAXAMOUNT END) FTHEMATCHNOTAXAMOUNT FROM T_AP_BILLINGMATCHLOGENTRY WHERE FSOURCEFROMID='AP_PAYABLE' GROUP BY FSRCBILLNO,FSRCBILLID,FSRCROWID ) C ON A.FID=C.FSRCBILLID AND B.FENTRYID=C.FSRCROWID LEFT JOIN ( SELECT LK.FSBILLID,LK.FSID,SUM(LK.FALLAMOUNTFOR_D) FALLAMOUNTFOR_D,SUM(LK.FBASICUNITQTY) FBASICUNITQTY_D FROM T_AP_PAYABLE PA LEFT JOIN T_AP_PAYABLEENTRY PAE ON PA.FID=PAE.FID INNER JOIN T_AP_PAYABLE_LK LK ON LK.FENTRYID=PAE.FENTRYID WHERE LK.FSTABLENAME='T_AP_PAYABLEENTRY' AND PA.FDOCUMENTSTATUS NOT IN ('Z','C') AND PA.FCANCELSTATUS<>'B' GROUP BY LK.FSBILLID,LK.FSID ) D ON B.FENTRYID=D.FSID AND B.FID=D.FSBILLID WHERE ( A.FBUSINESSTYPE='CG' AND A.FBYVERIFY<>2 AND FSETACCOUNTTYPE=2 AND B.FBASICUNITQTY<>0 AND B.FBUYIVBASICQTY<>ISNULL(C.FCUROPENQTY,0)+ISNULL(D.FBASICUNITQTY_D,0)) AND B.FENTRYID IN ( SELECT C.FSID FROM T_AP_PAYABLE A LEFT JOIN T_AP_PAYABLEENTRY B ON A.FID=B.FID INNER JOIN T_AP_PAYABLE_LK C ON B.FENTRYID=C.FENTRYID AND FSTABLENAME='T_AP_PAYABLEENTRY' WHERE A.FBILLNO IN ('财务应付单') )
应收款同理,修复语句示例:
SELECT A.FBILLNO 单据编号,A.FID 单据头内码,A.FDATE 业务日期,A.FISINIT 是否期初,A.FREDBLUE 红蓝字,A.FBILLTYPEID 单据类型,A.FSETACCOUNTTYPE 立账类型,A.FDOCUMENTSTATUS 单据状态,A.FWRITTENOFFSTATUS 付款核销状态,B.FENTRYID, B.FBASICUNITQTY 计价基本数量,B.FOPENSTATUS 明细开票状态,B.FALLAMOUNTFOR 价税合计,B.FOPENAMOUNTFOR 已开票核销金额,B.FOPENAMOUNT 已开票核销金额本位币,B.FOPENQTY 已开票核销数量,B.FPUSHREDQTY 已下推红字发票数量,B.FNOTAXAMOUNTFOR 不含税金额,B.FHADMATCHAMOUNTFOR 已开票金额,B.FMATCHNOTTAXAMTFOR 已开票不含税金额原,B.FMATCHNOTTAXAMT 已开票不含税金额本,B.FRECEIVEAMOUNT 已结算金额,B.FSOURCEBILLNO 源单编号, B.FBUYIVQTY 销售发票数量,B.FBUYIVBASICQTY 销售发票基本数量,B.FIVALLAMOUNTFOR 销售发票价税合计,ISNULL(C.FTHEMATCHNOTAXAMOUNT,0) 核销记录已核销不含税金额,ISNULL(C.FCUROPENQTY,0) 核销记录开票核销数量,ISNULL(D.FBASICUNITQTY,0)+ISNULL(C.FCUROPENQTY,0),ISNULL(D.FALLAMOUNTFOR,0)+ISNULL(C.FCUROPENAMOUNTFOR,0),D.*,C.* --UPDATE B SET B.FBUYIVBASICQTY=ISNULL(C.FCUROPENQTY,0)+ISNULL(D.FBASICUNITQTY,0),B.FBUYIVQTY=ISNULL(C.FCUROPENQTY,0)+ISNULL(D.FBASICUNITQTY,0),B.FIVALLAMOUNTFOR=ISNULL(C.FCUROPENAMOUNTFOR,0)+ISNULL(D.FALLAMOUNTFOR,0) FROM T_AR_RECEIVABLE A LEFT JOIN T_AR_RECEIVABLEENTRY B ON A.FID=B.FID LEFT JOIN T_AR_RECEIVABLEENTRY_O O ON B.FENTRYID=O.FENTRYID LEFT JOIN ( SELECT FSRCBILLID,FSRCROWID,SUM(FCUROPENQTY) FCUROPENQTY,SUM(FCUROPENAMOUNTFOR) FCUROPENAMOUNTFOR,SUM(CASE WHEN FCURMATCHNOTTAXAMOUNT=0 THEN FTHEMATCHNOTAXAMOUNT ELSE FCURMATCHNOTTAXAMOUNT END) FTHEMATCHNOTAXAMOUNT FROM T_AR_BILLINGMATCHLOGENTRY WHERE FSOURCEFROMID='AR_RECEIVABLE' GROUP BY FSRCBILLID,FSRCROWID ) C ON A.FID=C.FSRCBILLID AND B.FENTRYID=C.FSRCROWID --应收开票核销记录 LEFT JOIN ( SELECT C.FSBILLID,C.FSID,SUM(C.FALLAMOUNTFOR_D) FALLAMOUNTFOR,SUM(C.FBASICUNITQTY) FBASICUNITQTY FROM T_AR_RECEIVABLE A LEFT JOIN T_AR_RECEIVABLEENTRY B ON A.FID=B.FID LEFT JOIN T_AR_RECEIVABLEENTRY_LK C ON B.FENTRYID=C.FENTRYID AND C.FSTABLENAME='T_AR_RECEIVABLEENTRY' WHERE A.FDOCUMENTSTATUS NOT IN ('Z','C') AND A.FCANCELSTATUS<>'B' GROUP BY C.FSBILLID,C.FSID ) D ON B.FENTRYID=D.FSID AND B.FID=D.FSBILLID --财务 WHERE ( A.FBUSINESSTYPE='BZ' AND A.FBYVERIFY<>2 AND FSETACCOUNTTYPE=2 AND B.FBASICUNITQTY<>0 AND B.FBUYIVBASICQTY<>ISNULL(C.FCUROPENQTY,0)+ISNULL(D.FBASICUNITQTY,0)) AND B.FENTRYID IN ( SELECT C.FSID FROM T_AR_RECEIVABLE A LEFT JOIN T_AR_RECEIVABLEENTRY B ON A.FID=B.FID INNER JOIN T_AR_RECEIVABLEENTRY_LK C ON B.FENTRYID=C.FENTRYID AND FSTABLENAME='T_AR_RECEIVABLEENTRY' WHERE A.FBILLNO IN ('财务应收单') )
推荐阅读