– 10.0以后有很多新年度第一个月月末值对不上的问题
– 原因是老产品(尤其是10.0的 ImportAccountData12End 方法)判断fa_total是否结转判断:and convert(decimal(38,10),dblDeprTotal)<>0 ;
– 如果是12月新增资产不提折旧就会漏结转;
– 最根本的解决方案是恢复12月再进行年结
– 下面是不恢复,直接在1月份补数据的解决方案,执行前务必备份数据
– 0.使用前先看一下
–select *from fa_total where iyear = 2014 and iperiod = 12
–select *from fa_total where iyear = 2015 and iperiod = 1
–是否数据不一样
–如果是的话，可以使用下列语句补充数据
–使用时务必先备份数据，并且只能在月份未作业务的时候使用，否则会造成数据不一致
– 1.备份：
– begin tran
select *
into fa_total_201501
from fa_total where iyear = 2015 – and iperiod = 1
– 2.补漏结转
select sDeptNum, sTypeNum, 1 as iperiod, lMonthCount, lCount, dblYearValue,
dblYearDeprTotal, dblLastAccrualValue, dblLastDepr, dblLastAddValue, dblLastDecValue, dblLastWork,
dblLastWorkTotal, dblWork, dblWorkT, dblMonthValue, dblValue, dblMonthDeprTotal, dblDeprTotal, dblAddValue,
dblDecValue, dblTransInDeprTotal, dblTransOutDeprTotal, dblAccrualValue, dblDepr,2015 as iyear
into #1
FROM fa_Total where iyear = 2014 and iPeriod = 12 and sTypeNum+sDeptNum not in
(select sTypeNum+sDeptNum from fa_Total where iyear = 2015 and iPeriod=1)
UPDATE #1 SET lMonthCount = lCount,dblLastAccrualValue = dblAccrualValue,
dblLastDepr = dblDepr,dblLastAddValue=dblAddValue,dblLastDecValue=dblDecValue,dblLastWork=dblWork,
dblLastWorkTotal=dblWorkT,dblMonthValue=dblValue,dblMonthDeprTotal=dblDeprTotal
UPDATE #1 SET dblAccrualValue = 0,dblDepr = 0,dblTransInDeprTotal = 0,dblTransOutDeprTotal = 0,dblAddValue = 0,dblDecValue = 0
UPDATE #1 SET iPeriod = 1,dblYearValue = dblMonthValue,dblYearDeprTotal = dblMonthDeprTotal
insert into fa_Total(sDeptNum, sTypeNum, iperiod, lMonthCount, lCount, dblYearValue,
dblYearDeprTotal, dblLastAccrualValue, dblLastDepr, dblLastAddValue, dblLastDecValue, dblLastWork,
dblLastWorkTotal, dblWork, dblWorkT, dblMonthValue, dblValue, dblMonthDeprTotal, dblDeprTotal, dblAddValue,
dblDecValue, dblTransInDeprTotal, dblTransOutDeprTotal, dblAccrualValue, dblDepr, iyear)
select sDeptNum, sTypeNum, iperiod, lMonthCount, lCount, dblYearValue,
dblYearDeprTotal, dblLastAccrualValue, dblLastDepr, dblLastAddValue, dblLastDecValue, dblLastWork,
dblLastWorkTotal, dblWork, dblWorkT, dblMonthValue, dblValue, dblMonthDeprTotal, dblDeprTotal, dblAddValue,
dblDecValue, dblTransInDeprTotal, dblTransOutDeprTotal, dblAccrualValue, dblDepr, iyear
from #1
– 3.2014.12和2015.1存在同部门/类别的新增资产,期末金额更新处理
– DROP TABLE fa_total_subTypeDept
select sTypeNum,sDeptNum
into fa_total_subTypeDept
from fa_total where iyear = 2015 and iperiod = 1
and sTypeNum+sDeptNum
in
(
select a.sTypeNum+a.sDeptNum
from
(select dblValue,dblDeprTotal, sTypeNum,sDeptNum from fa_total where iyear = 2014 and iperiod = 12
–order by sTypeNum,sDeptNum
) a
inner join
(
select dblMonthValue,dblMonthDeprTotal, sTypeNum,sDeptNum from fa_total where iyear = 2015 and iperiod = 1
–order by sTypeNum,sDeptNum
) b
on a.sTypeNum = b.sTypeNum and a.sDeptNum = b.sDeptNum
where convert(decimal(18,2),abs(dblValue-dblMonthValue)) >= 0.01
or convert(decimal(18,2),abs(dblDeprTotal-dblMonthDeprTotal)) >= 0.01
)
– 开始更新
update a
set dblMonthValue = dblMonthValue+ b.dblValue, dblYearValue = dblYearValue+ b.dblValue,dblValue = a.dblValue+ b.dblValue
–select a.sDeptNum,a.sTypeNum,a.dblMonthValue,a.dblYearValue,a.dblValue,a.dblDeprTotal,
–b.dblValue as dblValue_b,b.dblDeprTotal as dblDeprTotal_b
from fa_total a
inner join
(
select dblValue,dblDeprTotal,sTypeNum+sDeptNum as sNum from fa_total where iyear = 2014 and iperiod = 12
and sTypeNum+sDeptNum in (select sTypeNum+sDeptNum from fa_total_subTypeDept)
) b
on a.sTypeNum+a.sDeptNum = b.sNum
where iyear = 2015 and iperiod = 1
– commit tran
– rollback tran
GO
