Query: Oracle Employee Supervisor Hierarchy

This Query is about linking Employee record to their immediate Supervisor and their Supervisor's. This come handy mostly in the case of Workflow Approval process where we have to see who this notification should be going for approval.

SELECT LEVEL, a.*
FROM (SELECT a.full_name employee_name,
supervisor_id,
c.full_name supervisor_name
FROM per_people_f a, per_assignments_f b, per_people_f c
WHERE 1 = 1
AND a.effective_end_date > SYSDATE
AND c.effective_end_date > SYSDATE
AND a.person_id = b.person_id
AND b.effective_end_date > SYSDATE
AND c.person_id = b.supervisor_id) a
START WITH employee_name LIKE '%' || :employee_name || '%'
CONNECT BY PRIOR supervisor_name = employee_name
ORDER BY 1