问题现象: | 固定资产统计表与累计折旧表(一)中的 累计折旧 不一致,卡片管理中累计折旧合计数与总账对账数值也不一致。 |
问题原因: | |
解决方案: | 您好 如果六月份已做月结,计提七月折旧后,再执行如下语句做调整即可,经测试以后月份也正常; 注:执行前做好备份; begin TRANSACTION kk --创建临时表 CREATE TABLE #fq([sCardID] int ,PRIMARY key nonCLUSTERED ([sCardID])) commit TRANSACTION kk --初始化定义变量 declare @Begin datetime,@end datetime,@iperiod int, @acc_id varchar(10), @acc_year varchar(10) declare @SQL nchar(2000) --得到当前本数据库的有关信息 Select @acc_id=substring(db_name(),8,3), @acc_year=substring(db_name(),12,4), @iperiod=7 SELECT @Begin=[dBegin], @end =[dEnd]FROM [UFSystem].[dbo].[UA_Period] where [cAcc_Id]=@acc_id and [iYear] =@acc_year and iId=@iperiod --Select @acc_id,@acc_year,@iperiod,@Begin,@end --取月初有效卡片序号数据,计算月初累计折旧数据 if @iperiod>12 return insert #fq SELECT max(C.[sCardID])FROM [fa_Cards] C WHERE ( (c.dInputDate<@Begin) AND (c.dTransDate<@Begin Or c.dTransDate Is Null) AND (c.dDisposeDate<@Begin Or c.dDisposeDate Is Null) ) group by C.[sCardNum] --Set @SQL= update fa_total set dblMonthDeprTotal=isnull(DT,0) ,dblMonthvalue=isnull(Dv,0) FROM fa_total T left join ( Select D.[sDeptNum],C.[sTypeNum], sum(D.[dblValue]) DV, sum(case @iperiod-1 when 0 then [dblDeprT1]-[dblDepr1] when 1 then [dblDeprT1] when 2 then [dblDeprT2] when 3 then [dblDeprT3] when 4 then [dblDeprT4] when 5 then [dblDeprT5] when 6 then [dblDeprT6] when 7 then [dblDeprT7] when 8 then [dblDeprT8] when 9 then [dblDeprT9] when 10 then [dblDeprT10] when 11 then [dblDeprT11] else 0 end) DT from [fa_Cards] C JOIN [fa_Cards_Detail] D ON D.[sCardID]=C.[sCardID] JOIN [fa_DeprTransactions_Detail] P ON C.[sCardNum]=P.[sCardNum] AND D.[sDeptNum]=P.[sDeptNum] join #fq on #fq.[sCardID]=C.[sCardID] WHERE C.[dDisposeDate] is null group by D.[sDeptNum],C.[sTypeNum] ) as A on T.[sDeptNum]=a.[sDeptNum] and T.[sTypeNum]=a.[sTypeNum] where (T.dblMonthDeprTotal<> isnull(DT,0) or T.dblMonthvalue<>isnull(Dv,0)) and T.iperiod=@iperiod --取月末有效卡片序号数据,计算月末累计折旧数据 truncate table #fq insert #fq SELECT max(C.[sCardID])FROM [fa_Cards] C WHERE ( (c.dInputDate<=@end) AND (c.dTransDate<=@end Or c.dTransDate Is Null) AND (c.dDisposeDate<=@end Or c.dDisposeDate Is Null) ) group by C.[sCardNum] --Set @SQL= update fa_total set dblDeprTotal=isnull(DT,0),dblDepr=isnull(DP,0) ,dblvalue=isnull(Dv,0) FROM fa_total T left join ( Select D.[sDeptNum],C.[sTypeNum], sum(D.[dblValue]) DV, sum( case @iperiod when 1 then [dblDeprT1] when 2 then [dblDeprT2] when 3 then [dblDeprT3] when 4 then [dblDeprT4] when 5 then [dblDeprT5] when 6 then [dblDeprT6] when 7 then [dblDeprT7] when 8 then [dblDeprT8] when 9 then [dblDeprT9] when 10 then [dblDeprT10] when 11 then [dblDeprT11] when 12 then [dblDeprT12] else 0 end ) DT , sum( case @iperiod when 1 then [dblDepr1] when 2 then [dblDepr2] when 3 then [dblDepr3] when 4 then [dblDepr4] when 5 then [dblDepr5] when 6 then [dblDepr6] when 7 then [dblDepr7] when 8 then [dblDepr8] when 9 then [dblDepr9] when 10 then [dblDepr10] when 11 then [dblDepr11] when 12 then [dblDepr12] else 0 end) DP from [fa_Cards] C JOIN [fa_Cards_Detail] D ON D.[sCardID]=C.[sCardID] JOIN [fa_DeprTransactions_Detail] P ON C.[sCardNum]=P.[sCardNum] AND D.[sDeptNum]=P.[sDeptNum] join #fq on #fq.[sCardID]=C.[sCardID] WHERE C.[dDisposeDate] is null --or ( C.[dDisposeDate]<=@end and C.[dDisposeDate] >=@Begin) --如果有减少资产需要将此条件加上否则可以不加 group by D.[sDeptNum],C.[sTypeNum] ) as A on T.[sDeptNum]=a.[sDeptNum] and T.[sTypeNum]=a.[sTypeNum] where (T.dblDeprTotal<> isnull(DT,0) or t.dblDepr<>isnull(DP,0) or t.dblvalue<>isnull(Dv,0)) and T.iperiod=@iperiod truncate table #fq --取本月新增或变动有效卡片序号数据,计算本月折旧变动情况 insert #fq SELECT C.[sCardID] FROM [fa_Cards] C WHERE ( (c.dInputDate between @begin and @end) or (c.dTransDate between @begin and @end)or (c.dDisposeDate between @begin and @end) ) update fa_total set [dblTransInDeprTotal]=indt,[dblTransOutDeprTotal]=(outdt) FROM fa_total T left join ( Select D.[sDeptNum],C.[sTypeNum], sum(d.[dblTransInDeprTCard]) inDt, sum(d.[dblTransOutDeprTCard]+ case when c.iopttype<>3 then 0 else case @iperiod when 1 then p.[dblDepr1] when 2 then p.[dblDepr2] when 3 then p.[dblDepr3] when 4 then p.[dblDepr4] when 5 then p.[dblDepr5] when 6 then p.[dblDepr6] when 7 then p.[dblDepr7] when 8 then p.[dblDepr8] when 9 then p.[dblDepr9] when 10 then p.[dblDepr10] when 11 then p.[dblDepr11] when 12 then p.[dblDepr12] else 0 end end ) outDT from [fa_Cards] C JOIN [fa_Cards_Detail] D ON D.[sCardID]=C.[sCardID] JOIN [fa_DeprTransactions_Detail] P ON C.[sCardNum]=P.[sCardNum] AND D.[sDeptNum]=P.[sDeptNum] join #fq on #fq.[sCardID]=C.[sCardID] group by D.[sDeptNum],C.[sTypeNum] ) as A on T.[sDeptNum]=a.[sDeptNum] and T.[sTypeNum]=a.[sTypeNum] where T.iperiod=@iperiod and ([dblTransOutDeprTotal]<>isnull(outdt,0) or [dblTransInDeprTotal]<>isnull(indt,0)) drop table #fq 【注意事项】修改前请务必做好数据备份; |
相关补丁: | |
版本: | 8.90 |
模块: | 固定资产 |
产品: | 财务会计 |
问题名称: | 固定资产统计表与累计折旧表(一)不一致 |
最后更新: | 2011-08-02 23:20:25 |