修复科目余额表等核算维度相同不合并的问题
作者： 日期：2018-07-02 来源： 关注：2050
本脚本修复以下数据问题：
核算维度V表存在核算维度组合相同但FID不同的记录，导致科目余额表等报表核算维度有相同的记录不合并的问题
注意事项：
1，备份正式账套，并恢复成测试账套
2，在测试账套上执行修复操作，检查数据修复成功，且凭证、科目余额表等数据正确后再在正式账套上执行
3，查看未过账余额数据时需要在凭证查询的菜单中执行“刷新未过账余额表”
按以下步骤执行：
0，修改脚本中的日期（2017-09-01）为发现问题的期间的第一天（脚本只处理此日期及以后的凭证，大大节省时间）
1，所有账簿反结账反过账到发现问题前一个期间。
2，执行脚本以修复总账凭证及业务凭证表的数据。
3，重新过账以修复科目余额表、数据余额表以及损益余额表的数据
4，检查数据修复情况。
*/
DECLARE @glvid int,@bizvid int;
SELECT @glvid = MIN(FVOUCHERID) FROM T_GL_Voucher WHERE FCREATEDATE>='2017-09-01';
SELECT @bizvid = MIN(FVOUCHERID) FROM T_BAS_Voucher WHERE FCREATEDATE>='2017-09-01';
--备份指定日期以后通过凭证生成新产生的凭证分录数据及核算维度V表
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'T_GL_VOUCHERENTRY_20180101') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
SELECT * INTO T_GL_VOUCHERENTRY_20180101 FROM T_GL_VOUCHERENTRY WHERE FVOUCHERID>=@glvid
AND FVOUCHERID IN(
SELECT DISTINCT
FVOUCHERID
FROM T_GL_VOUCHER V
JOIN T_BAS_BILLTYPE BT ON BT.FBILLFORMID = V.FSOURCEBILLKEY
);
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'T_BAS_VOUCHERENTRY_20180101') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
SELECT * INTO T_BAS_VOUCHERENTRY_20180101 FROM T_BAS_VOUCHERENTRY WHERE FVOUCHERID>=@bizvid
AND FVOUCHERID IN(
SELECT DISTINCT
FVOUCHERID
FROM T_BAS_VOUCHER V
JOIN T_BAS_BILLTYPE BT ON BT.FBILLFORMID = V.FSOURCEBILLKEY
);
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'T_BD_FLEXITEMDETAILV_20180101') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
SELECT * INTO T_BD_FLEXITEMDETAILV_20180101 FROM T_BD_FLEXITEMDETAILV;
--删除唯一索引
IF EXISTS(SELECT * FROM SYSINDEXES WHERE ID=OBJECT_ID('T_BD_FLEXITEMDETAILV') AND NAME='IDX_FLEXITEMUNIQUE')
DROP INDEX IDX_FLEXITEMUNIQUE ON T_BD_FLEXITEMDETAILV;
--开始批处理
BEGIN
DECLARE @flexfield VARCHAR(50) ,
@FCALCOL VARCHAR(2000) ,
@flexid1 INT ,
@flexid2 INT;
--更新辅助资料核算维度为空的值为默认的空格
DECLARE flex_cursore CURSOR
FOR
( SELECT FFLEXNUMBER
FROM T_BD_FLEXITEMPROPERTY
WHERE FVALUETYPE = 1
AND FDOCUMENTSTATUS = 'C'
);
OPEN flex_cursore;
FETCH NEXT FROM flex_cursore INTO @flexfield;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC( 'UPDATE T_BD_FLEXITEMDETAILV SET ' + @flexfield+ ' ='' '' WHERE '+@flexfield+' = '' '' AND LEN('+@flexfield+')<=1');
FETCH NEXT FROM flex_cursore INTO @flexfield;
END;
CLOSE flex_cursore;
DEALLOCATE flex_cursore;
DECLARE @cnt INT;
SELECT @cnt = COUNT(*)
FROM ( SELECT MIN(FID) fid1 ,
MAX(FID) fid2 ,
FCALCOL
FROM T_BD_FLEXITEMDETAILV
GROUP BY FCALCOL
) TM
WHERE fid1 <> fid2;
WHILE @cnt>0
BEGIN
--找出所有重复的核算维度，更新凭证等表中的维度组合ID为最小的那个，且保留FID值最小的那个，删除此后生成的重复维度ID
DECLARE flexid_cursore CURSOR
FOR
( SELECT fid1 ,
fid2 ,
FCALCOL
FROM ( SELECT MIN(FID) fid1 ,
MAX(FID) fid2 ,
FCALCOL
FROM T_BD_FLEXITEMDETAILV
GROUP BY FCALCOL
) TM
WHERE fid1 <> fid2
);
OPEN flexid_cursore;
FETCH NEXT FROM flexid_cursore INTO @flexid1, @flexid2, @FCALCOL;
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE T_GL_VOUCHERENTRY
SET FDETAILID = @flexid1
WHERE FDETAILID = @flexid2
AND FVOUCHERID>=@glvid
AND FVOUCHERID IN(
SELECT DISTINCT
FVOUCHERID
FROM T_GL_VOUCHER V
--JOIN T_BAS_BILLTYPE BT ON BT.FBILLFORMID = V.FSOURCEBILLKEY
);
UPDATE T_BAS_VOUCHERENTRY
SET FDETAILID = @flexid1
WHERE FDETAILID = @flexid2
AND FVOUCHERID>=@bizvid
AND FVOUCHERID IN(
SELECT DISTINCT
FVOUCHERID
FROM T_BAS_VOUCHER V
--JOIN T_BAS_BILLTYPE BT ON BT.FBILLFORMID = V.FSOURCEBILLKEY
);
DELETE T_BD_FLEXITEMDETAILV
WHERE FID > @flexid1
AND FCALCOL = @FCALCOL
AND FID NOT IN (
SELECT DISTINCT
FDETAILID
FROM T_GL_VOUCHERENTRY
UNION
SELECT DISTINCT
FDETAILID
FROM T_BAS_VOUCHERENTRY );
FETCH NEXT FROM flexid_cursore INTO @flexid1, @flexid2, @FCALCOL;
END;
CLOSE flexid_cursore;
DEALLOCATE flexid_cursore;
--检查是否还存在重复的ID
SELECT @cnt = COUNT(*)
FROM ( SELECT MIN(FID) fid1 ,
MAX(FID) fid2 ,
FCALCOL
FROM T_BD_FLEXITEMDETAILV
GROUP BY FCALCOL
) TM
WHERE fid1 <> fid2;
END;
END;
GO
--重新创建唯一索引
CREATE UNIQUE NONCLUSTERED INDEX IDX_FLEXITEMUNIQUE ON T_BD_FLEXITEMDETAILV (FCALCOL);
GO