ERP ADempiere/Compiere 已有領料的工單

SELECT o.C_MfgOrder_ID, p.Name AS "工單成品", o.MOQty AS "工單量", ol.C_MfgOrderLine_ID,c.Name AS "用料", ol.MOQty AS "用料量",

(SELECT SUM(NVL(MovementQty,0)) FROM M_ProductionMILine ml
INNER JOIN M_ProductionMI m ON(ml.M_ProductionMI_ID=m.M_ProductionMI_ID AND m.DocStatus='CO' AND m.IsReturnTrx='N' AND m.IsActive='Y')
WHERE ml.IsActive='Y' AND ml.C_MfgOrderLine_ID=ol.C_MfgOrderLine_ID) AS MovementQtyCO,

(SELECT SUM(NVL(MovementQty,0)) FROM M_ProductionMILine ml
INNER JOIN M_ProductionMI m ON(ml.M_ProductionMI_ID=m.M_ProductionMI_ID AND m.DocStatus='VO' AND m.IsReturnTrx='N' AND m.IsActive='Y')
WHERE ml.IsActive='Y' AND ml.C_MfgOrderLine_ID=ol.C_MfgOrderLine_ID) AS MovementQtyVO,

(SELECT SUM(NVL(MovementQty,0)) FROM M_ProductionMILine ml
INNER JOIN M_ProductionMI m ON(ml.M_ProductionMI_ID=m.M_ProductionMI_ID AND m.DocStatus='CO' AND m.IsReturnTrx='Y' AND m.IsActive='Y')
WHERE ml.IsActive='Y' AND ml.C_MfgOrderLine_ID=ol.C_MfgOrderLine_ID) AS ReturnQtyCO,

(SELECT SUM(NVL(MovementQty,0)) FROM M_ProductionMILine ml
INNER JOIN M_ProductionMI m ON(ml.M_ProductionMI_ID=m.M_ProductionMI_ID AND m.DocStatus='VO' AND m.IsReturnTrx='Y' AND m.IsActive='Y')
WHERE ml.IsActive='Y' AND ml.C_MfgOrderLine_ID=ol.C_MfgOrderLine_ID) AS ReturnQtyVO


FROM C_MfgOrderLine ol
INNER JOIN C_MfgOrder o ON(o.C_MfgOrder_ID=ol.C_MfgOrder_ID)
INNER JOIN M_Product p ON(o.M_Product_ID=p.M_Product_ID)
INNER JOIN M_Product c ON(ol.M_Product_ID=c.M_Product_ID)
WHERE (SELECT SUM(NVL(MovementQty,0)) FROM M_ProductionMILine ml
INNER JOIN M_ProductionMI m ON(ml.M_ProductionMI_ID=m.M_ProductionMI_ID AND m.DocStatus='CO' AND m.IsReturnTrx='N' AND m.IsActive='Y')
WHERE ml.IsActive='Y' AND ml.C_MfgOrderLine_ID=ol.C_MfgOrderLine_ID) > 0 --已有領料的工單