科目期初余额试算平衡，但是显示有差额

iufida

T+-总账

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

                               
登录/注册后可看大图

【解决方案】：备份好账套数据，选择账套库，执行脚本：
--检查是否有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
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
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
end