应付单新增字段,历史数据更新的SQL语句参考原创
20人赞赏了该文章
344次浏览
编辑于2024年06月21日 14:28:02
业务场景:应付单新增字段,历史单据的该字段需要进行数据库更新
如下语句适用于采购入库单/采购退料单 -- 应付单 的业务流程
且明细行一对一下推不存在分录合并的情况(如果有分录合并下推的情况,执行语句时可能出现“MERGE语句试图更新同一行”的报错信息),
且自定义字段实体在明细(如果在单据头,语句需要根据情况修改)
如下SQL语句仅供参考!!!更新前建议做全表备份!!!
语句核心:
上下游单据通过下游单据的_LK表进行关联,下游单据的_LK表名可通过BOS-单据-单据关联配置-关联表名得到。
重点需要说明下LK表中几个重要字段的含义(几乎所有关联关系表这几个字段含义都一样):
LK表中的FSBILLID代表上游单据的单据头内码,FSID代表上游单据分录行内码,
FSTABLENAME代表来源表名,FRULEID代表单据转换规则内码,FENTRYID代表下游单据的分录行内码。
示例:应付单携带上游仓库字段
--应付单明细携带期初采购入库单明细字段 MERGE INTO T_AP_PAYABLEENTRY T1 USING ( SELECT A.FBILLNO 应付单号,A.FDATE 业务日期,A.FSETACCOUNTTYPE 立账类型,B.FENTRYID,B.FSEQ 应付明细序号,E.FBILLNO 入库单号,D.FSEQ 入库明细序号,B.FSTOCKID 应付明细字段,D.FSTOCKID 入库明细字段 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 UPPER(C.FSTABLENAME)='T_STK_INITINSTOCKENTRY' LEFT JOIN T_STK_INITINSTOCKENTRY D ON C.FSBILLID=D.FID AND C.FSID=D.FENTRYID LEFT JOIN T_STK_INITINSTOCK E ON D.FID=E.FID WHERE B.FSTOCKID<>D.FSTOCKID AND B.FSTOCKID=0 --基础资料字段 --WHERE B.FSTOCKID<>D.FSTOCKID AND B.FSTOCKID='' --文本字段 --WHERE B.FSTOCKID<>D.FSTOCKID OR B.FSTOCKID is null --日期字段 ) T2 ON (T1.FENTRYID=T2.FENTRYID) WHEN MATCHED THEN UPDATE SET T1.FSTOCKID=T2.入库明细字段; --应付单明细携带采购入库单明细字段 MERGE INTO T_AP_PAYABLEENTRY T1 USING ( SELECT A.FBILLNO 应付单号,A.FDATE 业务日期,A.FSETACCOUNTTYPE 立账类型,B.FENTRYID,B.FSEQ 应付明细序号,E.FBILLNO 入库单号,D.FSEQ 入库明细序号,B.FSTOCKID 应付明细字段,D.FSTOCKID 入库明细字段 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 UPPER(C.FSTABLENAME)='T_STK_INSTOCKENTRY' LEFT JOIN T_STK_INSTOCKENTRY D ON C.FSBILLID=D.FID AND C.FSID=D.FENTRYID LEFT JOIN T_STK_INSTOCK E ON D.FID=E.FID WHERE B.FSTOCKID<>D.FSTOCKID AND B.FSTOCKID=0 --基础资料字段 --WHERE B.FSTOCKID<>D.FSTOCKID AND B.FSTOCKID='' --文本字段 --WHERE B.FSTOCKID<>D.FSTOCKID OR B.FSTOCKID is null --日期字段 ) T2 ON (T1.FENTRYID=T2.FENTRYID) WHEN MATCHED THEN UPDATE SET T1.FSTOCKID=T2.入库明细字段; --应付单明细携带期初采购退料单明细字段 MERGE INTO T_AP_PAYABLEENTRY T1 USING ( SELECT A.FBILLNO 应付单号,A.FDATE 业务日期,A.FSETACCOUNTTYPE 立账类型,B.FENTRYID,B.FSEQ 应付明细序号,E.FBILLNO 退料单号,D.FSEQ 退料明细序号,B.FSTOCKID 应付明细字段,D.FSTOCKID 退料明细字段 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 UPPER(C.FSTABLENAME)='T_PUR_INITMRSENTRY' LEFT JOIN T_PUR_INITMRSENTRY D ON C.FSBILLID=D.FID AND C.FSID=D.FENTRYID LEFT JOIN T_PUR_INITMRS E ON D.FID=E.FID WHERE B.FSTOCKID<>D.FSTOCKID AND B.FSTOCKID=0 --基础资料字段 --WHERE B.FSTOCKID<>D.FSTOCKID AND B.FSTOCKID='' --文本字段 --WHERE B.FSTOCKID<>D.FSTOCKID OR B.FSTOCKID is null --日期字段 ) T2 ON (T1.FENTRYID=T2.FENTRYID) WHEN MATCHED THEN UPDATE SET T1.FSTOCKID=T2.退料明细字段; --应付单明细携带采购退料单明细字段 MERGE INTO T_AP_PAYABLEENTRY T1 USING ( SELECT A.FBILLNO 应付单号,A.FDATE 业务日期,A.FSETACCOUNTTYPE 立账类型,B.FENTRYID,B.FSEQ 应付明细序号,E.FBILLNO 退料单号,D.FSEQ 退料明细序号,B.FSTOCKID 应付明细字段,D.FSTOCKID 退料明细字段 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 UPPER(C.FSTABLENAME)='T_PUR_MRBENTRY' LEFT JOIN T_PUR_MRBENTRY D ON C.FSBILLID=D.FID AND C.FSID=D.FENTRYID LEFT JOIN T_PUR_MRB E ON D.FID=E.FID WHERE B.FSTOCKID<>D.FSTOCKID AND B.FSTOCKID=0 --基础资料字段 --WHERE B.FSTOCKID<>D.FSTOCKID AND B.FSTOCKID='' --文本字段 --WHERE B.FSTOCKID<>D.FSTOCKID OR B.FSTOCKID is null --日期字段 ) T2 ON (T1.FENTRYID=T2.FENTRYID) WHEN MATCHED THEN UPDATE SET T1.FSTOCKID=T2.退料明细字段; --财务应付单明细携带暂估应付单明细字段 MERGE INTO T_AP_PAYABLEENTRY T1 USING ( SELECT A.FBILLNO 暂估单单号,A.FDATE 业务日期,B.FSEQ 暂估单明细序号,B.FSOURCEBILLNO 来源单号,B.FSRCROWID 来源行内码,E.FBILLNO 财务单号,D.FENTRYID,D.FSEQ 财务明细序号,B.FSTOCKID 暂估明细字段,D.FSTOCKID 财务明细字段 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.FSID AND B.FID=C.FSBILLID AND UPPER(C.FSTABLENAME)='T_AP_PAYABLEENTRY' LEFT JOIN T_AP_PAYABLEENTRY D ON C.FENTRYID=D.FENTRYID --财务单单据体 LEFT JOIN T_AP_PAYABLE E ON E.FID=D.FID --财务单单据头 WHERE A.FSETACCOUNTTYPE=2 AND B.FSTOCKID<>D.FSTOCKID AND B.FSTOCKID=0 --基础资料字段 --WHERE A.FSETACCOUNTTYPE=2 AND B.FSTOCKID<>D.FSTOCKID AND B.FSTOCKID='' --文本字段 --WHERE A.FSETACCOUNTTYPE=2 AND ( B.FSTOCKID<>D.FSTOCKID OR B.FSTOCKID is null ) --日期字段 ) T2 ON (T1.FENTRYID=T2.FENTRYID) WHEN MATCHED THEN UPDATE SET T1.FSTOCKID=T2.暂估明细字段; --暂估冲回单明细携带暂估明细字段 MERGE INTO T_AP_PAYABLEENTRY T1 USING ( SELECT A.FBILLNO 暂估冲回单号,A.FDATE 业务日期,B.FENTRYID,B.FSEQ 暂估冲回明细序号,B.FSOURCETYPE 暂估冲回单源单类型,B.FSOURCEBILLNO 源单编号,B.FSRCROWID 源单行内码,D.FBILLNO 暂估单号,C.FSEQ 暂估明细序号,B.FSTOCKID 暂估冲回明细字段,C.FSTOCKID 暂估明细字段 FROM T_AP_PAYABLE A LEFT JOIN T_AP_PAYABLEENTRY B ON A.FID=B.FID --暂估冲回单 INNER JOIN T_AP_PAYABLEENTRY C ON UPPER(B.FSOURCETYPE)='AP_PAYABLE' AND B.FSRCROWID=C.FENTRYID --暂估单 LEFT JOIN T_AP_PAYABLE D ON C.FID=D.FID WHERE A.FSETACCOUNTTYPE=2 AND A.FBYVERIFY=2 AND B.FSTOCKID<>C.FSTOCKID AND B.FSTOCKID=0 --基础资料字段 --WHERE A.FSETACCOUNTTYPE=2 AND A.FBYVERIFY=2 AND B.FSTOCKID<>C.FSTOCKID AND B.FSTOCKID='' --文本字段 --WHERE A.FSETACCOUNTTYPE=2 AND A.FBYVERIFY=2 AND ( B.FSTOCKID<>C.FSTOCKID OR B.FSTOCKID is null ) --日期字段 ) T2 ON (T1.FENTRYID=T2.FENTRYID) WHEN MATCHED THEN UPDATE SET T1.FSTOCKID=T2.暂估明细字段;
推荐阅读