OPM Transactions, Reservations, Pending Product Lots

Description
Categories: Toolkit - Operations
Listing of OPM transactions with reservations and pending product lot details.
Run OPM Transactions, Reservations, Pending Product Lots and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT 'MMT' as table_name,
       mmt.transaction_id as trans_or_rsrv_id,
       mty.transaction_type_name,
       gbh.BATCH_NO,
       decode(gbh.BATCH_STATUS,
              3,
              'Completed',
              4,
              'Closed',
              2,
              'WIP',
              'Just Created') Batch_Status,
       gmd.batch_id as batch_id,
       mmt.transaction_source_id as trans_or_rsrv_source_id,
       DECODE(GMD.LINE_TYPE,
              '1',
              'PRODUCT',
              '2',
              'BY-PRODUCT',
              '-1',
              'INGREDIENT') line_type,
       mmt.trx_source_line_id as material_detail_id,
       mp.organization_code,
       msib.segment1 as item_number,
       mmt.subinventory_code,
       mmt.locator_id,
       mtln.lot_number as lot_number,
       mmt.primary_quantity,
       msib.primary_uom_code,
       mmt.transaction_quantity as trans_or_rsrv_qty,
       mtln.transaction_quantity as lot_trans_qty,
       mmt.transaction_uom as trans_or_rsrv_uom,
       mmt.secondary_transaction_quantity as sec_qty,
       mmt.secondary_uom_code,
       mtln.primary_quantity as lot_primary_qty,
       to_char(mmt.transaction_date, 'DD-MON-YYYY HH24:MI:SS') as trans_or_rsrv_date,
       mmt.LPN_ID,
       mmt.TRANSFER_LPN_ID,
       decode(mmt.transaction_mode,
              1,
              'On-line processing',
              2,
              'Concurrent processing',
              3,
              'Background processing',
              8,
              'Internal Processing') txnmode,
       NULL as lock_flag,
       NULL as process_flag,
       to_char(mmt.creation_date, 'DD-MON-YYYY HH24:MI:SS') as creation_date,
       to_char(mmt.last_update_date, 'DD-MON-YYYY HH24:MI:SS') as last_update_date,
       opm_costed_flag
  FROM mtl_material_transactions   mmt,
       gme_material_details        gmd,
       gme_batch_header            gbh,
       mtl_transaction_lot_numbers mtln,
       mtl_lot_numbers             mln,
       mtl_system_items_b          msib,
       mtl_transaction_types       mty,
       mtl_parameters              mp
 WHERE 1 = 1
   and mmt.transaction_source_type_id = 5
      -- AND gbh.batch_id in
      --    (:batch_id1, :batch_id2, :batch_id3, :batch_id4, :batch_id5)
   AND mmt.transaction_source_id = gbh.batch_id
   AND mmt.organization_id = gbh.organization_id
   AND gmd.batch_id = gbh.batch_id
   AND gmd.material_detail_id = mmt.trx_source_line_id
   AND mtln.transaction_id(+) = mmt.transaction_id -- This join allows us to get the lot number
   AND mln.lot_number(+) = mtln.lot_number -- This join allows us to get lot specific info if needegmd.
   AND mln.organization_id(+) = mtln.organization_id
   AND mln.inventory_item_id(+) = mtln.inventory_item_id
   AND mmt.organization_id = msib.organization_id
   AND mmt.inventory_item_id = msib.inventory_item_id
   AND mmt.transaction_type_id = mty.transaction_type_id
   And mmt.organization_id = mp.organization_id
UNION ALL
SELECT 'RSRV' as table_name,
       reservation_id as trans_or_rsrv_id,
       NULL,
       gbh.BATCH_NO,
       decode(gbh.BATCH_STATUS,
              3,
              'Completed',
              4,
              'Closed',
              2,
              'WIP',
              'Just Created') Batch_Status,
       gmd.batch_id as batch_id,
       demand_source_header_id as trans_or_rsrv_source_id,
       DECODE(GMD.LINE_TYPE,
              '1',
              'PRODUCT',
              '2',
              'BY-PRODUCT',
              '-1',
              'INGREDIENT') line_type,
       demand_source_line_id as material_detail_id,
       mp.organization_code,
       msib.segment1 as item_number,
       mr.subinventory_code,
       mr.locator_id,
       mr.lot_number,
       primary_reservation_quantity,
       msib.primary_uom_code,
       reservation_quantity as trans_or_rsrv_qty,
       NULL,
       reservation_uom_code as trans_or_rsrv_uom,
       secondary_reservation_quantity as sec_qty,
       mr.secondary_uom_code,
       NULL,
       to_char(requirement_date, 'DD-MON-YYYY HH24:MI:SS') as trans_or_rsrv_date,
       LPN_ID,
       NULL,
       NULL,
       NULL,
       NULL,
       to_char(mr.creation_date, 'DD-MON-YYYY HH24:MI:SS') as creation_date,
       to_char(mr.last_update_date, 'DD-MON-YYYY HH24:MI:SS') as last_update_date,
       NULL
  FROM mtl_reservations     mr,
       gme_material_details gmd,
       gme_batch_header     gbh,
       mtl_system_items_b   msib,
       mtl_parameters       mp
 WHERE 1 = 1
   and demand_source_type_id = 5
      -- AND gbh.batch_id in   (:batch_id1, :batch_id2, :batch_id3, :batch_id4, :batch_id5)
   AND demand_source_header_id = gbh.batch_id
   AND mr.organization_id = gbh.organization_id
   AND gmd.batch_id = gbh.batch_id
   AND gmd.material_detail_id = demand_source_line_id
   AND mr.organization_id = msib.organization_id
   AND mr.inventory_item_id = msib.inventory_item_id
   And mr.organization_id = mp.organization_id
UNION ALL
SELECT 'PPL' as table_name,
       pending_product_lot_id as trans_or_rsrv_id,
       NULL,
       gbh.BATCH_NO,
       decode(gbh.BATCH_STATUS,
              3,
              'Completed',
              4,
              'Closed',
              2,
              'WIP',
              'Just Created') Batch_Status,
       gmd.batch_id as batch_id,
       NULL,
       DECODE(GMD.LINE_TYPE,
              '1',
              'PRODUCT',
              '2',
              'BY-PRODUCT',
              '-1',
              'INGREDIENT') line_type,
       gmd.material_detail_id,
       mp.organization_code,
       msib.segment1 as item_number,
       NULL,
       NULL,
       lot_number,
       NULL,
       NULL,
       quantity as trans_or_rsrv_qty,
       NULL,
       NULL,
       secondary_quantity as sec_qty,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       to_char(gppl.creation_date, 'DD-MON-YYYY HH24:MI:SS') as creation_date,
       to_char(gppl.last_update_date, 'DD-MON-YYYY HH24:MI:SS') as last_update_date,
       NULL
  FROM gme_pending_product_lots gppl,
       gme_material_details     gmd,
       gme_batch_header         gbh,
       mtl_system_items_b       msib,
       mtl_parameters           mp
 WHERE 1 = 1
 
      --gbh.batch_id in
      --    (:batch_id1, :batch_id2, :batch_id3, :batch_id4, :batch_id5)
   AND gppl.batch_id = gbh.batch_id
   AND gmd.batch_id = gbh.batch_id
   AND gmd.material_detail_id = gppl.material_detail_id
   AND gbh.organization_id = msib.organization_id
   AND gmd.inventory_item_id = msib.inventory_item_id
   And gbh.organization_id = mp.organization_id
UNION ALL
-- Note that there should not be any transactions in MMTT. If there are, they are usually "stuck" there and
-- need to be processed or deleted
SELECT 'MMTT' as table_name,
       mmtt.TRANSACTION_TEMP_ID as trans_or_rsrv_id,
       mty.transaction_type_name,
       gbh.BATCH_NO,
       decode(gbh.BATCH_STATUS,
              3,
              'Completed',
              4,
              'Closed',
              2,
              'WIP',
              'Just Created') Batch_Status,
       gmd.batch_id as batch_id,
       mmtt.transaction_source_id as trans_or_rsrv_source_id,
       DECODE(GMD.LINE_TYPE,
              '1',
              'PRODUCT',
              '2',
              'BY-PRODUCT',
              '-1',
              'INGREDIENT') line_type,
       mmtt.trx_source_line_id as material_detail_id,
       mp.organization_code,
       msib.segment1 as item_number,
       mmtt.subinventory_code,
       mmtt.locator_id,
       mtln.lot_number as lot_number,
       mmtt.primary_quantity,
       msib.primary_uom_code,
       mmtt.transaction_quantity as trans_or_rsrv_qty,
       mtln.transaction_quantity as lot_trans_qty,
       mmtt.transaction_uom as trans_or_rsrv_uom,
       mmtt.secondary_transaction_quantity as sec_qty,
       mmtt.secondary_uom_code,
       mtln.primary_quantity as lot_primary_qty,
       to_char(mmtt.transaction_date, 'DD-MON-YYYY HH24:MI:SS') as trans_or_rsrv_date,
       mmtt.LPN_ID,
       mmtt.TRANSFER_LPN_ID,
       decode(mmtt.transaction_mode,
              1,
              'On-line processing',
              2,
              'Concurrent processing',
              3,
              'Background processing',
              8,
              'Internal Processing') txnmode,
       mmtt.lock_flag,
       mmtt.process_flag,
       to_char(mmtt.creation_date, 'DD-MON-YYYY HH24:MI:SS') as creation_date,
       to_char(mmtt.last_update_date, 'DD-MON-YYYY HH24:MI:SS') as last_update_date,
       NULL
 
  FROM mtl_material_transactions_temp mmtt,
       gme_material_details           gmd,
       gme_batch_header               gbh,
       mtl_transaction_lots_temp      mtln,
       mtl_system_items_b             msib,
       mtl_transaction_types          mty,
       mtl_parameters                 mp --mtl_lot_numbers lot
 WHERE 1 = 1
   and mmtt.transaction_source_type_id = 5
      --AND gbh.batch_id in
      --   (:batch_id1, :batch_id2, :batch_id3, :batch_id4, :batch_id5)
   AND transaction_source_id = gbh.batch_id
   AND mmtt.organization_id = gbh.organization_id
   AND gmd.batch_id = gbh.batch_id
   AND gmd.material_detail_id = trx_source_line_id
   AND mtln.TRANSACTION_TEMP_ID(+) = mmtt.TRANSACTION_TEMP_ID -- This join allows us to get the lot number
      --AND mln.lot_number(+) = mtln.lot_number
      --AND t.organization_id = mln.organization_id
   AND mmtt.organization_id = msib.organization_id
   AND mmtt.inventory_item_id = msib.inventory_item_id
   AND mmtt.transaction_type_id = mty.transaction_type_id
   And mmtt.organization_id = mp.organization_id
UNION ALL
SELECT 'MTI' as table_name,
       mti.TRANSACTION_INTERFACE_ID as trans_or_rsrv_id,
       mty.transaction_type_name,
       gbh.BATCH_NO,
       decode(gbh.BATCH_STATUS,
              3,
              'Completed',
              4,
              'Closed',
              2,
              'WIP',
              'Just Created') Batch_Status,
       gmd.batch_id as batch_id,
       mti.transaction_source_id as trans_or_rsrv_source_id,
       DECODE(GMD.LINE_TYPE,
              '1',
              'PRODUCT',
              '2',
              'BY-PRODUCT',
              '-1',
              'INGREDIENT') line_type,
       mti.trx_source_line_id as material_detail_id,
       mp.organization_code,
       msib.segment1 as item_number,
       mti.subinventory_code,
       mti.locator_id,
       mtln.lot_number as lot_number,
       mti.primary_quantity,
       msib.primary_uom_code,
       mti.transaction_quantity as trans_or_rsrv_qty,
       mtln.transaction_quantity as lot_trans_qty,
       mti.transaction_uom as trans_or_rsrv_uom,
       mti.secondary_transaction_quantity as sec_qty,
       mti.secondary_uom_code,
       mtln.primary_quantity as lot_primary_qty,
       to_char(mti.transaction_date, 'DD-MON-YYYY HH24:MI:SS') as trans_or_rsrv_date,
       mti.LPN_ID,
       mti.TRANSFER_LPN_ID,
       decode(mti.transaction_mode,
              1,
              'On-line processing',
              2,
              'Concurrent processing',
              3,
              'Background processing',
              8,
              'Internal Processing') txnmode,
       to_char(mti.lock_flag),
       to_char(mti.process_flag),
       to_char(mti.creation_date, 'DD-MON-YYYY HH24:MI:SS') as creation_date,
       to_char(mti.last_update_date, 'DD-MON-YYYY HH24:MI:SS') as last_update_date,
       NULL
  FROM mtl_transactions_interface     mti,
       gme_material_details           gmd,
       gme_batch_header               gbh,
       mtl_transaction_lots_interface mtln,
       mtl_system_items_b             msib,
       mtl_transaction_types          mty,
       mtl_parameters                 mp --mtl_lot_numbers lot
 WHERE 1 = 1
   and mti.transaction_source_type_id = 5
      --AND gbh.batch_id in   (:batch_id1, :batch_id2, :batch_id3, :batch_id4, :batch_id5)
   AND transaction_source_id = gbh.batch_id
   AND mti.organization_id = gbh.organization_id
   AND gmd.batch_id = gbh.batch_id
   AND gmd.material_detail_id = trx_source_line_id
   AND mtln.TRANSACTION_INTERFACE_ID(+) = mti.TRANSACTION_INTERFACE_ID -- This join allows us to get the lot number
      --AND mln.lot_number(+) = mtln.lot_number
      --AND t.organization_id = mln.organization_id
   AND mti.organization_id = msib.organization_id
   AND mti.inventory_item_id = msib.inventory_item_id
   AND mti.transaction_type_id = mty.transaction_type_id
   And mti.organization_id = mp.organization_id
 ORDER BY batch_id,
          table_name,
          line_type,
          material_detail_id,
          trans_or_rsrv_id
Parameter Name SQL text Validation
Organization Code
mp.organization_code=:organization_code
LOV
Blitz Report™