SQL Query to get the list of direct reports from a line manager

select papf_Reportee.person_number,

to_char(pasf.EFFECTIVE_START_DATE,'yyyy/dd/mm') start_dt_spr

from per_all_people_f papf,

per_All_assignments_m paam,

PER_ASSIGNMENT_SUPERVISORS_F pasf,

per_all_people_F papf_reportee

where papf.person_id = paam.person_id

and paam.assignment_id = pasf.MANAGER_ASSIGNMENT_ID

and papf.person_number = '839' -- enter the line manager or supervisor person number

and sysdate between trunc(papf.effective_start_Date) and trunc(papf.effective_End_Date)

and sysdate between trunc(paam.effective_start_Date) and trunc(paam.effective_End_Date)

and papf_Reportee.person_id = pasf.PERSON_ID

and sysdate between trunc(papf_Reportee.effective_start_Date) and trunc(papf_Reportee.effective_End_Date)

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.

Posted by : Mohammad