U8知识库| 问题号: | 14281 |
|---|
| 解决状态: | 临时解决方案 |
|---|
| 软件版本: | 8.60sp |
|---|
| 软件模块: | 固定资产 |
|---|
| 行业: | 通用 |
|---|
| 关键字: | 账表查询 |
|---|
| 适用产品: | U860–财务会计–固定资产 |
|---|
| 问题名称: | 固定资产部门折旧汇总表本月折旧数错误 |
|---|
| 问题现象: | 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 |
|---|
| 补丁编号: | |
|---|
| 录入日期: | 2016-03-16 15:23:45 |
|---|
| 最后更新时间: | |
|---|
