生产订单成本汇总查询

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

效果图

财务

示例代码

SELECT T0.[DocNum]
	, 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], T1.[ItemName], T0.[PlannedQty], T0.[CmpltQty]
	, T0.[Comments], N'=>' AS 汇总
	, ISNULL(TA.q1, 0) - ISNULL(TE.Q1, 0) AS '实际物料组件成本'
	, TE.Q1 AS '退料总计', TB.q1 AS '实际资源组件成本', TC.Q1 AS '实际产品成本', TD.Q1 AS '实际副产品成本'
	, ISNULL(TC.Q1, 0) + ISNULL(TD.Q1, 0) - ISNULL(TA.Q1, 0) - ISNULL(TB.Q1, 0) + ISNULL(TE.Q1, 0) AS '总差异'
	, T0.[DueDate], T0.[CloseDate] AS '实际结算日期'
FROM OWOR T0
	INNER JOIN OITM T1 ON T1.itemcode = T0.itemcode
	LEFT JOIN (
		SELECT T0.[BaseEntry], SUM(T0.[LineTotal]) AS 'Q1'
		FROM IGE1 T0
		WHERE T0.[BaseType] = '202'
			AND T0.[ItemType] = '4'
		GROUP BY T0.[BaseEntry]
	) TA
	ON TA.[BaseEntry] = T0.docentry
	LEFT JOIN (
		SELECT T0.[BaseEntry], SUM(T0.[LineTotal]) AS 'Q1'
		FROM IGE1 T0
		WHERE T0.[BaseType] = '202'
			AND T0.[ItemType] = '290'
		GROUP BY T0.[BaseEntry]
	) TB
	ON TB.[BaseEntry] = T0.docentry
	LEFT JOIN (
		SELECT T0.[BaseEntry], SUM(T0.[LineTotal]) AS 'Q1'
		FROM IGN1 T0
		WHERE T0.[BaseType] = '202'
			AND T0.[IsByPrdct] = 'N'
			AND T0.[TranType] = 'C'
		GROUP BY T0.[BaseEntry]
	) TC
	ON TC.[BaseEntry] = T0.docentry
	LEFT JOIN (
		SELECT T0.[BaseEntry], SUM(T0.[LineTotal]) AS 'Q1'
		FROM IGN1 T0
		WHERE T0.[BaseType] = '202'
			AND T0.[IsByPrdct] = 'Y'
			AND T0.[TranType] = 'C'
		GROUP BY T0.[BaseEntry]
	) TD
	ON TD.[BaseEntry] = T0.docentry
	LEFT JOIN (
		SELECT T0.[BaseEntry], SUM(T0.[LineTotal]) AS 'Q1'
		FROM IGN1 T0
		WHERE T0.[BaseType] = '202'
			AND T0.[IsByPrdct] = 'N'
			AND T0.[TranType] IS NULL
		GROUP BY T0.[BaseEntry]
	) TE
	ON TE.[BaseEntry] = T0.docentry
WHERE T0.[PostDate] >= [%0]
	AND T0.[PostDate] <= [%1]
	AND (T0.[ItemCode] = '[%2]'
		OR '[%2]' = '')
	AND (T1.[ItemName] LIKE '%%[%3]%%'
		OR '[%3]' = '')
	AND (T0.[Warehouse] = '[%4]'
		OR '[%4]' = '')
ORDER BY T0.[DocNum] DESC