[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
INTO l_retval
FROM mtl_lot_numbers
WHERE gen_object_id = p_object_id;
SELECT 2
INTO l_retval
FROM mtl_serial_numbers
WHERE gen_object_id = p_object_id;
SELECT 5
INTO l_retval
FROM wip_entities
WHERE gen_object_id = p_object_id;
SELECT concatenated_segments
, msivl.description
, mln.expiration_date
, msivl.primary_uom_code
, mln.inventory_item_id
, mln.lot_number
, mln.status_id
INTO p_object_name
, p_object_description
, p_expiration_date
, p_primary_uom
, p_inventory_item_id
, p_object_number
, l_status_id
FROM mtl_system_items_vl msivl, mtl_lot_numbers mln
WHERE mln.gen_object_id = p_object_id
AND mln.inventory_item_id = msivl.inventory_item_id
AND mln.organization_id = msivl.organization_id;
SELECT status_code
INTO p_material_status
FROM mtl_material_statuses_vl
WHERE status_id = l_status_id;
SELECT concatenated_segments
, msivl.description
, msn.end_item_unit_number
, msn.serial_number
, msn.inventory_item_id
, msn.status_id
INTO p_object_name
, p_object_description
, p_unit_number
, p_object_number
, p_inventory_item_id
, l_status_id
FROM mtl_system_items_vl msivl, mtl_serial_numbers msn
WHERE msn.gen_object_id = p_object_id
AND msn.inventory_item_id = msivl.inventory_item_id
AND msn.current_organization_id = msivl.organization_id;
SELECT status_code
INTO p_material_status
FROM mtl_material_statuses_vl
WHERE status_id = l_status_id;
SELECT meaning
INTO p_object_type_name
FROM mfg_lookups
WHERE lookup_code = p_object_type
AND lookup_type = 'INV_GENEALOGY_OBJECT_TYPE';
SELECT concatenated_segments
, msivl.description
, mln.expiration_date
, msivl.primary_uom_code
, mln.inventory_item_id
, mln.lot_number
, mln.status_id
INTO p_object_name
, p_object_description
, p_expiration_date
, p_primary_uom
, p_inventory_item_id
, p_object_number
, l_status_id
FROM mtl_system_items_vl msivl, mtl_lot_numbers mln
WHERE mln.gen_object_id = p_object_id
AND mln.inventory_item_id = msivl.inventory_item_id
AND mln.organization_id = msivl.organization_id;
SELECT status_code
INTO p_material_status
FROM mtl_material_statuses_vl
WHERE status_id = l_status_id;
SELECT concatenated_segments
, msivl.description
, msn.end_item_unit_number
, msn.serial_number
, msn.inventory_item_id
, msn.status_id
, msn.wip_entity_id
, msn.operation_seq_num
, msn.intraoperation_step_type
, msn.lot_number
--Serial Tracking in WIP project. Retrieve wip_entity_id, operation_seq_num and
-- intraoperation_step_type also.
INTO p_object_name
, p_object_description
, p_unit_number
, p_object_number
, p_inventory_item_id
, l_status_id
, x_wip_entity_id
, x_operation_seq_num
, x_intraoperation_step_type
, x_current_lot_number
FROM mtl_system_items_vl msivl, mtl_serial_numbers msn
WHERE msn.gen_object_id = p_object_id
AND msn.inventory_item_id = msivl.inventory_item_id
AND msn.current_organization_id = msivl.organization_id;
SELECT status_code
INTO p_material_status
FROM mtl_material_statuses_vl
WHERE status_id = l_status_id;
SELECT meaning
INTO p_object_type_name
FROM mfg_lookups
WHERE lookup_code = p_object_type
AND lookup_type = 'INV_GENEALOGY_OBJECT_TYPE';
SELECT 1
FROM DUAL
WHERE EXISTS (
SELECT 1 FROM MTL_OBJECT_GENEALOGY
WHERE GENEALOGY_ORIGIN = 1 AND OBJECT_TYPE = 2 AND PARENT_OBJECT_TYPE = 5
AND ( END_DATE_ACTIVE IS NULL OR END_DATE_ACTIVE > SYSDATE )
AND OBJECT_ID = nvl(p_object_id, p_object_id2)
union ALL
SELECT MOG.PARENT_OBJECT_ID
FROM MTL_OBJECT_GENEALOGY MOG
WHERE OBJECT_TYPE = 2 AND PARENT_OBJECT_TYPE = 2
AND OBJECT_ID = nvl(p_object_id, p_object_id2)
AND EXISTS (
SELECT 1 FROM MTL_OBJECT_GENEALOGY
WHERE GENEALOGY_ORIGIN = 1 AND OBJECT_TYPE = 2 AND PARENT_OBJECT_TYPE = 5
AND ( END_DATE_ACTIVE IS NULL OR END_DATE_ACTIVE > SYSDATE )
AND OBJECT_ID = MOG.PARENT_OBJECT_ID )
union ALL
SELECT MOG.PARENT_OBJECT_ID
FROM MTL_OBJECT_GENEALOGY MOG
WHERE OBJECT_TYPE = 1 AND PARENT_OBJECT_TYPE = 5
AND OBJECT_ID = nvl(p_object_id, p_object_id2)
AND EXISTS (
SELECT 1 FROM MTL_OBJECT_GENEALOGY
WHERE GENEALOGY_ORIGIN = 1 AND OBJECT_TYPE = 2 AND PARENT_OBJECT_TYPE = 5
AND ( END_DATE_ACTIVE IS NULL OR END_DATE_ACTIVE > SYSDATE )
AND PARENT_OBJECT_ID = MOG.PARENT_OBJECT_ID)
union ALL
SELECT MOG.OBJECT_ID
FROM MTL_OBJECT_GENEALOGY MOG
WHERE PARENT_OBJECT_TYPE = 1 AND OBJECT_TYPE = 5
AND PARENT_OBJECT_ID = nvl(p_object_id, p_object_id2)
AND EXISTS (
SELECT 1 FROM MTL_OBJECT_GENEALOGY
WHERE GENEALOGY_ORIGIN = 1 AND OBJECT_TYPE = 2 AND PARENT_OBJECT_TYPE = 5
AND ( END_DATE_ACTIVE IS NULL OR END_DATE_ACTIVE > SYSDATE )
AND PARENT_OBJECT_ID = MOG.OBJECT_ID )
union ALL
SELECT 1 FROM MTL_OBJECT_GENEALOGY MOG
WHERE PARENT_OBJECT_TYPE = 5 AND OBJECT_TYPE = 2 AND GENEALOGY_ORIGIN=1
AND PARENT_OBJECT_ID=p_object_id
) ; --bug 13866934
SELECT 1 --when queried by asembly serial
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM mtl_object_genealogy
WHERE genealogy_origin = 1
AND object_type = 2
AND parent_object_type = 5
AND (end_date_active IS NULL OR end_date_active > SYSDATE)
AND (object_id = NVL (p_object_id, p_object_id2))
OR --when quereid by component serial
EXISTS (
SELECT 1
FROM DUAL
WHERE EXISTS (
SELECT mog.parent_object_id
FROM mtl_object_genealogy mog
WHERE object_type = 2
AND parent_object_type = 2
AND object_id =
NVL (p_object_id, p_object_id2)
AND EXISTS (
SELECT 1
--if queried by comp serial then check if it's parent aser has job as parent
FROM mtl_object_genealogy
WHERE genealogy_origin = 1
AND object_type = 2
AND parent_object_type = 5
AND ( end_date_active IS NULL
OR end_date_active > SYSDATE
)
AND object_id =
mog.parent_object_id))))
OR
-- if queried by component lot, then check if it's parent has aser as child
EXISTS (
SELECT 1
FROM DUAL
WHERE EXISTS (
SELECT mog.parent_object_id
FROM mtl_object_genealogy mog
WHERE object_type = 1
AND parent_object_type = 5
AND object_id = NVL (p_object_id, p_object_id2)
AND EXISTS (
SELECT 1
FROM mtl_object_genealogy
WHERE genealogy_origin = 1
AND object_type = 2
AND parent_object_type = 5
AND ( end_date_active IS NULL
OR end_date_active > SYSDATE
)
AND parent_object_id = mog.parent_object_id)))
OR
--if queried by assembly lot of lot serial, then check if it's parent has aser as child
EXISTS (
SELECT 1
FROM DUAL
WHERE EXISTS (
SELECT mog.object_id
FROM mtl_object_genealogy mog
WHERE parent_object_type = 1
AND object_type = 5
AND parent_object_id = NVL (p_object_id, p_object_id2)
AND EXISTS (
SELECT 1
FROM mtl_object_genealogy
WHERE genealogy_origin = 1
AND object_type = 2
AND parent_object_type = 5
AND ( end_date_active IS NULL
OR end_date_active > SYSDATE
)
AND parent_object_id = mog.object_id)))
OR
--if queried by job and if it has aser as child
EXISTS (
SELECT 1
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM mtl_object_genealogy mog
WHERE parent_object_type = 5
AND object_type = 2
AND genealogy_origin=1
AND parent_object_id=p_object_id)) ;*/
SELECT lot_number || l_genealogy_delimitter || concatenated_segments
INTO l_object_number
FROM mtl_lot_numbers mln, mtl_system_items_kfv msikfv
WHERE mln.gen_object_id = p_object_id
AND mln.inventory_item_id = msikfv.inventory_item_id
AND mln.organization_id = msikfv.organization_id;
SELECT concatenated_segments || l_genealogy_delimitter || lot_number
INTO l_object_number
FROM mtl_lot_numbers mln, mtl_system_items_kfv msikfv
WHERE mln.gen_object_id = p_object_id
AND mln.inventory_item_id = msikfv.inventory_item_id
AND mln.organization_id = msikfv.organization_id;
SELECT lot_number
INTO l_object_number
FROM mtl_lot_numbers mln, mtl_system_items_kfv msikfv
WHERE mln.gen_object_id = p_object_id
AND mln.inventory_item_id = msikfv.inventory_item_id
AND mln.organization_id = msikfv.organization_id;
SELECT lot_number
INTO l_lot_number
FROM mtl_lot_numbers mln, mtl_system_items_kfv msikfv
WHERE mln.gen_object_id = p_object_id2
AND mln.inventory_item_id = msikfv.inventory_item_id
AND mln.organization_id = msikfv.organization_id;
SELECT l_lot_number
|| DECODE(l_lot_number, NULL, '', l_genealogy_delimitter)
|| serial_number
|| l_genealogy_delimitter
|| concatenated_segments
INTO l_object_number
FROM mtl_serial_numbers msn, mtl_system_items_kfv msikfv
WHERE msn.gen_object_id = p_object_id
AND msn.inventory_item_id = msikfv.inventory_item_id
AND msn.current_organization_id = msikfv.organization_id;
SELECT concatenated_segments
|| DECODE(l_lot_number, NULL, '', l_genealogy_delimitter)
|| l_lot_number
|| l_genealogy_delimitter
|| serial_number
INTO l_object_number
FROM mtl_serial_numbers msn, mtl_system_items_kfv msikfv
WHERE msn.gen_object_id = p_object_id
AND msn.inventory_item_id = msikfv.inventory_item_id
AND msn.current_organization_id = msikfv.organization_id;
SELECT l_lot_number || DECODE(l_lot_number, NULL, '', l_genealogy_delimitter) || serial_number
INTO l_object_number
FROM mtl_serial_numbers msn, mtl_system_items_kfv msikfv
WHERE msn.gen_object_id = p_object_id
AND msn.inventory_item_id = msikfv.inventory_item_id
AND msn.current_organization_id = msikfv.organization_id;
SELECT l_lot_number || DECODE(l_lot_number, NULL, '', l_genealogy_delimitter) || serial_number
INTO l_object_number
FROM mtl_serial_numbers
WHERE gen_object_id = p_object_id;
SELECT we.primary_item_id
, we.organization_id
, we.wip_entity_name
INTO l_item_id
, l_orgn_id
, l_job_name
FROM wip_entities we
WHERE we.gen_object_id = p_object_id;
SELECT concatenated_segments
INTO l_con_seg
FROM mtl_system_items_kfv msikfv
WHERE msikfv.inventory_item_id = l_item_id
AND msikfv.organization_id = l_orgn_id;
SELECT segment1
INTO l_trx_src
FROM po_headers_all
WHERE po_header_id = p_trx_src_id;
SELECT SUBSTRB(concatenated_segments, 1, 30) --BUG11856545
INTO l_trx_src
FROM mtl_sales_orders_kfv
WHERE sales_order_id = p_trx_src_id;
SELECT request_number
INTO l_trx_src
FROM mtl_txn_request_headers
WHERE header_id = p_trx_src_id;
SELECT wip_entity_name
INTO l_trx_src
FROM wip_entities
WHERE wip_entity_id = p_trx_src_id
AND organization_id = p_org_id;
SELECT SUBSTR(concatenated_segments, 1, 30)
INTO l_trx_src
FROM mtl_generic_dispositions_kfv
WHERE disposition_id = p_trx_src_id
AND organization_id = p_org_id;
SELECT segment1
INTO l_trx_src
FROM po_requisition_headers_all
WHERE requisition_header_id = p_trx_src_id;
SELECT cycle_count_header_name
INTO l_trx_src
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = p_trx_src_id
AND organization_id = p_org_id;
SELECT physical_inventory_name
INTO l_trx_src
FROM mtl_physical_inventories
WHERE physical_inventory_id = p_trx_src_id
AND organization_id = p_org_id;
ELSIF p_trx_src_type = 11 THEN -- Standard Cost Update
SELECT description
INTO l_trx_src
FROM cst_cost_updates
WHERE cost_update_id = p_trx_src_id
AND organization_id = p_org_id;
SELECT COUNT(*)
INTO row_count
FROM mtl_txn_request_lines mol
WHERE txn_source_id = p_trx_src_id
AND organization_id = p_org_id
AND EXISTS(SELECT NULL
FROM mtl_txn_request_headers
WHERE header_id = mol.header_id
AND move_order_type = 5
AND mol.transaction_source_type_id = 13);
SELECT wip_entity_name
INTO l_trx_src
FROM wip_entities
WHERE wip_entity_id = p_trx_src_id
AND organization_id = p_org_id;
SELECT vendor_name
INTO l_trading_partner
FROM po_vendors pov, po_headers_all poh
WHERE poh.po_header_id = p_trx_src_id
AND poh.vendor_id = pov.vendor_id;
SELECT party_name
INTO l_trading_partner
FROM hz_parties hp, hz_cust_accounts hca ,
-- R12 TCA Mandate to replace RA_CUSTOMERS with the above 2
oe_order_headers_all sha, oe_order_lines_all sla
WHERE sla.line_id = p_trx_src_line_id
AND sha.header_id = sla.header_id
--AND sha.sold_to_org_id = rac.customer_id; As part of R12 TCA changes
SELECT organization_code
INTO l_trading_partner
FROM mtl_parameters mp
WHERE mp.organization_id = p_transfer_org_id;
DELETE FROM mtl_gen_temp;
SELECT *
FROM mtl_gen_temp
WHERE treeno < g_treeno
AND DEPTH < g_depth
ORDER BY ind DESC;
SELECT object_id
, parent_object_id
, object_type
, NULL object_id2
, NULL object_type2
, NULL parent_object_id2
FROM mtl_object_genealogy
WHERE parent_object_id = p_object_id
AND object_type<>2
AND (end_date_active is null or end_date_active > SYSDATE);
SELECT object_id
, parent_object_id
, object_type
, object_id2
, object_type2
, parent_object_id2
FROM mtl_object_genealogy
WHERE parent_object_id = p_object_id
AND (p_object_id2 IS NULL OR parent_object_id2 = p_object_id2)
AND (end_date_active IS NULL OR end_date_active > SYSDATE);
SELECT object_id
, parent_object_id
, object_type
, object_id2
, object_type2
, parent_object_id2
FROM mtl_object_genealogy
WHERE parent_object_id = p_object_id
AND (p_object_id2 IS NULL OR parent_object_id2 = p_object_id2)
And (End_Date_Active Is Null Or End_Date_Active > Sysdate)
order by creation_date desc;
SELECT object_id
, parent_object_id
, object_type
, object_id2
, object_type2
, parent_object_id2
FROM mtl_object_genealogy
WHERE parent_object_id = p_object_id
AND (p_object_id2 IS NULL OR parent_object_id2 = p_object_id2)
And (End_Date_Active Is Null Or End_Date_Active > Sysdate)
order by creation_date asc;
SELECT 1
INTO l_count
FROM mtl_gen_temp
WHERE label = p_object_id
AND related_label = 0
AND (p_object_id2 is null or label2=p_object_id2)
AND ROWNUM < 2;
inv_trx_util_pub.TRACE('insert1, g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
INSERT INTO mtl_gen_temp
(
ind
, treeno
, DEPTH
, label
, related_label
, child_object_type
, label2
, child_object_type2
)
VALUES (
g_ind
, g_treeno
, g_depth
, p_object_id
, p_related_object_id
, p_object_type
, p_object_id2
, p_object_type2
);
SELECT 1
INTO l_count
FROM mtl_gen_temp
WHERE treeno = g_treeno
AND label = p_object_id
AND (p_object_id2 is null or label2=p_object_id2)
AND ROWNUM < 2;
inv_trx_util_pub.TRACE('insert2 g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
INSERT INTO mtl_gen_temp
(
ind
, treeno
, DEPTH
, label
, related_label
, child_object_type
, label2
, child_object_type2
)
VALUES (
g_ind
, g_treeno
, g_depth
, p_object_id
, p_related_object_id
, p_object_type
, p_object_id2
, p_object_type2
);
inv_trx_util_pub.TRACE('insert3 g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
INSERT INTO mtl_gen_temp
(
ind
, treeno
, DEPTH
, label
, related_label
, child_object_type
, label2
, child_object_type2
)
VALUES (
g_ind
, g_treeno
, g_depth
, p_object_id
, p_related_object_id
, p_object_type
, p_object_id2
, p_object_type2
);
inv_trx_util_pub.TRACE('insert4 g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
INSERT INTO mtl_gen_temp
(
ind
, treeno
, DEPTH
, label
, related_label
, child_object_type
, label2
, child_object_type2
)
VALUES (
g_ind
, g_treeno
, g_depth
, p_object_id
, p_related_object_id
, p_object_type
, p_object_id2
, p_object_type2
);
SELECT level
, mog.object_id
, mog.parent_object_id
, mog.object_type
, NULL object_id2
, NULL object_type2
, NULL parent_object_id2
FROM mtl_object_genealogy mog
-- WHERE object_type<>2--((object_type = 2 AND parent_object_type = object_type) OR (object_type <> 2))
-- AND (end_date_active is null or end_date_active > SYSDATE)
START WITH (parent_object_id=p_object_id and (end_date_active is null or end_date_active > SYSDATE))
CONNECT BY nocycle prior object_id = parent_object_id --12701288
AND object_type<>2 --((object_type = 2 AND parent_object_type = object_type) OR (object_type <> 2))
AND (end_date_active is null or end_date_active > SYSDATE);
SELECT LEVEL
, object_id
, parent_object_id
, object_type
, object_id2
, object_type2
, parent_object_id2
FROM mtl_object_genealogy
-- WHERE end_date_active IS NULL
-- OR end_date_active > SYSDATE
START WITH (parent_object_id=p_object_id AND (end_date_active IS NULL OR end_date_active > SYSDATE))
CONNECT BY nocycle PRIOR object_id = parent_object_id --12701288
AND (end_date_active IS NULL OR end_date_active > SYSDATE)
AND LEVEL < v_level_num;
INSERT INTO mtl_gen_temp
(
ind
, treeno
, DEPTH
, label
, related_label
, child_object_type
, label2
, child_object_type2
)
VALUES (
g_ind
, g_treeno
, g_depth
, p_object_id
, p_related_object_id
, p_object_type
, p_object_id2
, p_object_type2
);
INSERT INTO mtl_gen_temp
(
ind
, treeno
, DEPTH
, label
, related_label
, child_object_type
, label2
, child_object_type2
)
VALUES
( g_ind,
g_treeno,
child_rec.level+1,
child_rec.object_id,
child_rec.parent_object_id,
child_rec.object_type,
child_rec.object_id2,
child_rec.object_type2);
SELECT Count(*)
INTO n_rows
FROM mtl_object_genealogy
--WHERE level < n_level
START WITH (parent_object_id=p_object_id AND (end_date_active IS NULL OR end_date_active > SYSDATE))
CONNECT BY nocycle PRIOR object_id = parent_object_id --12701288
AND (end_date_active IS NULL OR end_date_active > SYSDATE)
AND LEVEL < n_level;
INSERT INTO mtl_gen_temp
(
ind
, treeno
, DEPTH
, label
, related_label
, child_object_type
, label2
, child_object_type2
)
VALUES
( g_ind,
g_treeno,
child_rec.level+1,
child_rec.object_id,
child_rec.parent_object_id,
child_rec.object_type,
child_rec.object_id2,
child_rec.object_type2);
SELECT parent_object_id
, object_id
, parent_object_type
, parent_object_id2
, parent_object_type2, object_id2,object_type2,object_type
FROM mtl_object_genealogy
WHERE object_id = p_object_id
AND (p_object_id2 IS NULL OR object_id2 = p_object_id2)
AND(end_date_active IS NULL
OR end_date_active > SYSDATE);
SELECT *
FROM mtl_gen_temp
WHERE treeno < g_treeno
AND DEPTH < g_depth
ORDER BY ind DESC;
SELECT 1
INTO l_count
FROM mtl_gen_temp
WHERE label = p_object_id
AND related_label = 0
AND (p_object_id2 is null or label2=p_object_id2)
AND ROWNUM < 2;
inv_trx_util_pub.TRACE('insert1, g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
INSERT INTO mtl_gen_temp
(
ind
, treeno
, DEPTH
, label
, related_label
, child_object_type
, label2
, child_object_type2
)
VALUES (
g_ind
, g_treeno
, g_depth
, p_object_id
, p_related_object_id
, p_object_type
, p_object_id2
, p_object_type2
);
SELECT 1
INTO l_count
FROM mtl_gen_temp
WHERE treeno = g_treeno
AND label = p_object_id
AND (p_object_id2 is null or label2=p_object_id2)
AND ROWNUM < 2;
inv_trx_util_pub.TRACE('insert2, g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
INSERT INTO mtl_gen_temp
(
ind
, treeno
, DEPTH
, label
, related_label
, child_object_type
, label2
, child_object_type2
)
VALUES (
g_ind
, g_treeno
, g_depth
, p_object_id
, p_related_object_id
, p_object_type
, p_object_id2
, p_object_type2
);
inv_trx_util_pub.TRACE('insert3, g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
INSERT INTO mtl_gen_temp
(
ind
, treeno
, DEPTH
, label
, related_label
, child_object_type
, label2
, child_object_type2
)
VALUES (
g_ind
, g_treeno
, g_depth
, p_object_id
, p_related_object_id
, p_object_type
, p_object_id2
, p_object_type2
);
inv_trx_util_pub.TRACE('insert4, g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
INSERT INTO mtl_gen_temp
(
ind
, treeno
, DEPTH
, label
, related_label
, child_object_type
, label2
, child_object_type2
)
VALUES (
g_ind
, g_treeno
, g_depth
, p_object_id
, p_related_object_id
, p_object_type
, p_object_id2
, p_object_type2
);
SELECT LEVEL
, mog.parent_object_id
, mog.object_id
, mog.parent_object_type
, mog.parent_object_id2
, mog.parent_object_type2
FROM mtl_object_genealogy mog
-- WHERE end_date_active IS NULL
-- OR end_date_active > SYSDATE
START WITH (object_id = p_object_id AND (end_date_active IS NULL OR end_date_active > SYSDATE))
CONNECT BY nocycle PRIOR parent_object_id = object_id --12701288
AND (end_date_active IS NULL OR end_date_active > SYSDATE)
AND LEVEL < v_level_num;
INSERT INTO mtl_gen_temp
(
ind
, treeno
, DEPTH
, label
, related_label
, child_object_type
, label2
, child_object_type2
)
VALUES (
g_ind
, g_treeno
, g_depth
, p_object_id
, p_related_object_id
, p_object_type
, p_object_id2
, p_object_type2
);
SELECT Count(*)
INTO n_rows
FROM mtl_object_genealogy
--WHERE level < n_level
START WITH (object_id=p_object_id AND (end_date_active IS NULL OR end_date_active > SYSDATE))
CONNECT BY nocycle PRIOR parent_object_id = object_id --12701288
AND (end_date_active IS NULL OR end_date_active > SYSDATE)
AND LEVEL < n_level;
INSERT INTO mtl_gen_temp
(
ind
, treeno
, DEPTH
, label
, related_label
, child_object_type
, label2
, child_object_type2
)
VALUES (
g_ind
,g_treeno
,parent_rec.level+1
,parent_rec.parent_object_id
,parent_rec.object_id
,parent_rec.parent_object_type
,parent_rec.parent_object_id2
,parent_rec.parent_object_type2);