Skip to content

Instantly share code, notes, and snippets.

@jgilmour

jgilmour/OracleEBS-Employee-Supervisor-TreeView.sql

Last active December 11, 2015 14:09
Show Gist options
  • Save jgilmour/4612481 to your computer and use it in GitHub Desktop.
Save jgilmour/4612481 to your computer and use it in GitHub Desktop.
Download ZIP
Oracle EBS SQL Query which will take an employees person_id (on line 8), and run a query to the organizational hierarchy for that employee. Output: Employee1 Name -> Employee1 Manager Name -> Director of Whatever Name -> CEO Name
Raw
OracleEBS-Employee-Supervisor-TreeView.sql
# Oracle EBS SQL Query which will take an employees person_id (on line 8),
# and run a query to the organizational hierarchy for that employee.
# Output: Employee1 Name -> Employee1 Manager Name -> Director of Whatever Name -> CEO Name
SELECT lpad('->',8*(level-1)) ||
(
select distinct full_name from per_all_people_f
where person_id = paf.person_id
and sysdate between effective_start_date and effective_end_date
) TREE
FROM per_all_assignments_f paf
START WITH paf.person_id = <PUT_IN_PERSON_ID>
AND paf.primary_flag = 'Y'
AND SYSDATE BETWEEN paf.effective_start_date AND
paf.effective_end_date
CONNECT BY paf.person_id = PRIOR paf.supervisor_id
AND paf.primary_flag = 'Y'
AND SYSDATE BETWEEN paf.effective_start_date AND
paf.effective_end_date
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment