Tuesday, November 5, 2013

Low Level Employee Supervisor Relationships



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 = 1623
--      (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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect