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.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_PUR_MRBENTRY'

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

INNER JOIN T_PUR_MRB 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

INNER 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

统计结果示例如下：

图2-3 后台统计需要进行调整的数据展示

2.2.3 但是本期调整上期的异常入库金额，可能会导致本期算出来的入库单价偏低甚至为负单价（因为客户大部分都是采购入库单发生异常，相当于上期入库成本偏高）本期通过负金额的成本调整单调整后，入库成本就会拉低；而且上期部分物料已经发生了出库业务，沿用了错误的入库成本。但是系统控制手工新增的入库成本调整单是不会生成出库成本调整单（只有业务类型为【期初入库调整】/【期初应付调整】的成本调整单才会根据存货参数产生相应的成本调整单，默认为按照【上期出库、结存比例】生成）

此时则需要通过后台更新入库成本调整单的标识，使得系统可以纳入该部分单据去生成出库成本调整单，同时通过与备份表的关联，补充入库和应付的单号、分录行、内码等信息（前台手工无法录入），使其可以归集到具体单据上，保证入库暂估对账可以对上，且方便后续查账时溯源，具体更新脚本于2.2.2中，此处截取更新部分做展示：

--补充入库和应付的单号、分录行、内码等信息

--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

--更新成本调整单的FCrossPeriodAdjust为1，手工做的是0

--更新成本调整单的单据类型FBUSINESSTYPE为8，手工做的是1

--UPDATE HADE SET FCrossPeriodAdjust = 1

--UPDATE HAD SET FBUSINESSTYPE = 8

调整效果示例如下：

3 后续的规避和解决方案

应付单重复审核，成本调整单导致重复生成问题，系统需要进行控制。列入标准产品下个补丁版本支持。需求实现方案：重复审核时生成重复钩稽日志，通过应付单内码进行索引控制，重复时进行报错提醒。预计2020年1月底前发布。

当前变通处理方案：平台参数两秒内不允许重复审核，存货核算提供钩稽日志重复生成唯一索引校验语句，处理方案如下：

3.1 平台参数两秒内不允许重复审核

找到应用服务器下 \Kingdee\K3Cloud\ManageSite\App_Data\Common.config，修改或添加以下节点内容：

<add key=”CheckBarItemBusyInterval”value=”0”>

将value= ”0”的0改成2，即可控制2s内不重复审核；

3.2 添加钩稽日志重复生成唯一索引校验语句

CREATE UNIQUE INDEX IDX_HS_PURHOOKLOGENTRY_U

ON T_HS_PURHOOKLOGENTRY(FHOOKEDBILLENTRYID) WHERE FIBTAG = '0';