Oracle EBS SQL Query that will list all employees in the hr.per_all_assignments_f table and their supervisor, in readable format. Output is [employee full name, employee number, supervisor full name, supervisor employee number], and ordered by employee name.

OracleEBS-Employee-Supervisor-Query.sql

# Oracle EBS SQL Query that will list all employees in the hr.per_all_assignments_f 
# table and their supervisor, in readable format. Output is [employee full name, 
# employee number, supervisor full name, supervisor employee number], and ordered 
# by employee name.

SELECT DISTINCT papf1.full_name leve1_full_name
     , papf1.employee_number level1_empno
     , papf2.full_name leve2_full_name
     , papf2.employee_number level2_empno
  FROM hr.per_all_people_f papf1
     , hr.per_all_assignments_f paaf1
     , hr.per_all_assignments_f paaf2
     , hr.per_all_people_f papf2
  WHERE  papf1.person_id = paaf1.person_id
  AND paaf1.supervisor_id = papf2.person_id(+)
  AND papf2.person_id = paaf2.person_id
  ORDER BY leve1_full_name;