修改应付单立账类型原创
6人赞赏了该文章
248次浏览
编辑于2024年07月04日 11:23:00
由于业务需求变更,启用了暂估模式,想要修改单据立账类型,业务改为暂估或者业务改为财务,数据库修改条件:
应收或者应付款模块需要反结账到修改的单据最早期间,其他模块不需要反结账
业务改为暂估需要满足:应付单没有下游单据,没有开票和收款核销记录。
业务改为财务需要满足:单据没有下推过发票,无开票核销记录。
可参考以下语句,注意修改前进行全表备份!!!
应付单:
1、业务改为暂估 select * --UPDATE A SET A.FSETACCOUNTTYPE=2 from T_AP_PAYABLE a where a.FSETACCOUNTTYPE=1 and a.FOPENSTATUS in ('','A') AND A.FWRITTENOFFSTATUS in ('','A') AND not exists (select 1 from T_AP_PAYBILLSRCENTRY_LK where FSTABLENAME='T_AP_PAYABLEPLAN' and FSBILLID=a.fid) and not exists (select 1 from T_IV_PURCHASEICentry_lk where FSTABLENAME='T_AP_PAYABLEENTRY' and FSBILLID=a.fid) and not exists (select 1 from T_IV_PUREXPINVENTRY_LK where FSTABLENAME='T_AP_PAYABLEENTRY' and FSBILLID=a.fid) and not exists (select 1 from T_CN_PAYAPPLYENTRY_LK where FSTABLENAME='T_AP_PAYABLEENTRY' and FSBILLID=a.fid) 2、业务改为财务 select * --UPDATE A SET A.FSETACCOUNTTYPE=3 from T_AP_PAYABLE a where a.FSETACCOUNTTYPE=1 and not exists (select 1 from T_IV_PURCHASEICentry_lk where FSTABLENAME='T_AP_PAYABLEENTRY' and FSBILLID=a.fid) and not exists (select 1 from T_IV_PUREXPINVENTRY_LK where FSTABLENAME='T_AP_PAYABLEENTRY' and FSBILLID=a.fid) and not exists (select 1 from T_AP_BILLINGMATCHLOGENTRY where FSOURCEFROMID='AP_PAYABLE' and FSRCBILLID=a.fid and a.FBILLNO=FSRCBILLNO)
应收单:
1、业务改为暂估 select * --UPDATE A SET A.FSETACCOUNTTYPE=2 from T_AR_RECEIVABLE a where a.FSETACCOUNTTYPE=1 and a.FOPENSTATUS in ('','A') AND A.FWRITTENOFFSTATUS in ('','A') AND not exists (select 1 from T_AR_RECEIVEBILLSRCENTRY_LK where FSTABLENAME='t_AR_receivablePlan' and FSBILLID=a.fid) and not exists (select 1 from T_IV_SALESICENTRY_LK where FSTABLENAME='t_AR_receivableEntry' and FSBILLID=a.fid) and not exists (select 1 from T_IV_SALEEXINVENTRY_LK where FSTABLENAME='t_AR_receivableEntry' and FSBILLID=a.fid) and not exists (select 1 from T_CN_PAYAPPLYENTRY_LK where FSTABLENAME='t_AR_receivablePlan' and FSBILLID=a.fid) 2、业务改为财务 select * --UPDATE A SET A.FSETACCOUNTTYPE=3 from T_AR_RECEIVABLE a where a.FSETACCOUNTTYPE=1 and not exists (select 1 from T_IV_SALESICENTRY_LK where FSTABLENAME='t_AR_receivableEntry' and FSBILLID=a.fid) and not exists (select 1 from T_IV_SALEEXINVENTRY_LK where FSTABLENAME='t_AR_receivableEntry' and FSBILLID=a.fid) and not exists (select 1 from T_AR_BILLINGMATCHLOGENTRY where FSOURCEFROMID='AR_receivable' and FSRCBILLID=a.fid and a.FBILLNO=FSRCBILLNO)
推荐阅读