博主 用友畅捷通T+12月结账提示科目对照存在问题，请联系代理商或者总部处理后再年结
[全站通告] 想快速节省您的时间并可接受付费的朋友，可扫右边二维码加博主微信-非诚勿扰！
【问题现象】：
财务结账提示科目对照存在问题，请联系代理商或者总部处理后再年结
【解决方案】：
请备份好账套数据，选择账套库，执行脚本
if (select value from EAP_AccInformation where name=’MultiAccountingOrgan’)=1 and (select COUNT( distinct accountingyear) from AA_Account )>1
begin
delete from AA_AccountAssociation
declare @IdMarketingOrgan int
declare cur2 cursor for
select distinct IdMarketingOrgan from AA_Account order by IdMarketingOrgan
open cur2
fetch next from cur2 into @IdMarketingOrgan
while @@FETCH_STATUS=0
begin
print @IdMarketingOrgan
set nocount on
DECLARE @CurrentYear INT –当年
DECLARE @NewAccountId int–本年科目ID
DECLARE @PreAccountId int–上年科目ID
DECLARE @NewAccountCode NVARCHAR(50)–本年科目编码
DECLARE @PreAccountCode NVARCHAR(50)–上年科目编码
DECLARE @NewisEndNode tinyint–本年科目是否末级
DECLARE @PreisEndNode tinyint–上年科目是否末级
DECLARE @PreYear INT –上年
declare cur cursor for
select a.accountingyear currentaccountingyear,b.accountingyear preaccountingyear from (
select distinct top 100 accountingyear from AA_Account where IdMarketingOrgan=@IdMarketingOrgan order by
accountingyear desc) a left join (
select distinct top 100 accountingyear from AA_Account where IdMarketingOrgan=@IdMarketingOrgan order by
accountingyear desc) b on a.accountingyear >=b.accountingyear
open cur
fetch next from cur into @CurrentYear,@PreYear
while @@FETCH_STATUS=0
begin
–年度一致直接插入
if @CurrentYear<>@PreYear
begin
–科目完全一致且都是末级
INSERT INTO AA_AccountAssociation(currentaccountingyear,idcurrentaccountDTO,idpreaccountDTO,preaccountingyear)
select @CurrentYear,a.id,b.id,@PreYear from aa_account a left join AA_Account b on a.code=b.code where a.accountingyear=@CurrentYear and b.accountingyear =@PreYear and
a.isEndNode=1 and b.isEndNode=1 and a.IdMarketingOrgan=@IdMarketingOrgan and b.IdMarketingOrgan=@IdMarketingOrgan
–科目一致其中一个年度不是末级，对应下级科目第一个
declare curNoEnd cursor for
select a.id,b.id,a.isEndNode,b.isEndNode,a.code,b.code from aa_account a left join AA_Account b on a.code=b.code where a.accountingyear=@CurrentYear and b.accountingyear =@PreYear and
((a.isEndNode=1 and b.isEndNode=0) or (a.isEndNode=0 and b.isEndNode=1)) and a.IdMarketingOrgan=@IdMarketingOrgan and b.IdMarketingOrgan=@IdMarketingOrgan
open curNoEnd
fetch next from curNoEnd into @NewAccountId,@PreAccountId,@NewisEndNode,@PreisEndNode,@NewAccountCode,@PreAccountCode
while @@FETCH_STATUS=0
begin
if @NewisEndNode =0
select top 1 @NewAccountId=id from AA_Account where accountingyear=@CurrentYear and code like ”+@NewAccountCode+’%’ and isEndNode=1 and IdMarketingOrgan=@IdMarketingOrgan order by code
if @PreisEndNode =0
select top 1 @PreAccountId=id from AA_Account where accountingyear=@PreYear and code like ”+@PreAccountCode+’%’ and isEndNode=1 and IdMarketingOrgan=@IdMarketingOrgan order by code
INSERT INTO AA_AccountAssociation(currentaccountingyear,idcurrentaccountDTO,idpreaccountDTO,preaccountingyear)
values(@CurrentYear,@NewAccountId,@PreAccountId,@PreYear)
fetch next from curNoEnd into @NewAccountId,@PreAccountId,@NewisEndNode,@PreisEndNode,@NewAccountCode,@PreAccountCode
end
close curNoEnd
deallocate curNoEnd
end
else
begin
INSERT INTO AA_AccountAssociation(currentaccountingyear,idcurrentaccountDTO,idpreaccountDTO,preaccountingyear)
select @CurrentYear,id,id,@CurrentYear from aa_account where accountingyear=@CurrentYear and IdMarketingOrgan=@IdMarketingOrgan
end
fetch next from cur into @CurrentYear,@PreYear
end
close cur
deallocate cur
fetch next from cur2 into @IdMarketingOrgan
end
close cur2
deallocate cur2
end
