0 Replies Latest reply on Sep 23, 2017 6:44 PM by Kevin P.K.

    SQL Query in EBS for User, Security Profile, Responsibilities.

    Kevin P.K.

      We are using EBS 12.1 and I have been asked to provide an export that provides the Full Name, their Responsibilities, their supervisor, and the MO:Security Profile or the HR: Security Profile that is assigned to the users. We have had success with the query below that provides the first three but we can't seem to find a way to attach the Security Profile to the query and have it work. Any suggestions or direction would be appreciated.

       

      SELECT PER.full_name

      ,FU.USER_NAME

      , FRTL.RESPONSIBILITY_NAME

      --,PER.PERSON_ID
      --,paaf1.person_id,
      --,papf1.full_name leve1_full_name
      -- , papf1.employee_number level1_empno
        , papf2.full_name leve2_full_name

      -- , papf2.employee_number level2_empno
      --,paaf2.person_id
        

      FROM

      APPS.FND_USER_RESP_GROUPS_ALL FURGA,

      APPS.FND_USER FU,

      APPS.FND_USER FUCB,

      APPS.FND_USER FULUB,

      APPS.FND_USER FULUL,

      APPS.FND_APPLICATION FA,

      APPS.FND_RESPONSIBILITY_TL FRTL,

      APPS.FND_SECURITY_GROUPS FSG,

      APPS.PER_PEOPLE_F PER,

       

      hr.per_all_people_f papf1,

      hr.per_all_assignments_f paaf1,

      hr.per_all_assignments_f paaf2,

      hr.per_all_people_f papf2

       

      WHERE

      FURGA.USER_ID = FU.USER_ID (+)

      AND FURGA.CREATED_BY = FUCB.USER_ID (+)

      AND FURGA.LAST_UPDATED_BY = FULUB.USER_ID (+)

      AND FURGA.LAST_UPDATE_LOGIN = FULUL.USER_ID (+)

      AND FURGA.RESPONSIBILITY_APPLICATION_ID = FA.APPLICATION_ID (+)

      AND FURGA.RESPONSIBILITY_ID = FRTL.RESPONSIBILITY_ID (+)

      AND FRTL.LANGUAGE = 'US'

      AND FURGA.SECURITY_GROUP_ID = FSG.SECURITY_GROUP_ID (+)

      AND FU.EMPLOYEE_ID = PER.PERSON_ID(+)

      AND (to_char(FU.END_DATE) is null

        OR FU.END_DATE > sysdate)

      AND (to_char(FURGA.END_DATE) is null

        OR FURGA.END_DATE > sysdate) 

      AND papf1.person_id = PER.PERSON_ID

       

      AND papf1.person_id = paaf1.person_id

      AND paaf1.supervisor_id = papf2.person_id(+)

      AND papf2.person_id = paaf2.person_id

       

      ORDER BY papf2.full_name;