VIEW: APPS.ENG_SECURITY_PEOPLE_V

Source

View Text - Preformatted

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.CUSTOMER_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.CUSTOMER_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.CUSTOMER_ID IS NOT NULL UNION ALL SELECT fnd_user.CUSTOMER_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 where party_id = fnd_user.CUSTOMER_ID and status = 'A') AND fnd_user.EMPLOYEE_ID IS NULL AND fnd_user.CUSTOMER_ID IS NOT NULL ) WHERE START_DATE <= SYSDATE AND NVL(END_DATE, SYSDATE) >= SYSDATE

View Text - HTML Formatted

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.CUSTOMER_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.CUSTOMER_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.CUSTOMER_ID IS NOT NULL UNION ALL SELECT FND_USER.CUSTOMER_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
WHERE PARTY_ID = FND_USER.CUSTOMER_ID
AND STATUS = 'A')
AND FND_USER.EMPLOYEE_ID IS NULL
AND FND_USER.CUSTOMER_ID IS NOT NULL )
WHERE START_DATE <= SYSDATE
AND NVL(END_DATE
, SYSDATE) >= SYSDATE