库存审计报表

提示

备注：针对本报表，我将会在网校单独做一份课程讲解其中的关系

代码示例

SELECT T0.[ItemCode], T0.[ItemName], T0.[FrgnName], T2.whscode AS 仓库, T2.[OnHand]
	, TS.[BinCode] AS '库位号', Ts.[KWqty] AS 库位库存, TB.[BatchNum] AS '批次', TB.[Quantity] AS 批次库存, TA.[DistNumber] AS 序列号
	, TA.[OnHandQty] AS 序列号库存
FROM OITM T0
	INNER JOIN OITB T1 ON T0.[ItmsGrpCod] = T1.[ItmsGrpCod]
	INNER JOIN OITW T2 ON T2.itemcode = t0.itemcode
	LEFT JOIN (
		SELECT T0.[ItemCode], T0.[WhsCode], T3.[BinCode], T0.[OnHandQty] AS 'KWqty', T1.[SnBMDAbs] AS 'PCabs'
			, T1.[OnHandQty] AS 'XLHqty', T2.[SnBMDAbs] AS 'XLHabs', T2.[OnHandQty] AS 'PCqty'
		FROM OIBQ T0
			LEFT JOIN OBBQ T1
			ON T1.[ItemCode] = T0.[ItemCode]
				AND T1.[WhsCode] = T0.[WhsCode]
				AND T0.[BinAbs] = T1.[BinAbs]
			LEFT JOIN OSBQ T2
			ON T2.[ItemCode] = T0.[ItemCode]
				AND T2.[WhsCode] = T0.[WhsCode]
				AND T0.[BinAbs] = T2.[BinAbs]
			INNER JOIN OBIN T3 ON T0.[BinAbs] = T3.[AbsEntry]
		WHERE T0.[OnHandQty] > 0
	) TS  ----库位库存
	ON TS.itemcode = T0.[ItemCode]
		AND TS.[WhsCode] = T2.[WhsCode]
	LEFT JOIN (
		SELECT T0.[ItemCode], T1.[absentry], T0.[WhsCode], T0.[Quantity], T0.[BatchNum]
		FROM OIBT T0
			INNER JOIN OBTN T1
			ON T0.[BatchNum] = T1.[DistNumber]
				AND T0.[ItemCode] = T1.[ItemCode]
				AND T0.[Quantity] > 0
	) TB  ----批次库存
	ON TB.itemcode = t0.itemcode
		AND TB.whscode = T2.whscode
		AND TB.[absentry] = TS.PCabs
	LEFT JOIN (
		SELECT T0.[ItemCode], T1.[DistNumber], T0.[WhsCode], T2.[BinCode], T0.[OnHandQty]
			, T0.absentry
		FROM OSBQ T0
			INNER JOIN OSRN T1 ON T0.[SnBMDAbs] = T1.[AbsEntry]
			INNER JOIN OBIN T2 ON T0.[BinAbs] = T2.[AbsEntry]
	) TA  ----序列号库存
	ON TA.[ItemCode] = T0.[ItemCode]
		AND T2.[WhsCode] = Ta.[WhsCode]
		AND TA.absentry = TS.XLHabs
WHERE T2.[OnHand] > 0
	AND (T0.[ItemCode] >= '[%0]'
		OR '[%0]' = '')
	AND (T0.[ItemCode] <= '[%1]'
		OR '[%1]' = '')
	AND (T0.[FrgnName] >= '[%2]'
		OR '[%2]' = '')
	AND (T0.[FrgnName] <= '[%3]'
		OR '[%3]' = '')
	AND (T1.[ItmsGrpNam] >= '[%7]'
		OR '[%7]' = '')
	AND (T1.[ItmsGrpNam] <= '[%8]'
		OR '[%8]' = '') 
	AND (T2.whscode >= '[%9]'
		OR '[%9]' = '')
	AND (T2.whscode <= '[%10]'
		OR '[%10]' = '') 

ORDER BY T0.[ItemCode] DESC, T2.WHSCODE, TS.[BinCode], TB.[BatchNum], TA.[DistNumber]