【问题现象】：

科目期初余额点击试算平衡是平衡的，但是科目期初页面有差额，检查过导出期初余额核对也是平衡的

HIPFCBAAV4

【解决方案】：

备份好账套数据，选择账套库，执行脚本：

--检查是否有idparent为空的数据

if exists (select top 1 1 from AA_Account where depth>1 and ISNULL(idParent,'‘)=’')

begin

declare @accountlevel nvarchar(50)

select @accountlevel=value from EAP_AccInformation where Name=‘AccountCodingLevelItemLength’

declare @depth int

select @depth=max(Value_index) from GL_Split(@accountlevel,‘-’)

if @depth<=15

begin

declare @len1 int

declare @len2 int

declare @len3 int

declare @len4 int

declare @len5 int

declare @len6 int

declare @len7 int

declare @len8 int

declare @len9 int

declare @len10 int

declare @len11 int

declare @len12 int

declare @len13 int

declare @len14 int

declare @len15 int

select @len1=Split_value from GL_Split(@accountlevel,‘-’) where Value_index=1

select @len2=Split_value from GL_Split(@accountlevel,‘-’) where Value_index=2

select @len3=Split_value from GL_Split(@accountlevel,‘-’) where Value_index=3

select @len4=Split_value from GL_Split(@accountlevel,‘-’) where Value_index=4

select @len5=Split_value from GL_Split(@accountlevel,‘-’) where Value_index=5

select @len6=Split_value from GL_Split(@accountlevel,‘-’) where Value_index=6

select @len7=Split_value from GL_Split(@accountlevel,‘-’) where Value_index=7

select @len8=Split_value from GL_Split(@accountlevel,‘-’) where Value_index=8

select @len9=Split_value from GL_Split(@accountlevel,‘-’) where Value_index=9

select @len10=Split_value from GL_Split(@accountlevel,‘-’) where Value_index=10

select @len11=Split_value from GL_Split(@accountlevel,‘-’) where Value_index=11

select @len12=Split_value from GL_Split(@accountlevel,‘-’) where Value_index=12

select @len13=Split_value from GL_Split(@accountlevel,‘-’) where Value_index=13

select @len14=Split_value from GL_Split(@accountlevel,‘-’) where Value_index=14

select @len15=Split_value from GL_Split(@accountlevel,‘-’) where Value_index=15

declare @year nvarchar(10)

declare cur cursor for

select distinct accountingyear from AA_Account

open cur

fetch next from cur into @year

while @@FETCH_STATUS=0

begin

---修复某年度的idParent值

if @depth>=2

begin

update aa_account set idparent=(select id from (select id,substring(code,1,@len1) code,accountingyear from aa_account where code in

(select substring(code,1,@len1) from aa_account where LEN(code)=@len1+@len2 ) and accountingyear=@year

) as a where aa_account.depth=2 and aa_account.accountingyear=@year and substring(aa_account.code,1,@len1)=a.code)

where depth=2 and accountingyear=@year

end

if @depth>=3

begin

update aa_account set idparent=(select id from (select id,substring(code,1,@len1+@len2) code,accountingyear from aa_account where code in

(select substring(code,1,@len1+@len2) from aa_account where LEN(code)=@len1+@len2+@len3) and accountingyear=@year

) as a where aa_account.depth=3 and aa_account.accountingyear=@year and substring(aa_account.code,1,@len1+@len2)=a.code)

where depth=3 and accountingyear=@year

end

if @depth>=4

begin

update aa_account set idparent=(select id from (select id,substring(code,1,@len1+@len2+@len3) code,accountingyear from aa_account where code in

(select substring(code,1,@len1+@len2+@len3) from aa_account where LEN(code)=@len1+@len2+@len3+@len4) and accountingyear=@year

) as a where aa_account.depth=4 and aa_account.accountingyear=@year and substring(aa_account.code,1,@len1+@len2+@len3)=a.code)

where depth=4 and accountingyear=@year

end

if @depth>=5

begin

update aa_account set idparent=(select id from (select id,substring(code,1,@len1+@len2+@len3+@len4) code,accountingyear from aa_account where code in

(select substring(code,1,@len1+@len2+@len3+@len4) from aa_account where LEN(code)=@len1+@len2+@len3+@len4+@len5) and accountingyear=@year

) as a where aa_account.depth=5 and aa_account.accountingyear=@year and substring(aa_account.code,1,@len1+@len2+@len3+@len4)=a.code)

where depth=5 and accountingyear=@year

end

if @depth>=6

begin

update aa_account set idparent=(select id from (select id,substring(code,1,@len1+@len2+@len3+@len4+@len5) code,accountingyear from aa_account where code in

(select substring(code,1,@len1+@len2+@len3+@len4+@len5) from aa_account where LEN(code)=@len1+@len2+@len3+@len4+@len5+@len6) and accountingyear=@year

) as a where aa_account.depth=6 and aa_account.accountingyear=@year and substring(aa_account.code,1,@len1+@len2+@len3+@len4+@len5)=a.code)

where depth=6 and accountingyear=@year

end

if @depth>=7

begin

update aa_account set idparent=(select id from (select id,substring(code,1,@len1+@len2+@len3+@len4+@len5+@len6) code,accountingyear from aa_account where code in

(select substring(code,1,@len1+@len2+@len3+@len4+@len5+@len6) from aa_account where LEN(code)=@len1+@len2+@len3+@len4+@len5+@len6+@len7) and accountingyear=@year

) as a where aa_account.depth=7 and aa_account.accountingyear=@year and substring(aa_account.code,1,@len1+@len2+@len3+@len4+@len5+@len6)=a.code)

where depth=7 and accountingyear=@year

end

if @depth>=8

begin

update aa_account set idparent=(select id from (select id,substring(code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7) code,accountingyear from aa_account where code in

(select substring(code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7) from aa_account where LEN(code)=@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8) and accountingyear=@year

) as a where aa_account.depth=8 and aa_account.accountingyear=@year and substring(aa_account.code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7)=a.code)

where depth=8 and accountingyear=@year

end

if @depth>=9

begin

update aa_account set idparent=(select id from (select id,substring(code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8) code,accountingyear from aa_account where code in

(select substring(code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8) from aa_account where LEN(code)=@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9) and accountingyear=@year

) as a where aa_account.depth=9 and aa_account.accountingyear=@year and substring(aa_account.code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8)=a.code)

where depth=9 and accountingyear=@year

end

if @depth>=10

begin

update aa_account set idparent=(select id from (select id,substring(code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9) code,accountingyear from aa_account where code in

(select substring(code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9) from aa_account where LEN(code)=@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9+@len10) and accountingyear=@year

) as a where aa_account.depth=10 and aa_account.accountingyear=@year and substring(aa_account.code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9)=a.code)

where depth=10 and accountingyear=@year

end

if @depth>=11

begin

update aa_account set idparent=(select id from (select id,substring(code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9+@len10) code,accountingyear from aa_account where code in

(select substring(code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9+@len10) from aa_account where LEN(code)=@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9+@len10+@len11) and accountingyear=@year

) as a where aa_account.depth=11 and aa_account.accountingyear=@year and substring(aa_account.code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9+@len10)=a.code)

where depth=11 and accountingyear=@year

end

if @depth>=12

begin

update aa_account set idparent=(select id from (select id,substring(code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9+@len10+@len11) code,accountingyear from aa_account where code in

(select substring(code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9+@len10+@len11) from aa_account where LEN(code)=@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9+@len10+@len11+@len12) and accountingyear=@year

) as a where aa_account.depth=12 and aa_account.accountingyear=@year and substring(aa_account.code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9+@len10+@len11)=a.code)

where depth=12 and accountingyear=@year

end

if @depth>=13

begin

update aa_account set idparent=(select id from (select id,substring(code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9+@len10+@len11+@len12) code,accountingyear from aa_account where code in

(select substring(code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9+@len10+@len11+@len12) from aa_account where LEN(code)=@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9+@len10+@len11+@len12+@len13) and accountingyear=@year

) as a where aa_account.depth=13 and aa_account.accountingyear=@year and substring(aa_account.code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9+@len10+@len11+@len12)=a.code)

where depth=13 and accountingyear=@year

end

if @depth>=14

begin

update aa_account set idparent=(select id from (select id,substring(code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9+@len10+@len11+@len12+@len13) code,accountingyear from aa_account where code in

(select substring(code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9+@len10+@len11+@len12+@len13) from aa_account where LEN(code)=@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9+@len10+@len11+@len12+@len13+@len14) and accountingyear=@year

) as a where aa_account.depth=14 and aa_account.accountingyear=@year and substring(aa_account.code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9+@len10+@len11+@len12+@len13)=a.code)

where depth=14 and accountingyear=@year

end

if @depth>=15

begin

update aa_account set idparent=(select id from (select id,substring(code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9+@len10+@len11+@len12+@len13+@len14) code,accountingyear from aa_account where code in

(select substring(code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9+@len10+@len11+@len12+@len13+@len14) from aa_account where LEN(code)=@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9+@len10+@len11+@len12+@len13+@len14+@len15) and accountingyear=@year

) as a where aa_account.depth=15 and aa_account.accountingyear=@year and substring(aa_account.code,1,@len1+@len2+@len3+@len4+@len5+@len6+@len7+@len8+@len9+@len10+@len11+@len12+@len13+@len14)=a.code)

where depth=15 and accountingyear=@year

end

--修复isendnode

declare m_cursor cursor scroll for

select id from AA_Account where accountingyear=@year

open m_cursor

declare @id int

fetch next from m_cursor into @id

while @@FETCH_STATUS=0

begin

if exists (select top 1 1 from AA_Account where idParent=@id)

update AA_Account set isEndNode=0 where id=@id

else

update AA_Account set isEndNode=1 where id=@id

fetch next from m_cursor into @id

end

close m_cursor

deallocate m_cursor

---前提是科目的idParent有正确的值

declare @currentyear int

declare @MaxDepthValue int

declare @CurrentDetphValue int

set @currentyear=@year

set @maxDepthValue=(select MAX(depth) from AA_Account where accountingyear=@currentyear )

set @CurrentDetphValue=1

while(@CurrentDetphValue<=@MaxDepthValue)

begin

if(@CurrentDetphValue=1)

begin

update AA_Account set inId = CONVERT(varchar(36),id) where depth=1 and accountingyear=@currentyear

end

else if (@CurrentDetphValue=2)

begin

update AA_Account set inId =CONVERT(varchar(36),idParent) + ‘_’ + CONVERT(varchar(36),id) where depth=2 and accountingyear=@currentyear

end

else if (@CurrentDetphValue >2)

begin

update AA_Account set inId =(select distinct CONVERT(varchar(max),inid) from AA_Account A where A.id=AA_Account.idParent and A.depth=AA_Account.depth-1 ) + ‘_’ + CONVERT(varchar(36),id)

from AA_Account where depth=@CurrentDetphValue and accountingyear=@currentyear

end

set @CurrentDetphValue=@CurrentDetphValue+1

end

fetch next from cur into @year

end

close cur

deallocate cur

truncate table AA_ParentAccount

insert into AA_ParentAccount (idParent,idSon,Depth,isEndNode)

select p.id as idParent,s.id as idSon,p.depth-1 as Depth,p.IsEndNode as isEndNode from AA_Account p

left join AA_Account s

on s.code like p.code+‘%’ and s.isEndNode =1 and s.accountingyear=p.accountingyear

end

作者：熊嘉颖 |