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

财务模块年结常见问题

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

一、结账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<=@EndYear

begin

set @NowCount=1

while(@NowCount<=@AllCount)

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<@StartYear)

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<=@EndYear

begin

set @NowCount=1

while(@NowCount<=@AllCount)

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<@StartYear)

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)