select U.user_id user_id , wur.role_orig_system_id RESPONSIBILITY_ID ,
FA.application_id RESPONSIBILITY_APPLICATION_ID ,
FSG.security_group_id SECURITY_GROUP_ID ,
fnd_date.canonical_to_date ( '1000/01/01' ) START_DATE , to_date (
NULL )
END_DATE ,
to_char ( NULL ) DESCRIPTION , to_number ( NULL ) CREATED_BY ,
to_date ( NULL ) CREATION_DATE , to_number ( NULL )
LAST_UPDATED_BY , to_date ( NULL ) LAST_UPDATE_DATE , to_number (
NULL )
LAST_UPDATE_LOGIN
from fnd_user u ,
wf_user_role_assignments_v wura ,
wf_user_roles wur
, fnd_application FA
, fnd_security_groups FSG
where wura.user_name = u.user_name
and wur.role_orig_system = 'FND_RESP'
and wur.partition_id = 2
and wura.role_name = wur.role_name
and wura.user_name = wur.user_name
AND FA.application_short_name =/* Val between 1 st and 2 nd 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' ,
':' )
AND FSG.security_group_key =/* Val after 3 rd separator */
replace ( substr ( WURA.ROLE_NAME , INSTR (
WURA.ROLE_NAME , '|' , 1 , 3 ) +1 ) , '%col' , ':' )
SELECT U.USER_ID USER_ID
, WUR.ROLE_ORIG_SYSTEM_ID RESPONSIBILITY_ID
,
FA.APPLICATION_ID RESPONSIBILITY_APPLICATION_ID
,
FSG.SECURITY_GROUP_ID SECURITY_GROUP_ID
,
FND_DATE.CANONICAL_TO_DATE ( '1000/01/01' ) START_DATE
, TO_DATE (
NULL )
END_DATE
,
TO_CHAR ( NULL ) DESCRIPTION
, TO_NUMBER ( NULL ) CREATED_BY
,
TO_DATE ( NULL ) CREATION_DATE
, TO_NUMBER ( NULL )
LAST_UPDATED_BY
, TO_DATE ( NULL ) LAST_UPDATE_DATE
, TO_NUMBER (
NULL )
LAST_UPDATE_LOGIN
FROM FND_USER U
,
WF_USER_ROLE_ASSIGNMENTS_V WURA
,
WF_USER_ROLES WUR
, FND_APPLICATION FA
, FND_SECURITY_GROUPS FSG
WHERE WURA.USER_NAME = U.USER_NAME
AND WUR.ROLE_ORIG_SYSTEM = 'FND_RESP'
AND WUR.PARTITION_ID = 2
AND WURA.ROLE_NAME = WUR.ROLE_NAME
AND WURA.USER_NAME = WUR.USER_NAME
AND FA.APPLICATION_SHORT_NAME =/* VAL BETWEEN 1 ST
AND 2 ND 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'
,
':' )
AND FSG.SECURITY_GROUP_KEY =/* VAL AFTER 3 RD SEPARATOR */
REPLACE ( SUBSTR ( WURA.ROLE_NAME
, INSTR (
WURA.ROLE_NAME
, '|'
, 1
, 3 ) +1 )
, '%COL'
, ':' )
|
|
|