VIEW: APPS.ENG_SECURITY_PEOPLE_V

Source

View Text - Preformatted

SELECT hr_employee.party_id person_id, fnd_user.user_id user_id, fnd_user.user_name user_name, 'INTERNAL' person_type 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 ) AND fnd_user.start_date <= SYSDATE AND NVL (fnd_user.end_date, SYSDATE) >= SYSDATE UNION ALL SELECT fnd_user.customer_id person_id, fnd_user.user_id user_id, fnd_user.user_name user_name, 'VENDOR' person_type 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 AND fnd_user.start_date <= SYSDATE AND NVL (fnd_user.end_date, SYSDATE) >= SYSDATE UNION ALL SELECT fnd_user.customer_id person_id, fnd_user.user_id user_id, fnd_user.user_name user_name, 'CUSTOMER' person_type 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 AND fnd_user.start_date <= SYSDATE AND NVL (fnd_user.end_date, SYSDATE) >= SYSDATE

View Text - HTML Formatted

SELECT HR_EMPLOYEE.PARTY_ID PERSON_ID
, FND_USER.USER_ID USER_ID
, FND_USER.USER_NAME USER_NAME
, 'INTERNAL' PERSON_TYPE
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 )
AND FND_USER.START_DATE <= SYSDATE
AND NVL (FND_USER.END_DATE
, SYSDATE) >= SYSDATE UNION ALL SELECT FND_USER.CUSTOMER_ID PERSON_ID
, FND_USER.USER_ID USER_ID
, FND_USER.USER_NAME USER_NAME
, 'VENDOR' PERSON_TYPE
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
AND FND_USER.START_DATE <= SYSDATE
AND NVL (FND_USER.END_DATE
, SYSDATE) >= SYSDATE UNION ALL SELECT FND_USER.CUSTOMER_ID PERSON_ID
, FND_USER.USER_ID USER_ID
, FND_USER.USER_NAME USER_NAME
, 'CUSTOMER' PERSON_TYPE
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
AND FND_USER.START_DATE <= SYSDATE
AND NVL (FND_USER.END_DATE
, SYSDATE) >= SYSDATE