[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT msi.concatenated_segments item_number ,
msi.primary_uom_code ,
mln.lot_number ,
waid.from_serial_number from_serial_number ,
waid.to_serial_number to_serial_number ,
wdd.organization_id ,
NULL job_name ,
msi.inventory_item_id ,
mln.origination_date ,
mln.best_by_date ,
mln.retest_date ,
mln.expiration_date ,
waid.organization_code ,
1 rlevel ,
0 v_parent_rlevel ,
msi.lot_control_code ,
msi.serial_number_control_code ,
decode(( SELECT upper(mcr.cross_reference)
FROM mtl_cross_references_vl mcr
WHERE mcr.inventory_item_id = msi.inventory_item_id + 0
AND mcr.organization_id = msi.organization_id + 0
AND to_char(mcr.last_update_date, 'DD/MM/YYYY-HH24.MM.SS') = (SELECT MAX(to_char(mcr1.last_update_date, 'DD/MM/YYYY-HH24.MM.SS'))
FROM mtl_cross_references_vl mcr1
WHERE mcr1.inventory_item_id = msi.inventory_item_id + 0
AND mcr1.organization_id = msi.organization_id + 0
AND mcr1.cross_reference_type ='OPSM INTEGRATED')), 'NO', 0, 1) cross_reference,
(SELECT mcr.attribute1
FROM mtl_cross_references_vl mcr
WHERE mcr.inventory_item_id = msi.inventory_item_id + 0
AND mcr.organization_id = (SELECT master_organization_id
FROM mtl_parameters
WHERE organization_id = msi.organization_id + 0)
AND mcr.cross_reference_type ='OPSM INTEGRATED'
AND mcr.cross_reference = 'YES') serial_type,
NULL parent_lot_number ,
NULL parent_serial_number ,
0 parent_inventory_item_id
FROM mtl_system_items_vl msi ,
mtl_lot_numbers mln ,
wsh_opsm_asn_item_details_v waid ,
wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = p_delivery_detail_id
AND wdd.organization_id = p_organization_id
AND waid.delivery_detail_id = wdd.delivery_detail_id + 0
AND waid.organization_id = wdd.organization_id + 0
AND msi.inventory_item_id = wdd.inventory_item_id + 0
AND msi.organization_id = wdd.organization_id + 0
AND mln.inventory_item_id = wdd.inventory_item_id + 0
AND mln.organization_id = wdd.organization_id + 0
AND mln.lot_number = waid.lot_number || ''
AND waid.from_serial_number = p_frm_serial
AND waid.to_serial_number = p_to_serial;
'SELECT msi.concatenated_segments item_nbr ,
msi.primary_uom_code ,
mtln.lot_number ,
NULL serial_number ,
mmt.organization_id ,
we.wip_entity_name job_name ,
msi.inventory_item_id ,
mtln.origination_date ,
mtln.best_by_date ,
mtln.retest_date ,
mln.expiration_date ,
ood.organization_code ,
1 rlevel ,
0 v_parent_rlevel ,
msi.lot_control_code ,
msi.serial_number_control_code ,
decode(( SELECT upper(mcr.cross_reference)
FROM mtl_cross_references_vl mcr
WHERE mcr.inventory_item_id = msi.inventory_item_id+0
AND mcr.organization_id = msi.organization_id+0
AND to_char(mcr.last_update_date, ''DD/MM/YYYY-HH24.MM.SS'') = (SELECT MAX(to_char(mcr1.last_update_date, ''DD/MM/YYYY-HH24.MM.SS''))
FROM mtl_cross_references_vl mcr1
WHERE mcr1.inventory_item_id = msi.inventory_item_id+0
AND mcr1.organization_id = msi.organization_id+0
AND mcr1.cross_reference_type =''OPSM INTEGRATED'')),''NO'',0,1) cross_reference,
(SELECT mcr.attribute1
FROM mtl_cross_references_vl mcr
WHERE mcr.inventory_item_id = msi.inventory_item_id+0
AND mcr.organization_id = (SELECT master_organization_id
FROM mtl_parameters
WHERE organization_id=msi.organization_id+0)
AND mcr.cross_reference_type =''OPSM INTEGRATED''
AND mcr.cross_reference = ''YES'') serial_type,
NULL parent_lot_number ,
NULL parent_serial_number ,
0 parent_inventory_item_id
,
to_number(null) DELIVERY_DETAIL_ID,
to_number(null) DELIVERY_ID,
to_number(null) GENEALOGY_OBJECT_ID
FROM mtl_transaction_lot_numbers mtln ,
wip_entities we ,
mtl_system_items_vl msi ,
org_organization_definitions ood ,
mtl_material_transactions mmt ,
mtl_object_genealogy mog ,
mtl_lot_numbers mln
WHERE mmt.transaction_source_id+0 = we.wip_entity_id
AND msi.inventory_item_id = mmt.inventory_item_id+0
AND msi.organization_id = mmt.organization_id+0
AND we.gen_object_id = mog.object_id
AND mog.parent_object_id = :1
AND mln.inventory_item_id = mtln.inventory_item_id+0
AND mln.organization_id = mtln.organization_id+0
AND mln.lot_number = mtln.lot_number||''''
AND ood.organization_id = mtln.organization_id
AND mmt.transaction_id = mog.origin_txn_id+0
AND mtln.transaction_id(+) = mmt.transaction_id+0
AND msi.serial_number_control_code NOT IN (6)';
'SELECT mtln.lot_number ,
mut.serial_number ,
msi.concatenated_segments item_nbr ,
msi.primary_uom_code ,
msi.inventory_item_id ,
we.wip_entity_name job_name ,
mtln.origination_date ,
mtln.best_by_date ,
mtln.retest_date ,
mln.expiration_date ,
ood.organization_code ,
abs(mmt.transaction_quantity) transaction_quantity,
msi.lot_control_code ,
msi.serial_number_control_code ,
decode(( SELECT upper(mcr.cross_reference)
FROM mtl_cross_references_vl mcr
WHERE mcr.inventory_item_id = msi.inventory_item_id+0
AND mcr.organization_id = msi.organization_id+0
AND to_char(mcr.last_update_date, ''DD/MM/YYYY-HH24.MM.SS'') = (SELECT MAX(to_char(mcr1.last_update_date, ''DD/MM/YYYY-HH24.MM.SS''))
FROM mtl_cross_references_vl mcr1
WHERE mcr1.inventory_item_id = msi.inventory_item_id+0
AND mcr1.organization_id = msi.organization_id+0
AND mcr1.cross_reference_type =''OPSM INTEGRATED'')),''NO'',0,1) cross_reference,
(SELECT mcr.attribute1
FROM mtl_cross_references_vl mcr
WHERE mcr.inventory_item_id = msi.inventory_item_id+0
AND mcr.organization_id = (SELECT master_organization_id
FROM mtl_parameters
WHERE organization_id=msi.organization_id+0)
AND mcr.cross_reference_type =''OPSM INTEGRATED''
AND mcr.cross_reference = ''YES'') serial_type,
NULL parent_lot_number ,
NULL parent_serial_number ,
0 parent_inventory_item_id
FROM mtl_material_transactions mmt1 ,
mtl_transaction_lot_numbers mtln1 ,
mtl_unit_transactions mut1 ,
mtl_material_transactions mmt ,
mtl_transaction_lot_numbers mtln ,
mtl_unit_transactions mut ,
wip_entities we ,
mtl_system_items_vl msi ,
org_organization_definitions ood ,
mtl_lot_numbers mln
WHERE mtln1.transaction_id+0 = mmt1.transaction_id
AND mut1.transaction_id+0 = mtln1.serial_transaction_id
AND mmt1.transaction_type_id = 44
AND mmt1.transaction_quantity = 1
AND mut1.organization_id = :1
AND mut1.transaction_date = mut1.transaction_date
AND mut1.inventory_item_id = mut1.inventory_item_id
AND mut1.serial_number = :2
AND mmt1.inventory_item_id = :3
AND mmt.transaction_set_id = mmt1.transaction_set_id
AND mmt.transaction_type_id = 35
AND mtln.transaction_id = mmt.transaction_id+0
AND mut.transaction_id = mtln.serial_transaction_id+0
AND we.wip_entity_id = mmt.transaction_source_id+0
AND we.organization_id = :4
AND msi.inventory_item_id = mmt.inventory_item_id+0
AND msi.organization_id = mmt.organization_id+0
AND ood.organization_id = mmt.organization_id+0
AND mln.lot_number = mtln.lot_number||''''
AND mln.inventory_item_id = mmt.inventory_item_id+0
AND mln.organization_id = mtln.organization_id+0';
SELECT ROWNUM row_num ,
mog.object_id ,
mog.object_type ,
mtln.lot_number ,
mtln.transaction_source_id ,
msn.inventory_item_id ,
mut.serial_number ,
we.wip_entity_name job_name
FROM mtl_object_genealogy mog ,
mtl_transaction_lot_numbers mtln ,
mtl_unit_transactions mut ,
mtl_serial_numbers msn ,
mtl_material_transactions mmt ,
wip_entities we
WHERE msn.serial_number = r_serial_number
AND msn.inventory_item_id = r_item_id
AND mut.serial_number = r_serial_number
AND mut.inventory_item_id = r_item_id
AND mut.organization_id = r_org_id
AND mtln.organization_id = r_org_id
AND mut.transaction_id + 0 = mtln.serial_transaction_id
AND msn.serial_number = mut.serial_number
AND msn.inventory_item_id = mut.inventory_item_id
AND we.wip_entity_id = mtln.transaction_source_id + 0
AND we.organization_id = mtln.organization_id + 0
AND mog.object_id = msn.gen_object_id
AND mmt.transaction_id = mtln.transaction_id + 0
AND mmt.transaction_type_id = 44
AND mmt.transaction_action_id = 31
ORDER BY 7 DESC;
SELECT (LEVEL - 1) rlevel ,
mog.* ,
'' serial_number ,
'' parent_serial_number ,
0 delivery_id ,
0 rTree
FROM mtl_object_genealogy mog
WHERE object_type IN (1, 2)
AND genealogy_origin = 1
AND (end_date_active IS NULL
OR TRUNC(end_date_active) >= TRUNC(SYSDATE))
START WITH parent_object_id = rv_object_id
CONNECT BY PRIOR object_id = parent_object_id;
SELECT we.wip_entity_name job_name ,
msi.concatenated_segments item_number ,
msi.primary_uom_code ,
msn.lot_number ,
msn.serial_number ,
mtln.origination_date ,
mtln.best_by_date ,
mtln.retest_date ,
mtln.expiration_date ,
ood.organization_code ,
msi.inventory_item_id ,
msi.organization_id ,
msi.lot_control_code ,
msi.serial_number_control_code ,
decode(( SELECT upper(mcr.cross_reference)
FROM mtl_cross_references_vl mcr
WHERE mcr.inventory_item_id = msi.inventory_item_id + 0
AND mcr.organization_id = msi.organization_id + 0
AND to_char(mcr.last_update_date, 'DD/MM/YYYY-HH24.MM.SS') = (SELECT MAX(to_char(mcr1.last_update_date, 'DD/MM/YYYY-HH24.MM.SS'))
FROM mtl_cross_references_vl mcr1
WHERE mcr1.inventory_item_id = msi.inventory_item_id + 0
AND mcr1.organization_id = msi.organization_id + 0
AND mcr1.cross_reference_type ='OPSM INTEGRATED')), 'NO', 0, 1) cross_reference,
(SELECT mcr.attribute1
FROM mtl_cross_references_vl mcr
WHERE mcr.inventory_item_id = msi.inventory_item_id + 0
AND mcr.organization_id = (SELECT master_organization_id
FROM mtl_parameters
WHERE organization_id = msi.organization_id + 0)
AND mcr.cross_reference_type ='OPSM INTEGRATED'
AND mcr.cross_reference = 'YES') serial_type
FROM wip_entities we ,
mtl_system_items_vl msi ,
mtl_serial_numbers msn ,
mtl_lot_numbers mtln ,
org_organization_definitions ood,
mtl_cross_references mcr
WHERE msn.gen_object_id = r_gen_object_id
AND msi.inventory_item_id = msn.inventory_item_id
AND msi.organization_id = we.organization_id + 0
AND we.wip_entity_id = msn.original_wip_entity_id
AND mtln.lot_number = msn.lot_number || ''
AND mtln.organization_id = we.organization_id + 0
AND ood.organization_id = we.organization_id + 0;
SELECT waid.genealogy_object_id
FROM mtl_object_genealogy mog ,
mtl_material_transactions mmt ,
wip_entities we ,
mtl_system_items_vl msi ,
wsh_opsm_asn_item_details_v waid ,
mtl_transaction_lot_numbers mtln ,
mtl_unit_transactions mut ,
mtl_lot_numbers mln ,
org_organization_definitions ood
WHERE mog.parent_object_id = waid.genealogy_object_id + 0
AND mmt.transaction_id = mog.origin_txn_id + 0
AND mmt.transaction_type_id = 44
AND mmt.transaction_action_id = 31
AND we.wip_entity_id = mmt.transaction_source_id + 0
AND msi.inventory_item_id = mmt.inventory_item_id + 0
AND msi.organization_id = mmt.organization_id + 0
AND waid.delivery_detail_id = r_delivery_detail_id
AND mtln.transaction_id = mmt.transaction_id + 0
AND mut.transaction_id = mtln.serial_transaction_id + 0
AND mln.lot_number = mtln.lot_number || ''
AND mln.inventory_item_id = mtln.inventory_item_id + 0
AND mln.organization_id = mtln.organization_id + 0
AND ood.organization_id = msi.organization_id + 0
AND ood.organization_id = r_organization_id;
SELECT DECODE(wms_enabled_flag, 'Y', 1, 0)
INTO v_wms_enabled
FROM mtl_parameters
WHERE organization_id = p_organization_id;
v_tab_nodes.DELETE(v_tab_nodes.LAST);
v_tab_nodes.DELETE(v_tab_nodes.LAST);
SELECT wnd.delivery_id
INTO v_delivery_id
FROM mtl_transaction_details_v mtd ,
mtl_material_transactions mmt ,
wsh_new_deliveries wnd
WHERE mtd.object_id = v_top(i).object_id
AND mmt.transaction_id = mtd.transaction_id + 0
AND wnd.delivery_id = mmt.trx_source_delivery_id + 0;
SELECT wnd.delivery_id
INTO v_delivery_id1
FROM mtl_transaction_details_v mtd ,
mtl_material_transactions mmt ,
wsh_new_deliveries wnd
WHERE mtd.object_id = v_top(i).object_id
AND mmt.transaction_id = mtd.transaction_id + 0
AND wnd.delivery_id = mmt.trx_source_delivery_id + 0;
SELECT serial_number,
NVL(parent_serial_number, serial_number || TO_CHAR(i))
INTO v_top(i).serial_number,
v_top(i).parent_serial_number
FROM mtl_serial_numbers
WHERE gen_object_id = v_top(i).object_id
AND (serial_number <> parent_serial_number
OR parent_serial_number IS NULL);
SELECT serial_number,
serial_number
INTO v_top(i).serial_number,
v_top(i).parent_serial_number
FROM mtl_serial_numbers
WHERE gen_object_id = v_top(i).object_id;
SELECT msi.concatenated_segments item_number ,
msi.primary_uom_code ,
mln.lot_number ,
waid.from_serial_number from_serial_number ,
waid.to_serial_number to_serial_number ,
wdd.organization_id ,
NULL job_name ,
msi.inventory_item_id ,
mln.origination_date ,
mln.best_by_date ,
mln.retest_date ,
mln.expiration_date ,
waid.organization_code ,
1 rlevel ,
0 v_parent_rlevel ,
msi.lot_control_code ,
msi.serial_number_control_code ,
decode(( SELECT upper(mcr.cross_reference)
FROM mtl_cross_references_vl mcr
WHERE mcr.inventory_item_id = msi.inventory_item_id + 0
AND mcr.organization_id = msi.organization_id + 0
AND to_char(mcr.last_update_date, 'DD/MM/YYYY-HH24.MM.SS') = (SELECT MAX(to_char(mcr1.last_update_date, 'DD/MM/YYYY-HH24.MM.SS'))
FROM mtl_cross_references_vl mcr1
WHERE mcr1.inventory_item_id = msi.inventory_item_id + 0
AND mcr1.organization_id = msi.organization_id + 0
AND mcr1.cross_reference_type ='OPSM INTEGRATED')), 'NO', 0, 1) cross_reference,
(SELECT mcr.attribute1
FROM mtl_cross_references_vl mcr
WHERE mcr.inventory_item_id = msi.inventory_item_id + 0
AND mcr.organization_id = (SELECT master_organization_id
FROM mtl_parameters
WHERE organization_id = msi.organization_id + 0)
AND mcr.cross_reference_type ='OPSM INTEGRATED'
AND mcr.cross_reference = 'YES') serial_type,
NULL parent_lot_number ,
NULL parent_serial_number ,
0 parent_inventory_item_id
FROM mtl_system_items_vl msi ,
mtl_lot_numbers mln ,
wsh_opsm_asn_item_details_v waid ,
wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = p_delivery_detail_id
AND wdd.organization_id = p_organization_id
AND waid.delivery_detail_id = wdd.delivery_detail_id + 0
AND waid.organization_id = wdd.organization_id + 0
AND msi.inventory_item_id = wdd.inventory_item_id + 0
AND msi.organization_id = wdd.organization_id + 0
AND mln.inventory_item_id = wdd.inventory_item_id + 0
AND mln.organization_id = wdd.organization_id + 0
AND mln.lot_number = waid.lot_number || ''
AND waid.from_serial_number = p_frm_serial
AND waid.to_serial_number = p_to_serial;
'SELECT msi.concatenated_segments item_nbr ,
msi.primary_uom_code ,
mtln.lot_number ,
NULL serial_number ,
mmt.organization_id ,
we.wip_entity_name job_name ,
msi.inventory_item_id ,
mtln.origination_date ,
mtln.best_by_date ,
mtln.retest_date ,
mln.expiration_date ,
ood.organization_code ,
1 rlevel ,
0 v_parent_rlevel ,
msi.lot_control_code ,
msi.serial_number_control_code ,
decode(( SELECT upper(mcr.cross_reference)
FROM mtl_cross_references_vl mcr
WHERE mcr.inventory_item_id = msi.inventory_item_id+0
AND mcr.organization_id = msi.organization_id+0
AND to_char(mcr.last_update_date, ''DD/MM/YYYY-HH24.MM.SS'') = (SELECT MAX(to_char(mcr1.last_update_date, ''DD/MM/YYYY-HH24.MM.SS''))
FROM mtl_cross_references_vl mcr1
WHERE mcr1.inventory_item_id = msi.inventory_item_id+0
AND mcr1.organization_id = msi.organization_id+0
AND mcr1.cross_reference_type =''OPSM INTEGRATED'')),''NO'',0,1) cross_reference,
(SELECT mcr.attribute1
FROM mtl_cross_references_vl mcr
WHERE mcr.inventory_item_id = msi.inventory_item_id+0
AND mcr.organization_id = (SELECT master_organization_id
FROM mtl_parameters
WHERE organization_id=msi.organization_id+0)
AND mcr.cross_reference_type =''OPSM INTEGRATED''
AND mcr.cross_reference = ''YES'') serial_type,
NULL parent_lot_number ,
NULL parent_serial_number ,
0 parent_inventory_item_id
FROM mtl_transaction_lot_numbers mtln ,
wip_entities we ,
mtl_system_items_vl msi ,
org_organization_definitions ood ,
mtl_material_transactions mmt ,
mtl_object_genealogy mog ,
mtl_lot_numbers mln
WHERE mmt.transaction_source_id+0 = we.wip_entity_id
AND msi.inventory_item_id = mmt.inventory_item_id+0
AND msi.organization_id = mmt.organization_id+0
AND we.gen_object_id = mog.object_id
AND mog.parent_object_id = :1
AND mln.inventory_item_id = mtln.inventory_item_id+0
AND mln.organization_id = mtln.organization_id+0
AND mln.lot_number = mtln.lot_number||''''
AND ood.organization_id = mtln.organization_id
AND mmt.transaction_id = mog.origin_txn_id+0
AND mtln.transaction_id(+) = mmt.transaction_id+0
AND msi.serial_number_control_code NOT IN (6)';
'SELECT mtln.lot_number ,
mut.serial_number ,
msi.concatenated_segments item_nbr ,
msi.primary_uom_code ,
msi.inventory_item_id ,
we.wip_entity_name job_name ,
mtln.origination_date ,
mtln.best_by_date ,
mtln.retest_date ,
mln.expiration_date ,
ood.organization_code ,
abs(mmt.transaction_quantity) transaction_quantity,
msi.lot_control_code ,
msi.serial_number_control_code ,
decode(( SELECT upper(mcr.cross_reference)
FROM mtl_cross_references_vl mcr
WHERE mcr.inventory_item_id = msi.inventory_item_id+0
AND mcr.organization_id = msi.organization_id+0
AND to_char(mcr.last_update_date, ''DD/MM/YYYY-HH24.MM.SS'') = (SELECT MAX(to_char(mcr1.last_update_date, ''DD/MM/YYYY-HH24.MM.SS''))
FROM mtl_cross_references_vl mcr1
WHERE mcr1.inventory_item_id = msi.inventory_item_id+0
AND mcr1.organization_id = msi.organization_id+0
AND mcr1.cross_reference_type =''OPSM INTEGRATED'')),''NO'',0,1) cross_reference,
(SELECT mcr.attribute1
FROM mtl_cross_references_vl mcr
WHERE mcr.inventory_item_id = msi.inventory_item_id+0
AND mcr.organization_id = (SELECT master_organization_id
FROM mtl_parameters
WHERE organization_id=msi.organization_id+0)
AND mcr.cross_reference_type =''OPSM INTEGRATED''
AND mcr.cross_reference = ''YES'') serial_type,
NULL parent_lot_number ,
NULL parent_serial_number ,
0 parent_inventory_item_id
FROM mtl_material_transactions mmt1 ,
mtl_transaction_lot_numbers mtln1 ,
mtl_unit_transactions mut1 ,
mtl_material_transactions mmt ,
mtl_transaction_lot_numbers mtln ,
mtl_unit_transactions mut ,
wip_entities we ,
mtl_system_items_vl msi ,
org_organization_definitions ood ,
mtl_lot_numbers mln
WHERE mtln1.transaction_id+0 = mmt1.transaction_id
AND mut1.transaction_id+0 = mtln1.serial_transaction_id
AND mmt1.transaction_type_id = 44
AND mmt1.transaction_quantity = 1
AND mut1.organization_id = :1
AND mut1.transaction_date = mut1.transaction_date
AND mut1.inventory_item_id = mut1.inventory_item_id
AND mut1.serial_number = :2
AND mmt1.inventory_item_id = :3
AND mmt.transaction_set_id = mmt1.transaction_set_id
AND mmt.transaction_type_id = 35
AND mtln.transaction_id = mmt.transaction_id+0
AND mut.transaction_id = mtln.serial_transaction_id+0
AND we.wip_entity_id = mmt.transaction_source_id+0
AND we.organization_id = :4
AND msi.inventory_item_id = mmt.inventory_item_id+0
AND msi.organization_id = mmt.organization_id+0
AND ood.organization_id = mmt.organization_id+0
AND mln.lot_number = mtln.lot_number||''''
AND mln.inventory_item_id = mmt.inventory_item_id+0
AND mln.organization_id = mtln.organization_id+0';
SELECT ROWNUM row_num ,
mog.object_id ,
mog.object_type ,
mtln.lot_number ,
mtln.transaction_source_id ,
msn.inventory_item_id ,
mut.serial_number ,
we.wip_entity_name job_name
FROM mtl_object_genealogy mog ,
mtl_transaction_lot_numbers mtln ,
mtl_unit_transactions mut ,
mtl_serial_numbers msn ,
mtl_material_transactions mmt ,
wip_entities we
WHERE msn.serial_number = r_serial_number
AND msn.inventory_item_id = r_item_id
AND mut.serial_number = r_serial_number
AND mut.inventory_item_id = r_item_id
AND mut.organization_id = r_org_id
AND mtln.organization_id = r_org_id
AND mut.transaction_id + 0 = mtln.serial_transaction_id
AND msn.serial_number = mut.serial_number
AND msn.inventory_item_id = mut.inventory_item_id
AND we.wip_entity_id = mtln.transaction_source_id + 0
AND we.organization_id = mtln.organization_id + 0
AND mog.object_id = msn.gen_object_id
AND mmt.transaction_id = mtln.transaction_id + 0
AND mmt.transaction_type_id = 44
AND mmt.transaction_action_id = 31
ORDER BY 7 DESC;
SELECT (LEVEL - 1) rlevel ,
mog.* ,
'' serial_number ,
'' parent_serial_number ,
0 delivery_id ,
0 rTree
FROM mtl_object_genealogy mog
WHERE object_type IN (1, 2)
AND genealogy_origin = 1
AND (end_date_active IS NULL
OR TRUNC(end_date_active) >= TRUNC(SYSDATE))
START WITH parent_object_id = rv_object_id
CONNECT BY PRIOR object_id = parent_object_id;
SELECT we.wip_entity_name job_name ,
msi.concatenated_segments item_number ,
msi.primary_uom_code ,
msn.lot_number ,
msn.serial_number ,
mtln.origination_date ,
mtln.best_by_date ,
mtln.retest_date ,
mtln.expiration_date ,
ood.organization_code ,
msi.inventory_item_id ,
msi.organization_id ,
msi.lot_control_code ,
msi.serial_number_control_code ,
decode(( SELECT upper(mcr.cross_reference)
FROM mtl_cross_references_vl mcr
WHERE mcr.inventory_item_id = msi.inventory_item_id + 0
AND mcr.organization_id = msi.organization_id + 0
AND to_char(mcr.last_update_date, 'DD/MM/YYYY-HH24.MM.SS') = (SELECT MAX(to_char(mcr1.last_update_date, 'DD/MM/YYYY-HH24.MM.SS'))
FROM mtl_cross_references_vl mcr1
WHERE mcr1.inventory_item_id = msi.inventory_item_id + 0
AND mcr1.organization_id = msi.organization_id + 0
AND mcr1.cross_reference_type ='OPSM INTEGRATED')), 'NO', 0, 1) cross_reference,
(SELECT mcr.attribute1
FROM mtl_cross_references_vl mcr
WHERE mcr.inventory_item_id = msi.inventory_item_id + 0
AND mcr.organization_id = (SELECT master_organization_id
FROM mtl_parameters
WHERE organization_id = msi.organization_id + 0)
AND mcr.cross_reference_type ='OPSM INTEGRATED'
AND mcr.cross_reference = 'YES') serial_type
FROM wip_entities we ,
mtl_system_items_vl msi ,
mtl_serial_numbers msn ,
mtl_lot_numbers mtln ,
org_organization_definitions ood,
mtl_cross_references mcr
WHERE msn.gen_object_id = r_gen_object_id
AND msi.inventory_item_id = msn.inventory_item_id
AND msi.organization_id = we.organization_id + 0
AND we.wip_entity_id = msn.original_wip_entity_id
AND mtln.lot_number = msn.lot_number || ''
AND mtln.organization_id = we.organization_id + 0
AND ood.organization_id = we.organization_id + 0;
SELECT waid.genealogy_object_id
FROM mtl_object_genealogy mog ,
mtl_material_transactions mmt ,
wip_entities we ,
mtl_system_items_vl msi ,
wsh_opsm_asn_item_details_v waid ,
mtl_transaction_lot_numbers mtln ,
mtl_unit_transactions mut ,
mtl_lot_numbers mln ,
org_organization_definitions ood
WHERE mog.parent_object_id = waid.genealogy_object_id + 0
AND mmt.transaction_id = mog.origin_txn_id + 0
AND mmt.transaction_type_id = 44
AND mmt.transaction_action_id = 31
AND we.wip_entity_id = mmt.transaction_source_id + 0
AND msi.inventory_item_id = mmt.inventory_item_id + 0
AND msi.organization_id = mmt.organization_id + 0
AND waid.delivery_detail_id = r_delivery_detail_id
AND mtln.transaction_id = mmt.transaction_id + 0
AND mut.transaction_id = mtln.serial_transaction_id + 0
AND mln.lot_number = mtln.lot_number || ''
AND mln.inventory_item_id = mtln.inventory_item_id + 0
AND mln.organization_id = mtln.organization_id + 0
AND ood.organization_id = msi.organization_id + 0
AND ood.organization_id = r_organization_id;
SELECT DECODE(wms_enabled_flag, 'Y', 1, 0)
INTO v_wms_enabled
FROM mtl_parameters
WHERE organization_id = p_organization_id;
v_tab_nodes.DELETE(v_tab_nodes.LAST);
v_tab_nodes.DELETE(v_tab_nodes.LAST);
SELECT wnd.delivery_id
INTO v_delivery_id
FROM mtl_transaction_details_v mtd ,
mtl_material_transactions mmt ,
wsh_new_deliveries wnd
WHERE mtd.object_id = v_top(i).object_id
AND mmt.transaction_id = mtd.transaction_id + 0
AND wnd.delivery_id = mmt.trx_source_delivery_id + 0;
SELECT wnd.delivery_id
INTO v_delivery_id1
FROM mtl_transaction_details_v mtd ,
mtl_material_transactions mmt ,
wsh_new_deliveries wnd
WHERE mtd.object_id = v_top(i).object_id
AND mmt.transaction_id = mtd.transaction_id + 0
AND wnd.delivery_id = mmt.trx_source_delivery_id + 0;
SELECT serial_number,
NVL(parent_serial_number, serial_number || TO_CHAR(i))
INTO v_top(i).serial_number,
v_top(i).parent_serial_number
FROM mtl_serial_numbers
WHERE gen_object_id = v_top(i).object_id
AND (serial_number <> parent_serial_number
OR parent_serial_number IS NULL);
SELECT serial_number,
serial_number
INTO v_top(i).serial_number,
v_top(i).parent_serial_number
FROM mtl_serial_numbers
WHERE gen_object_id = v_top(i).object_id;