T+财务模块年结常见问题

iufida

财务模块年结常见问题

请注意：执行脚本前务必备份好可用的账套数据！！！

一、结账2020年12月，选择覆盖下年度期初，报错【输入字符串的格式不正确】

答：【问题原因】有科目设置了辅助核算，但有凭证没有录入辅助核算。

【解决方案】备份账套，执行下面脚本修复

select idaccount,idauxAccDepartment,* from GL_Journal where idaccount in(select id from AA_Account where isauxaccdepartment=1)and idauxAccDepartment is null
select idaccount from GL_Journal where idaccount in (select id from AA_Account where isauxaccinventory=1)and idauxAccInventory is null
select idaccount from GL_Journal where idaccount in (select id from AA_Account where isauxacccustomer=1)and idauxAccCustomer is null
select idaccount from GL_Journal where idaccount in (select id from AA_Account where isauxaccsupplier=1)and idauxAccSupplier is null
select idaccount from GL_Journal where idaccount in (select id from AA_Account where isauxaccperson=1) and idauxAccPerson is null
select idaccount from GL_Journal where idaccount in (select id from AA_Account where isauxaccproject=1) and idauxAccProject is null
select idaccount from GL_Journal where idaccount in (select id from AA_Account where isexauxacc1=1)and idexauxacc1 is null
select idaccount from GL_Journal where idaccount in (select id from AA_Account where isexauxacc2=1)and idexauxacc2 is null
select idaccount from GL_Journal where idaccount in (select id from AA_Account where isexauxacc3=1)and idexauxacc3 is null
select idaccount from GL_Journal where idaccount in (select id from AA_Account where isexauxacc4=1)and idexauxacc4 is null
select idaccount from GL_Journal where idaccount in (select id from AA_Account where isexauxacc5=1)and idexauxacc5 is null
select idaccount from GL_Journal where idaccount in (select id from AA_Account where isexauxacc6=1)and idexauxacc6 is null
select idaccount from GL_Journal where idaccount in (select id from AA_Account where isexauxacc7=1)and idexauxacc7 is null
select idaccount from GL_Journal where idaccount in (select id from AA_Account where isexauxacc8=1)and idexauxacc8 is null
select idaccount from GL_Journal where idaccount in (select id from AA_Account where isexauxacc9=1)and idexauxacc9 is null
select idaccount from GL_Journal where idaccount in (select id from AA_Account where isexauxacc10=1)and idexauxacc10 is null

二、月度工作报告为空不显示

答：【问题原因】客户的AA_Account_Ext表有问题.需要修复.

【解决方案】修复脚本如下,修复后打开科目档案重新保存一下任何一个科目即可。

脚本如下,重建AA_Account_Ext表.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
drop table [AA_Account_Ext]
go
CREATE TABLE [dbo].[AA_Account_Ext](
[accountcode_lev1] [nvarchar](40) NULL,
[accountname_lev1] [nvarchar](200) NULL,
[accountcode_lev2] [nvarchar](40) NULL,
[accountname_lev2] [nvarchar](200) NULL,
[accountcode_lev3] [nvarchar](40) NULL,
[accountname_lev3] [nvarchar](200) NULL,
[accountcode_lev4] [nvarchar](40) NULL,
[accountname_lev4] [nvarchar](200) NULL,
[accountcode_lev5] [nvarchar](40) NULL,
[accountname_lev5] [nvarchar](200) NULL,
[accountcode_lev6] [nvarchar](40) NULL,
[accountname_lev6] [nvarchar](200) NULL,
[accountcode_lev7] [nvarchar](40) NULL,
[accountname_lev7] [nvarchar](200) NULL,
[accountcode_lev8] [nvarchar](40) NULL,
[accountname_lev8] [nvarchar](200) NULL,
[accountcode_lev9] [nvarchar](40) NULL,
[accountname_lev9] [nvarchar](200) NULL,
[accountcode_lev10] [nvarchar](40) NULL,
[accountname_lev10] [nvarchar](200) NULL,
[accountcode_lev11] [nvarchar](40) NULL,
[accountname_lev11] [nvarchar](200) NULL,
[accountcode_lev12] [nvarchar](40) NULL,
[accountname_lev12] [nvarchar](200) NULL,
[accountcode_lev13] [nvarchar](40) NULL,
[accountname_lev13] [nvarchar](200) NULL,
[accountcode_lev14] [nvarchar](40) NULL,
[accountname_lev14] [nvarchar](200) NULL,
[accountcode_lev15] [nvarchar](40) NULL,
[accountname_lev15] [nvarchar](200) NULL,
[depth] [nvarchar](10) NULL,
[ts] [timestamp] NULL,
[accountid_lev1] [int] NULL,
[accountid_lev10] [int] NULL,
[accountid_lev11] [int] NULL,
[accountid_lev12] [int] NULL,
[accountid_lev13] [int] NULL,
[accountid_lev14] [int] NULL,
[accountid_lev15] [int] NULL,
[accountid_lev2] [int] NULL,
[accountid_lev3] [int] NULL,
[accountid_lev4] [int] NULL,
[accountid_lev5] [int] NULL,
[accountid_lev6] [int] NULL,
[accountid_lev7] [int] NULL,
[accountid_lev8] [int] NULL,
[accountid_lev9] [int] NULL,
[id] [int] NOT NULL,
[createTime] [datetime] NULL,
CONSTRAINT [AA_Account_Ext_PK] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

三、科目余额表联查科目明细账对应的明细账中的科目是其他的科目

答：【解决方案】备份账套，执行下面脚本整理科目对照关系

declare @debugger varchar(50)
if object_id(N'AA_BackUp',N'U') is null
                select * into AA_BackUp from AA_AccountAssociation --删除科目对照表数据前备份
--if(@debugger='test')
--begin
--        insert into AA_AccountAssociation(idcurrentaccountingyear,createdtime,sequencenumber,idcurrentaccountDTO,idpreaccountDTO,preaccountingyear)
--        select
id,code,name,currentaccountingyear,createdtime,sequencenumber,idcurrentaccountDTO,idpreaccountDTO,preaccountingyear from AA_BackUp
--end

--修复12.1 AccountAssociation

declare @StartYear int
declare @EndYear int
select @StartYear=2017
select @EndYear=2022
delete AA_AccountAssociation where currentaccountingyear  between @StartYear and @EndYear  --删除科目对照表数据
declare @TempAccount table(Code varchar(100),Num int)
declare @AllCount int
declare @NowCount int
--查出所有科目CODE
insert into @TempAccount(Code,Num) select code,ROW_NUMBER() over(order by code) as Num from aa_account  group by code
set @AllCount=(select count(*) from @TempAccount)

--循环科目CODE
declare @differYear int;
set @differYear=@EndYear;
while @StartYear
        begin
                set @NowCount=1
                        while(@NowCount
                                begin
                                        declare @TempCode varchar(20)
                                        select @TempCode=Code from @TempAccount where num=@NowCount
                                        declare @PreAccountID varchar(50)
                                        declare @CurAccountID varchar(50)
                                        select @CurAccountID=id from AA_Account where code=@TempCode and accountingyear=@EndYear
                                        select @PreAccountID=id from AA_Account where code=@TempCode and accountingyear=@differYear                       
                                         if(@PreAccountID!='' and @CurAccountID!='')
                                                insert into AA_AccountAssociation(currentaccountingyear,idcurrentaccountDTO,idpreaccountDTO,preaccountingyear)
                                                values
                                                (@EndYear,@CurAccountID,@PreAccountID,@differYear)
                                                set @NowCount=@NowCount+1
                                        set @PreAccountID=''
                                        set @CurAccountID=''
                                end
                set @differYear=@differYear-1
        if(@differYear
                        BEGIN
                                set @EndYear=@EndYear-1
                                set @differYear=@EndYear       
                        END                                       
        end

四、22年财务结账提示下年科目存在非末级科目，不能年结。请调整科目对照后再进行年结！[上年科目,下年科目]：[405,405],[503,503]

                               
登录/注册后可看大图

答：【解决方案】备份账套数据后执行，如果是22年12月份结账，

脚本： delete AA_AccountAssociation from AA_AccountAssociation a left join AA_Account b on a.idpreaccountDTO=b.id left join AA_Account c on a.idcurrentaccountDTO=c.id   where currentaccountingyear=2023 and preaccountingyear=2022 and b.isEndNode=0 and c.isEndNode=0

五、期间结转不了，用时在10小时以上，提示正在创建数据库

                               
登录/注册后可看大图

答：【问题原因】发生库里很多临时表没有删除，需要先清理临时表。
【解决方案】
1、备份好账套。
2、执行在账套库中执行下面sql语句清理临时表。
if(exists(select 1 from sysobjects where name='EAP_SP_DeleteTempTable' and Type='P')) begin Exec EAP_SP_DeleteTempTable end; else if(exists(select 1 from sysobjects where name='DeleteTempTable' and Type='P')) begin Exec DeleteTempTable end
3、收缩数据库日志。
4、期间结转，因为数据量大，最好晚上做期间结转。

六、月末结账后，12月份期末值不等于1月份期初，对照过科目，科目一样，取消12月份结账再结账，还是不相等。

答：【解决方案】
sql能查询出科目对照有多余一条的记录有很多。
select currentaccountingyear,preaccountingyear,idcurrentaccountDTO,idpreaccountDTO,COUNT(*) from AA_AccountAssociation
group by currentaccountingyear,preaccountingyear,idcurrentaccountDTO,idpreaccountDTO having COUNT(*)>1
执行科目对照修复脚本，年结就能结转到期初了
科目对照关系有问题，最后的年可以根据需要进行修改

--修复12.1以上 AccountAssociation科目对照
IF (object_id('AAFI_SP_GenerateAccountAssociation', 'P') is not null)
DROP PROC AAFI_SP_GenerateAccountAssociation
GO
Create PROC AAFI_SP_GenerateAccountAssociation(@StartYear int, @EndYear int)
AS
delete AA_AccountAssociation where currentaccountingyear between @StartYear and @EndYear --删除科目对照表数据
declare @TempAccount table(Code varchar(100),Num int)
declare @AllCount int
declare @NowCount int
declare @CurIsEndNode int
declare @PreIsEndNode int
--查出所有科目CODE
insert into @TempAccount(Code,Num) select code,ROW_NUMBER() over(order by code) as Num from aa_account group by code
set @AllCount=(select count(*) from @TempAccount)
--循环科目CODE
declare @differYear int;
set @differYear=@EndYear;
while @StartYear
begin
set @NowCount=1
while(@NowCount
begin
declare @TempCode varchar(20)
select @TempCode=Code from @TempAccount where num=@NowCount
declare @PreAccountID varchar(50)
declare @CurAccountID varchar(50)
select @CurAccountID=id,@CurIsEndNode=isEndNode from AA_Account where code=@TempCode and accountingyear=@EndYear
select @PreAccountID=id,@PreIsEndNode=isEndNode from AA_Account where code=@TempCode and accountingyear=@differYear
--同一年的科目不管上下级默认都有一条
if(@PreAccountID!='' and @CurAccountID!='' and @EndYear=@differYear)
begin
insert into AA_AccountAssociation(currentaccountingyear,idcurrentaccountDTO,idpreaccountDTO,preaccountingyear)
values (@EndYear,@CurAccountID,@PreAccountID,@differYear)
end
else if (@PreAccountID!='' and @CurAccountID!='' and @CurIsEndNode!=0 and @PreIsEndNode!=0 and @EndYear!=@differYear)
begin
insert into AA_AccountAssociation(currentaccountingyear,idcurrentaccountDTO,idpreaccountDTO,preaccountingyear)
values (@EndYear,@CurAccountID,@PreAccountID,@differYear)
end
set @NowCount=@NowCount+1
set @PreAccountID=''
set @CurAccountID=''
set @CurIsEndNode=0
set @PreIsEndNode=0
end
set @differYear=@differYear-1
if(@differYear
BEGIN
set @EndYear=@EndYear-1
set @differYear=@EndYear
END
end
GO
exec AAFI_SP_GenerateAccountAssociation 2008,2020;
DROP PROC AAFI_SP_GenerateAccountAssociation

解决方案：
select * from AA_Account where id in (
select distinct idaccount from GL_Journal where idaccount in (select id from AA_Account where dcdirection=653)
and periodBeginAmountDr is not null and periodBeginAmountDr!=0 and isPeriodBegin=1 and year=2023)