破析U8+出纳模块开账、结转业务

通过SQL跟踪，得到出纳模块开账、结转的过程：

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 出纳管理账套参数

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 开账\结转标志

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,‘结转标志‘,‘2015‘)

1.5、2021年前月份的出纳月结标志改成 “已月结”；

UPDATE GL_mend SET bFlag_SC = 1 WHERE iyear < 2015

2.出纳结转：

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; ---获取账号数；

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、更新用户状态；

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