应收单新增字段,历史数据更新的SQL语句参考原创
30人赞赏了该文章
538次浏览
编辑于2024年08月12日 16:12:42
业务场景:应收单新增字段,历史单据的该字段需要进行数据库更新,下游单据字段为空才更新。
如下语句适用于销售合同 -- 销售单据 -- 应收单 -- 销售发票的业务流程(明细行一对一下推不存在分录合并的情况),
且自定义字段实体在明细(如果在单据头,语句需要根据情况修改),
且数据库是SQL SERVER(如果是ORACLE也需要根据其语法做修改)
如下SQL语句仅供参考!!!更新前建议做全表备份!!!
相关参考:
语句核心:
上下游单据通过下游单据的_LK表进行关联,下游单据的_LK表名可通过BOS-单据-单据关联配置-关联表名得到。
重点需要说明下LK表中几个重要字段的含义(几乎所有关联关系表这几个字段含义都一样):
LK表中的FSBILLID代表上游单据的单据头内码,FSID代表上游单据分录行内码,
FSTABLENAME代表来源表名,FRULEID代表单据转换规则内码,FENTRYID代表下游单据的分录行内码。
--将如下语句中的F_ORA_TEXT替换为自定义字段的字段名,更新时执行update语句即可 --销售合同明细字段携带单据头字段 select a.fbillno 单据编号,a.fdate 业务日期,a.F_ORA_TEXT 单据头字段,b.F_ORA_TEXT 明细字段 --update b set b.F_ORA_TEXT=a.F_ORA_TEXT from T_CRM_CONTRACT a left join T_CRM_CONTRACTENTRY b on a.fid=b.fid where a.F_ORA_TEXT<>b.F_ORA_TEXT --销售订单明细字段携带单据头字段 select a.fbillno 单据编号,a.fdate 业务日期,a.F_ORA_TEXT 单据头字段,b.F_ORA_TEXT 明细字段 --update b set b.F_ORA_TEXT=a.F_ORA_TEXT from T_SAL_ORDER a left join T_SAL_ORDERENTRY b on a.fid=b.fid where a.F_ORA_TEXT<>b.F_ORA_TEXT --and not exists (select 1 from T_SAL_ORDERENTRY_LK where FENTRYID=b.fentryid) --筛选不存在上游单据的销售订单 --销售订单明细携带销售合同明细字段 select a.fbillno 销售合同单据编号,b.fentryid 合同明细分录内码,b.fseq 合同明细序号,e.fbillno 销售订单单据编号,d.fseq 订单分录序号,b.F_ORA_TEXT 合同明细字段,d.F_ORA_TEXT 订单明细字段 --update d set d.F_ORA_TEXT=b.F_ORA_TEXT from T_CRM_CONTRACT a left join T_CRM_CONTRACTENTRY b on a.fid=b.fid inner join T_SAL_ORDERENTRY_LK c on b.fentryid=c.fsid and b.fid=c.fsbillid and upper(c.FSTABLENAME)='T_CRM_CONTRACTENTRY' left join T_SAL_ORDERENTRY d on c.fentryid=d.fentryid left join T_SAL_ORDER e on d.fid=e.fid where b.F_ORA_TEXT<>d.F_ORA_TEXT --销售出库单明细字段携带订单明细字段 select a.fbillno 销售出库单据编号,b.fentryid 出库明细分录内码,b.fseq 出库明细序号,e.fbillno 销售订单单据编号,d.fseq 订单分录序号,b.F_ORA_TEXT 出库明细字段,d.F_ORA_TEXT 订单明细字段 --update b set b.F_ORA_TEXT=d.F_ORA_TEXT from T_SAL_OUTSTOCK a left join T_SAL_OUTSTOCKENTRY b on a.fid=b.fid inner join T_SAL_OUTSTOCKENTRY_LK c on b.fentryid=c.fentryid and upper(c.fstablename)='T_SAL_ORDERENTRY' left join T_SAL_ORDERENTRY d on c.FSBILLID=d.fid and c.fsid=d.fentryid left join T_SAL_ORDER e on d.fid=e.fid where b.F_ORA_TEXT<>d.F_ORA_TEXT and b.F_ORA_TEXT='' or b.F_ORA_TEXT is null --发货通知单明细字段携带订单明细字段 select a.fbillno 发货通知单据编号,b.fentryid 发货通知明细分录内码,b.fseq 发货通知明细序号,e.fbillno 销售订单单据编号,d.fseq 订单分录序号,b.F_ORA_TEXT 发货通知明细字段,d.F_ORA_TEXT 订单明细字段 --update b set b.F_ORA_TEXT=d.F_ORA_TEXT from T_SAL_DELIVERYNOTICE a left join T_SAL_DELIVERYNOTICEENTRY b on a.fid=b.fid inner join T_SAL_DELIVERYNOTICEENTRY_LK c on b.fentryid=c.fentryid and upper(c.fstablename)='T_SAL_ORDERENTRY' left join T_SAL_ORDERENTRY d on c.FSBILLID=d.fid and c.fsid=d.fentryid left join T_SAL_ORDER e on d.fid=e.fid where b.F_ORA_TEXT<>d.F_ORA_TEXT and b.F_ORA_TEXT='' or b.F_ORA_TEXT is null --销售出库单明细字段携带发货通知明细字段 select a.fbillno 销售出库单据编号,b.fentryid 出库明细分录内码,b.fseq 出库分录序号,e.fbillno 发货通知单据编号,d.fseq 发货通知明细序号,b.F_ORA_TEXT 出库明细字段,d.F_ORA_TEXT 发货通知明细字段 --update b set b.F_ORA_TEXT=d.F_ORA_TEXT from T_SAL_OUTSTOCK a left join T_SAL_OUTSTOCKENTRY b on a.fid=b.fid inner join T_SAL_OUTSTOCKENTRY_LK c on b.fentryid=c.fentryid and upper(c.fstablename)='T_SAL_DELIVERYNOTICEENTRY' left join T_SAL_DELIVERYNOTICEENTRY d on c.FSBILLID=d.fid and c.fsid=d.fentryid left join T_SAL_DELIVERYNOTICE e on d.fid=e.fid where b.F_ORA_TEXT<>d.F_ORA_TEXT and b.F_ORA_TEXT='' or b.F_ORA_TEXT is null --销售退货单明细字段携带销售出库单明细字段 select a.fbillno 销售退货单据编号,b.fentryid 退货明细分录内码,b.fseq 退货明细序号,e.fbillno 销售出库单据编号,d.fseq 出库分录序号,b.F_ORA_TEXT 退货明细字段,d.F_ORA_TEXT 出库明细字段 --update b set b.F_ORA_TEXT=d.F_ORA_TEXT from T_SAL_RETURNSTOCK a left join T_SAL_RETURNSTOCKENTRY b on a.fid=b.fid inner join T_SAL_RETURNSTOCKENTRY_LK c on b.fentryid=c.fentryid and upper(c.fstablename)='T_SAL_OUTSTOCKENTRY' left join T_SAL_OUTSTOCKENTRY d on c.FSBILLID=d.fid and c.fsid=d.fentryid left join T_SAL_OUTSTOCK e on d.fid=e.fid where b.F_ORA_TEXT<>d.F_ORA_TEXT and b.F_ORA_TEXT='' or b.F_ORA_TEXT is null --退货通知单明细字段携带销售出库单明细字段 select a.fbillno 退货通知单据编号,b.fentryid 退货通知明细分录内码,b.fseq 退货通知明细序号,e.fbillno 销售出库单据编号,d.fseq 出库分录序号, b.F_ORA_TEXT 退货通知明细字段,d.F_ORA_TEXT 出库明细字段 --update b set b.F_ORA_TEXT=d.F_ORA_TEXT from T_SAL_RETURNNOTICE a left join T_SAL_RETURNNOTICEENTRY b on a.fid=b.fid inner join T_SAL_RETURNNOTICEENTRY_LK c on b.fentryid=c.fentryid and upper(c.fstablename)='T_SAL_OUTSTOCKENTRY' left join T_SAL_OUTSTOCKENTRY d on c.FSBILLID=d.fid and c.fsid=d.fentryid left join T_SAL_OUTSTOCK e on d.fid=e.fid where b.F_ORA_TEXT<>d.F_ORA_TEXT and b.F_ORA_TEXT='' or b.F_ORA_TEXT is null --销售退货单明细字段携带退货通知单明细字段 select a.fbillno 销售退货单据编号,b.fentryid 退货明细分录内码,b.fseq 退货明细序号,e.fbillno 退货通知单据编号,d.fseq 退货通知明细序号, b.F_ORA_TEXT 退货明细字段,d.F_ORA_TEXT 退货通知明细字段 --update b set b.F_ORA_TEXT=d.F_ORA_TEXT from T_SAL_RETURNSTOCK a left join T_SAL_RETURNSTOCKENTRY b on a.fid=b.fid inner join T_SAL_RETURNSTOCKENTRY_LK c on b.fentryid=c.fentryid and upper(c.fstablename)='T_SAL_RETURNNOTICEENTRY' left join T_SAL_RETURNNOTICEENTRY d on c.FSBILLID=d.fid and c.fsid=d.fentryid left join T_SAL_RETURNNOTICE e on d.fid=e.fid where b.F_ORA_TEXT<>d.F_ORA_TEXT and b.F_ORA_TEXT='' or b.F_ORA_TEXT is null --应收单明细字段携带销售出库单明细字段 select a.fbillno 应收单据编号,a.FDATE 业务日期,a.FSETACCOUNTTYPE 立账类型,b.fentryid 应收明细内码,b.fseq 应收明细序号,e.fbillno 销售出库单据编号,d.fseq 出库明细序号,b.F_ORA_TEXT 应收明细字段,d.F_ORA_TEXT 销售出库明细字段 --update b set b.F_ORA_TEXT=d.F_ORA_TEXT 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 upper(c.fstablename)='T_SAL_OUTSTOCKENTRY' left join T_SAL_OUTSTOCKENTRY d on c.FSBILLID=d.fid and c.fsid=d.fentryid left join T_SAL_OUTSTOCK e on d.fid=e.fid where b.F_ORA_TEXT<>d.F_ORA_TEXT and b.F_ORA_TEXT='' or b.F_ORA_TEXT is null --应收单明细字段携带销售退货单明细字段 select a.fbillno 应收单据编号,a.FDATE 业务日期,a.FSETACCOUNTTYPE 立账类型,b.fentryid 应收明细内码,b.fseq 应收明细序号,e.fbillno 销售退货单据编号,d.fseq 退货明细序号,b.F_ORA_TEXT 应收明细字段,d.F_ORA_TEXT 退货明细字段 --update b set b.F_ORA_TEXT=d.F_ORA_TEXT 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 upper(c.fstablename)='T_SAL_RETURNSTOCKENTRY' left join T_SAL_RETURNSTOCKENTRY d on c.FSBILLID=d.fid and c.fsid=d.fentryid left join T_SAL_RETURNSTOCK e on d.fid=e.fid where b.F_ORA_TEXT<>d.F_ORA_TEXT and b.F_ORA_TEXT='' or b.F_ORA_TEXT is null --财务应收单携带暂估应收单数据 select a.fbillno 暂估单单据编号,a.FDATE 业务日期,b.fentryid 暂估单行内码,b.fseq 暂估明细序号,b.FSOURCEBILLNO 来源单号,b.FSRCROWID 来源行内码,e.fbillno 财务单号,d.fseq 财务单明细序号,b.F_ORA_TEXT 暂估单字段,d.F_ORA_TEXT 财务单字段 --update d set d.F_ORA_TEXT=b.F_ORA_TEXT 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.fsid and b.fid=c.fsbillid and upper(c.fstablename)='T_AR_RECEIVABLEENTRY' left join T_AR_RECEIVABLEENTRY d on c.fentryid=d.fentryid --财务单单据体 left join T_AR_RECEIVABLE e on e.fid=d.fid --财务单单据头 where a.fsetaccounttype=2 and ( b.F_ORA_TEXT<>d.F_ORA_TEXT and d.F_ORA_TEXT='' or d.F_ORA_TEXT is null ) --暂估冲回单明细字段携带暂估应收单明细字段 select a.FBILLNO 暂估冲回单据编号,a.FDATE 业务日期,d.FBILLNO 暂估单号,b.FSOURCETYPE 暂估冲回单源单类型,b.fsourcebillno 源单编号,b.fsrcrowid 源单行内码,b.fseq 冲回单明细序号,c.fseq 暂估单明细序号,b.F_ORA_TEXT 暂估冲回单明细字段,c.F_ORA_TEXT 暂估单明细字段,b.* --update b set b.F_ORA_TEXT=c.F_ORA_TEXT from T_AR_RECEIVABLE a left join T_AR_RECEIVABLEENTRY b on a.FID=b.FID --暂估冲回单 inner join T_AR_RECEIVABLEENTRY c on upper(b.FSOURCETYPE)='AR_RECEIVABLE' and b.FSRCROWID=c.FENTRYID --暂估单 left join T_AR_RECEIVABLE d on c.FID=d.fid where a.FSETACCOUNTTYPE=2 and a.fbyverify=2 and ( b.F_ORA_TEXT<>c.F_ORA_TEXT and c.F_ORA_TEXT='' or c.F_ORA_TEXT is null ) --销售普通发票明细携带应收单明细字段 select a.fbillno 销售发票单据编号,b.fseq 发票明细序号,e.fbillno 应收单号,d.fseq 应收单明细序号,b.F_ORA_TEXT 发票明细字段,d.F_ORA_TEXT 应收单明细字段 --update b set b.F_ORA_TEXT=d.F_ORA_TEXT from T_IV_SALESIC a left join T_IV_SALESICENTRY b on a.fid=b.fid inner join T_IV_SALESICENTRY_LK c on c.fentryid=b.fentryid and FSTABLENAME='t_AR_receivableEntry' left join T_AR_RECEIVABLEENTRY d on c.fsid=d.fentryid and c.fsbillid=d.fid left join T_AR_RECEIVABLE e on d.fid=e.fid where a.FFORMID='IV_SALESOC' and ( b.F_ORA_TEXT<>d.F_ORA_TEXT and b.F_ORA_TEXT='' or b.F_ORA_TEXT is null ) --销售增值税专用发票携带应收单明细字段 select a.fbillno 销售发票单据编号,b.fseq 发票明细序号,e.fbillno 应收单号,d.fseq 应收单明细序号,b.F_ORA_TEXT 发票明细字段,d.F_ORA_TEXT 应收单明细字段 --update b set b.F_ORA_TEXT=d.F_ORA_TEXT from T_IV_SALESIC a left join T_IV_SALESICENTRY b on a.fid=b.fid inner join T_IV_SALESICENTRY_LK c on c.fentryid=b.fentryid and FSTABLENAME='t_AR_receivableEntry' left join T_AR_RECEIVABLEENTRY d on c.fsid=d.fentryid and c.fsbillid=d.fid left join T_AR_RECEIVABLE e on d.fid=e.fid where a.FFORMID='IV_SALESIC' and ( b.F_ORA_TEXT<>d.F_ORA_TEXT and b.F_ORA_TEXT='' or b.F_ORA_TEXT is null )
财务应收单明细携带暂估单单据头字段
示例:财务明细源单编号携带暂估单单据编号字段(分录一对一下推的情况)
select a.fbillno 暂估单单据编号,a.FDATE 业务日期,b.fentryid 暂估单行内码,b.fseq 暂估明细序号,e.fbillno 财务单号,d.fseq 财务单明细序号,d.FSOURCEBILLNO 财务明细来源单号,b.FSRCROWID 来源行内码 --update d set d.FSOURCEBILLNO=a.fbillno 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.fsid and b.fid=c.fsbillid and upper(c.fstablename)='T_AR_RECEIVABLEENTRY' left join T_AR_RECEIVABLEENTRY d on c.fentryid=d.fentryid --财务单单据体 left join T_AR_RECEIVABLE e on e.fid=d.fid --财务单单据头 where a.fsetaccounttype=2 and d.FSOURCEBILLNO<>a.fbillno
推荐阅读