SELECT NAME PATH_NAME,PARENT_NAME FROM
(SELECT pps.position_structure_id,pps.NAME, LPAD (' ', 5 * LEVEL) || has.NAME HIERARCHY,
has.position_id, LEVEL rep_level, hap.NAME parent_name,
pse.parent_position_id, has.NAME child_name,
pse.subordinate_position_id
FROM (SELECT NAME, position_id
FROM hr_all_positions_f_tl
WHERE LANGUAGE = USERENV ('LANG')) hap,
(SELECT NAME, position_id
FROM hr_all_positions_f_tl
WHERE LANGUAGE = USERENV ('LANG')) has,
per_pos_structure_elements pse,
per_pos_structure_versions pve,
per_position_structures pps
WHERE pse.business_group_id = 0
AND pve.position_structure_id = pps.position_structure_id
AND pse.pos_structure_version_id = pve.pos_structure_version_id
AND SYSDATE BETWEEN pve.date_from AND NVL (pve.date_to, SYSDATE)
AND hap.position_id = pse.parent_position_id
AND has.position_id = pse.subordinate_position_id
START WITH pse.parent_position_id =
(SELECT parent_position_id
FROM per_pos_structure_elements a
WHERE a.pos_structure_version_id = pse.pos_structure_version_id
AND a.pos_structure_element_id =
(SELECT MIN (pos_structure_element_id)
FROM per_pos_structure_elements b
WHERE b.pos_structure_version_id =
a.pos_structure_version_id))
CONNECT BY PRIOR pse.subordinate_position_id = pse.parent_position_id
AND PRIOR pse.pos_structure_version_id = pse.pos_structure_version_id
AND PRIOR pse.business_group_id = pse.business_group_id)
WHERE position_structure_id=
(Select approval_path_id From po_action_history pha where object_id= :po_header_id and sequence_num=
(Select max(sequence_num) From po_action_history pha where object_id= :po_header_id) And ROWNUM=1)
GROUP BY NAME,PARENT_NAME;
(SELECT pps.position_structure_id,pps.NAME, LPAD (' ', 5 * LEVEL) || has.NAME HIERARCHY,
has.position_id, LEVEL rep_level, hap.NAME parent_name,
pse.parent_position_id, has.NAME child_name,
pse.subordinate_position_id
FROM (SELECT NAME, position_id
FROM hr_all_positions_f_tl
WHERE LANGUAGE = USERENV ('LANG')) hap,
(SELECT NAME, position_id
FROM hr_all_positions_f_tl
WHERE LANGUAGE = USERENV ('LANG')) has,
per_pos_structure_elements pse,
per_pos_structure_versions pve,
per_position_structures pps
WHERE pse.business_group_id = 0
AND pve.position_structure_id = pps.position_structure_id
AND pse.pos_structure_version_id = pve.pos_structure_version_id
AND SYSDATE BETWEEN pve.date_from AND NVL (pve.date_to, SYSDATE)
AND hap.position_id = pse.parent_position_id
AND has.position_id = pse.subordinate_position_id
START WITH pse.parent_position_id =
(SELECT parent_position_id
FROM per_pos_structure_elements a
WHERE a.pos_structure_version_id = pse.pos_structure_version_id
AND a.pos_structure_element_id =
(SELECT MIN (pos_structure_element_id)
FROM per_pos_structure_elements b
WHERE b.pos_structure_version_id =
a.pos_structure_version_id))
CONNECT BY PRIOR pse.subordinate_position_id = pse.parent_position_id
AND PRIOR pse.pos_structure_version_id = pse.pos_structure_version_id
AND PRIOR pse.business_group_id = pse.business_group_id)
WHERE position_structure_id=
(Select approval_path_id From po_action_history pha where object_id= :po_header_id and sequence_num=
(Select max(sequence_num) From po_action_history pha where object_id= :po_header_id) And ROWNUM=1)
GROUP BY NAME,PARENT_NAME;