-- 查询集团科目表主键 1001A510000000000EQO select pk_accchart,*from bd_accchart where code='AJ'; -- 查询AJJT0101新建立的组织级科目表主键 1001B310000000007HEW select pk_accchart,*from bd_accchart where code='AJJT0101'; -- 查询AJJT0101当前对应财务核算账簿的科目表 1001A510000000000EQO select pk_curraccchart,pk_accountingbook from org_accountingbook where code='AJJT0101-0001'; /*逐个更新财务帐簿的科目表(由集团级改为组织级)*/ update org_accountingbook set pk_curraccchart=(select pk_accchart from bd_accchart where code='AJJT0101') where pk_relorg=(select pk_org from org_orgs where code='AJJT0101');
/*创建视图case_accasoaVo体现集团科目体系和财务组织(AJJT0101)的pk_accasoa对应关系。*/ select a.pk_accasoa as old_accasoa,b.pk_accasoa as new_accasoa from bd_accasoa a leftjoin bd_accasoa b on a.pk_account=b.pk_account where a.pk_accchart =(select pk_accchart from bd_accchart where code='AJ') -- 固定集团 and b.pk_accchart=(select pk_accchart from bd_accchart where code='AJJT0101') -- 此次调整的财务组织
1 2
/*修改凭证明细表中科目表主键。*/ update gl_detail set pk_accchart=( select pk_accchart from bd_accchart where code='AJJT0101') where pk_org=(select pk_org from org_orgs where code='AJJT0101' )
1 2
/*修改凭证明细表中会计科目主键。*/ update gl_detail set pk_accasoa=(select new_accasoa from case_accasoaVo where old_accasoa=pk_accasoa) where pk_org=(select pk_org from org_orgs where code='AJJT0101' );
1 2
/*修改凭证余额表中会计科目主键。*/ update gl_balance set pk_accasoa=(select new_accasoa from case_accasoaVo where old_accasoa=pk_accasoa) where pk_org=(select pk_org from org_orgs where code='AJJT0101' );
/* 创建存储过程 gldetail_oppositesubj_update ,更新凭证明细表中的对方科目 */ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: chendi -- Create date: 2022-11-04 -- Description: XX集团凭证明细表对方科目更新 -- ============================================= CREATEPROCEDURE [dbo].[gldetail_oppositesubj_update ] ( @p_org_code varchar(100) ) AS BEGIN SET NOCOUNT ON; if exists (select old_accasoa,new_accasoa from case_accasoaVo) begin --声明变量 declare@old_accasoa varchar(max) declare@new_accasoa varchar(max) declare@pk_org varchar(max) set@pk_org = (select pk_org from org_orgs where code=@p_org_code ); --将SQL语句设置为游标,游标名new_cursor declare new_cursor cursorforselect old_accasoa,new_accasoa from case_accasoaVo; open new_cursor
--获取查询数据赋值给变量 fetch next from new_cursor into@old_accasoa,@new_accasoa --若检索到数据继续执行 while @@FETCH_STATUS=0 BEGIN print @old_accasoa +'_'+@new_accasoa+'_'+@pk_org update gl_detail set oppositesubj=REPLACE(oppositesubj,@old_accasoa,@new_accasoa) where pk_org=@pk_org; --获取下一条数据并赋值给变量 fetch next from new_cursor into@old_accasoa,@new_accasoa END close new_cursor deallocate new_cursor END END GO