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

/*

查：即时库存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  ) );

SELECT Distinct FSOURFORMID FROM T_STK_INVENTORYLOG WHERE FINVENTORYID = '52540064-f5d6-a67f-11e6-ec088eba06de'

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