Boa noite,
Em nossa sessão SQL, segue query para listar toda a hierarquia para um determinado usuário.
-- Query HIERARQUIA
SELECT PEHA.POSITION_STRUCTURE_ID,
PEHA.EMPLOYEE_ID,
FNDU.USER_ID,
PPOS.POSITION_ID,
PPS.NAME HIERARCHY,
FNDU.USER_NAME USERNAME,
PAPF.FULL_NAME PERSON,
PPOS.NAME POSITION,
PEHA.SUPERIOR_LEVEL SUPERIORPOSITIONLEVEL,
PPOS2.NAME SUPERIORPOSITION,
PAPF2.FULL_NAME SUPERIORPERSON,
FNDU2.USER_NAME SUPERIORUSERNAME,
PEHA.SUPERIOR_ID SUPERIORPERSONID,
FNDU2.USER_ID SUPERIORUSERID,
PPOS2.POSITION_ID SUPERIORPOSID
FROM PO_EMPLOYEE_HIERARCHIES_ALL PEHA,
PER_POSITIONS PPOS,
PER_POSITIONS PPOS2,
PER_ALL_PEOPLE_F PAPF,
PER_ALL_PEOPLE_F PAPF2,
FND_USER FNDU,
FND_USER FNDU2,
PER_POSITION_STRUCTURES PPS
WHERE PPS.BUSINESS_GROUP_ID = PEHA.BUSINESS_GROUP_ID
AND PPS.POSITION_STRUCTURE_ID = PEHA.POSITION_STRUCTURE_ID
AND FNDU2.EMPLOYEE_ID = PAPF2.PERSON_ID
AND PAPF2.PERSON_ID = PEHA.SUPERIOR_ID
AND PAPF2.EFFECTIVE_END_DATE > SYSDATE
AND PAPF.PERSON_ID = PEHA.EMPLOYEE_ID
AND PAPF.EFFECTIVE_END_DATE > SYSDATE
AND PPOS2.POSITION_ID = PEHA.SUPERIOR_POSITION_ID
AND PPOS.POSITION_ID = PEHA.EMPLOYEE_POSITION_ID
AND PEHA.SUPERIOR_LEVEL > 0
AND PEHA.EMPLOYEE_ID = FNDU.EMPLOYEE_ID
-----------------------------------------------
AND FNDU.USER_NAME = UPPER('BRUNOP') -- Informe Usuario Oracle EBS
-----------------------------------------------
ORDER BY PEHA.POSITION_STRUCTURE_ID,
PEHA.SUPERIOR_LEVEL,
PAPF2.FULL_NAME;
Abraço,
Junio Vitor