Query to find supervisor hierarchy : Oracle employee supervisor query

Query to find supervisor hierarchy : Oracle employee supervisor query

In this post , we will discuss about employee supervisor hierarchy query. In Oracle apps , When we define the Employees we also specifies that employee supervisor and that supervisor is also created in Oracle application as an Employee with his supervisor information's so this organization structure create the supervisor hierarchy in oracle apps. To find this supervisor hierarchy from oracle backend we need to have sql query. Here below I will share the complete sql query which helps to find the employee supervisor hierarchy from level 1.
 

Complete SQL Query to find supervisor hierarchy

select (select full_name from per_all_people_f where person_id=a1.person_id and effective_end_date > syadate) Employee_name,
 (select full_name from per_all_people_f where person_id=a1.supervisor_id and effective_end_date > syadate) Supervisor_name,
PATH,
LEVEL
from
(
SELECT paf.person_id, paf.supervisor_id
 , LPAD (' ', 2 * LEVEL - 1)
 || SYS_CONNECT_BY_PATH (paf.person_id, '/') PATH
 , LEVEL
 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) a1
order by LEVEL