生产订单收货发料汇总查询

提示

逻辑：以生产订单为核心，汇总相关生产发料总数和生产收货总数 条件：日期范围必选；其他3个条件可不填，不填默认显示所有；物料描述可为关键词搜索，

代码示例

SELECT T0.[PostDate], T0.[DocNum] AS 生产订单编号
	, CASE 
		WHEN T0.[Type] = 'S' THEN N'标准'
		WHEN T0.[Type] = 'P' THEN N'特殊'
		WHEN T0.[Type] = 'D' THEN N'分拆'
	END AS '生产订单类型'
	, CASE 
		WHEN T0.[Status] = 'P' THEN N'已计划'
		WHEN T0.[Status] = 'R' THEN N'已下达'
		WHEN T0.[Status] = 'L' THEN N'已结算'
		WHEN T0.[Status] = 'C' THEN N'已取消'
	END AS '生产订单状态', T0.[ItemCode], T2.[ItemName] AS 成品描述, T0.[PlannedQty] AS '计划数量', T0.[Warehouse]
	, T1.[ItemCode] AS '子件料号', T3.[ItemName] AS 子件描述, TB.QTY AS '生产发料总数', TA.QTY AS '生产收货总数'
FROM OWOR T0
	INNER JOIN WOR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
	INNER JOIN OITM T2 ON T2.ITEMCODE = T0.ITEMCODE
	INNER JOIN OITM T3 ON T3.[ItemCode] = T1.ITEMCODE
	LEFT JOIN (
		SELECT T0.[BaseEntry], T0.[ItemCode], SUM(T0.[Quantity]) AS 'QTY'
		FROM IGN1 T0
		WHERE T0.[BaseType] = '202'
		GROUP BY T0.[BaseEntry], T0.[ItemCode]
	) TA
	ON TA.baseentry = T0.docentry
	LEFT JOIN (
		SELECT T0.[BaseEntry], T0.[ItemCode], SUM(T0.[Quantity]) AS 'QTY'
		FROM IGE1 T0
		WHERE T0.[BaseType] = '202'
		GROUP BY T0.[BaseEntry], T0.[ItemCode]
	) TB
	ON TB.[BaseEntry] = T1.[DocEntry]
		AND TB.itemcode = T1.itemcode
WHERE T0.[PostDate] >= [%0]
	AND T0.[PostDate] <= [%1]
	AND (T0.[ItemCode] = '[%2]'
		OR '[%2]' = '')
	AND (T2.[ItemName] LIKE '%%[%3]%%'
		OR '[%3]' = '')
	AND (T0.[Warehouse] = '[%4]'
		OR '[%4]' = '')
ORDER BY T0.[DocNum] DESC, T1.[LineNum]