I was recently asked this question.
The following script finds the Employee Supervisor Hierarchy:
SELECT paf.person_id, paf.supervisor_id
, LPAD (' ', 2 * LEVEL - 1)
|| SYS_CONNECT_BY_PATH (paf.person_id, '/') PATH
, LEVEL, paf.job_id -- used for linking to hr.per_jobs ,
, paf.effective_start_date, paf.effective_end_date
FROM per_all_assignments_f paf
START WITH paf.person_id = <:person_id>
AND paf.primary_flag = 'Y'
AND paf.assignment_type = 'E'
AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date
AND assignment_status_type_id = 1
CONNECT BY PRIOR paf.person_id = paf.supervisor_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type = 'E'
AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date
AND assignment_status_type_id = 1
Note: In order to find the Person_id, select Person_id, Last_name, First_name from Per_all_people_f
The above sql will find topdown (Highest Level and follows down)
If you want to start from the lowest level and go all the way to CEO, then change the CONNECT BY clause as mentioned below:
CONNECT BY PRIOR paf.supervisor_id = paf.person_id
