解决方案: |
备份数据。反记账到年初,并用新建余额表脚本进行更新fd_accbal表的操作:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[fd_accBal]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
--------清空账户余额表--------
DELETE FROM fd_accBal
--------判断账户开户日期是否超前系统起用日期:是--读入系统起用日期 ;否--读入账户开户日期--------
DECLARE @dFDNatStartDate DateTime
SET @dFDNatStartDate = (SELECT CONVERT(DATETIME,cValue) AS cValue
FROM AccInformation WHERE cSysID = 'FD' AND cName = 'dFDNatStartDate')
--------从账户定义表中读取相应数据到账户余额表--------
INSERT INTO fd_accBal (accdef_id,bill_date,bal_mny,bal_booked_mny,natural_bal_mny,natural_bal_booked_mny)
SELECT accdef_id,(CASE WHEN dOpenDate < @dFDNatStartDate
THEN @dFDNatStartDate ELSE dOpenDate END) AS OpenDate,isnull(mb,0),
isnull(mb,0),isnull(qcye_natural_mny,0),isnull(qcye_natural_mny,0)
FROM FD_AccDef
-------------------------------
--------账户余额表升级---------
-------FD_transactions---------
-------------------------------
DECLARE @vchId fd_udt_id,@vchType varchar(2),@derVchType varchar(2), @billDate datetime,
@rcvAccId fd_udt_id,@parAccId fd_udt_id, @fixedAccId fd_udt_id,@sumMny decimal(20,2),
@comMny decimal(20,2),@natMny decimal(21,2), @bookBill int
DECLARE @openDate datetime DECLARE @statues INT
DECLARE @trncnt INT
--影响账户余额的字段变更 --
SET cursor_close_on_commit ON
DECLARE accBal CURSOR
FOR
SELECT fd_transactions.transactions_id,fd_entities.ibitype,fd_entities.iderivebitype,
fd_transactions.bill_date,fd_transactions.rcv_acc_id,fd_transactions.pay_acc_id,
fd_transactions.fixed_acc_id, SNULL(fd_transactions.sum_mny,0),ISNULL(fd_transactions.commission_mny,0),
ISNULL(fd_transactions.natural_mny,0),CASE ISNULL(book_name,0) when '0' then 0 else 1 end
FROM fd_transactions
INNER JOIN fd_entities ON SUBSTRING(fd_transactions.transactions_id,1,2)=fd_entities.ibitype
WHERE fd_entities.iderivebitype=@dFDNatStartDate
begin
EXEC @statues=fd_updatebalance @vchId,@vchType,@derVchType,@billDate,@rcvAccId,
@parAccId,@fixedAccId,@sumMny,@comMny,@natMny,0
IF @statues!=0 OR @@error!=0
GOTO Errhandle
if @bookbill<>0
begin
EXEC @statues=fd_updatebalance @vchId,@vchType,@derVchType,@billDate,@rcvAccId,
@parAccId,@fixedAccId,@sumMny,@comMny,@natMny,1
IF @statues!=0 OR @@error!=0 GOTO Errhandle
end
end
FETCH accbal INTO @vchId,@vchType,@derVchType,@billDate,@rcvAccId,
@parAccId,@fixedAccId,@sumMny,@comMny,@natMny,@bookbill
END
CLOSE accbal DEALLOCATE accbal
--SET cursor_close_on_commit OFF
GOTO ExitProc ErrHandle:
DECLARE @errmsg VARCHAR(50)
IF @statues!=0
BEGIN
SELECT @errmsg=(CASE WHEN @statues=-101 THEN '账户余额表错误!账户在表中无记录!'
WHEN @statues=-102 THEN '账户余额表错误!账户在表中余额记录丢失!'
WHEN @statues=-103 THEN '账户余额表更新失败!补记账户余额记录操作失败!'
WHEN @statues=-104 THEN '账户余额表更新失败!更新账户余额记录操作失败!'
END)
RAISERROR(@errmsg,16,1)
END
CLOSE accbal
DEALLOCATE accbal
--SET cursor_close_on_commit OFF END ExitProc: GO 再重新记帐. |