Thursday, 16 February 2012

How to Find Total Production of Finished Goods in a Query

SELECT
(Select MSI.Segment1 From MTL_SYSTEM_ITEMS_B MSI where MSI.INVENTORY_ITEM_ID=M.INVENTORY_ITEM_ID AND M.ORGANIZATION_ID=MSI.ORGANIZATION_ID)Item,
(Select MSI.DESCRIPTION From MTL_SYSTEM_ITEMS_B MSI where MSI.INVENTORY_ITEM_ID=M.INVENTORY_ITEM_ID AND M.ORGANIZATION_ID=MSI.ORGANIZATION_ID) DESCRIPTION,
(Select MSI.PRIMARY_UOM_CODE From MTL_SYSTEM_ITEMS_B MSI where MSI.INVENTORY_ITEM_ID=M.INVENTORY_ITEM_ID AND M.ORGANIZATION_ID=MSI.ORGANIZATION_ID) UOM,
NVL(M.Total_qty+NVL(N.Total_QTY,0),0) Total_Quantity
FROM
(SELECT MMT.INVENTORY_ITEM_ID,MMT.ORGANIZATION_ID, NVL(SUM(MMT.PRIMARY_QUANTITY),0) Total_QTY
From MTL_MATERIAL_TRANSACTIONS MMT
WHERE      MMT.ORGANIZATION_ID=86 --ORGANIZATION_ID
AND     MMT.TRANSACTION_TYPE_ID=44 --WIP Complition
AND     MMT.TRANSACTION_SOURCE_TYPE_ID=5  --Job or Schedule
AND     TRUNC(MMT.TRANSACTION_DATE) BETWEEN :start_date AND :end_date
GROUP BY MMT.INVENTORY_ITEM_ID,MMT.ORGANIZATION_ID)M,
(SELECT MMT.INVENTORY_ITEM_ID,MMT.ORGANIZATION_ID, NVL(SUM(MMT.PRIMARY_QUANTITY),0) Total_QTY
From MTL_MATERIAL_TRANSACTIONS MMT
WHERE      MMT.ORGANIZATION_ID=86 --ORGANIZATION_ID
AND     MMT.TRANSACTION_TYPE_ID=17  --WIP Complition Return
AND     MMT.TRANSACTION_SOURCE_TYPE_ID=5  --Job or Schedule
AND     TRUNC(MMT.TRANSACTION_DATE) BETWEEN :start_date AND :end_date
GROUP BY MMT.INVENTORY_ITEM_ID,MMT.ORGANIZATION_ID)N
Where M.INVENTORY_ITEM_ID=N.INVENTORY_ITEM_ID(+)
AND     M.ORGANIZATION_ID=N.ORGANIZATION_ID(+)
Order By Item