SELECT e.*
FROM (SELECT DISTINCT
papf.employee_number,
papf.full_name "EMPLOYEE_FULL_NAME",
papf1.employee_number "SUPERVISOR_EMP_NUMBER",
papf1.full_name "SUPERVISOR_FULL_NAME",
papf.person_id,
paaf.supervisor_id
FROM apps.per_all_people_f papf,
apps.per_all_assignments_f paaf,
apps.per_all_people_f papf1,
apps.per_person_types ppt
WHERE papf.person_id = paaf.person_id
AND papf1.person_id = paaf.supervisor_id
AND papf.business_group_id = paaf.business_group_id
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND TRUNC (SYSDATE) BETWEEN papf1.effective_start_date
AND papf1.effective_end_date
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND ppt.person_type_id = papf.person_type_id
AND ppt.user_person_type <> 'Ex-employee') e
CONNECT BY PRIOR person_id = supervisor_id
START WITH person_id = :Person_id ; -- List the person id to know who all report under him like Manager id or person id of VP
FROM (SELECT DISTINCT
papf.employee_number,
papf.full_name "EMPLOYEE_FULL_NAME",
papf1.employee_number "SUPERVISOR_EMP_NUMBER",
papf1.full_name "SUPERVISOR_FULL_NAME",
papf.person_id,
paaf.supervisor_id
FROM apps.per_all_people_f papf,
apps.per_all_assignments_f paaf,
apps.per_all_people_f papf1,
apps.per_person_types ppt
WHERE papf.person_id = paaf.person_id
AND papf1.person_id = paaf.supervisor_id
AND papf.business_group_id = paaf.business_group_id
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND TRUNC (SYSDATE) BETWEEN papf1.effective_start_date
AND papf1.effective_end_date
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND ppt.person_type_id = papf.person_type_id
AND ppt.user_person_type <> 'Ex-employee') e
CONNECT BY PRIOR person_id = supervisor_id
START WITH person_id = :Person_id ; -- List the person id to know who all report under him like Manager id or person id of VP