VIEW: APPS.ENG_SEARCH_PEOPLE_V

Source

View Text - Preformatted

SELECT distinct EMPLOYEE.party_id person_id, EMPLOYEE.party_name person_name, FND_USER.USER_ID USER_ID, FND_USER.USER_NAME USER_NAME, 'INTERNAL' PERSON_TYPE FROM hz_parties employee, FND_USER FND_user, PER_ALL_PEOPLE_F HR_EMPLOYEE WHERE HR_EMPLOYEE.PERSON_ID = FND_user.EMPLOYEE_ID AND employee.party_type = 'PERSON' AND employee.party_id = HR_EMPLOYEE.PARTY_ID UNION ALL SELECT VENDOR_USER.party_id person_id, VENDOR_USER.party_name person_name, FND_USER.USER_ID USER_ID, FND_USER.USER_NAME USER_NAME, 'VENDOR' PERSON_TYPE FROM HZ_PARTIES VENDOR_USER, FND_USER FND_user, HZ_CODE_ASSIGNMENTS VEND_ASSIGN WHERE VENDOR_USER.party_type = 'PERSON' AND VENDOR_USER.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.PERSON_PARTY_ID = VENDOR_USER.PARTY_ID AND FND_USER.EMPLOYEE_ID IS NULL UNION ALL SELECT CUSTOMER_USER.party_id person_id, CUSTOMER_USER.party_name person_name, FND_USER.user_id USER_ID, FND_USER.user_name USER_NAME, 'CUSTOMER' PERSON_TYPE FROM HZ_PARTIES CUSTOMER_USER, FND_USER FND_USER WHERE CUSTOMER_USER.party_type = 'PERSON' AND EXISTS (SELECT 'X' FROM hz_cust_accounts where party_id = CUSTOMER_USER.party_id ) AND FND_USER.PERSON_PARTY_ID = CUSTOMER_USER.PARTY_ID AND FND_USER.EMPLOYEE_ID IS NULL

View Text - HTML Formatted

SELECT DISTINCT EMPLOYEE.PARTY_ID PERSON_ID
, EMPLOYEE.PARTY_NAME PERSON_NAME
, FND_USER.USER_ID USER_ID
, FND_USER.USER_NAME USER_NAME
, 'INTERNAL' PERSON_TYPE
FROM HZ_PARTIES EMPLOYEE
, FND_USER FND_USER
, PER_ALL_PEOPLE_F HR_EMPLOYEE
WHERE HR_EMPLOYEE.PERSON_ID = FND_USER.EMPLOYEE_ID
AND EMPLOYEE.PARTY_TYPE = 'PERSON'
AND EMPLOYEE.PARTY_ID = HR_EMPLOYEE.PARTY_ID UNION ALL SELECT VENDOR_USER.PARTY_ID PERSON_ID
, VENDOR_USER.PARTY_NAME PERSON_NAME
, FND_USER.USER_ID USER_ID
, FND_USER.USER_NAME USER_NAME
, 'VENDOR' PERSON_TYPE
FROM HZ_PARTIES VENDOR_USER
, FND_USER FND_USER
, HZ_CODE_ASSIGNMENTS VEND_ASSIGN
WHERE VENDOR_USER.PARTY_TYPE = 'PERSON'
AND VENDOR_USER.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.PERSON_PARTY_ID = VENDOR_USER.PARTY_ID
AND FND_USER.EMPLOYEE_ID IS NULL UNION ALL SELECT CUSTOMER_USER.PARTY_ID PERSON_ID
, CUSTOMER_USER.PARTY_NAME PERSON_NAME
, FND_USER.USER_ID USER_ID
, FND_USER.USER_NAME USER_NAME
, 'CUSTOMER' PERSON_TYPE
FROM HZ_PARTIES CUSTOMER_USER
, FND_USER FND_USER
WHERE CUSTOMER_USER.PARTY_TYPE = 'PERSON'
AND EXISTS (SELECT 'X'
FROM HZ_CUST_ACCOUNTS
WHERE PARTY_ID = CUSTOMER_USER.PARTY_ID )
AND FND_USER.PERSON_PARTY_ID = CUSTOMER_USER.PARTY_ID
AND FND_USER.EMPLOYEE_ID IS NULL