• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:June 12, 2020
  • Reading time:5 mins read
You are currently viewing SQL Query to fetch Supervisor Direct and Indirect reportees
SQL Query to fetch Supervisor Direct and Indirect reportees

In this article we will look into getting in the Direct and Indirect reportees of a Supervisor using SQL Query.

We assign the Supervisor on the Employment screen and they get stored in the PER_ASSIGNMENT_SUPERVISORS_F table. We will have to join the PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_M and PER_ASSIGNMENT_SUPERVISORS_F tables to get the manager information of an employee.

However Fusion has a scheduled process “Refresh Manager Hierarchy” which needs to be scheduled daily to refresh data in PER_MANAGER_HRCHY_DN table. We will look into this process in detail in another post.

This table PER_MANAGER_HRCHY_DN stores the flattened manager hierarchy with the latest information retrieved from PER_ALL_ASSIGNMENTS_M and PER_ASSIGNMENT_SUPERVISORS_F tables.
Below are some of the important fields this tables fetches data:
MANAGER_ID
MANAGER_ASSIGNMENT_ID
MANAGER_TYPE
MANAGER_LEVEL
PRIMARY_MANAGER_FLAG
PERSON_ID
ASSIGNMENT_ID
PRIMARY_ASSIGNMENT_FLAG
EFFECTIVE_START_DATE
EFFECTIVE_END_DATE

So, we will use this supervisor hierarchy table to get the Direct and Indirect reportees now.

MANAGER_TYPE is a lookup code and we need to use the right MANAGER_TYPE to get the right hierarchy. Normally everyone uses the LINE_MANAGER hierarchy, However some clients create new manager types as per their requirement.

We can check the lookups that have been configured for Manager types using the below Query:

select * from hr_lookups where lookup_type = ‘PER_SUPERVISOR_TYPE’

Manager Lookup - SQL Query to fetch Supervisor Direct and Indirect reportees

SQL Query:

SELECT ppnf_emp.full_name
,pmhd.person_id
,papf_emp.person_number
,pmhd.assignment_id
,pmhd.manager_id
,papf_sup.person_number manager_number
,pmhd.manager_assignment_id
,pmhd.manager_level
,pmhd.manager_type
,pmhd.effective_start_date
,pmhd.effective_end_date
,decode(pmhd.manager_level, '1', 'Direct Reportee', 'Indirect Reportee') Direct_Indirect
FROM per_manager_hrchy_dn pmhd
,per_person_names_f_v ppnf_emp
,per_all_people_f papf_emp
,per_all_people_f papf_sup
,per_person_names_f_v ppnf_sup
WHERE 1 = 1
AND pmhd.manager_type = 'LINE_MANAGER'
AND pmhd.person_id = ppnf_emp.person_id
AND ppnf_emp.person_id = papf_emp.person_id
AND papf_sup.person_id = pmhd.manager_id
AND ppnf_sup.person_id = pmhd.manager_id
AND ppnf_emp.name_type = 'GLOBAL'
AND ppnf_sup.name_type = 'GLOBAL'
AND sysdate BETWEEN papf_emp.effective_start_date
AND papf_emp.effective_end_date
AND sysdate BETWEEN papf_sup.effective_start_date
AND papf_sup.effective_end_date
AND sysdate BETWEEN ppnf_emp.effective_start_date
AND ppnf_emp.effective_end_date
AND sysdate BETWEEN ppnf_sup.effective_start_date
AND ppnf_sup.effective_end_date
AND sysdate BETWEEN pmhd.effective_start_date
AND pmhd.effective_end_date
AND papf_sup.person_number = :MANAGER_PERSON_NUMBER
--AND pmhd.manager_level = '1' -- Use 1 for direct reports, comment it for all reportees
ORDER BY papf_emp.person_number

You can pass the Manager Person as input to the above query to get the reportees reporting to that manager.

There is one more condition which is commented above.. Manager_Level = ‘1’. Manager Level of ‘1’ indicates direct reportees to that manager and anything above ‘1’ will indicate indirect reportees.

You can also change the order by clause to order by reportees person number, name, or manager level.

If you have any questions, please feel free to reach out to me by posting in comments section.

If you are interested in learning Fusion Technical tools go through this post

If you liked the article, please share it with your friends/ colleagues/ teammates or anyone who might also benefit from it.