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(WURA.ROLE_NAME,
INSTR(WURA.ROLE_NAME, '|', 1, 1)+1,
( INSTR(WURA.ROLE_NAME, '|', 1, 2)
-INSTR(WURA.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(WURA.ROLE_NAME,
INSTR(WURA.ROLE_NAME, '|', 1, 3)+1
)
,'%col', ':')
) SECURITY_GROUP_ID,
WURA.START_DATE,
WURA.END_DATE END_DATE,
WURA.CREATED_BY CREATED_BY,
WURA.CREATION_DATE CREATION_DATE,
WURA.LAST_UPDATED_BY LAST_UPDATED_BY,
WURA.LAST_UPDATE_DATE LAST_UPDATE_DATE,
WURA.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN,
WURA.ASSIGNMENT_REASON DESCRIPTION
from fnd_user u, wf_user_role_assignments wura,
wf_all_user_roles wur
where u.user_name = wura.user_name
and wura.relationship_id <> -1
and wur.role_orig_system = 'FND_RESP'
and not wura.role_name like 'FND_RESP|%|ANY'
and wura.role_name = wur.role_name
and wura.user_name = wur.user_name
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(WURA.ROLE_NAME
,
INSTR(WURA.ROLE_NAME
, '|'
, 1
, 1)+1
,
( INSTR(WURA.ROLE_NAME
, '|'
, 1
, 2)
-INSTR(WURA.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(WURA.ROLE_NAME
,
INSTR(WURA.ROLE_NAME
, '|'
, 1
, 3)+1
)
, '%COL'
, ':')
) SECURITY_GROUP_ID
,
WURA.START_DATE
,
WURA.END_DATE END_DATE
,
WURA.CREATED_BY CREATED_BY
,
WURA.CREATION_DATE CREATION_DATE
,
WURA.LAST_UPDATED_BY LAST_UPDATED_BY
,
WURA.LAST_UPDATE_DATE LAST_UPDATE_DATE
,
WURA.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
,
WURA.ASSIGNMENT_REASON DESCRIPTION
FROM FND_USER U
, WF_USER_ROLE_ASSIGNMENTS WURA
,
WF_ALL_USER_ROLES WUR
WHERE U.USER_NAME = WURA.USER_NAME
AND WURA.RELATIONSHIP_ID <> -1
AND WUR.ROLE_ORIG_SYSTEM = 'FND_RESP'
AND NOT WURA.ROLE_NAME LIKE 'FND_RESP|%|ANY'
AND WURA.ROLE_NAME = WUR.ROLE_NAME
AND WURA.USER_NAME = WUR.USER_NAME
|
|
|