免費(fèi)問答

你好?? 本腳本修復(fù)以下數(shù)據(jù)問題： 核算維度V表存在核算維度組合相同但FID不同的記錄，導(dǎo)致科目余額表等報(bào)表核算維度有相同的記錄不合并的問題 注意事項(xiàng)： 1，備份正式賬套，并恢復(fù)成測(cè)試賬套 2，在測(cè)試賬套上執(zhí)行修復(fù)操作，檢查數(shù)據(jù)修復(fù)成功，且憑證、科目余額表等數(shù)據(jù)正確后再在正式賬套上執(zhí)行 3，查看未過賬余額數(shù)據(jù)時(shí)需要在憑證查詢的菜單中執(zhí)行“刷新未過賬余額表” 按以下步驟執(zhí)行： 0，修改腳本中的日期（2017-09-01）為發(fā)現(xiàn)問題的期間的第一天（腳本只處理此日期及以后的憑證，大大節(jié)省時(shí)間） 1，所有賬簿反結(jié)賬反過賬到發(fā)現(xiàn)問題前一個(gè)期間。 2，執(zhí)行腳本以修復(fù)總賬憑證及業(yè)務(wù)憑證表的數(shù)據(jù)。 3，重新過賬以修復(fù)科目余額表、數(shù)據(jù)余額表以及損益余額表的數(shù)據(jù) 4，檢查數(shù)據(jù)修復(fù)情況。 */ 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; --備份指定日期以后通過憑證生成新產(chǎn)生的憑證分錄數(shù)據(jù)及核算維度V表 IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(NT_GL_VOUCHERENTRY_20180101) AND OBJECTPROPERTY(ID, NISUSERTABLE) = 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(NT_BAS_VOUCHERENTRY_20180101) AND OBJECTPROPERTY(ID, NISUSERTABLE) = 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(NT_BD_FLEXITEMDETAILV_20180101) AND OBJECTPROPERTY(ID, NISUSERTABLE) = 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; --更新輔助資料核算維度為空的值為默認(rèn)的空格 ? ?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 ?--找出所有重復(fù)的核算維度，更新憑證等表中的維度組合ID為最小的那個(gè)，且保留FID值最小的那個(gè)，刪除此后生成的重復(fù)維度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; ?--檢查是否還存在重復(fù)的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 --重新創(chuàng)建唯一索引 CREATE UNIQUE NONCLUSTERED INDEX IDX_FLEXITEMUNIQUE ON T_BD_FLEXITEMDETAILV (FCALCOL); GO