Subject: SQL Query required to get the employee and Manager hierarchy
--SQL Query:--
SELECT
papf.employee_number,
papf.full_name,
papf.person_id,
ppg.segment1 officer_flag, --executive flag for officers (Y is officer, N is not)
nvl(spapf.full_name, 'NONE') supervisor_name,
spapf.person_id supv_person_id,
level,
pjobs.name "Job"
FROM
per_people_x papf,
per_people_x spapf,
per_assignments_x paaf,
pay_people_groups ppg,
per_jobs pjobs
WHERE
papf.person_id = paaf.person_id
AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date
AND sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND sysdate BETWEEN spapf.effective_start_date AND spapf.effective_end_date
--AND_xxx_person_type.is_ employee(:p_eff_date, papf.person_id)
AND paaf.assignment_type IN ( 'E', 'C' )
AND paaf.supervisor_id = spapf.person_id
AND paaf.people_group_id = ppg.people_group_id
AND paaf.primary_flag = 'Y'
AND pjobs.job_id (+) = paaf.job_id
-- AND pjobs.name like '%Senior Director%'
START WITH
papf.employee_number = :p_emp_num --Enter emp num of employee whom Hierachy Need to Find
CONNECT BY
PRIOR spapf.employee_number = papf.employee_number --AND LEVEL < :p_level
ORDER BY
level DESC