OPM Transactions, Reservations, Pending Product Lots

Description

Listing of OPM transactions with reservations and pending product lot details.

OPM Transactions, Reservations, Pending Product Lots and other Oracle EBS reports with Blitz Report™ on our demo environment

Copy

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

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