生产订单成本汇总查询
大约 2 分钟
- 逻辑:以生产订单为核心,汇总相关生产订单相关发料和收货成本金额
- 条件:日期范围必选;其他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