1．业务背景

由于客户网络状况原因，偶有发生极短时间内重复点击审核操作（常出现于特定用户或特定机器上），从操作日志上也能看到两条时间相邻极短的审核记录，在程序上，由于这种情况属于非常规场景，未做太多控制与校验（直至19年10月补丁仍未有相关控制）；本例分析的是应付单重复点击【审核】而产生重复钩稽日志的数据处理方案。

图1-1 上机操作日志中应付单有重复审核的情况

2．案例分析

2.1 业务情况

     采购退料单单价跟委外入库成本核算的单价不一致，发现材料成本核算的金额刚好是实际金额的4倍，查看钩稽日志发现两条钩稽序号相近，内容基本一致的记录（除了应付单那行对应的【本次钩稽金额本位币】不一致，因为这个字段是核算反写的）由于钩稽日志重复，导致核算时重复核算/产生异常成本调整单，核算出的成本已经翻了4倍，可以从钩稽日志看到错误的数据具体表现为：本次钩稽金额本位币为本次钩稽金额的4倍 / 产生了不应存在的成本调整单，异常金额为本次钩稽金额的3倍，且正负相反。

 图2-1 材料成本核算的金额刚好是实际金额的4倍

图2-2 重复钩稽日志记录

2.2 数据修复分析

2.2.1 如果是当期的重复数据，单量少时可以直接反审核应付单，对应的钩稽日志就会跟着删除，然后重新审核即可；

如果单量多的时候，可以通过后台删除其中一张钩稽日志处理，然后重新核算即可；脚本参考如下：

--1 BAK

SELECT * INTO T_HS_PURHOOKLOG_BAK190723 FROM T_HS_PURHOOKLOG

SELECT * INTO T_HS_PURHOOKLOGENTRY_BAK190723 FROM T_HS_PURHOOKLOGENTRY

SELECT * INTO T_HS_PURHOOKLOGENTRY_C_BAK190723 FROM T_HS_PURHOOKLOGENTRY_C

SELECT * INTO T_HS_PURHOOKLOGDETAIL_BAK190723 FROM T_HS_PURHOOKLOGDETAIL

--2 DEL

DECLARE @hookid INT

  , @Sql NVARCHAR(4000);

DECLARE TName CURSOR

FOR

SELECT DISTINCT

        MAX(HE.FID)

FROM    T_HS_PURHOOKLOG H

INNER JOIN T_HS_PURHOOKLOGENTRY HE ON HE.FID = H.FID

WHERE   1 = 1

        AND FIBTAG = '0'

GROUP BY HE.FHOOKEDBILLENTRYID

HAVING  COUNT(1) > 1;       

OPEN TName;

FETCH NEXT FROM TName INTO @hookid;

WHILE @@FETCH_STATUS = 0

BEGIN

    DELETE  T_HS_PURHOOKLOG

    WHERE   FID = @hookid;

    DELETE  T_HS_PURHOOKLOGDETAIL

    WHERE   FENTRYID IN ( SELECT    FENTRYID

                          FROM      T_HS_PURHOOKLOGENTRY

                          WHERE     FID = @hookid );

    DELETE  T_HS_PURHOOKLOGENTRY_C

    WHERE   FID = @hookid;

    DELETE  T_HS_PURHOOKLOGENTRY

    WHERE   FID = @hookid;

    FETCH NEXT FROM TName INTO @hookid;

END;

CLOSE TName;

DEALLOCATE TName;

2.2.2 如果是历史期间的数据，则无法通过修复处理，因为核算金额已经结转到余额表，此时只能本期新增入库成本调整调整；但由于涉及单据众多，需要统计问题数据方便客户前台做单调整，参考实现脚本如下：

--脚本思路分析：

--由于涉及两大类单据：采购入库单和采购退料单，可用UNION汇总结果；

--同时根据前台录单必录项，关联主表抓取编码/名称

--调整金额为正确金额 - 错误核算金额

--由于原币正确，核算的本位币错误，且原币与本位币币别一致，

--用正确金额减去错误金额即为调整金额

SELECT STOCKORG.FNUMBER 库存组织编码,BS.FNUMBER 供应商编码,BM.FNUMBER 物料编码,BSK.FNUMBER 仓库编码,

CASE WHEN FF100001.FNUMBER <> '' THEN FF100001.FNUMBER

WHEN FF100007.FNUMBER <> '' THEN FF100007.FNUMBER

WHEN FF100018.FNUMBER <> '' THEN FF100018.FNUMBER

ELSE  '/'

END AS 仓位编码,

HE.FHOOKAMOUNTFOR - HE.FHOOKAMOUNT AS 调整金额,

'采购入库单' 单据类型,SIS.FBILLNO,SISE.FSEQ,SIS.FBILLTYPEID,SIS.FID,SISE.FENTRYID,APB.FBILLNO,APBE.FSEQ,APB.FBILLTYPEID,APB.FID,APBE.FENTRYID

--UPDATE HADE SET HADE.FSRCBILLTYPEID = SIS.FBILLTYPEID,FSRCBILLID = SIS.FID,FSRCBILLNO = SIS.FBILLNO,FSRCENTRYID = SISE.FENTRYID,FSRCSEQ = SISE.FSEQ,FINVOICEBILLTYPEID = APB.FBILLTYPEID,FINVOICEBILLNO = APB.FBILLNO,FINVOICESEQ = APBE.FSEQ

--UPDATE HADE SET FCrossPeriodAdjust = 1

--UPDATE HAD SET FBUSINESSTYPE = 8

FROM T_HS_PURHOOKLOG H

INNER JOIN T_HS_PURHOOKLOGENTRY HE ON HE.FID = H.FID

INNER JOIN T_AP_PAYABLEENTRY APBE ON HE.FHOOKEDBILLENTRYID = APBE.FENTRYID

INNER JOIN T_AP_PAYABLE APB ON APBE.FID = APB.FID AND HE.FHOOKEDBILLID = APB.FID

INNER JOIN T_AP_PAYABLE_LK APBK ON APBE.FENTRYID = APBK.FENTRYID AND APBK.FSTABLENAME = 'T_STK_INSTOCKENTRY'

INNER JOIN T_STK_INSTOCKENTRY SISE ON APBK.FSID = SISE.FENTRYID

INNER JOIN T_STK_INSTOCK SIS ON SIS.FID = SISE.FID

INNER JOIN T_BD_SUPPLIER BS ON SIS.FSUPPLIERID = BS.FSUPPLIERID

INNER JOIN T_ORG_ORGANIZATIONS STOCKORG ON SIS.FSTOCKORGID = STOCKORG.FORGID

INNER JOIN T_BD_MATERIAL BM ON SISE.FMATERIALID = BM.FMATERIALID

INNER JOIN T_BD_STOCK BSK ON BSK.FSTOCKID = SISE.FSTOCKID

INNER JOIN T_BAS_FLEXVALUESDETAIL BFVD ON BFVD.FID = SISE.FSTOCKLOCID

LEFT OUTER JOIN T_BAS_FLEXVALUESENTRY FF100001 ON BFVD.FF100001 = FF100001.FENTRYID

LEFT OUTER JOIN T_BAS_FLEXVALUESENTRY FF100007 ON BFVD.FF100007 = FF100007.FENTRYID

LEFT OUTER JOIN T_BAS_FLEXVALUESENTRY FF100018 ON BFVD.FF100018 = FF100018.FENTRYID

RIGHT OUTER JOIN T_HS_ADJUSTMENTBILLENTRY HADE ON HADE.FADJUSTMENTAMOUNT = HE.FHOOKAMOUNTFOR - HE.FHOOKAMOUNT AND HADE.FMATERIALID = SISE.FMATERIALID

INNER JOIN T_HS_ADJUSTMENTBILL HAD ON HADE.FID = HAD.FID

WHERE 1 = 1

--AND H.FPERIOD = 9

AND HE.FHOOKAMOUNTFOR <> 0

AND HE.FCURRENCYID = HE.FMAINCURRENCYID

AND HE.FHOOKAMOUNT = HE.FHOOKAMOUNTFOR * 4

AND YEAR(HAD.FCREATEDATE) = 2019 AND MONTH(HAD.FCREATEDATE) = 11

AND HAD.FACCTORGID = 111154

AND HAD.FBUSINESSTYPE = 1

UNION

SELECT STOCKORG.FNUMBER 库存组织编码,BS.FNUMBER 供应商编码,BM.FNUMBER 物料编码,BSK.FNUMBER 仓库编码,

CASE WHEN FF100001.FNUMBER <> '' THEN FF100001.FNUMBER

WHEN FF100007.FNUMBER <> '' THEN FF100007.FNUMBER

WHEN FF100018.FNUMBER <> '' THEN FF100018.FNUMBER

ELSE  '/'

END AS 仓位编码,

HE.FHOOKAMOUNTFOR - HE.FHOOKAMOUNT AS 调整金额,

'采购退料单' 单据类型,SIS.FBILLNO,SISE.FSEQ,SIS.FBILLTYPEID,SIS.FID,SISE.FENTRYID,APB.FBILLNO,APBE.FSEQ,APB.FBILLTYPEID,APB.FID,APBE.FENTRYID

--UPDATE HADE SET HADE.FSRCBI