先开票业务和出库后开票混用的前提下,销售订单列表获取已开票金额SQL参考原创
8人赞赏了该文章
70次浏览
编辑于2024年07月25日 14:39:13
业务场景:
先开票业务和出库后开票混用的前提下,需要在销售订单列表获取已开票金额,
订单明细已新增金额字段,反写规则已经配置,历史数据需要做数据修复处理。
注意:
1. 适用于出库单下推应收单不存在分录合并业务(标准产品下就不允许分录合并下推)
2. 适用于已开票金额字段在订单明细表
如下SQL语句仅供参考!!!更新前建议做全表备份!!!
MERGE INTO T_SAL_ORDERENTRY T1 USING ( SELECT A.FBILLNO 销售订单单号,B.FENTRYID,B.FQTY 销售数量,B.FBASEUNITQTY 销售基本数量,B.FUNITID 销售单位,E.FPRICEUNITID 计价单位, B.FSTOCKUNITID 库存单位,E.FPRICEUNITQTY 计价数量,B.FSTOCKBASEQTY 库存基本数量,C.FBASEARJOINQTY 关联应收数量计价基本, C.FSALBASEARJOINQTY 关联应收数量销售基本,C.FSTOCKBASEARJOINQTY 关联应收数量库存基本,E.FPRICEBASEQTY 计价基本数量, D.FBASEFINARQTY 先开票数量计价基本,D.FSALBASEFINARQTY 先开票数量销售基本,C.FBASEARQTY 累计应收数量销售基本, C.FARQTY 累计应收数量销售,C.FARAMOUNT 累计应收金额,F.FJOINORDERAMOUNT 关联应收金额订单 ,F_ORA_AMOUNT 已开票金额,G.FALLAMOUNTFOR,H.FALLAMOUNTFOR FALLAMOUNTFOR1,ISNULL(G.FALLAMOUNTFOR,0)+ISNULL(H.FALLAMOUNTFOR,0) FALLAMOUNTFORNEW FROM T_SAL_ORDER A LEFT JOIN T_SAL_ORDERENTRY B ON A.FID=B.FID LEFT JOIN T_SAL_ORDERENTRY_R C ON B.FENTRYID=C.FENTRYID LEFT JOIN T_SAL_ORDERENTRY_E D ON B.FENTRYID=D.FENTRYID LEFT JOIN T_SAL_ORDERENTRY_F E ON B.FENTRYID=E.FENTRYID LEFT JOIN T_SAL_ORDERFIN F ON A.FID=F.FID LEFT JOIN ( SELECT C.FSID,SUM(C.FBASICUNITQTY) FBASICUNITQTY,SUM(C.FALLAMOUNTFOR) FALLAMOUNTFOR FROM T_IV_SALESIC A LEFT JOIN T_IV_SALESICENTRY B ON A.FID=B.FID INNER JOIN T_IV_SALESICENTRY_LK C ON B.FENTRYID=C.FENTRYID AND UPPER(FSTABLENAME)='T_SAL_ORDERENTRY' WHERE FCANCELSTATUS='A' AND A.FDOCUMENTSTATUS<>'Z' AND B.FPUSHREDQTY=0 GROUP BY C.FSID ) G ON B.FENTRYID=G.FSID --订单下推发票 LEFT JOIN ( SELECT D.FORDERENTRYID,SUM(C.FBASICUNITQTY) FBASICUNITQTY,SUM(C.FALLAMOUNTFOR) FALLAMOUNTFOR FROM T_IV_SALESIC A LEFT JOIN T_IV_SALESICENTRY B ON A.FID=B.FID INNER JOIN T_IV_SALESICENTRY_LK C ON B.FENTRYID=C.FENTRYID AND UPPER(FSTABLENAME)='T_AR_RECEIVABLEENTRY' INNER JOIN T_AR_RECEIVABLEENTRY D ON C.FSID=D.FENTRYID AND C.FSBILLID=D.FID WHERE FCANCELSTATUS='A' AND A.FDOCUMENTSTATUS<>'Z' AND B.FPUSHREDQTY=0 GROUP BY D.FORDERENTRYID ) H ON B.FENTRYID=H.FORDERENTRYID --订单下推应收下推发票 WHERE F_ORA_AMOUNT<>ISNULL(G.FALLAMOUNTFOR,0)+ISNULL(H.FALLAMOUNTFOR,0) AND A.FBILLNO='订单单号' --通过单号查询 ) T2 ON (T1.FENTRYID=T2.FENTRYID) WHEN MATCHED THEN UPDATE SET T1.F_ORA_AMOUNT=T2.FALLAMOUNTFORNEW;
推荐阅读