VIEW: APPS.WF_FND_USR_ROLES

Source

View Text - Preformatted

(
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)

View Text - HTML Formatted

( 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)