[Home] [Help]
View: WF_FND_USR_ROLES
View Text
(
SELECT
USR.USER_NAME
,
PER.DISPLAY_NAME
,
PER.DESCRIPTION
,
NVL(DECODE(FUP1.USER_NAME
, USR.USER_NAME
, FUP1.PREFERENCE_VALUE
, (SELECT PREFERENCE_VALUE
FROM FND_USER_PREFERENCES
WHERE USER_NAME='-WF_DEFAULT-'
AND MODULE_NAME='WF'
AND PREFERENCE_NAME ='MAILTYPE')
)
, 'MAILHTML')
,
NVL(DECODE(FUP2.USER_NAME
, USR.USER_NAME
, FUP2.PREFERENCE_VALUE
, (SELECT PREFERENCE_VALUE
FROM FND_USER_PREFERENCES
WHERE USER_NAME='-WF_DEFAULT-'
AND MODULE_NAME='WF'
AND PREFERENCE_NAME ='LANGUAGE')
)
, 'AMERICAN')
,
NVL(DECODE(FUP3.USER_NAME
, USR.USER_NAME
, FUP3.PREFERENCE_VALUE
, (SELECT PREFERENCE_VALUE
FROM FND_USER_PREFERENCES
WHERE USER_NAME='-WF_DEFAULT-'
AND MODULE_NAME='WF'
AND PREFERENCE_NAME ='TERRITORY')
)
, 'AMERICA')
,
PER.EMAIL_ADDRESS
,
USR.FAX
,
'PER'
,
PER.ORIG_SYSTEM_ID
,
USR.START_DATE
,
DECODE(SUBSTR(TO_CHAR(NVL(USR.END_DATE
, SYSDATE+1)-SYSDATE)
, 1
, 1)
,
'-'
, 'INACTIVE'
,
'ACTIVE')
,
USR.END_DATE
,
NULL
,
'Y'
,
1
FROM FND_USER USR
,
WF_LOCAL_ROLES PARTITION (PER_ROLE) PER
,
FND_USER_PREFERENCES FUP1
,
FND_USER_PREFERENCES FUP2
,
FND_USER_PREFERENCES FUP3
WHERE SYSDATE BETWEEN NVL(PER.START_DATE
, SYSDATE)
AND NVL(PER.EXPIRATION_DATE
, SYSDATE+1)
AND PER.ORIG_SYSTEM_ID = USR.EMPLOYEE_ID
AND USR.USER_NAME = FUP1.USER_NAME(+)
AND FUP1.PREFERENCE_NAME(+) = 'MAILTYPE'
AND FUP1.MODULE_NAME(+) = 'WF'
AND USR.USER_NAME = FUP2.USER_NAME(+)
AND FUP2.PREFERENCE_NAME(+) = 'LANGUAGE'
AND FUP2.MODULE_NAME(+) = 'WF'
AND USR.USER_NAME = FUP3.USER_NAME(+)
AND FUP3.PREFERENCE_NAME(+) = 'TERRITORY'
AND FUP3.MODULE_NAME(+) = 'WF'
UNION ALL
SELECT USR.USER_NAME
,
USR.USER_NAME
,
USR.DESCRIPTION
,
'MAILHTML'
,
'AMERICA'
,
'AMERICAN'
,
USR.EMAIL_ADDRESS
,
USR.FAX
,
'FND_USR'
,
USR.USER_ID
,
USR.START_DATE
,
DECODE(SUBSTR(TO_CHAR(NVL(USR.END_DATE
, SYSDATE+1)-SYSDATE)
, 1
, 1)
,
'-'
, 'INACTIVE'
,
'ACTIVE')
,
USR.END_DATE
,
NULL
,
'Y'
,
1
FROM FND_USER USR
WHERE USR.EMPLOYEE_ID IS NULL)
Columns
Name |
NAME |
DISPLAY_NAME |
DESCRIPTION |
NOTIFICATION_PREFERENCE |
LANGUAGE |
TERRITORY |
EMAIL_ADDRESS |
FAX |
ORIG_SYSTEM |
ORIG_SYSTEM_ID |
START_DATE |
STATUS |
EXPIRATION_DATE |
SECURITY_GROUP_ID |
USER_FLAG |
PARTITION_ID |