Product: | PQH - Public Sector HR |
---|---|
Description: | |
Implementation/DBA Data: | APPS.PQH_ROLE_USERS_V |
SELECT PEI.ROWID ROW_ID
, PEI.PERSON_EXTRA_INFO_ID ROLE_USER_ID
, PEI.PERSON_ID
, PPF.FULL_NAME PERSON_NAME
, USR.USER_NAME
, USR.USER_ID
, RLS.ROLE_NAME
, DECODE(INFORMATION_TYPE
, 'PQH_ROLE_USERS'
, TO_NUMBER(PEI.PEI_INFORMATION3)
, 0) ROLE_ID
, PEI.PEI_INFORMATION4 DEFAULT_ROLE
, RLS.ENABLE_FLAG ROLE_ENABLE_FLAG
, PEI.CREATED_BY
, PEI.CREATION_DATE
, PEI.LAST_UPDATED_BY
, PEI.LAST_UPDATE_DATE
, PEI.LAST_UPDATE_LOGIN
, PEI.OBJECT_VERSION_NUMBER
FROM PER_PEOPLE_EXTRA_INFO PEI
, PER_ALL_PEOPLE_F PPF
, FND_USER USR
, PQH_ROLES RLS
WHERE INFORMATION_TYPE = 'PQH_ROLE_USERS'
AND PEI.PERSON_ID = PPF.PERSON_ID
AND (SELECT SES.EFFECTIVE_DATE
FROM FND_SESSIONS SES
WHERE SES.SESSION_ID = USERENV('SESSIONID')) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND USR.EMPLOYEE_ID = PPF.PERSON_ID
AND RLS.ROLE_ID = TO_NUMBER(PEI.PEI_INFORMATION3)
AND NVL(PEI.PEI_INFORMATION5
, 'Y')='Y'