Thursday, 8 May 2014

oracle hrms supervisor hierarchy query


SELECT paf.person_id, paf.supervisor_id
, LPAD (' ', 2 * LEVEL - 1)
|| SYS_CONNECT_BY_PATH (paf.person_id, '/') PATH
, LEVEL, paf.job_id -- used for linking to hr.per_jobs ,
, paf.effective_start_date, paf.effective_end_date
FROM per_all_assignments_f paf
START WITH paf.person_id = <:person_id>
AND paf.primary_flag = 'Y'
AND paf.assignment_type = 'E'
AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date
AND assignment_status_type_id = 1
CONNECT BY PRIOR paf.person_id = paf.supervisor_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type = 'E'
AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date
AND assignment_status_type_id = 1

No comments:

Post a Comment