[概述]

科目5001.001.001.001和5001.001.001.999、5001.001.0032011年数据缺少核算项目项目,导致非系统管理员查询不到 。

参考语句:

use AIS20180418114204

--修复语句,历史调整数据都以10.02.02数据插入

--1、插入5001.001.001.001科目2011年数据

----插入综合本位币记录

INSERT INTO t_Balance ( FYear,FPeriod,FAccountID,fdetailid,FCurrencyID, FBeginBalanceFor,FDebitFor,FCreditFor,FYtdDebitFor,FYtdCreditFor,FEndBalanceFor,

FBeginBalance , FDebit, FCredit, FYtdDebit, FYtdCredit, FEndBalance )

select FYear,FPeriod,FAccountID,1141,FCurrencyID, FBeginBalanceFor,FDebitFor,FCreditFor,FYtdDebitFor,FYtdCreditFor,FEndBalanceFor,

FBeginBalance , FDebit, FCredit, FYtdDebit, FYtdCredit, FEndBalance from t_Balance where FAccountID in (1164) and FCurrencyID =0 and FDetailID=0 and FYear=2011

----插入人民币记录

INSERT INTO t_Balance ( FYear,FPeriod,FAccountID,fdetailid,FCurrencyID, FBeginBalanceFor,FDebitFor,FCreditFor,FYtdDebitFor,FYtdCreditFor,FEndBalanceFor,

FBeginBalance , FDebit, FCredit, FYtdDebit, FYtdCredit, FEndBalance )

select FYear,FPeriod,FAccountID,1141,FCurrencyID, FBeginBalanceFor,FDebitFor,FCreditFor,FYtdDebitFor,FYtdCreditFor,FEndBalanceFor,

FBeginBalance , FDebit, FCredit, FYtdDebit, FYtdCredit, FEndBalance from t_Balance where FAccountID in (1164) and FCurrencyID =1 and FDetailID=0 and FYear=2011

--修改2012年后5001.001.001.001的期初和期末数据

update t_Balance set FBeginBalance=FBeginBalance+2355973.8149,FBeginBalanceFor=FBeginBalancefor+2355973.8149,

FEndBalance=fendbalance+2355973.8149 ,FEndBalanceFor=fendbalancefor+2355973.8149

--select * from t_Balance

where FYear>=2012 and FAccountID=1164 and FDetailID=1141

----2、插入5001.001.001.999科目2011年数据

----插入综合本位币记录

INSERT INTO t_Balance ( FYear,FPeriod,FAccountID,fdetailid,FCurrencyID, FBeginBalanceFor,FDebitFor,FCreditFor,FYtdDebitFor,FYtdCreditFor,FEndBalanceFor,

FBeginBalance , FDebit, FCredit, FYtdDebit, FYtdCredit, FEndBalance )

select FYear,FPeriod,FAccountID,1141,FCurrencyID, FBeginBalanceFor,FDebitFor,FCreditFor,FYtdDebitFor,FYtdCreditFor,FEndBalanceFor,

FBeginBalance , FDebit, FCredit, FYtdDebit, FYtdCredit, FEndBalance from t_Balance where FAccountID in (1166) and FCurrencyID =0 and FDetailID=0 and FYear=2011

----插入人民币记录

INSERT INTO t_Balance ( FYear,FPeriod,FAccountID,fdetailid,FCurrencyID, FBeginBalanceFor,FDebitFor,FCreditFor,FYtdDebitFor,FYtdCreditFor,FEndBalanceFor,

FBeginBalance , FDebit, FCredit, FYtdDebit, FYtdCredit, FEndBalance )

select FYear,FPeriod,FAccountID,1141,FCurrencyID, FBeginBalanceFor,FDebitFor,FCreditFor,FYtdDebitFor,FYtdCreditFor,FEndBalanceFor,

FBeginBalance , FDebit, FCredit, FYtdDebit, FYtdCredit, FEndBalance from t_Balance where FAccountID in (1166) and FCurrencyID =1 and FDetailID=0 and FYear=2011

----3、插入5001.001.003科目2011年数据

----插入综合本位币记录

INSERT INTO t_Balance ( FYear,FPeriod,FAccountID,fdetailid,FCurrencyID, FBeginBalanceFor,FDebitFor,FCreditFor,FYtdDebitFor,FYtdCreditFor,FEndBalanceFor,

FBeginBalance , FDebit, FCredit, FYtdDebit, FYtdCredit, FEndBalance )

select FYear,FPeriod,FAccountID,1141,FCurrencyID, FBeginBalanceFor,FDebitFor,FCreditFor,FYtdDebitFor,FYtdCreditFor,FEndBalanceFor,

FBeginBalance , FDebit, FCredit, FYtdDebit, FYtdCredit, FEndBalance from t_Balance where FAccountID in (1170) and FCurrencyID =0 and FDetailID=0 and FYear=2011

----插入人民币记录

INSERT INTO t_Balance ( FYear,FPeriod,FAccountID,fdetailid,FCurrencyID, FBeginBalanceFor,FDebitFor,FCreditFor,FYtdDebitFor,FYtdCreditFor,FEndBalanceFor,

FBeginBalance , FDebit, FCredit, FYtdDebit, FYtdCredit, FEndBalance )

select FYear,FPeriod,FAccountID,1141,FCurrencyID, FBeginBalanceFor,FDebitFor,FCreditFor,FYtdDebitFor,FYtdCreditFor,FEndBalanceFor,

FBeginBalance , FDebit, FCredit, FYtdDebit, FYtdCredit, FEndBalance from t_Balance where FAccountID in (1170) and FCurrencyID =1 and FDetailID=0 and FYear=2011

update t_Balance set FBeginBalance=FBeginBalance+3129910.85,FBeginBalanceFor=FBeginBalancefor+3129910.85,

FEndBalance=fendbalance+3129910.85 ,FEndBalanceFor=fendbalancefor+3129910.85

--select * from t_Balance

where FYear>=2012 and FAccountID=1170 and FDetailID=1141

update t_Balance set FBeginBalance=FBeginBalance+3129910.85,FBeginBalanceFor=FBeginBalancefor+3129910.85,

FEndBalance=fendbalance+3129910.85 ,FEndBalanceFor=fendbalancefor+3129910.85

--select * from t_Balance

where FYear>=2014 and FAccountID=1170 and FDetailID=0

update a set FDetailID=1141

--select FVoucherID ,fdetailid

from t_VoucherEntry a

where FDetailID=0 and FAccountID in

(select FAccountID from t_Account where FNumber in ('5001.001.001.001','5001.001.001.999','5001.001.003'))

[注意]

执行语句前请备份好账套。