解决用友U8固定资产对账不平.docVIP

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=999, @acc_year=2013, @iperiod=1, @Begin=2013-01-01 00:00:00.000, @end=2013-01-31 00:00:00.000 -----修改成需要重算总账的相关信息 --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 @iperiod12 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 and iyear=@acc_year 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.dblMonthvalueisnull(Dv,0)) and T.iperiod=@iperiod --取月末有效卡片序号数据，计算月末累计折旧数据 truncate table #fq inse