How To Query Supervisor Chain Of Command For A User

How To Query Supervisor Chain Of Command For A User

How to query for supervisors hierarchy above an approver user when using employee supervisor chain of command? 

Solution
Substitute the username in place of DCROCKETT in the following query.  This is useful when troubleshooting approval list issues with employee supervisor relationships for requisitions and purchasing documents

select fndu.user_name, pecx.full_name 
From FND_USER fndu, per_employees_current_x pecx, 
( 
SELECT PERA.SUPERVISOR_ID --, fndu.user_name, pecx.full_name 
FROM 
PER_ASSIGNMENTS_F PERA 
WHERE EXISTS 
(SELECT '1' FROM PER_PEOPLE_F PERF, 
PER_ASSIGNMENTS_F PERA1 WHERE TRUNC(SYSDATE) BETWEEN 
PERF.EFFECTIVE_START_DATE AND PERF.EFFECTIVE_END_DATE AND PERF.PERSON_ID = 
PERA.SUPERVISOR_ID AND PERA1.PERSON_ID = PERF.PERSON_ID AND TRUNC(SYSDATE) 
BETWEEN PERA1.EFFECTIVE_START_DATE AND PERA1.EFFECTIVE_END_DATE AND 
PERA1.PRIMARY_FLAG = 'Y' AND PERA1.ASSIGNMENT_TYPE = 'E' AND EXISTS (SELECT 
'1' FROM PER_PERSON_TYPES PPT WHERE PPT.SYSTEM_PERSON_TYPE IN ('EMP', 
'EMP_APL') AND PPT.PERSON_TYPE_ID = PERF.PERSON_TYPE_ID)) START WITH 
PERA.PERSON_ID = 
(select employee_id from fnd_user 
where user_name = 'DCROCKETT') -- ** Replace DCROCKETT with your username 
AND TRUNC(SYSDATE) BETWEEN PERA.EFFECTIVE_START_DATE 
AND PERA.EFFECTIVE_END_DATE AND PERA.PRIMARY_FLAG = 'Y' AND 
PERA.ASSIGNMENT_TYPE = 'E' 
CONNECT BY PRIOR PERA.SUPERVISOR_ID = 
PERA.PERSON_ID AND TRUNC(SYSDATE) BETWEEN PERA.EFFECTIVE_START_DATE AND 
PERA.EFFECTIVE_END_DATE AND PERA.PRIMARY_FLAG = 'Y' AND 
PERA.ASSIGNMENT_TYPE = 'E' 
) c 
where fndu.employee_id = c.supervisor_id and pecx.employee_id = c.supervisor_id