SELECT lk.meaning as line_type_dsp,
item.item_number,
-- fnd_date.displaydt_to_date(pnd.transaction_date) transaction_date, -- Bug 16814297 Uskumari
to_char(pnd.transaction_date, 'dd-mm-yyyy hh24:mi:ss') transaction_date, -- Bug 16814297 Uskumari
pnd.primary_quantity as transaction_quantity,
item.primary_uom_code as transaction_uom,
mp.organization_code,
pnd.transaction_id,
to_number(decode(pnd.source_line_id, -99, NULL, pnd.source_line_id)) reverse_id,
ol.layer_id,
CASE to_number(lk.lookup_code)
WHEN -1 THEN 0
WHEN 0 THEN 1
WHEN 2 THEN 2
WHEN 1 THEN 3
END line_type
FROM mtl_material_transactions pnd,
mtl_item_flexfields item,
gem_lookups lk,
gmf_outgoing_material_layers ol,
mtl_parameters mp
WHERE pnd.transaction_source_type_id = 5
AND pnd.transaction_action_id in (1, 27)
AND item.inventory_item_id = pnd.inventory_item_id
AND item.organization_id = pnd.organization_id
AND pnd.transaction_source_id = :P_BATCH_ID
AND lk.lookup_type = 'GMD_FORMULA_ITEM_TYPE'
AND lk.lookup_code = -1
AND ol.mmt_transaction_id = pnd.transaction_id
AND ol.mmt_organization_id = pnd.organization_id
AND mp.organization_id = pnd.organization_id
UNION ALL
SELECT lk.meaning as line_type_dsp,
item.item_number,
-- fnd_date.displaydt_to_date(pnd.transaction_date) transaction_date, -- Bug 16814297 Uskumari
to_char(pnd.transaction_date, 'dd-mm-yyyy hh24:mi:ss') transaction_date, -- Bug 16814297 Uskumari
pnd.primary_quantity as transaction_quantity,
item.primary_uom_code as transaction_uom,
mp.organization_code,
pnd.transaction_id,
to_number(decode(pnd.source_line_id, -99, NULL, pnd.source_line_id)) reverse_id,
il.layer_id,
CASE to_number(lk.lookup_code)
WHEN -1 THEN 0
WHEN 0 THEN 1
WHEN 2 THEN 2
WHEN 1 THEN 3
END line_type
FROM mtl_material_transactions pnd,
mtl_item_flexfields item,
gem_lookups lk,
gmf_incoming_material_layers il,
mtl_parameters mp
WHERE pnd.transaction_source_type_id = 5
AND pnd.transaction_action_id in (31, 32)
AND item.inventory_item_id = pnd.inventory_item_id
AND item.organization_id = pnd.organization_id
AND pnd.transaction_source_id = :P_BATCH_ID
AND lk.lookup_type = 'GMD_FORMULA_ITEM_TYPE'
AND lk.lookup_code = 1
AND il.mmt_transaction_id = pnd.transaction_id
AND il.mmt_organization_id = pnd.organization_id
AND mp.organization_id = pnd.organization_id
--begin Bug 25364925
UNION ALL
SELECT lk.meaning as line_type_dsp,
item.item_number,
-- fnd_date.displaydt_to_date(pnd.transaction_date) transaction_date, -- Bug 16814297 Uskumari
to_char(pnd.transaction_date, 'dd-mm-yyyy hh24:mi:ss') transaction_date, -- Bug 16814297 Uskumari
pnd.primary_quantity as transaction_quantity,
item.primary_uom_code as transaction_uom,
mp.organization_code,
pnd.transaction_id,
to_number(decode(pnd.source_line_id, -99, NULL, pnd.source_line_id)) reverse_id,
ol.layer_id,
CASE to_number(lk.lookup_code)
WHEN -1 THEN 0
WHEN 0 THEN 1
WHEN 2 THEN 2
WHEN 1 THEN 3
END line_type
FROM mtl_material_transactions pnd,
mtl_item_flexfields item,
gem_lookups lk,
gmf_outgoing_material_layers ol,
mtl_parameters mp
WHERE pnd.transaction_source_type_id = 5
AND pnd.transaction_action_id in (31, 32)
AND item.inventory_item_id = pnd.inventory_item_id
AND item.organization_id = pnd.organization_id
AND pnd.transaction_source_id = :P_BATCH_ID
AND lk.lookup_type = 'GMD_FORMULA_ITEM_TYPE'
AND lk.lookup_code = 2
AND ol.mmt_transaction_id = pnd.transaction_id
AND ol.mmt_organization_id = pnd.organization_id
AND mp.organization_id = pnd.organization_id
--end Bug 25364925
UNION ALL
SELECT 'Resource' as line_type_dsp,
rt.resources,
-- fnd_date.displaydt_to_date(rt.trans_date) as transaction_date, -- Bug 16814297 Uskumari
to_char(rt.trans_date, 'dd-mm-yyyy hh24:mi:ss') transaction_date, -- Bug 16814297 Uskumari
rt.resource_usage as trans_qty,
rt.trans_qty_um,
mp.organization_code,
rt.poc_trans_id as transaction_id,
rt.reverse_id,
rl.layer_id,
CASE rt.line_type
WHEN -1 THEN 0
WHEN 0 THEN 1
WHEN 2 THEN 2
WHEN 1 THEN 3
END line_type
FROM gme_resource_txns rt,
gmf_resource_layers rl,
mtl_parameters mp
WHERE rt.doc_type = 'PROD'
and rt.completed_ind = 1
AND rt.doc_id = :P_BATCH_ID
AND rl.poc_trans_id = rt.poc_trans_id
AND mp.organization_id = rt.organization_id
ORDER BY transaction_date, line_type, transaction_id |