OPM Batch Material Transactions Query

 SELECT  GMD.ORGANIZATION_ID

      ,GBH.BATCH_ID

      ,GBH.BATCH_NO

, mmt.transaction_date

      ,GBH.PLAN_START_DATE

      ,GBH.PLAN_CMPLT_DATE

      ,GBH.DUE_DATE

      ,GBH.ACTUAL_START_DATE

      ,GBH.ACTUAL_CMPLT_DATE

      ,GBH.BATCH_CLOSE_DATE

      ,GBH.FORMULA_ID

      ,FFM.FORMULA_NO

      ,GBH.ROUTING_ID

      ,GBH.RECIPE_VALIDITY_RULE_ID

      ,GBH.BATCH_STATUS

      ,GBH.BATCH_TYPE

      ,GMD.LINE_NO

      ,LINE_TYPE

      ,DECODE(GMD.LINE_TYPE,'1','PRODUCT','2','BY-PRODUCT','-1','INGREDIENT')LINE_TYPE_DESC

      ,GMD.INVENTORY_ITEM_ID

      ,MSIB.SEGMENT1 ITEM_CODE

      ,MSIB.DESCRIPTION

      ,NVL(GMD.ORIGINAL_QTY,0) ORIGINAL_QTY

      ,NVL(GMD.PLAN_QTY,0) PLAN_QTY

      ,NVL(GMD.ACTUAL_QTY,0) ACTUAL_QTY

      ,NVL(GMD.WIP_PLAN_QTY,0) WIP_PLAN_QTY

      ,NVL(MMT.TRANSACTION_QUANTITY,0) TRANSACTED_QTY

      ,NVL(MMT.SECONDARY_TRANSACTION_QUANTITY,0)SECONDARY_QTY

      ,MMT.SECONDARY_UOM_CODE

      ,DTL_UM

      ,RELEASE_TYPE CONSUMPTION_TYPE

      ,DECODE(RELEASE_TYPE,'0','AUTOMATIC','1','MANUAL','2','INCREMENTAL','3','AUTOMATIC BY STEP') CONSUMPTION_TYPE_DESC

      ,GMD.SUBINVENTORY

      ,GMD.LOCATOR_ID

      ,(CASE WHEN GMD.LOCATOR_ID IS NOT NULL THEN (MIL.SEGMENT1||'.'||MIL.SEGMENT2||'.'||MIL.SEGMENT3||'.'||MIL.SEGMENT4) END) LOCATOR

      ,GMD.SCALE_TYPE

      ,DECODE(GMD.SCALE_TYPE,'0','FIXED','1','PROPOTIONAL') SCALE_TYPE_DESC

      ,COST_ALLOC

      ,CONTRIBUTE_STEP_QTY_IND

      ,DECODE(GMD.CONTRIBUTE_STEP_QTY_IND,'Y','YES','N','NO') CONTRIBUTION

      ,SCALE_MULTIPLE

      ,SCALE_ROUNDING_VARIANCE

      ,ROUNDING_DIRECTION

      ,DECODE(ROUNDING_DIRECTION,'0','EITHER','1','UP','2','DOWN') ROUNDING_DIR

      ,SCRAP_FACTOR

      ,MMT.REASON_ID

      ,(SELECT REASON_NAME FROM  MTL_TRANSACTION_REASONS WHERE REASON_ID = MMT.REASON_ID) REASON_NAME

FROM GME_BATCH_HEADER GBH,

     GME_MATERIAL_DETAILS GMD,

     MTL_SYSTEM_ITEMS_B MSIB,

     FM_FORM_MST FFM,

     MTL_ITEM_LOCATIONS MIL,

     MTL_MATERIAL_TRANSACTIONS MMT

WHERE     1=1 

      AND GBH.BATCH_ID = GMD.BATCH_ID

      AND GBH.ORGANIZATION_ID = GMD.ORGANIZATION_ID

      AND GMD.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID

      AND GMD.ORGANIZATION_ID = MSIB.ORGANIZATION_ID

      AND FFM.FORMULA_ID(+) = GBH.FORMULA_ID

      AND FFM.OWNER_ORGANIZATION_ID = GMD.ORGANIZATION_ID

      AND MIL.INVENTORY_LOCATION_ID (+) = GMD.LOCATOR_ID

      AND MIL.ORGANIZATION_ID (+) = GMD.ORGANIZATION_ID

      AND MMT.INVENTORY_ITEM_ID (+)= GMD.INVENTORY_ITEM_ID

      AND MMT.TRX_SOURCE_LINE_ID  (+) = GMD.MATERIAL_DETAIL_ID 

     ----AND MMT.TRANSACTION_SOURCE_ID (+) = GBH.BATCH_ID

      --AND BATCH_STATUS NOT IN ('-1','1')

      AND GMD.ORGANIZATION_ID=222

      and GBH.BATCH_NO='5555'

ORDER BY BATCH_NO,LINE_TYPE DESC