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