((select
WR.NAME,
WRT.DISPLAY_NAME,
WRT.DESCRIPTION,
WR.NOTIFICATION_PREFERENCE,
WR.LANGUAGE,
WR.TERRITORY,
WR.EMAIL_ADDRESS,
WR.FAX,
WR.ORIG_SYSTEM,
WR.ORIG_SYSTEM_ID,
WR.PARENT_ORIG_SYSTEM,
WR.PARENT_ORIG_SYSTEM_ID,
WR.START_DATE,
WR.STATUS,
WR.EXPIRATION_DATE,
WR.OWNER_TAG,
WR.PARTITION_ID
from wf_local_roles WR, wf_local_roles_tl WRT
WHERE (WR.PARTITION_ID = 1 /* FND_USR and PER */
or (WR.PARTITION_ID IN (0,9)
and WR.USER_FLAG = 'Y')) /*Adhoc Users and HZ_PARTY Persons*/
and nvl(WR.EXPIRATION_DATE, sysdate+1) > sysdate
and wr.name = wrt.name
and wr.orig_system = wrt.orig_system
and wr.orig_system_id = wrt.orig_system_id
and wr.partition_id = wrt.partition_id
and wrt.language = userenv('LANG'))
UNION ALL
(select
WR.NAME,
WR.DISPLAY_NAME,
WR.DESCRIPTION,
WR.NOTIFICATION_PREFERENCE,
WR.LANGUAGE,
WR.TERRITORY,
WR.EMAIL_ADDRESS,
WR.FAX,
WR.ORIG_SYSTEM,
WR.ORIG_SYSTEM_ID,
WR.PARENT_ORIG_SYSTEM,
WR.PARENT_ORIG_SYSTEM_ID,
WR.START_DATE,
WR.STATUS,
WR.EXPIRATION_DATE,
WR.OWNER_TAG,
WR.PARTITION_ID
from wf_local_roles WR
WHERE (WR.PARTITION_ID = 1 /* FND_USR and PER */
or (WR.PARTITION_ID IN (0,9)
and WR.USER_FLAG = 'Y')) /*Adhoc Users and HZ_PARTY Persons*/
and nvl(WR.EXPIRATION_DATE, sysdate+1) > sysdate
and NOT EXISTS (SELECT NULL
FROM WF_LOCAL_ROLES_TL WRT
WHERE wrt.language = userenv('LANG')
AND wrt.name = wr.name
AND wrt.orig_system = wr.orig_system
AND wrt.orig_system_id = wr.orig_system_id
AND wrt.partition_id = wr.partition_id)))
((SELECT
WR.NAME
,
WRT.DISPLAY_NAME
,
WRT.DESCRIPTION
,
WR.NOTIFICATION_PREFERENCE
,
WR.LANGUAGE
,
WR.TERRITORY
,
WR.EMAIL_ADDRESS
,
WR.FAX
,
WR.ORIG_SYSTEM
,
WR.ORIG_SYSTEM_ID
,
WR.PARENT_ORIG_SYSTEM
,
WR.PARENT_ORIG_SYSTEM_ID
,
WR.START_DATE
,
WR.STATUS
,
WR.EXPIRATION_DATE
,
WR.OWNER_TAG
,
WR.PARTITION_ID
FROM WF_LOCAL_ROLES WR
, WF_LOCAL_ROLES_TL WRT
WHERE (WR.PARTITION_ID = 1 /* FND_USR
AND PER */
OR (WR.PARTITION_ID IN (0
, 9)
AND WR.USER_FLAG = 'Y')) /*ADHOC USERS
AND HZ_PARTY PERSONS*/
AND NVL(WR.EXPIRATION_DATE
, SYSDATE+1) > SYSDATE
AND WR.NAME = WRT.NAME
AND WR.ORIG_SYSTEM = WRT.ORIG_SYSTEM
AND WR.ORIG_SYSTEM_ID = WRT.ORIG_SYSTEM_ID
AND WR.PARTITION_ID = WRT.PARTITION_ID
AND WRT.LANGUAGE = USERENV('LANG'))
UNION ALL
(SELECT
WR.NAME
,
WR.DISPLAY_NAME
,
WR.DESCRIPTION
,
WR.NOTIFICATION_PREFERENCE
,
WR.LANGUAGE
,
WR.TERRITORY
,
WR.EMAIL_ADDRESS
,
WR.FAX
,
WR.ORIG_SYSTEM
,
WR.ORIG_SYSTEM_ID
,
WR.PARENT_ORIG_SYSTEM
,
WR.PARENT_ORIG_SYSTEM_ID
,
WR.START_DATE
,
WR.STATUS
,
WR.EXPIRATION_DATE
,
WR.OWNER_TAG
,
WR.PARTITION_ID
FROM WF_LOCAL_ROLES WR
WHERE (WR.PARTITION_ID = 1 /* FND_USR
AND PER */
OR (WR.PARTITION_ID IN (0
, 9)
AND WR.USER_FLAG = 'Y')) /*ADHOC USERS
AND HZ_PARTY PERSONS*/
AND NVL(WR.EXPIRATION_DATE
, SYSDATE+1) > SYSDATE
AND NOT EXISTS (SELECT NULL
FROM WF_LOCAL_ROLES_TL WRT
WHERE WRT.LANGUAGE = USERENV('LANG')
AND WRT.NAME = WR.NAME
AND WRT.ORIG_SYSTEM = WR.ORIG_SYSTEM
AND WRT.ORIG_SYSTEM_ID = WR.ORIG_SYSTEM_ID
AND WRT.PARTITION_ID = WR.PARTITION_ID)))
|
|
|