销售发票反写发票号至应收单,历史数据处理SQL参考原创
79人赞赏了该文章
407次浏览
编辑于2024年05月15日 17:23:52
业务场景:自定义反写规则:应收单下推销售发票后,销售发票反写单据头发票号至应收单明细自定义字段
适用于应收单自定义字段在明细(如在单据头需要适当修改语句),且SQL SERVER数据库
如下SQL语句仅供参考!!!更新前建议做全表备份!!!
--场景1:有分批下推的情况 需要拼接发票号,按逗号,拼接 SELECT A.FBILLNO 应收单号,A.FSETACCOUNTTYPE,B.FENTRYID 应收行内码,ISNULL(C.FIVNUMBERNEW,''),C.* --UPDATE B SET B.F_BGP_TEXT=ISNULL(C.FIVNUMBERNEW,'') FROM T_AR_RECEIVABLE A LEFT JOIN T_AR_RECEIVABLEENTRY B ON A.FID=B.FID INNER JOIN ( SELECT A.FBILLNO 应收单号,B.FID,B.FENTRYID,STUFF((SELECT ',' + CC.FIVNUMBER FROM T_IV_SALESICENTRY_LK AA LEFT JOIN T_IV_SALESICENTRY BB ON AA.FENTRYID=BB.FENTRYID LEFT JOIN T_IV_SALESIC_O CC ON BB.FID=CC.FID WHERE AA.FSTABLENAME='T_AR_RECEIVABLEENTRY' AND AA.FSID = B.FENTRYID AND AA.FSBILLID=B.FID AND CC.FIVNUMBER<>'' FOR XML PATH('') ), 1, 1, '' ) AS FIVNUMBERNEW FROM T_AR_RECEIVABLE A INNER JOIN T_AR_RECEIVABLEENTRY B ON A.FID=B.FID GROUP BY A.FBILLNO,B.FID,B.FENTRYID ) C ON A.FID=C.FID AND B.FENTRYID=C.FENTRYID WHERE ( B.F_BGP_TEXT IS NULL OR ISNULL(C.FIVNUMBERNEW,'')<>'' AND B.F_BGP_TEXT<>ISNULL(C.FIVNUMBERNEW,'')) --and len(isnull(c.fivnumbernew,''))<=50 --场景2:反写最后一张发票的发票号 SELECT * --UPDATE B SET B.F_BGP_TEXT=C.FIVNUMBER FROM T_AR_RECEIVABLE A LEFT JOIN T_AR_RECEIVABLEENTRY B ON A.FID=B.FID INNER JOIN ( SELECT T1.FSID,T1.FSBILLID,T3.FBILLNO,T4.FIVNUMBER,ROW_NUMBER() OVER(PARTITION BY T1.FSID ORDER BY T3.FDATE DESC,T1.FENTRYID DESC) RN FROM T_IV_SALESICENTRY_LK T1 LEFT JOIN T_IV_SALESICENTRY T2 ON T1.FENTRYID=T2.FENTRYID LEFT JOIN T_IV_SALESIC T3 ON T2.FID=T3.FID LEFT JOIN T_IV_SALESIC_O T4 ON T3.FID=T4.FID WHERE T3.FCANCELSTATUS<>'B' AND T3.FDOCUMENTSTATUS<>'Z' AND T1.FSTABLENAME='T_AR_RECEIVABLEENTRY' ) C ON B.FENTRYID=C.FSID WHERE C.RN=1 AND ( B.F_BGP_TEXT IS NULL OR C.FIVNUMBER<>'' AND B.F_BGP_TEXT<>C.FIVNUMBER )
默认的文本字段长度为50,在更新过程中可能出现长度不够,此时就需要把自定义字段长度改大一些。数据库和BOS同步修改,数据库修改脚本如下:
--如拼接后的发票号字段超过现有自定义字段的长度,即更新时出现截断二进制报错,则需要扩大自定义字段长度 EXEC p_AlterColumn 'T_AR_RECEIVABLEENTRY', '字段名称', 'NVARCHAR(字段长度)', 'NULL', '0100', ''; --示例 EXEC p_AlterColumn 'T_AR_RECEIVABLEENTRY', 'F_BGP_TEXT', 'NVARCHAR(200)', 'NULL', '0100', '';
推荐阅读