In this article, we will look into how to query the backend tables to pull the flattened supervisor hierarchy of the employees on a single row.
Supervisor information is stored with respect to the assignment information in PER_ASSIGNMENT_SUPERVISORS_F table and is linked to PER_ALL_ASSIGNMENTS_M table by ASSIGNMENT_ID. It stores the supervisor information by MANAGER_TYPE and the most commonly used Manager Type is ‘LINE_MANAGER’.
Now, if Employee A has B as Manager, B has C as manager and C has D as manager, it is really tough to get the complete manager hierarchy B > C > D in a single query.
I have attempted to give it a try by modifying the query using various functions and was able to get this achieved. This query will pull supervisors until it reaches the top-level/CEO who would supervise all employees.
SQL Query to pull flattened supervisor hierarchy information:
SELECT PAPF.PERSON_NUMBER, PAAM.ASSIGNMENT_NUMBER, SUP.ASSIGNMENT_ID, SUP.REPORTING_PATH FROM
(
SELECT ASSIGNMENT_ID, MAX(REPORTING_PATH) REPORTING_PATH FROM
(SELECT SUBSTR(SYS_CONNECT_BY_PATH(FULL_NAME, ' -> '), 5) AS REPORTING_PATH, CONNECT_BY_ROOT ASSIGNMENT_ID AS ASSIGNMENT_ID
FROM (
SELECT PASF.ASSIGNMENT_ID, PASF.MANAGER_ASSIGNMENT_ID, PPNF.FULL_NAME
FROM PER_ASSIGNMENT_SUPERVISORS_F PASF
JOIN PER_PERSON_NAMES_F PPNF
ON PPNF.PERSON_ID = PASF.MANAGER_ID
WHERE 1=1
AND TRUNC(SYSDATE) BETWEEN PASF.EFFECTIVE_START_DATE AND PASF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
AND PASF.MANAGER_TYPE = 'LINE_MANAGER'
AND PPNF.NAME_TYPE = 'GLOBAL'
) SUP
WHERE 1=1
CONNECT BY SUP.ASSIGNMENT_ID = PRIOR SUP.MANAGER_ASSIGNMENT_ID
) GROUP BY ASSIGNMENT_ID) SUP, PER_ALL_ASSIGNMENTS_M PAAM, PER_ALL_PEOPLE_F PAPF
WHERE SUP.ASSIGNMENT_ID = PAAM.ASSIGNMENT_ID
AND PAAM.PERSON_ID = PAPF.PERSON_ID
AND PAAM.ASSIGNMENT_TYPE = 'E'
AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND PAAM.PRIMARY_FLAG = 'Y'
AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATEHere is how the output will look like:
Hope you will find the query useful. Feel free to modify the query as per your requirement.
If you like the content, please follow us on LinkedIn, Facebook, and Twitter to get updated with the latest content.
