View: ENG_SECURITY_PEOPLE_V

Product:	ENG - Engineering
Description:
Implementation/DBA Data:	APPS.ENG_SECURITY_PEOPLE_V

View Text

SELECT PERSON_ID
, USER_ID
, USER_NAME
, PERSON_TYPE
FROM ( SELECT HR_EMPLOYEE.PARTY_ID PERSON_ID
, FND_USER.USER_ID USER_ID
, FND_USER.USER_NAME USER_NAME
, 'INTERNAL' PERSON_TYPE
, FND_USER.START_DATE START_DATE
, FND_USER.END_DATE END_DATE
FROM FND_USER FND_USER
, PER_ALL_PEOPLE_F HR_EMPLOYEE
WHERE HR_EMPLOYEE.PERSON_ID = FND_USER.EMPLOYEE_ID
AND HR_EMPLOYEE.CURRENT_EMPLOYEE_FLAG = 'Y'
AND HR_EMPLOYEE.EFFECTIVE_START_DATE <= SYSDATE
AND (HR_EMPLOYEE.EFFECTIVE_END_DATE >= SYSDATE OR HR_EMPLOYEE.EFFECTIVE_END_DATE IS NULL) UNION ALL SELECT FND_USER.PERSON_PARTY_ID PERSON_ID
, FND_USER.USER_ID USER_ID
, FND_USER.USER_NAME USER_NAME
, 'VENDOR' PERSON_TYPE
, FND_USER.START_DATE START_DATE
, FND_USER.END_DATE END_DATE
FROM FND_USER FND_USER
, HZ_CODE_ASSIGNMENTS VEND_ASSIGN
WHERE FND_USER.PERSON_PARTY_ID = VEND_ASSIGN.OWNER_TABLE_ID
AND VEND_ASSIGN.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND VEND_ASSIGN.CLASS_CODE = 'VENDOR_USER'
AND VEND_ASSIGN.CLASS_CATEGORY = 'POS_PARTICIPANT_TYPE'
AND FND_USER.EMPLOYEE_ID IS NULL
AND FND_USER.PERSON_PARTY_ID IS NOT NULL UNION ALL SELECT FND_USER.PERSON_PARTY_ID PERSON_ID
, FND_USER.USER_ID USER_ID
, FND_USER.USER_NAME USER_NAME
, 'CUSTOMER' PERSON_TYPE
, FND_USER.START_DATE
, FND_USER.END_DATE END_DATE
FROM FND_USER FND_USER
WHERE EXISTS (SELECT 'X'
FROM HZ_CUST_ACCOUNTS HCA
WHERE HCA.PARTY_ID = FND_USER.PERSON_PARTY_ID
AND HCA.STATUS = 'A')
AND FND_USER.EMPLOYEE_ID IS NULL
AND FND_USER.PERSON_PARTY_ID IS NOT NULL )
WHERE START_DATE <= SYSDATE
AND NVL(END_DATE
, SYSDATE) >= SYSDATE

Columns