用友U8 截止到11月，固定资产卡片管理和折旧清单里的本月折旧数都对，但部门折旧汇总表里的本月折旧数少了很多．经检查，发现其fa_total表里缺少很多本年新增卡片的记录，但表fa_cards里有相应记录．如何调整

用友U8 截止到11月，固定资产卡片管理和折旧清单里的本月折旧数都对，但部门折旧汇总表里的本月折旧数少了很多．经检查，发现其fa_total表里缺少很多本年新增卡片的记录，但表fa_cards里有相应记录．如何调整 问题原因:数据库问题 解决方法:if exists(select * from master..sysdatabases where name=db_name() and name like 'ufdata[_]___[_]____' and ISNUMERIC (substring(name,8,3))=1and ISNUMERIC(substring(name,12,4))=1)beginbegin TRANSACTION kkif exists (select * from dbo.sysobjects where id = object_id(N'[fq]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [fq]if exists (select * from dbo.sysobjects where id = object_id(N'[fa]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [fa]CREATE TABLE fq([sCardID] int ,PRIMARY key CLUSTERED ([sCardID])) CREATE TABLE fa ([sDeptNum] [varchar] (12) NOT NULL ,[sTypeNum] [varchar] (10) NOT NULL ,--[iPeriod] [smallint] NOT NULL ,[dblDeprTotal] [float] NULL , [dblDepr] [float] NULL ,PRIMARY KEY CLUSTERED ([sDeptNum],[sTypeNum] ) ) commit TRANSACTION kk--初始化declare @LoginDate DateTimeDECLARE @acc_id varchar(10)declare @acc_year varchar(10)declare @iPeriod int--得到当前本数据库的有关信息set @acc_id=substring(db_name(),8,3)set @acc_year=substring(db_name(),12,4)--取开始的期间set @iPeriod=(select min(iid) from ufsystem..ua_periodwhere cacc_id=@acc_id and iyear=@acc_year)if not @iperiod between 1 and 12 beginreturnend--循环到年末WHILE @iperiod<13BEGINprint '期间是：' + cast(@iperiod AS char(2) )--清空临时表--取月末的日期set @LoginDate=(select top 1 dendfrom ufsystem..ua_periodwhere cacc_id=@acc_id and iyear=@acc_year and iid=@iperiod)truncate table fqtruncate table fa--保存有效卡片id到faINSERT INTO fq([sCardID]) SELECT Max([sCardID]) FROM fa_Cards WHERE ((dInputDate<=@LoginDate) AND (dTransDate<=@LoginDate Or dTransDate Is Null) AND (dDisposeDate<=@LoginDate Or dDisposeDate Is Null)) GROUP BY sCardNum --无卡片则退出IF @@ROWCOUNT = 0BEGINset @iperiod=@iperiod+1CONTINUEENDinsert fa([sDeptNum],[sTypeNum],[dblDeprTotal],[dblDepr])select c.sDeptNum,c.sTypeNum,sum(case @iPeriod when 1 then d.dblDeprT1 when 2 then d.dblDeprT2 when 3 then d.dblDeprT3 when 4 then d.dblDeprT4 when 5 then d.dblDeprT5 when 6 then d.dblDeprT6 when 7 then d.dblDeprT7 when 8 then d.dblDeprT8 when 9 then d.dblDeprT9 when 10 then d.dblDeprT10 when 11 then d.dblDeprT11 when 12 then d.dblDeprT12 else 0end)as sum1,sum(case @iPeriod when 1 then d.dblDepr1 when 2 then d.dblDepr2 when 3 then d.dblDepr3 when 4 then d.dblDepr4 when 5 then d.dblDepr5 when 6 then d.dblDepr6 when 7 then d.dblDepr7 when 8 then d.dblDepr8 when 9 then d.dblDepr9 when 10 then d.dblDepr10 when 11 then d.dblDepr11 when 12 then d.dblDepr12 else 0end) as sum2FROM (fq INNER JOIN fa_Cards c ON c.sCardID = fq.sCardID) INNER JOIN fa_DeprTransactions d ON c.sCardNum = d.sCardNumWHERE (c.dDisposeDate) Is Null group by c.sDeptNum,c.sTypeNumIF @@ROWCOUNT = 0BEGINset @iperiod=@iperiod+1CONTINUEENDupdate fa_Total set dblDeprTotal = fa.dblDeprTotal,dblDepr=fa.dblDeprfrom fa_Total t inner join fa on fa.sDeptNum = t.sDeptNum and t.sTypeNum = fa.sTypeNum where t.iperiod=@iperiodset @iperiod=@iperiod+1enddrop table fqdrop table faend
解决方案：
问题原因:数据库问题 解决方法:if exists(select * from master..sysdatabases where name=db_name() and name like 'ufdata[_]___[_]____' and ISNUMERIC (substring(name,8,3))=1and ISNUMERIC(substring(name,12,4))=1)beginbegin TRANSACTION kkif exists (select * from dbo.sysobjects where id = object_id(N'[fq]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [fq]if exists (select * from dbo.sysobjects where id = object_id(N'[fa]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [fa]CREATE TABLE fq([sCardID] int ,PRIMARY key CLUSTERED ([sCardID])) CREATE TABLE fa ([sDeptNum] [varchar] (12) NOT NULL ,[sTypeNum] [varchar] (10) NOT NULL ,--[iPeriod] [smallint] NOT NULL ,[dblDeprTotal] [float] NULL , [dblDepr] [float] NULL ,PRIMARY KEY CLUSTERED ([sDeptNum],[sTypeNum] ) ) commit TRANSACTION kk--初始化declare @LoginDate DateTimeDECLARE @acc_id varchar(10)declare @acc_year varchar(10)declare @iPeriod int--得到当前本数据库的有关信息set @acc_id=substring(db_name(),8,3)set @acc_year=substring(db_name(),12,4)--取开始的期间set @iPeriod=(select min(iid) from ufsystem..ua_periodwhere cacc_id=@acc_id and iyear=@acc_year)if not @iperiod between 1 and 12 beginreturnend--循环到年末WHILE @iperiod<13BEGINprint '期间是：' + cast(@iperiod AS char(2) )--清空临时表--取月末的日期set @LoginDate=(select top 1 dendfrom ufsystem..ua_periodwhere cacc_id=@acc_id and iyear=@acc_year and iid=@iperiod)truncate table fqtruncate table fa--保存有效卡片id到faINSERT INTO fq([sCardID]) SELECT Max([sCardID]) FROM fa_Cards WHERE ((dInputDate<=@LoginDate) AND (dTransDate<=@LoginDate Or dTransDate Is Null) AND (dDisposeDate<=@LoginDate Or dDisposeDate Is Null)) GROUP BY sCardNum --无卡片则退出IF @@ROWCOUNT = 0BEGINset @iperiod=@iperiod+1CONTINUEENDinsert fa([sDeptNum],[sTypeNum],[dblDeprTotal],[dblDepr])select c.sDeptNum,c.sTypeNum,sum(case @iPeriod when 1 then d.dblDeprT1 when 2 then d.dblDeprT2 when 3 then d.dblDeprT3 when 4 then d.dblDeprT4 when 5 then d.dblDeprT5 when 6 then d.dblDeprT6 when 7 then d.dblDeprT7 when 8 then d.dblDeprT8 when 9 then d.dblDeprT9 when 10 then d.dblDeprT10 when 11 then d.dblDeprT11 when 12 then d.dblDeprT12 else 0end)as sum1,sum(case @iPeriod when 1 then d.dblDepr1 when 2 then d.dblDepr2 when 3 then d.dblDepr3 when 4 then d.dblDepr4 when 5 then d.dblDepr5 when 6 then d.dblDepr6 when 7 then d.dblDepr7 when 8 then d.dblDepr8 when 9 then d.dblDepr9 when 10 then d.dblDepr10 when 11 then d.dblDepr11 when 12 then d.dblDepr12 else 0end) as sum2FROM (fq INNER JOIN fa_Cards c ON c.sCardID = fq.sCardID) INNER JOIN fa_DeprTransactions d ON c.sCardNum = d.sCardNumWHERE (c.dDisposeDate) Is Null group by c.sDeptNum,c.sTypeNumIF @@ROWCOUNT = 0BEGINset @iperiod=@iperiod+1CONTINUEENDupdate fa_Total set dblDeprTotal = fa.dblDeprTotal,dblDepr=fa.dblDeprfrom fa_Total t inner join fa on fa.sDeptNum = t.sDeptNum and t.sTypeNum = fa.sTypeNum where t.iperiod=@iperiodset @iperiod=@iperiod+1enddrop table fqdrop table faend

www.kuaiji66.com 天龙瑞德