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 |
