To Get Concurrent Program Responsibility and Request Group

SELECT DISTINCT

   FCPL.USER_CONCURRENT_PROGRAM_NAME

 , FCP.CONCURRENT_PROGRAM_NAME

 , FAPP.APPLICATION_NAME

 , FRG.REQUEST_GROUP_NAME

 , FNRTL.RESPONSIBILITY_NAME

 FROM

   APPS.FND_REQUEST_GROUPS FRG

 , APPS.FND_APPLICATION_TL FAPP

 , APPS.FND_REQUEST_GROUP_UNITS FRGU

 , APPS.FND_CONCURRENT_PROGRAMS FCP

 , APPS.FND_CONCURRENT_PROGRAMS_TL FCPL

 , APPS.FND_RESPONSIBILITY FNR

 , APPS.FND_RESPONSIBILITY_TL FNRTL

 WHERE 1=1

 AND FRG.APPLICATION_ID=fapp.APPLICATION_ID

 AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID

 AND FRG.REQUEST_GROUP_ID =FRGU.REQUEST_GROUP_ID

 AND FRG.REQUEST_GROUP_ID =FNR.REQUEST_GROUP_ID

 AND FRG.APPLICATION_ID = FNR.APPLICATION_ID

 AND FNR.RESPONSIBILITY_ID =FNRTL.RESPONSIBILITY_ID

 AND FRGU.REQUEST_UNIT_ID =FCP.CONCURRENT_PROGRAM_ID

 AND FRGU.UNIT_APPLICATION_ID =FCP.APPLICATION_ID

 AND FCP.CONCURRENT_PROGRAM_ID =FCPL.CONCURRENT_PROGRAM_ID

 AND FCPL.USER_CONCURRENT_PROGRAM_NAME = 'Workflow Definitions Loader'

 AND FNRTL.LANGUAGE = 'US'

 ANDFAPP.LANGUAGE ='US';

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

FROMPER_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 BYPATH