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