库存转储报表

代码示例

SELECT T0.[DocNum], T1.[ItemCode], T2.[FrgnName], T2.[ItemName], T1.[Dscription]
	, T4.[SlpName], T1.[Quantity], T1.[OpenQty], T2.[OnHand], T1.[FromWhsCod] AS 从仓库
	, T1.[WhsCode] AS 到仓库, TS.[BatchNum] AS 批次号, TS.[Quantity] AS 批次转出数量, TS.[BinCode] AS 库位号, TS.[Quantity] AS 库位转入数量
FROM OWTR T0
	INNER JOIN WTR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
	INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
	INNER JOIN OITB T3 ON T2.[ItmsGrpCod] = T3.[ItmsGrpCod]
	LEFT JOIN OSLP T4 ON T0.[SlpCode] = T4.[SlpCode]
	LEFT JOIN (
		SELECT T2.[BinCode], T1.[Quantity], T1.[SnBMDAbs], T4.[BatchNum], T0.[LocCode]
			, T0.[DocEntry], T0.[ItemCode], T0.[BaseType]
		FROM OILM T0
			INNER JOIN OBTL T1 ON T0.[MessageID] = T1.[MessageID]
			INNER JOIN OBIN T2 ON T1.[BinAbs] = T2.[AbsEntry]
			INNER JOIN OBTN T3 ON T3.ABSENTRY = T1.[SnBMDAbs]
			INNER JOIN IBT1 T4
			ON T4.[BatchNum] = T3.[DistNumber]
				AND T4.itemcode = t0.itemcode
				AND T4.[WhsCode] = T0.[LocCode]  				
                                                   AND T4.[BaseEntry]= T0.[DocEntry]
	) TS
	ON Ts.[DocEntry] = t0.docentry
		AND Ts.[ItemCode] = t1.itemcode
		AND Ts.[LocCode] = t1.whscode
		AND TS.[BaseType] = t0.objtype 
WHERE
----物料编码 大于等于 A    AND  物料编码小于等于 B
 (T1.[ItemCode] >= '[%0]' or '[%0]'='') AND ( T1.[ItemCode] <= '[%1]' or '[%1]'='') 
----单据日期
AND  ( T0.[taxDate] >= '[%4]' or '[%4]'='')  AND  ( T0.[taxDate]<= '[%5]' or '[%5]'='')  
----单据状态
AND   ( T0.[DocStatus] = '[%6]' or '[%6]'='') 
----物料组
AND (T3.[ItmsGrpNam]  >= '[%7]' or '[%7]'='') AND (T3.[ItmsGrpNam]  <= '[%8]' or '[%8]'='') 
----业务伙伴代码
AND   (T0.[CARDCODE]  >= '[%9]' or '[%9]'='')  AND   (T0.[CARDCODE]  <= '[%10]' or '[%10]'='')  
----业务伙伴名称
AND  (T0.[Cardname] >= '[%11]' or '[%11]'='') AND  (T0.[Cardname] <= '[%12]' or '[%12]'='') 
----从仓库
AND (T1.[FromWhsCod]  >='[%13]' or '[%13]'='')  AND (T1.[FromWhsCod]  <='[%14]' or '[%14]'='')
----到仓库
AND (T1.[WhsCode]  >='[%15]' or '[%15]'='')  AND (T1.[WhsCode]  <='[%16]' or '[%16]'='')
----单据编号
AND (T0.[DOCNUM]  >=[%17] or [%17]='')  AND (T0.[DOCNUM]  <=[%18] or [%18]='')
----采购/销售员
AND   (T4.[slpname]  >= '[%19]' or '[%19]'='')  AND   (T4.[slpname]  <= '[%20]' or '[%20]'='') 
ORDER BY T0.[DocNum] DESC