金蝶云数据库骚操作——总账非明细科目余额重算

【概述】

当科目表的父级字段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

FACCOUNTBO