(FAQ)批量和部分删除凭证摘要
金蝶云社区-金蝶云社区
金蝶云社区
0人赞赏了该文章 1548次浏览 未经作者许可,禁止转载编辑于2015年03月02日 00:00:00
【问题】: (FAQ)批量和部分删除凭证摘要
版本: 7.5
模块: 总账
【答复】:
--当前此脚本修改的是所有组织的凭证摘要,故无组织条件的限制;第二份脚本文件为部分凭证的修改 --注:t_gl_voucher 该表中的凭证摘要不显示,故修改的是t_gl_voucherenter表 --------------------------------修改凭证摘要-------------------------------- --凭证摘要 select a.* from T_GL_VoucherEntry a inner join t_gl_voucher b on a.FBillID=b.fid inner join T_ORG_Company c on b.FCompanyID=c.fid inner join T_BD_Period d on b.FPeriodID=d.fid where d.fnumber between '201401' and '201411'--会计期间 and b.FDescription like '%旧字符%' --备份 select a.* into T_GL_VoucherEntry_bak from T_GL_VoucherEntry a inner join t_gl_voucher b on a.FBillID=b.fid inner join T_ORG_Company c on b.FCompanyID=c.fid inner join T_BD_Period d on b.FPeriodID=d.fid where d.fnumber between '201401' and '201411'--会计期间 and b.FAbstract like '%旧字符%' --更改数据 update T_GL_VoucherEntry set FDescription=replace(FDescription,'旧','新') where fbillid in (select a.fid from T_GL_Voucher a where a.FPeriodID in (select fid from T_BD_Period where fnumber between '201401' and '201411'))--会计期间所在年度 and FDescription like '%旧%'--旧的字符串 ---------------------------------------------------------------------------- -------------------------------修改辅助账摘要-------------------------------- --查询辅助账摘要 select a.* from T_GL_VoucherAssistRecord a where a.FDescription like '%旧字符%'--旧字符 and a.FBillID in (select fid from T_GL_Voucher a where a.FPeriodID in (select fid from T_BD_Period where fnumber between '201401' and '201411'))--会计期间) --备份将要修改的辅助账摘要 select a.* into T_GL_VoucherAssistRecord_bak from T_GL_VoucherAssistRecord a where a.FDescription like '%旧字符%'--旧字符 and a.FBillID in (select fid from T_GL_Voucher a where a.FPeriodID in (select fid from T_BD_Period where fnumber between '201401' and '201411'))--会计期间) --修改辅助账摘要 update T_GL_VoucherAssistRecord set FDescription=replace(FDescription,'旧字符','新字符') where FDescription like '%旧字符%'--旧字符 and FBillID in (select fid from T_GL_Voucher a where a.FPeriodID in (select fid from T_BD_Period where fnumber between '201401' and '201411'))--会计期间) ---------------------------------------------------------------------------- --此脚本适用于修改部分凭证摘要的问题 --------------------------------修改凭证摘要-------------------------------- --凭证摘要 select a.* from T_GL_VoucherEntry a inner join t_gl_voucher b on a.FBillID=b.fid inner join T_ORG_Company c on b.FCompanyID=c.fid inner join T_BD_Period d on b.FPeriodID=d.fid where d.fnumber between '201401' and '201412'--会计期间 and b.FDescription like '%新字符%' and c.FName_L2 = ''--公司名称 and b.fnumber=''--凭证编码 --备份 select a.* into T_GL_VoucherEntry_bak from T_GL_VoucherEntry a inner join t_gl_voucher b on a.FBillID=b.fid inner join T_ORG_Company c on b.FCompanyID=c.fid inner join T_BD_Period d on b.FPeriodID=d.fid where d.fnumber between '201401' and '201411'--会计期间 and b.FDescription like '%旧字符%' and c.FName_L2=''--公司名称 and b.fnumber=''--凭证编码 --更改数据 update T_GL_VoucherEntry set FDescription=replace(FDescription,'旧','新') where fbillid in (select a.fid from T_GL_Voucher a where fnumber=''--凭证编码 and FCompanyID=(select fid from T_ORG_Company where fname_l2='')--公司名称 and FPeriodID in (select fid from T_BD_Period where fnumber between '201401' and '201412'))--会计期间 and FDescription like '%旧%'--旧的字符串 ---------------------------------------------------------------------------- -------------------------------修改辅助账摘要-------------------------------- --查询辅助账摘要 select a.* from T_GL_VoucherAssistRecord a where a.FDescription like '%旧字符%'--旧字符 and a.FBillID in (select fid from T_GL_Voucher a where a.FCompanyID=(select fid from T_ORG_Company where fname_l2='')--组织名称 and a.fnumber=''--凭证编码 and a.FPeriodID in (select fid from T_BD_Period where fnumber between '201401' and '201412'))--会计期间 --备份将要修改的辅助账摘要 select a.* into t_gl_voucherassistrecord_bak from T_GL_VoucherAssistRecord a where a.FDescription like '%旧字符%'--旧字符 and a.FBillID in (select fid from T_GL_Voucher a where a.FCompanyID=(select fid from T_ORG_Company where fname_l2='')--组织名称 and a.fnumber=''--凭证编码 and a.FPeriodID in (select fid from T_BD_Period where fnumber between '201401' and '201412'))--会计期间 --修改辅助账摘要 update T_GL_VoucherAssistRecord set fdescription=replace(fdescription,'旧字符','新字符') where FDescription like '%旧字符%'--旧字符 and FBillID in (select fid from T_GL_Voucher a where a.FCompanyID=(select fid from T_ORG_Company where fname_l2='')--组织名称 and fnumber=''--凭证编码 and FPeriodID in (select fid from T_BD_Period where fnumber between '201401' and '201412'))--会计期间 ----------------------------------------------------------------------------