| 问题现象： | 860软件，截止到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))=1 and ISNUMERIC(substring(name,12,4))=1) begin begin TRANSACTION kk if 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 DateTime DECLARE @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_period where cacc_id=@acc_id and iyear=@acc_year) if not @iperiod between 1 and 12 begin return end --循环到年末 WHILE @iperiod<=@LoginDate) AND (dTransDate<=@LoginDate Or dTransDate Is Null) AND (dDisposeDate<=@LoginDate Or dDisposeDate Is Null)) GROUP BY sCardNum --无卡片则退出 IF @@ROWCOUNT = 0 BEGIN set @iperiod=@iperiod+1 CONTINUE END insert 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 0 end) 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 0 end) as sum2 FROM (fq INNER JOIN fa_Cards c ON c.sCardID = fq.sCardID) INNER JOIN fa_DeprTransactions d ON c.sCardNum = d.sCardNum WHERE (c.dDisposeDate) Is Null group by c.sDeptNum,c.sTypeNum IF @@ROWCOUNT = 0 BEGIN set @iperiod=@iperiod+1 CONTINUE END update fa_Total set dblDeprTotal = fa.dblDeprTotal, dblDepr=fa.dblDepr from fa_Total t inner join fa on fa.sDeptNum = t.sDeptNum and t.sTypeNum = fa.sTypeNum where t.iperiod=@iperiod set @iperiod=@iperiod+1 end drop table fq drop table fa end |
| 解决方案： | 您的这个问题有可能是由于数据量较大、卡片做过多次变动后升级造成的。 请执行以下脚本就可解决： －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－ if exists(select * from master..sysdatabases where name=db_name() and name like 'ufdata[_]___[_]____' and ISNUMERIC (substring(name,8,3))=1 and ISNUMERIC(substring(name,12,4))=1) begin begin TRANSACTION kk if 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 DateTime DECLARE @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_period where cacc_id=@acc_id and iyear=@acc_year) if not @iperiod between 1 and 12 begin return end --循环到年末 WHILE @iperiod<=@LoginDate) AND (dTransDate<=@LoginDate Or dTransDate Is Null) AND (dDisposeDate<=@LoginDate Or dDisposeDate Is Null)) GROUP BY sCardNum --无卡片则退出 IF @@ROWCOUNT = 0 BEGIN set @iperiod=@iperiod+1 CONTINUE END insert 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 0 end) 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 0 end) as sum2 FROM (fq INNER JOIN fa_Cards c ON c.sCardID = fq.sCardID) INNER JOIN fa_DeprTransactions d ON c.sCardNum = d.sCardNum WHERE (c.dDisposeDate) Is Null group by c.sDeptNum,c.sTypeNum IF @@ROWCOUNT = 0 BEGIN set @iperiod=@iperiod+1 CONTINUE END update fa_Total set dblDeprTotal = fa.dblDeprTotal, dblDepr=fa.dblDepr from fa_Total t inner join fa on fa.sDeptNum = t.sDeptNum and t.sTypeNum = fa.sTypeNum where t.iperiod=@iperiod set @iperiod=@iperiod+1 end drop table fq drop table fa end |
| 相关补丁： | |
| 版本： | 8.60sp |
| 模块： | 固定资产 |
| 产品： | U860--财务会计--固定资产 |
| 问题名称： | 固定资产部门折旧汇总表本月折旧数错误 |
| 最后更新： | 2006-10-18 00:00:00 |