用友U8固定资产经常出现于总账对账不平的情况，可以通过执行此脚本对固定资产明细重算，并且进行汇总。

用友U8ERP 固定资产和总账对账不平重算脚本_用友

1.固定资产,固定资产和总账对账不平,SQL，固定资产重算

--调整第9行中的@iperiod=具体调整的月份

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=3

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

 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 


#用友

#用友U8

#固定资产模块