Query to Retrieve Employee, Supervisor, and Manager Department Details in Oracle HCM

Select Distinct papf.person_number,
ppnf.display_name Emp_Name,
(SELECT ppnf1.display_name FROM per_person_names_f ppnf1
WHERE ppnf1.person_id = pasf.manager_id
AND ppnf1.name_type='GLOBAL'
AND TRUNC(SYSDATE) BETWEEN TRUNC(ppnf1.effective_start_date) AND TRUNC(ppnf1.effective_end_date)
) supervisor_name,
/*manager_Department_Name*/
(select MAX(HOUFT.NAME)
from HR_ORGANIZATION_UNITS_F_TL  HOUFT,
PER_ALL_ASSIGNMENTS_M  PAAM1
where 1=1
AND PAAM1.ORGANIZATION_ID = HOUFT.ORGANIZATION_ID(+)
AND HOUFT.language(+) = 'US'
and PAAM1.person_id = pasf1.manager_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(HOUFT.effective_start_date) AND TRUNC(HOUFT.effective_end_date)
AND TRUNC(SYSDATE) BETWEEN TRUNC(PAAM1.effective_start_date) AND TRUNC(PAAM1.effective_end_date)
)manager_Department_Name
from
HR_ALL_POSITIONS_F_TL  HAPFT,
PER_ALL_ASSIGNMENTS_M  PAAM,
per_all_people_f papf,
per_person_names_f ppnf,
per_assignment_supervisors_f pasf, -- supervisor_name
per_assignment_supervisors_f pasf1 -- manager_Department_Name
where 1=1
And PAPF.PERSON_ID = PPNF.PERSON_ID
And PPNF.NAME_TYPE = 'GLOBAL'
AND PAAM.PERSON_ID = PAPF.PERSON_ID
AND PAAM.ASSIGNMENT_TYPE = 'E'
AND PAAM.PRIMARY_FLAG = 'Y'
AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
And PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND PAAM.POSITION_ID = HAPFT.POSITION_ID
AND HAPFT.language = 'US'
AND PAAM.assignment_id = pasf.assignment_id -- supervisor_name
AND PAAM.assignment_id = pasf1.assignment_id -- manager_Department_Name
AND trunc(sysdate) BETWEEN HAPFT.effective_start_date AND HAPFT.effective_end_date
AND trunc(sysdate) BETWEEN PAAM.effective_start_date AND PAAM.effective_end_date
AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND trunc(sysdate) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN TRUNC (pasf1.effective_start_date(+)) AND TRUNC (pasf1.effective_end_date(+))
AND TRUNC(SYSDATE) BETWEEN TRUNC (pasf.effective_start_date(+)) AND TRUNC (pasf.effective_end_date(+))
ORDER by 1

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