生产订单库存转储报表

代码示例

SELECT T0.[DocNum] AS '生产单号', T0.[PostDate]
	, CASE 
		WHEN T0.[Status] = 'P' THEN '已计划'
		WHEN T0.[Status] = 'R' THEN '已下达'
		WHEN T0.[Status] = 'L' THEN '已结算'
		WHEN T0.[Status] = 'C' THEN '已取消'
	END AS '单据状态', T0.[ItemCode] AS '成品编码', T0.[PlannedQty] AS '成品计划数量', T0.[CmpltQty] AS '成品完成数量', ts.itemcode AS '子件料号'
	, ts.docnum AS '转出申请单号', ts.sqqt AS '申请数量', ts.zc AS '已转储数量'
FROM OWOR T0
	INNER JOIN WOR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
	INNER JOIN OITM T2 ON t2.itemcode = t1.itemcode
	LEFT JOIN (
		SELECT t0.docnum
			, substring(T0.[Comments], charindex('生产订单 "', T0.[Comments]) + 6, 1) AS 'sc'
			, T1.[ItemCode], T1.[Quantity] AS 'sqqt', SUM(t2.quantity) AS 'zc'
		FROM OWTQ T0
			INNER JOIN WTQ1 T1 ON T0.[DocEntry] = T1.[DocEntry]
			INNER JOIN WTR1 T2
			ON T1.docentry = t2.baseentry
				AND t2.baseline = t1.linenum
		WHERE T0.[Comments] LIKE '%%生产订单%%'
		GROUP BY T0.[DocNum], T0.[Comments], T1.[ItemCode], T1.[Dscription], T1.[Quantity]
	) TS
	ON ts.sc = t0.docnum
		AND ts.itemcode = t1.itemcode
WHERE T0.[Status] <> 'C'
	AND T0.[PostDate] >= [%0]
	AND T0.[PostDate] <= [%1]
ORDER BY T0.[DocNum] DESC