【概述】

当科目表的父级字段fparentid异常时，往往会伴随着余额表父级汇总金额的问题。此种场景下如何修改父级的数据？

当科目调整需要重算父级科目余额时，该如何实现？

【步骤一】

先检查科目上下级关系的正确性

参考：科目父级内码错误修复

【步骤二】

创建中间表

if not exists (select 1 from sys.tables  where name='T_chlGL_BALANCE')

CREATE TABLE [dbo].[T_chlGL_BALANCE]([FACCOUNTBOOKID] [int] NOT NULL  DEFAULT ((0)),[FYEAR] [int] NOT NULL  DEFAULT ((0)),[FPERIOD] [int] NOT NULL  DEFAULT ((0)),[FACCOUNTID] [int] NOT NULL   DEFAULT ((0)),[FDETAILID] [int] NOT NULL  DEFAULT ((0)),[FCURRENCYID] [int] NOT NULL  DEFAULT ((0)),[FBEGINBALANCEFOR] [decimal](23, 10) NULL DEFAULT ((0)),[FBEGINBALANCE] [decimal](23, 10) NULL DEFAULT ((0)),[FDEBITFOR] [decimal](23, 10) NULL DEFAULT ((0)),[FDEBIT] [decimal](23, 10) NULL DEFAULT ((0)),[FCREDITFOR] [decimal](23, 10) NULL  DEFAULT ((0)),[FCREDIT] [decimal](23, 10) NULL DEFAULT ((0)),[FYTDDEBITFOR] [decimal](23, 10) NULL DEFAULT ((0)),[FYTDDEBIT] [decimal](23, 10) NULL  DEFAULT ((0)),[FYTDCREDITFOR] [decimal](23, 10) NULL   DEFAULT ((0)),[FYTDCREDIT] [decimal](23, 10) NULL DEFAULT ((0)),[FENDBALANCEFOR] [decimal](23, 10) NULL  DEFAULT ((0)),[FENDBALANCE] [decimal](23, 10) NULL  DEFAULT ((0)),[FADJUSTPERIOD] [int] NOT NULL DEFAULT ((0)),[FYEARPERIOD]  AS ([FYear]*(100)+[FPeriod]),

CONSTRAINT [PK_chlgl_BALANCE] PRIMARY KEY CLUSTERED ([FACCOUNTBOOKID] ASC,[FYEAR] ASC,[FPERIOD] ASC,[FACCOUNTID] ASC,[FDETAILID] ASC,[FCURRENCYID] ASC,[FADJUSTPERIOD] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

【步骤三】

往中间表插入明细科目数据（示例）

insert into [T_chlGL_BALANCE]( FACCOUNTBOOKID,FYEAR,FPERIOD,FACCOUNTID,FDETAILID,FCURRENCYID,FBEGINBALANCEFOR,FBEGINBALANCE,FDEBITFOR,FDEBIT,FCREDITFOR,FCREDIT, FYTDDEBITFOR,FYTDDEBIT,FYTDCREDITFOR,FYTDCREDIT,FENDBALANCEFOR,FENDBALANCE)

select

FACCOUNTBOOKID,FYEAR,FPERIOD,FACCOUNTID,FDETAILID,FCURRENCYID,FBEGINBALANCEFOR,FBEGINBALANCE,FDEBITFOR,FDEBIT,FCREDITFOR,FCREDIT, FYTDDEBITFOR,FYTDDEBIT,FYTDCREDITFOR,FYTDCREDIT,FENDBALANCEFOR,FENDBALANCE

from t_g_balance where FACCOUNTID in (select  facctid from t_bd_account where fisdetail=1)

【步骤四】生成上级科目的汇总数据

declare @flevel int

set @flevel=(select max(FLEVEL) from T_BD_ACCOUNT where FISDETAIL=0 )

while @flevel>0

begin

insert into [T_chlGL_BALANCE](FACCOUNTBOOKID,FYEAR,FPERIOD,FACCOUNTID,FDETAILID,FCURRENCYID,FBEGINBALANCEFOR,FBEGINBALANCE,FDEBITFOR,FDEBIT,FCREDITFOR,FCREDIT,FYTDDEBITFOR,FYTDDEBIT,FYTDCREDITFOR,FYTDCREDIT,FENDBALANCEFOR,FENDBALANCE)

select FACCOUNTBOOKID, FYEAR,FPERIOD,b.FPARENTID,0 FDETAILID,FCURRENCYID,

sum(FBEGINBALANCEFOR),sum(FBEGINBALANCE),sum(FDEBITFOR),sum(FDEBIT),sum(FCREDITFOR),sum(FCREDIT),

sum(FYTDDEBITFOR),sum(FYTDDEBIT),sum(FYTDCREDITFOR),sum(FYTDCREDIT),sum(FENDBALANCEFOR),sum(FENDBALANCE)

from [T_chlGL_BALANCE] a join T_BD_ACCOUNT b on a.FACCOUNTID=b.FACCTID and b.FLEVEL=@flevel+1

where a.fdetailid=0

group by FACCOUNTBOOKID,b.FPARENTID, FYEAR,FPERIOD,FCURRENCYID

set @flevel=@flevel-1

end

go

【步骤五】根据实际需要应用生成的数据（更新余额表也好，插回余额表也好）

注意：此脚本仅供交流使用，若非了解数据库，不能直接更改生产数据库中的数据。