资源描述：
《解决用友u8固定资产对账不平》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、beginTRANSACTIONkk--创建临时表CREATETABLE#fq([sCardID]int,PRIMARYkeynonCLUSTERED([sCardID]))commitTRANSACTIONkk--初始化定义变量declare@Begindatetime,@enddatetime,@iperiodint,@acc_idvarchar(10),@acc_yearvarchar(10)declare@SQLnchar(2000)--得到当前本数据库的有关信息select@acc_id='999',@acc_year='2013'
2、,@iperiod=1,@Begin='2013-01-0100:00:00.000',@end='2013-01-3100:00:00.000'-----修改成需要重算总账的相关信息--SELECT@Begin=[dBegin],@end=[dEnd]FROM[UFSystem].[dbo].[UA_Period]--where[cAcc_Id]=@acc_idand[iYear]=@acc_yearandiId=@iperiod----Select@acc_id,@acc_year,@iperiod,@Begin,@end--取月初有效卡
3、片序号数据，计算月初累计折旧数据if@iperiod>12returninsert#fqSELECTmax(C.[sCardID])FROM[fa_Cards]CWHERE((c.dInputDate<@Begin)AND(c.dTransDate<@BeginOrc.dTransDateIsNull)AND(c.dDisposeDate<@BeginOrc.dDisposeDateIsNull))groupbyC.[sCardNum]--Set@SQL=updatefa_totalsetdblMonthDeprTotal=isnull(DT
4、,0),dblMonthvalue=isnull(Dv,0)FROMfa_totalTleftjoin(SelectD.[sDeptNum],C.[sTypeNum],sum(D.[dblValue])DV,sum(case@iperiod-1when0then[dblDeprT1]-[dblDepr1]when1then[dblDeprT1]when2then[dblDeprT2]when3then[dblDeprT3]when4then[dblDeprT4]when5then[dblDeprT5]when6then[dblDeprT6]w
5、hen7then[dblDeprT7]when8then[dblDeprT8]when9then[dblDeprT9]when10then[dblDeprT10]when11then[dblDeprT11]else0end)DTfrom[fa_Cards]CJOIN[fa_Cards_Detail]DOND.[sCardID]=C.[sCardID]JOIN[fa_DeprTransactions_Detail]PONC.[sCardNum]=P.[sCardNum]ANDD.[sDeptNum]=P.[sDeptNum]join#fqon#
6、fq.[sCardID]=C.[sCardID]WHEREC.[dDisposeDate]isnullandiyear=@acc_yeargroupbyD.[sDeptNum],C.[sTypeNum])asAonT.[sDeptNum]=a.[sDeptNum]andT.[sTypeNum]=a.[sTypeNum]where(T.dblMonthDeprTotal<>isnull(DT,0)orT.dblMonthvalue<>isnull(Dv,0))andT.iperiod=@iperiod--取月末有效卡片序号数据，计算月末累计折旧
7、数据truncatetable#fqinsert#fqSELECTmax(C.[sCardID])FROM[fa_Cards]CWHERE((c.dInputDate<=@end)AND(c.dTransDate<=@endOrc.dTransDateIsNull)AND(c.dDisposeDate<=@endOrc.dDisposeDateIsNull))groupbyC.[sCardNum]--Set@SQL=updatefa_totalsetdblDeprTotal=isnull(DT,0),dblDepr=isnull(DP,0),
8、dblvalue=isnull(Dv,0)FROMfa_totalTleftjoin(SelectD.[sDeptNum],C.[sTypeNum],sum(D.[
