K3迁移星空企业版常见问题-数量金额明细账未汇总

问题描述：K3账套查看数量金额明细账，三级科目的余额汇总到二级科目，二级科目余额汇总到一级科目。升级到企业版后一级科目、二级数量金额表余额为空，只有三级科目余额。

原因：K/3本身数据有问题，K/3的t_quantitybalance 表实际没有一级科目、二级科目余额，前台是通过软件做的汇总。

建议：先修复K/3数据，修复后再迁移。

直接脚本修复，相当于直接对所有期间数据库做校验，如果缺汇总数据则加上，影响较大。由于总账反结账余额表数据会自动删除，重新结账余额表数据会自动重写。故反结账到初始化期间，仅对初始化期间数据做脚本修复影响较小。但是如果总账反结账、反初始化，修复余额表，再重新结账，再做迁移操作，余额表数据为系统自动重写，需要核对总账相关报表数据是否发生变化。请根据实际情况综合评估是否需要反结账总账模块。

即时总账未做反结账操作，数据修复后，建议也要核对报表数据，重点核对有问题的科目及期间数据。

修复脚本如下：（仅供参考，未做过多实验，请谨慎使用）

--【步骤1】、创建中间表

if  exists (select 1 from sys.tables  where name='T_chlGL_quantityBALANCE')
drop table T_chlGL_quantityBALANCE
CREATE TABLE [dbo].T_chlGL_quantityBALANCE(    [FYear] [int] NOT NULL,    [FPeriod] [int] NOT NULL,    [FAccountID] [int] NOT NULL,
    [FDetailID] [int] NOT NULL,    [FCurrencyID] [int] NOT NULL,    [FBeginQty] [float] NOT NULL,    [FDebitQty] [float] NOT NULL,
    [FCreditQty] [float] NOT NULL,    [FYtdDebitQty] [float] NOT NULL,    [FYtdCreditQty] [float] NOT NULL,
    [FEndQty] [float] NOT NULL,    [FFrameWorkID] [int] NOT NULL,    [FIsAdjustPeriod] [int] NOT NULL,
 CONSTRAINT [pk_T_chlGL_quantityBALANCE] PRIMARY KEY CLUSTERED
(
    [FYear] ASC,    [FPeriod] ASC,    [FAccountID] ASC,    [FCurrencyID] ASC,    [FDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].T_chlGL_quantityBALANCE ADD  DEFAULT (0) FOR [FDetailID]
ALTER TABLE [dbo].T_chlGL_quantityBALANCE ADD  DEFAULT ((-1)) FOR [FFrameWorkID]
ALTER TABLE [dbo].T_chlGL_quantityBALANCE ADD  DEFAULT (0) FOR [FIsAdjustPeriod]

--【步骤2】、往中间表插入明细科目数据（示例）

insert into T_chlGL_quantityBALANCE( FYear,[FPeriod],[FAccountID],[FDetailID],[FCurrencyID],[FBeginQty],[FDebitQty],[FCreditQty],[FYtdDebitQty],
[FYtdCreditQty],[FEndQty],[FFrameWorkID],[FIsAdjustPeriod])
select
FYear,[FPeriod],[FAccountID],[FDetailID],[FCurrencyID],[FBeginQty],[FDebitQty],[FCreditQty],[FYtdDebitQty],
[FYtdCreditQty],[FEndQty],[FFrameWorkID],[FIsAdjustPeriod]
from t_quantitybalance where FACCOUNTID in (select  FAccountID from t_account where FDetail=1)

--【步骤3】、生成上级科目的汇总数据

declare @flevel int
set @flevel=(select max(FLEVEL) from T_ACCOUNT where FDetail=0 )
while @flevel>0
begin
insert into T_chlGL_quantityBALANCE( FYear,[FPeriod],[FAccountID],[FDetailID],[FCurrencyID],[FBeginQty],[FDebitQty],
[FCreditQty],[FYtdCreditQty],[FYtdDebitQty],
[FEndQty],[FFrameWorkID],[FIsAdjustPeriod])
select fyear,FPERIOD,b.FPARENTID,0 FDETAILID,a.FCURRENCYID,
sum(FBeginQty),sum(FDebitQty),sum(FCreditQty),sum(FYtdCreditQty),sum(FYtdDebitQty),sum(FEndQty),-1,0
from T_chlGL_quantityBALANCE a join T_ACCOUNT b on a.FACCOUNTID=b.FAccountID and b.FLEVEL=@flevel+1
where a.fdetailid=0
group by b.FPARENTID, FYEAR,FPERIOD,a.FCURRENCYID
set @flevel=@flevel-1
end

go

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

insert into t_QuantityBalance
select * from T_chlGL_quantityBALANCE where FAccountID in(select FAccountID from t_Account where FDetail=0) except
select * from t_QuantityBalance