解决用友U8固定资产对账不平
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 @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 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.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 and iyear=@acc_year
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固定资产对账不平】，请使用软件OFFICE或WPS软件打开。作品中的文字与图均可以修改和编辑，
图片更改请在作品中右键图片并更换，文字修改请直接点击文字进行修改，也可以新增和删除文档中的内容。
该文档来自用户分享，如有侵权行为请发邮件ishare@vip.sina.com联系网站客服，我们会及时删除。
[版权声明] 本站所有资料为用户分享产生，若发现您的权利被侵害，请联系客服邮件isharekefu@iask.cn，我们尽快处理。
本作品所展示的图片、画像、字体、音乐的版权可能需版权方额外授权，请谨慎使用。
网站提供的党政主题相关内容(国旗、国徽、党徽..)目的在于配合国家政策宣传，仅限个人学习分享使用，禁止用于任何广告和商用目的。
浙公网安备 33021202002483