In this article we will look into the “Refresh Manager Hierarchy” process and it’s usage.
Table of Contents
Why Refresh Manager Hierarchy?
In many situations, a person’s manager hierarchy must be readily available. For example, a person’s line manager may be required during an approval process, and business intelligence OTBI reports often retrieve data based on a manager hierarchy.
How the Manager Hierarchy Is Maintained
A person’s manager hierarchy could be derived from active data tables PER_ALL_ASSIGNMENTS_M and PER_ASSIGNMENT_SUPERVISORS_F, but the impact of that approach on performance is unpredictable. Therefore, the complete manager hierarchy for each person is extracted from data tables and stored in a separate manager hierarchy table PER_MANAGER_HRCHY_DN.
This table is known as the denormalized manager hierarchy. The denormalized manager hierarchy ensures that a person’s manager hierarchy is both easily accessible and up to date and we can get the next level managers too easily.
Running the Refresh Manager Hierarchy Process
Whenever a change is made to a person’s manager hierarchy through the application pages, the change has to be reflected automatically in the denormalized manager hierarchy table. So, In order to accomplish this, we need to run the Refresh Manager Hierarchy process to populate the denormalized manager hierarchy table when person records are migrated from other applications.
We run the Refresh Manager Hierarchy process in the Scheduled Processes work area.
Security Access Required to run this process: Human Resource Specialist job role
The process has no default schedule. You can run the process occasionally to perform a complete refresh of the denormalized manager hierarchy. Alternatively, you can specify a schedule to run the process at regular intervals. Refresh Manager Hierarchy processes all types of manager hierarchies.
In addition to performing full refreshes of the manager hierarchy, you can perform incremental refreshes. With this approach, you refresh the hierarchy based on manager changes occurring in the previous N days. Schedule a full refresh every month or quarter and an incremental refresh every day or week.
For populating data into old denormalized tables (PER_MANAGER_HRCHY_DN) process can be run with No value/BLANK for run in full mode and +ve for incremental i.e. process assignment supervisors data updated in the last N days
The existing denormalized tables (PER_MANAGER_HRCHY_DN) is needed for evaluating line manager security, head counts and some of UI flows in HCM are based on this, also used for the Assignment list dimensions for OTBI
For populating data into new denormalized tables (PER_MANAGER_HRCHY_REPORTEES_DN) process can be run with 0 for run in full model and -ve for incremental i.e. process assignment supervisors data updated in the last N days
The new denormalized table (PER_MANAGER_HRCHY_REPORTEES_DN) is specific to OTBI reporting only and for Assignment manager dimensions data to be populated, so running with parameter 0 is needed only if there is OTBI reporting requirement and can be scheduled to run alternate days if needed
Navigator > Tools > Scheduled Processes > Schedule New Process
Choose “Refresh Manager Hierarchy” , click OK
We have an input parameter “Updated Within the Last N Days” which is crucial to why we are trying to update the Manager Hierarchy.
HCM OTBI subject areas have multiple hierarchical dimensions related to supervisors and workers. Each of the hierarchies have their own usage in various contexts.
The list of managerial and assignment hierarchies includes the following
- Assignment Manager
- Assignment Manager (Unsecured)
- Assignment Manager List
- Assignment Manager List (Unsecured)
- Matrix Manager
- Parent Managers
| Scenario | Updated Within the Last N Days | Comments |
| Full Refresh | Null/blank | This does a full refresh of the following manager hierarchies a. Assignment Manager List b. Assignment Manager List (Unsecured) c. Parent Managers |
| <Positive Integer> | for example: 5 | The above hierarchies would be refreshed with all the changes that have happened in the last 5 days |
| Full Refresh | 0 | This does a full refresh of the following manager hierarchies only without doing changes to other hierarchies. a. Assignment Manager b. Assignment Manager (Unsecured) c. Matrix Manager |
| <Negative Integer> | For example: -5 | The above hierarchies would be refreshed with all the changes that have happened in the last 5 days |
So, if you want to use the Line Manager hierarchy for both line manager security and all manager hierarchies in OTBI, then you would need to schedule the “Refresh Manager Hierarchy” process two times one for a positive days input value and another for negative days input value daily/alternate days.
Once in a month you can run the full refresh for manager hierarchies again two times, one with blank input value and another with 0 input value.
Do you know how to create report with the the line manager direct and indirect reportees?
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.
hi Sricharan, A great one again.
For standard Line Manager, do we still need this process? I believe it automatically populates through subject-areas.
thanks,
Santosh
No Santosh. We still need to run this process to populate the manager hierarchies in OTBI.
Interesting. Thanks for the quick response.
Hi Sricharan,
I’d like to use this to see the hierarchy regardless of if the manager is active or not.
Example: Employee -> Manager Level 1(Inactive) -> Manager Level 2 (Active) -> Manager Level 3 (Active)
In the above example, I’m only able to see the level 1 manager. Level 2 and 3 are not showing. Need this in an interface that guarantees there is an active manager. So thought I would use this and skip over the Level 1 and assign the employee to the Level 2. Doesn’t seem like the table (for current date) stores the hierarchy, if there is an inactive manager in the hierarchy?
Hi James,
I don’t think the manager hierarchies work if they are inactive. You will have to write SQL query explicitly to get the inactive manager hierarchies if that’s the requirement.
Thanks,
Sricharan