Monday 30 September 2019

Query to Get 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 = 6247
    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'
ORDER BY PATH

3 comments:

  1. Appreciating the dedication you put into your website and in depth information you offer. It's good to come across a blog every once in a while that isn't the same outdated rehashed information. Excellent read! I've bookmarked your site and I'm adding your RSS feeds to my Google account.
    cloud services perth

    ReplyDelete
  2. can u say the query for fetching assignment supervisor details

    ReplyDelete