即时库存与金蝶云星空序列号数量不一致问题分析

/*

查：即时库存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 = ''

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