Monday 8 May 2017

oracle hrms employee supervisor hierarchy query

/* Formatted on 5/9/2017 10:07:55 AM (QP5 v5.114.809.3010) */
    SELECT   DISTINCT LPAD (' ', 5 * (LEVEL - 1)) || PPF.FULL_NAME,
                      PERA.SUPERVISOR_ID,
                      PERA.PERSON_ID,
                      PER_JOBS.NAME JobName,
                      LEVEL,
                      SYS_CONNECT_BY_PATH (pera.person_id, '/') PATH
      FROM   PER_ASSIGNMENTS_F PERA, PER_JOBS, PER_ALL_PEOPLE_F PPF
     WHERE   PER_JOBS.JOB_ID = PERA.JOB_ID
             AND TRUNC (SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
                                     AND  PPF.EFFECTIVE_END_DATE
             AND PPF.PERSON_ID = PERA.PERSON_ID
             AND EXISTS
                   (SELECT   '1'
                      FROM   PER_PEOPLE_F PERF, PER_ASSIGNMENTS_F PERA1
                     WHERE   TRUNC (SYSDATE) BETWEEN PERF.EFFECTIVE_START_DATE
                                                 AND  PERF.EFFECTIVE_END_DATE
                             AND PERF.PERSON_ID = PERA.SUPERVISOR_ID
                             AND PERA1.PERSON_ID = PERF.PERSON_ID
                             AND TRUNC (SYSDATE) BETWEEN PERA1.EFFECTIVE_START_DATE
                                                     AND  PERA1.EFFECTIVE_END_DATE
                             AND PERA1.PRIMARY_FLAG = 'Y'
                             AND PERA1.ASSIGNMENT_TYPE = 'E'
                             AND EXISTS
                                   (SELECT   '1'
                                      FROM   PER_PERSON_TYPES PPT
                                     WHERE   PPT.SYSTEM_PERSON_TYPE IN
                                                   ('EMP', 'EMP_APL')
                                             AND PPT.PERSON_TYPE_ID =
                                                   PERF.PERSON_TYPE_ID))
START WITH   PERA.PERSON_ID = 200
             --      (SELECT employee_id FROM fnd_user WHERE user_name = '&UserName' ) -- ** Replace DCROCKETT with your username
             AND TRUNC (SYSDATE) BETWEEN PERA.EFFECTIVE_START_DATE
                                     AND  PERA.EFFECTIVE_END_DATE
             AND PERA.PRIMARY_FLAG = 'Y'
             AND PERA.ASSIGNMENT_TYPE = 'E'
CONNECT BY   NOCYCLE PRIOR PERA.PERSON_ID = PERA.SUPERVISOR_ID
                     AND TRUNC (SYSDATE) BETWEEN PERA.EFFECTIVE_START_DATE
                                             AND  PERA.EFFECTIVE_END_DATE
                     AND PERA.PRIMARY_FLAG = 'Y'
                     AND PERA.ASSIGNMENT_TYPE = 'E'
--AND LEVEL <= 4
ORDER BY PATH

Output:-

1 comment: