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