OPM Transactions, Reservations, Pending Product Lots
Description
Listing of OPM transactions with reservations and pending product lot details.
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
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 |
|
LOV |