OPM Batch Material Transactions

➜ Back to Library
Description
Categories: Draft
Run OPM Batch Material Transactions and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Download
Related Blitz Reports
GMD MUV Detail GME Material Usage Variance ECC Process Genealogy and Trace, Process Batch ECC Genealogy and Trace, Process Batch GMF MUV Detail GME Batch Ticket GME Batch Yield Variance GME Material Usage and Substitution Variance OPM Batches
Blitz Report™

Blitz Report™ provides multiple benefits: