Query to build the list the supervisors for a given employee in a single column
the format is as
Employee_1 -> Employee_2 -> Employee_3 -> Employee_4
where Employee_2 is the supervisor of Employee_1 and so on.
the format is as
Employee_1 -> Employee_2 -> Employee_3 -> Employee_4
where Employee_2 is the supervisor of Employee_1 and so on.
SELECT
DISTINCT SYS_CONNECT_BY_PATH(FULL_NAME, '->') "Path",
LEVEL LVL
FROM
PER_ALL_PEOPLE_F PPFS,PER_ALL_ASSIGNMENTS_F PAFE
WHERE 1 = 1
and PPFS.PERSON_ID = PAFE.person_ID
AND TRUNC (SYSDATE) BETWEEN PAFE.EFFECTIVE_START_DATE AND PAFE.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PPFS.EFFECTIVE_START_DATE AND PPFS.EFFECTIVE_END_DATE
and level = 3
CONNECT BY Ppfs.PERSON_ID = PRIOR Pafe.SUPERVISOR_ID -- PPFS,PAFE
START WITH Ppfs.PERSON_ID = &Numbe ;
DISTINCT SYS_CONNECT_BY_PATH(FULL_NAME, '->') "Path",
LEVEL LVL
FROM
PER_ALL_PEOPLE_F PPFS,PER_ALL_ASSIGNMENTS_F PAFE
WHERE 1 = 1
and PPFS.PERSON_ID = PAFE.person_ID
AND TRUNC (SYSDATE) BETWEEN PAFE.EFFECTIVE_START_DATE AND PAFE.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PPFS.EFFECTIVE_START_DATE AND PPFS.EFFECTIVE_END_DATE
and level = 3
CONNECT BY Ppfs.PERSON_ID = PRIOR Pafe.SUPERVISOR_ID -- PPFS,PAFE
START WITH Ppfs.PERSON_ID = &Numbe ;
No comments:
Post a Comment