库存审计报表

代码示例

DECLARE @Bdate DATETIME
DECLARE @Edate DATETIME
SELECT @Bdate = T0.[DocDate]
FROM OIVL T0
WHERE T0.[DocDate] = '[%0]'
SELECT @Edate = T0.[DocDate]
FROM OIVL T0
WHERE T0.[DocDate] = '[%1]'
SELECT T0.[ItemCode], T1.[ItemName] AS '物料描述', T0.[LocCode], T2.[WhsName] AS '仓库名称', Ts.qty AS '期初库存'
	, TA.qty AS '期末库存', TB.qty AS '生产发料', TD.qty AS '生产收货', Tc.qty AS '库存发货', TE.qty AS '库存收货'
	, TF.qty AS '采购收货', TG.qty AS '采购退货', TH.qty AS '转储出库', TJ.qty AS '转储入库', TK.qty AS '销售交货'
	, TM.qty AS '销售退货'
FROM OIVL T0
	INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode]
	INNER JOIN OWHS T2 ON T2.whscode = t0.loccode
	LEFT JOIN (
		SELECT T0.[ItemCode], T0.[LocCode], SUM(T0.[InQty] - T0.[OutQty]) AS 'qty'
		FROM OIVL T0
		WHERE T0.[DocDate] <@Bdate
		GROUP BY T0.[ItemCode], T0.[LocCode]
	) TS ----期初库存
	ON TS.itemcode = t0.itemcode
		AND TS.loccode = T0.loccode
	FULL JOIN (
		SELECT T0.[ItemCode], T0.[LocCode], SUM(T0.[InQty] - T0.[OutQty]) AS 'qty'
		FROM OIVL T0
		WHERE T0.[DocDate] <= @Edate
		GROUP BY T0.[ItemCode], T0.[LocCode]
	) TA ----期末库存
	ON TA.itemcode = t0.itemcode
		AND TA.loccode = T0.loccode
	LEFT JOIN (
		SELECT T0.[ItemCode], T0.[LocCode], -SUM(T0.[InQty] - T0.[OutQty]) AS 'qty'
		FROM OIVL T0
		WHERE T0.[TransType] = '60'
			AND T0.[WipAct] > 0
			AND T0.[DocDate] > @Bdate
			AND T0.[DocDate] <= @Edate
		GROUP BY T0.[ItemCode], T0.[LocCode]
	) TB ----生产发料
	ON TB.itemcode = t0.itemcode
		AND TB.loccode = T0.loccode
	LEFT JOIN (
		SELECT T0.[ItemCode], T0.[LocCode], SUM(T0.[InQty] - T0.[OutQty]) AS 'qty'
		FROM OIVL T0
		WHERE T0.[TransType] = '59'
			AND T0.[WipAct] > 0
			AND T0.[DocDate] > @Bdate
			AND T0.[DocDate] <= @Edate
		GROUP BY T0.[ItemCode], T0.[LocCode]
	) TD ----生产收货
	ON TD.itemcode = t0.itemcode
		AND TD.loccode = T0.loccode
	LEFT JOIN (
		SELECT T0.[ItemCode], T0.[LocCode], -SUM(T0.[InQty] - T0.[OutQty]) AS 'qty'
		FROM OIVL T0
		WHERE T0.[TransType] = '60'
			AND T0.[WipAct] = 0
			AND T0.[DocDate] > @Bdate
			AND T0.[DocDate] <= @Edate
		GROUP BY T0.[ItemCode], T0.[LocCode]
	) TC ----库存发货
	ON TC.itemcode = t0.itemcode
		AND TC.loccode = T0.loccode
	LEFT JOIN (
		SELECT T0.[ItemCode], T0.[LocCode], SUM(T0.[InQty] - T0.[OutQty]) AS 'qty'
		FROM OIVL T0
		WHERE T0.[TransType] = '59'
			AND T0.[WipAct] = 0
			AND T0.[DocDate] > @Bdate
			AND T0.[DocDate] <= @Edate
		GROUP BY T0.[ItemCode], T0.[LocCode]
	) TE ----库存收货
	ON TE.itemcode = t0.itemcode
		AND TE.loccode = T0.loccode
	LEFT JOIN (
		SELECT T0.[ItemCode], T0.[LocCode], SUM(T0.[InQty] - T0.[OutQty]) AS 'qty'
		FROM OIVL T0
		WHERE T0.[TransType] = '20'
			AND T0.[WipAct] = 0
			AND T0.[DocDate] > @Bdate
			AND T0.[DocDate] <= @Edate
		GROUP BY T0.[ItemCode], T0.[LocCode]
	) TF ----采购收货
	ON TF.itemcode = t0.itemcode
		AND TF.loccode = T0.loccode
	LEFT JOIN (
		SELECT T0.[ItemCode], T0.[LocCode], -SUM(T0.[InQty] - T0.[OutQty]) AS 'qty'
		FROM OIVL T0
		WHERE T0.[TransType] = '21'
			AND T0.[WipAct] = 0
			AND T0.[DocDate] > @Bdate
			AND T0.[DocDate] <= @Edate
		GROUP BY T0.[ItemCode], T0.[LocCode]
	) TG ----采购退货
	ON TG.itemcode = t0.itemcode
		AND TG.loccode = T0.loccode
	LEFT JOIN (
		SELECT T0.[ItemCode], T0.[LocCode], -SUM(T0.[InQty] - T0.[OutQty]) AS 'qty'
		FROM OIVL T0
		WHERE T0.[TransType] = '67'
			AND T0.[OutQty] > 0
			AND T0.[WipAct] = 0
			AND T0.[DocDate] > @Bdate
			AND T0.[DocDate] <= @Edate
		GROUP BY T0.[ItemCode], T0.[LocCode]
	) TH ----转储出库
	ON TH.itemcode = t0.itemcode
		AND TH.loccode = T0.loccode
	LEFT JOIN (
		SELECT T0.[ItemCode], T0.[LocCode], SUM(T0.[InQty] - T0.[OutQty]) AS 'qty'
		FROM OIVL T0
		WHERE T0.[TransType] = '67'
			AND T0.[InQty] > 0
			AND T0.[WipAct] = 0
			AND T0.[DocDate] > @Bdate
			AND T0.[DocDate] <= @Edate
		GROUP BY T0.[ItemCode], T0.[LocCode]
	) TJ ----转储入库
	ON TJ.itemcode = t0.itemcode
		AND TJ.loccode = T0.loccode
	LEFT JOIN (
		SELECT T0.[ItemCode], T0.[LocCode], -SUM(T0.[InQty] - T0.[OutQty]) AS 'qty'
		FROM OIVL T0
		WHERE T0.[TransType] = '15'
			AND T0.[WipAct] = 0
			AND T0.[DocDate] > @Bdate
			AND T0.[DocDate] <= @Edate
		GROUP BY T0.[ItemCode], T0.[LocCode]
	) TK ----销售交货
	ON TK.itemcode = t0.itemcode
		AND TK.loccode = T0.loccode
	LEFT JOIN (
		SELECT T0.[ItemCode], T0.[LocCode], SUM(T0.[InQty] - T0.[OutQty]) AS 'qty'
		FROM OIVL T0
		WHERE T0.[TransType] = '16'
			AND T0.[WipAct] = 0
			AND T0.[DocDate] > @Bdate
			AND T0.[DocDate] <= @Edate
		GROUP BY T0.[ItemCode], T0.[LocCode]
	) TM ----销售退货
	ON TM.itemcode = t0.itemcode
		AND TM.loccode = T0.loccode
WHERE T0.[DocDate] > 0
	AND (Ts.qty IS NOT NULL
		OR TA.qty IS NOT NULL)
GROUP BY T0.[ItemCode], T1.[ItemName], T0.[LocCode], T2.[WhsName], TS.qty, TA.qty, TB.qty, TC.qty, TD.qty, TE.qty, TF.qty, TG.qty, TH.qty, TJ.qty, TK.qty, TM.qty
ORDER BY T0.[ItemCode], T0.[LocCode]