VIEW: APPS.FND_USER_RESP_GROUPS_ALL

Source

View Text - Preformatted

select U.user_id user_id,
      WUR.ROLE_ORIG_SYSTEM_ID RESPONSIBILITY_ID ,
      (select application_id
          from fnd_application
         where application_short_name =/* Val between 1st and 2nd separator */
                 replace(
                   substr(WAUR.ROLE_NAME,
                        INSTR(WAUR.ROLE_NAME, '|', 1, 1)+1,
                             ( INSTR(WAUR.ROLE_NAME, '|', 1, 2)
                              -INSTR(WAUR.ROLE_NAME, '|', 1, 1)-1)
                        )
                   ,'%col', ':')
       ) RESPONSIBILITY_APPLICATION_ID,
       (select security_group_id
          from fnd_security_groups
         where security_group_key =/* Val after 3rd separator */
                 replace(
                   substr(WAUR.ROLE_NAME,
                          INSTR(WAUR.ROLE_NAME, '|', 1, 3)+1
                        )
                   ,'%col', ':')
       ) SECURITY_GROUP_ID,
       WAUR.START_DATE,
       WAUR.END_DATE END_DATE,
       WAUR.CREATED_BY CREATED_BY,
       WAUR.CREATION_DATE CREATION_DATE,
       WAUR.LAST_UPDATED_BY LAST_UPDATED_BY,
       WAUR.LAST_UPDATE_DATE LAST_UPDATE_DATE,
       WAUR.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
from fnd_user u, wf_all_user_role_assignments waur,
     wf_all_user_roles wur
where u.user_name = waur.user_name
  and waur.role_name = wur.role_name
  and waur.user_name = wur.user_name
  and wur.role_orig_system = 'FND_RESP'
  and not waur.role_name like 'FND_RESP|%|ANY'
   

View Text - HTML Formatted

SELECT U.USER_ID USER_ID
, WUR.ROLE_ORIG_SYSTEM_ID RESPONSIBILITY_ID
, (SELECT APPLICATION_ID
FROM FND_APPLICATION
WHERE APPLICATION_SHORT_NAME =/* VAL BETWEEN 1ST
AND 2ND SEPARATOR */ REPLACE( SUBSTR(WAUR.ROLE_NAME
, INSTR(WAUR.ROLE_NAME
, '|'
, 1
, 1)+1
, ( INSTR(WAUR.ROLE_NAME
, '|'
, 1
, 2) -INSTR(WAUR.ROLE_NAME
, '|'
, 1
, 1)-1) )
, '%COL'
, ':') ) RESPONSIBILITY_APPLICATION_ID
, (SELECT SECURITY_GROUP_ID
FROM FND_SECURITY_GROUPS
WHERE SECURITY_GROUP_KEY =/* VAL AFTER 3RD SEPARATOR */ REPLACE( SUBSTR(WAUR.ROLE_NAME
, INSTR(WAUR.ROLE_NAME
, '|'
, 1
, 3)+1 )
, '%COL'
, ':') ) SECURITY_GROUP_ID
, WAUR.START_DATE
, WAUR.END_DATE END_DATE
, WAUR.CREATED_BY CREATED_BY
, WAUR.CREATION_DATE CREATION_DATE
, WAUR.LAST_UPDATED_BY LAST_UPDATED_BY
, WAUR.LAST_UPDATE_DATE LAST_UPDATE_DATE
, WAUR.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN FROM FND_USER U
, WF_ALL_USER_ROLE_ASSIGNMENTS WAUR
, WF_ALL_USER_ROLES WUR WHERE U.USER_NAME = WAUR.USER_NAME
AND WAUR.ROLE_NAME = WUR.ROLE_NAME
AND WAUR.USER_NAME = WUR.USER_NAME
AND WUR.ROLE_ORIG_SYSTEM = 'FND_RESP'
AND NOT WAUR.ROLE_NAME LIKE 'FND_RESP|%|ANY'