通過SQL跟蹤，得到出納模塊開賬、結轉(zhuǎn)的過程：

1、出納開賬

1.1 出納管理_基礎檔案類型

INSERT INTO CN_LevelCLass(ClassID_N,ClassName,ClassName_Eng,TableName,ClassFlag,IsCancel,IsAutoSave,IsUseMCode,IsAsk,LoadID,lYear)

SELECT ClassID_N,ClassName,ClassName_Eng,TableName,ClassFlag,IsCancel,IsAutoSave,IsUseMCode,IsAsk,LoadID,2015 FROM CN_LevelCLass

WHERE lYear=2022

1.2 出納管理賬套參數(shù)

INSERT INTO CN_AcctParam (ParaName,ParaValue,PF,UserID,lYear)

SELECT ParaName,ParaValue,PF,UserID,2022 FROM CN_AcctParam WHERE lYear=2021

1.3 出納日記賬賬戶

INSERT INTO CN_AcctInfo (ID_Old,AcctName,AcctType,Period,PreBalance,PreDebit,PreCredit,PreBankBalance,FPreBalance,FPreDebit,FPreCredit,FPreBankBalance,CurrencyID,CreateDate,Creator,BankName,BankAcct,UnitName,Province,City,IsUsed,Subject2ndClass,Subject2ndFld,SubjectCode,IsFreeze,UnitType,ProjectClass,FullBankName,lModify,lYear,csAcctNum,lClose12,cBCode,lParentID,lHaveChild)

SELECT ID_Old,AcctName,AcctType,Period,PreBalance,PreDebit,PreCredit,PreBankBalance,FPreBalance,FPreDebit,FPreCredit,FPreBankBalance,CurrencyID,CreateDate,Creator,BankName,BankAcct,UnitName,Province,City,IsUsed,Subject2ndClass,Subject2ndFld,SubjectCode,IsFreeze,UnitType,ProjectClass,FullBankName,0,2015,csAcctNum,0,cBCode,lParentID,lHaveChild FROM CN_AcctInfo WHERE lYear=2021

1.4 開賬\結轉(zhuǎn)標志

INSERT INTO CN_AcctParam (ParaName,ParaValue,PF,lYear) values(‘KaiSign‘,1,‘開賬標志‘,‘2015‘)

go

INSERT INTO CN_AcctParam (ParaName,ParaValue,PF,lYear) values(‘Carry_Over_Sign‘,0,‘結轉(zhuǎn)標志‘,‘2015‘)

1.5、2021年前月份的出納月結標志改成 “已月結”；

UPDATE GL_mend SET bFlag_SC = 1 WHERE iyear < 2015

2.出納結轉(zhuǎn)：

2.1、更新出納管理用戶

DELETE FROM CN_UserInfo WHERE ISNULL(UserCode,‘‘) =‘‘

DELETE CN_UserInfo FROM CN_UserInfo AS A LEFT JOIN UA_User AS B ON A.UserCode=B.cUser_Id WHERE B.cUser_Id IS NULL

go

INSERT INTO CN_UserInfo(UserCode)

SELECT cUser_Id AS UserCode FROM UA_User AS a LEFT JOIN CN_UserInfo AS b ON a.iAdmin =0 AND a.cUser_Id = b.UserCode WHERE b.UserCode IS NULL

go

2.2、更新出納日記賬與出納自定義列對應關系：

Insert into CN_AcctBookCustomRule (AcctID,CustomColID,ClassID,FieldID,lYear)

SELECT AcctID,CustomColID,ClassID,FieldID,2015 FROM CN_AcctBookCustomRule

go

update CN_AcctBookCustomRule set AcctID=(select id from cn_acctinfo where lyear=2015 and id_old=(select id_old from cn_acctinfo WHERE id=CN_AcctBookCustomRule.acctid))

where lyear=2015

go

2.3、 更新賬號：賬戶ID

declare @i int declare @id_old int declare @iparentid int declare @id int declare @id1 int

select @i=count(id) from cn_acctinfo where lyear=2015 and lparentid<>0; ---獲取賬號數(shù)；

set @id=0

while @i>0

begin

select top 1 @id= id from cn_acctinfo where lyear=2015 and lparentid<>0 and id>@id order by id --取出第一個子賬號的賬戶ID；

select @id_old= id_old from cn_acctinfo where id=@id --通過子賬戶ID找到對應的id_old；

select @iparentid= lparentid from cn_acctinfo where id =@id_old --通過old賬戶找到對應的iparentid；

select @id1= id from cn_acctinfo where lyear=2015 and id_old=@iparentid --通過iparentid找到對應的ID；

update cn_acctinfo set lparentid=@id1 where id=@id --更新子賬號的lparentid值；

set @i = @i - 1

end

2.4、 更新日記賬 賬戶ID、lparentid值

update cn_acctbook set acctid= cn_acctinfo.lparentid from cn_acctbook --更新賬戶ID值；

left join cn_acctinfo on cn_acctinfo.id=cn_acctbook.acctid

where cn_acctbook.lyear=2015 and cn_acctbook.lparentid=0

go

update cn_acctbook set lparentid=acctid where acctid<>lparentid and lyear=2015 and AcctDate<‘2015-01-01‘

2.5、更新用戶狀態(tài)；

update cn_acctinfo set isused =2 where lyear=2015 ---1表示：啟用 2表示：暫停