【异常分析】即时库存与序列号数量不一致问题分析

问题现象

  问题:如图发现即时库存和对应序列号在库数量不一致。  


分析步骤

 1.查询系统即时库存和对应序列号不一致情况。

/*

查:即时库存ID对应和序列号数量不一致的数据。

注明:账套中物料基本单位和序列号单位换算比例均为1:1 则此脚本中序列号数量和即时库存数量不做单位换算直接比较

*/

SELECT  INV.FID '即时库存ID' ,

        ISNULL(A.FSERIALQTY, 0) '序列号数量' ,

        INV.FBASEQTY '即时库存基本单位数量'

FROM    T_STK_INVENTORY INV

        INNER JOIN dbo.T_BD_MATERIALSTOCK TMS ON INV.FMATERIALID = TMS.FMATERIALID

        LEFT JOIN ( SELECT  T1.FINVID ,

                            COUNT(1) AS FSERIALQTY

                    FROM    T_BD_SERIALMASTER T0

                            INNER JOIN T_BD_SERIALBILLTRACE T1 ON T0.FSERIALID = T1.FSERIALID

                            INNER JOIN ( SELECT MAX(FBILLTRACEID) fbilltraceid

                                         FROM   T_BD_SERIALBILLTRACE

                                         WHERE  ( ISNULL(FINVID, ' ') <> ' ' )

                                         GROUP BY FSERIALID

                                       ) t2 ON T1.FBILLTRACEID = t2.fbilltraceid

                    WHERE   ( ( T0.FFORBIDSTATUS = 'A'

                              AND T1.FSTATE = '1')

                            ) GROUP BY T1.FINVID

                  ) A ON A.FINVID = INV.FID

WHERE   TMS.FISSNMANAGE = '1'

        AND ( ( A.FINVID IS NOT NULL

                AND A.FSERIALQTY <> INV.FBASEQTY )

              OR ( A.FINVID IS NULL

                   AND INV.FBASEQTY <> 0  ) );

 2.选择其中某一条即时库存分析其涉及哪些出入库单据 。

SELECT Distinct FSOURFORMID FROM T_STK_INVENTORYLOG WHERE FINVENTORYID = ''

 3.根据2步中的单据出入库流转情况查看该维度对应序列号的出入库情况。

SELECT A.FFlag,FSERIALNO,A.FSOURFORMID,A.FUPDATETIME,FENTRYID FROM (

SELECT FUPDATETIME,FSOURFORMID,FFlag,FSERIALNO,FSERIALID,FENTRYID FROM dbo.T_STK_INVINITSERIAL a

INNER JOIN (SELECT FSOURENTRYID,FUPDATETIME,FSOURFORMID,CASE WHEN FUPDATEBASEQTY > 0 THEN '+' ELSE '-' END FFlag FROM dbo.T_STK_INVENTORYLOG WHERE FINVENTORYID = '52540064-f5d6-a67f-11e6-ec088eba06de' AND FSOURFORMID = 'STK_InvInit') b

ON a.FENTRYID = b.FSOURENTRYID 

UNION ALL 

SELECT FUPDATETIME,FSOURFORMID,FFlag,FSERIALNO,FSERIALID,FENTRYID FROM dbo.T_SAL_OUTSTOCKSERIAL a

INNER JOIN (SELECT FSOURENTRYID,FUPDATETIME,FSOURFORMID,CASE WHEN FUPDATEBASEQTY > 0 THEN '+' ELSE '-' END FFlag FROM dbo.T_STK_INVENTORYLOG WHERE FINVENTORYID = '52540064-f5d6-a67f-11e6-ec088eba06de' AND FSOURFORMID = 'SAL_OUTSTOCK') b

ON a.FENTRYID = b.FSOURENTRYID 

UNION ALL

SELECT FUPDATETIME,FSOURFORMID,FFlag,FSERIALNO,FSERIALID,FENTRYID FROM dbo.T_STK_MISDELIVERYSERIAL a

INNER JOIN (SELECT FSOURENTRYID,FUPDATETIME,FSOURFORMID,CASE WHEN FUPDATEBASEQTY > 0 THEN '+' ELSE '-' END FFlag FROM dbo.T_STK_INVENTORYLOG WHERE FINVENTORYID = '52540064-f5d6-a67f-11e6-ec088eba06de' AND FSOURFORMID = 'STK_MisDelivery') b

ON a.FENTRYID = b.FSOURENTRYID 

UNION ALL

SELECT FUPDATETIME,FSOURFORMID,FFlag,FSERIALNO,FSERIALID,FENTRYID FROM dbo.T_SAL_RETURNSTOCKSERIAL a

INNER JOIN (SELECT FSOURENTRYID,FUPDATETIME,FSOURFORMID,CASE WHEN FUPDATEBASEQTY > 0 THEN '+' ELSE '-' END FFlag FROM dbo.T_STK_INVENTORYLOG WHERE FINVENTORYID = '52540064-f5d6-a67f-11e6-ec088eba06de' AND FSOURFORMID = 'SAL_RETURNSTOCK') b

ON a.FENTRYID = b.FSOURENTRYID 

UNION ALL

SELECT FUPDATETIME,FSOURFORMID,FFlag,FSERIALNO,FSERIALID,FENTRYID FROM dbo.T_STK_STKTRANSFERINSERIAL a

INNER JOIN (SELECT FSOURENTRYID,FUPDATETIME,FSOURFORMID,CASE WHEN FUPDATEBASEQTY > 0 THEN '+' ELSE '-' END FFlag FROM dbo.T_STK_INVENTORYLOG WHERE FINVENTORYID = '52540064-f5d6-a67f-11e6-ec088eba06de' AND FSOURFORMID = 'STK_TransferDirect') b

ON a.FENTRYID = b.FSOURENTRYID 

UNION ALL

SELECT FUPDATETIME,FSOURFORMID,FFlag,FSERIALNO,FSERIALID,FENTRYID FROM dbo.T_STK_MISCELLANEOUSSERIAL a

INNER JOIN (SELECT FSOURENTRYID,FUPDATETIME,FSOURFORMID,CASE WHEN FUPDATEBASEQTY > 0 THEN '+' ELSE '-' END FFlag FROM dbo.T_STK_INVENTORYLOG WHERE FINVENTORYID = '52540064-f5d6-a67f-11e6-ec088eba06de' AND FSOURFORMID = 'STK_MISCELLANEOUS') b

ON a.FENTRYID = b.FSOURENTRYID 

) A ORDER BY FSERIALNO ASC ,FUPDATETIME ASC


4.根据3中的查询结果分析哪个序列号是重复出入库或者丢失,分析此序列号异常时单据的情况,正常序列号出入库是有逻辑校验,很多情况客户二开修改了相关规则会导致各种情况使得最后库存与序列号不一致。

所属分类
推荐知识