库存明细报表（只限采购收货+采购退货+销售交货+销售退货）

代码示例

SELECT 

T0.物料编号,  T0.物料描述, T0.型号,T0.[客户/供应商名称] ,T0.[客户/供应商代码],T0.产品批次号,
T0.期初库存
	,  T0.期末库存数量,T0.本期入库数量,T0.本期入库单价,T0.本期入库金额,T0.本期入库日期,T0.本期出库数量,T0.本期出库单价,T0.本期出库金额,T0.本期出库日期

FROM

(
SELECT T0.[ItemCode] AS'物料编号',  T1.[ItemName] AS'物料描述', T1.[ItmsGrpCod] AS'型号',A0.CN AS'客户/供应商名称' , A0.CD AS'客户/供应商代码',A0.PC AS'产品批次号',
Ta.qty AS '期初库存'
	,  Tb.qty AS '期末库存数量',A0.SL AS'本期入库数量',A0.DJ AS'本期入库单价',A0.JE AS'本期入库金额',A0.RQ AS'本期入库日期',NULL AS'本期出库数量',NULL AS'本期出库单价',NULL AS'本期出库金额',NULL AS'本期出库日期'
FROM OIVL T0 INNER JOIN OITM T1 ON T1.ITEMCODE=T0.ITEMCODE
	LEFT JOIN (
		SELECT T0.[ItemCode],SUM(T0.[InQty] - T0.[OutQty]) AS 'qty'
		FROM OIVL T0
		WHERE T0.[DocDate] <[%0] AND T0.[TransType] IN(20,21,15,16)
		GROUP BY T0.[ItemCode]
	) Ta ----期初库存
	ON Ta.itemcode = t0.itemcode

	LEFT JOIN ( 
		SELECT T0.[ItemCode], SUM(T0.[InQty] - T0.[OutQty]) AS 'qty'
		FROM OIVL T0
		WHERE T0.[DocDate] <=[%1] AND T0.[TransType] IN(20,21,15,16)
		GROUP BY T0.[ItemCode]
	) Tb ----期末库存
	ON Tb.itemcode = t0.itemcode

----计算库存金额

LEFT JOIN 
(
SELECT T0.[ItemCode], T3.[CardName] AS'CN',T3.[CardCode] AS'CD',T2.[BatchNum] AS'PC' ,T1.[Quantity] AS'SL', T1.[PriceAfVAT] AS'DJ', T1.[DocDate] AS'RQ',T1.[GTotal] AS 'JE'
FROM OIVL T0
	INNER JOIN PDN1 T1 
LEFT JOIN IBT1 T2 ON T2.[BaseEntry]= T1.[DocEntry] AND T2.[BaseLinNum]=T1.[LineNum] AND T2.[BaseType]= '20' 
INNER JOIN OPDN T3 ON T1.[DocEntry] = T3.[DocEntry]
	ON T0.[BASE_REF] = T1.[DocEntry]
		AND T0.[DocLineNum] = T1.[LineNum]
WHERE  T0.[TransType] = '20' AND T0.[DocDate] >=[%0]  AND T0.[DocDate] <=[%1]

UNION 

SELECT T0.[ItemCode], T3.[CardName] AS'CN',T3.[CardCode] AS'CD',T2.[BatchNum] AS'PC' ,T1.[Quantity] AS'SL', T1.[PriceAfVAT] AS'DJ', T1.[DocDate] AS'RQ',T1.[GTotal] AS 'JE'
FROM OIVL T0
	INNER JOIN RDN1 T1 
LEFT JOIN IBT1 T2 ON T2.[BaseEntry]= T1.[DocEntry] AND T2.[BaseLinNum]=T1.[LineNum] AND T2.[BaseType]= '16' 
INNER JOIN OPDN T3 ON T1.[DocEntry] = T3.[DocEntry]
	ON T0.[BASE_REF] = T1.[DocEntry]
		AND T0.[DocLineNum] = T1.[LineNum]
WHERE  T0.[TransType] = '16' AND T0.[DocDate] >=[%0]  AND T0.[DocDate] <=[%1]
) A0 ----期间采购入库
	ON A0.itemcode = t0.itemcode

WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1] AND A0.CN IS NOT NULL
GROUP BY T0.[ItemCode],   T1.[ItemName], T1.[ItmsGrpCod], A0.CN,  A0.CD,A0.PC,Ta.qty 
	,  Tb.qty ,A0.SL,A0.DJ,A0.JE,A0.RQ

UNION  ----大关联

SELECT T0.[ItemCode],   T1.[ItemName], T1.[ItmsGrpCod], A0.CN AS'客户/供应商名称' , A0.CD AS'客户/供应商代码',A0.PC AS'产品批次号',
Ta.qty AS '期初库存'
	,  Tb.qty AS '期末库存数量',NULL,NULL,NULL,NULL,A0.SL,A0.DJ,A0.JE,A0.RQ
FROM OIVL T0  INNER JOIN OITM T1 ON T1.ITEMCODE=T0.ITEMCODE
	LEFT JOIN (
		SELECT T0.[ItemCode],SUM(T0.[InQty] - T0.[OutQty]) AS 'qty'
		FROM OIVL T0
		WHERE T0.[DocDate] <[%0] AND T0.[TransType] IN(20,21,15,16)
		GROUP BY T0.[ItemCode]
	) Ta ----期初库存
	ON Ta.itemcode = t0.itemcode

	LEFT JOIN ( 
		SELECT T0.[ItemCode], SUM(T0.[InQty] - T0.[OutQty]) AS 'qty'
		FROM OIVL T0
		WHERE T0.[DocDate] <=[%1] AND T0.[TransType] IN(20,21,15,16)
		GROUP BY T0.[ItemCode]
	) Tb ----期末库存
	ON Tb.itemcode = t0.itemcode

----计算库存金额

LEFT JOIN 
(
SELECT T0.[ItemCode], T3.[CardName] AS'CN',T3.[CardCode] AS'CD',T2.[BatchNum] AS'PC' ,T1.[Quantity] AS'SL', T1.[PriceAfVAT] AS'DJ', T1.[DocDate] AS'RQ',T1.[GTotal] AS 'JE'
FROM OIVL T0
	INNER JOIN RPD1 T1 
LEFT JOIN IBT1 T2 ON T2.[BaseEntry]= T1.[DocEntry] AND T2.[BaseLinNum]=T1.[LineNum] AND T2.[BaseType]= '21' 
INNER JOIN OPDN T3 ON T1.[DocEntry] = T3.[DocEntry]
	ON T0.[BASE_REF] = T1.[DocEntry]
		AND T0.[DocLineNum] = T1.[LineNum]
WHERE  T0.[TransType] = '21' AND T0.[DocDate] >=[%0]  AND T0.[DocDate] <=[%1]

UNION 

SELECT T0.[ItemCode], T3.[CardName] AS'CN',T3.[CardCode] AS'CD',T2.[BatchNum] AS'PC' ,T1.[Quantity] AS'SL', T1.[PriceAfVAT] AS'DJ', T1.[DocDate] AS'RQ',T1.[GTotal] AS 'JE'
FROM OIVL T0
	INNER JOIN DLN1 T1 
LEFT JOIN IBT1 T2 ON T2.[BaseEntry]= T1.[DocEntry] AND T2.[BaseLinNum]=T1.[LineNum] AND T2.[BaseType]= '15' 
INNER JOIN OPDN T3 ON T1.[DocEntry] = T3.[DocEntry]
	ON T0.[BASE_REF] = T1.[DocEntry]
		AND T0.[DocLineNum] = T1.[LineNum]
WHERE  T0.[TransType] = '15' AND T0.[DocDate] >=[%0]  AND T0.[DocDate] <=[%1]
) A0 ----期间采购入库
	ON A0.itemcode = t0.itemcode


WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1] AND A0.CN IS NOT NULL
GROUP BY T0.[ItemCode],   T1.[ItemName], T1.[ItmsGrpCod], A0.CN,  A0.CD,A0.PC,Ta.qty 
	,  Tb.qty ,A0.SL,A0.DJ,A0.JE,A0.RQ
) T0
ORDER BY T0.物料编号 ,T0.本期入库日期, T0.本期出库日期