OPM Batch Material Transactions Copy link

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')
ORDER BY BATCH_NO,LINE_TYPE DESC