OPM Batch Material Transactions
Description
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 |