View: FND_USR_ROLES

Product:	FND - Application Object Library
Description:
Implementation/DBA Data:	APPS.FND_USR_ROLES

View Text

SELECT USR.USER_NAME NAME
, PER.DISPLAY_NAME DISPLAY_NAME
, PER.DESCRIPTION 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') NOTIFICATION_PREFERENCE
, 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') LANGUAGE
, 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') TERRITORY
, PER.EMAIL_ADDRESS EMAIL_ADDRESS
, USR.FAX FAX
, 'PER' ORIG_SYSTEM
, PER.ORIG_SYSTEM_ID ORIG_SYSTEM_ID
, USR.START_DATE START_DATE
, DECODE(SUBSTR(TO_CHAR(NVL(USR.END_DATE
, SYSDATE+1)-SYSDATE)
, 1
, 1)
, '-'
, 'INACTIVE'
, 'ACTIVE') STATUS
, USR.END_DATE EXPIRATION_DATE
, NULL SECURITY_GROUP_ID
, 'Y' USER_FLAG
, 1 PARTITION_ID
FROM FND_USER USR
, WF_LOCAL_ROLES PARTITION (PER_ROLE) PER
, FND_USER_PREFERENCES FUP1
, FND_USER_PREFERENCES FUP2
, FND_USER_PREFERENCES FUP3
WHERE TRUNC(SYSDATE) BETWEEN NVL(PER.START_DATE
, TRUNC(SYSDATE))
AND NVL(PER.EXPIRATION_DATE
, TRUNC(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 NAME
, USR.USER_NAME DISPLAY_NAME
, USR.DESCRIPTION DESCRIPTION
, 'MAILHTML' NOTIFICATION_PREFERENCE
, 'AMERICAN' LANGUAGE
, 'AMERICA' TERRITORY
, USR.EMAIL_ADDRESS EMAIL_ADDRESS
, USR.FAX FAX
, 'FND_USR' ORIG_SYSTEM
, USR.USER_ID ORIG_SYSTEM_ID
, USR.START_DATE START_DATE
, DECODE(SUBSTR(TO_CHAR(NVL(USR.END_DATE
, SYSDATE+1)-SYSDATE)
, 1
, 1)
, '-'
, 'INACTIVE'
, 'ACTIVE') STATUS
, USR.END_DATE EXPIRATION_DATE
, NULL SECURITY_GROUP_ID
, 'Y' USER_FLAG
, 1 PARTITION_ID
FROM FND_USER USR
WHERE USR.EMPLOYEE_ID IS NULL