(
SELECT
USR.USER_NAME,
'PER',
PER.ORIG_SYSTEM_ID,
USR.USER_NAME,
'PER',
PER.ORIG_SYSTEM_ID,
USR.START_DATE,
USR.END_DATE,
NULL,
1
from FND_USER USR,
WF_LOCAL_ROLES PARTITION (PER_ROLE) PER
where USR.EMPLOYEE_ID = PER.ORIG_SYSTEM_ID
and trunc(sysdate) between nvl(PER.START_DATE,trunc(sysdate))
and nvl(PER.EXPIRATION_DATE, trunc(sysdate+1))
union all
select usr.user_name,
'FND_USR',
usr.user_id,
usr.user_name,
'FND_USR',
usr.user_id,
usr.start_date,
usr.end_date,
NULL,
1
from FND_USER USR
where usr.employee_id is null)
(
SELECT
USR.USER_NAME
,
'PER'
,
PER.ORIG_SYSTEM_ID
,
USR.USER_NAME
,
'PER'
,
PER.ORIG_SYSTEM_ID
,
USR.START_DATE
,
USR.END_DATE
,
NULL
,
1
FROM FND_USER USR
,
WF_LOCAL_ROLES PARTITION (PER_ROLE) PER
WHERE USR.EMPLOYEE_ID = PER.ORIG_SYSTEM_ID
AND TRUNC(SYSDATE) BETWEEN NVL(PER.START_DATE
, TRUNC(SYSDATE))
AND NVL(PER.EXPIRATION_DATE
, TRUNC(SYSDATE+1))
UNION ALL
SELECT USR.USER_NAME
,
'FND_USR'
,
USR.USER_ID
,
USR.USER_NAME
,
'FND_USR'
,
USR.USER_ID
,
USR.START_DATE
,
USR.END_DATE
,
NULL
,
1
FROM FND_USER USR
WHERE USR.EMPLOYEE_ID IS NULL)
|
|
|