2,019 total views
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:
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’
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.
Latest posts by Sricharan Monigari (see all)
- Renaming HCM Extracts is a possibility from 20D - November 24, 2020
- Sangam20 – Oracle Users Group Conference - November 23, 2020
- Passing Parameter values from Flow Instance to HDL Transformation Formula - November 23, 2020
- Solve manual FTP server update issue for HCM Extracts post every P2T refresh - November 16, 2020