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