View: EGO_CUSTOMER_PEOPLE_V

Product:	EGO - Advanced Product Catalog
Description:	View contains all the Customer People.
Implementation/DBA Data:	APPS.EGO_CUSTOMER_PEOPLE_V

View Text

SELECT CUSTOMER_USER.PARTY_ID PERSON_ID
, FND_USER.USER_NAME USER_NAME
, CUSTOMER_USER.PARTY_NAME PERSON_NAME
, FND_USER.EMAIL_ADDRESS
, CUSTOMER.PARTY_ID COMPANY_ID
, CUSTOMER.PARTY_NAME COMPANY_NAME
, CUSTOMER_USER.PERSON_TITLE PERSON_TITLE
, CUSTOMER_USER.PERSON_PRE_NAME_ADJUNCT PERSON_PREFIX
, CUSTOMER_USER.PERSON_FIRST_NAME PERSON_FIRST_NAME
, CUSTOMER_USER.PERSON_MIDDLE_NAME PERSON_MIDDLE_NAME
, CUSTOMER_USER.PERSON_LAST_NAME PERSON_LAST_NAME
, CUSTOMER_USER.PERSON_NAME_SUFFIX PERSON_NAME_SUFFIX
, PHONE_CONTACT.PHONE_COUNTRY_CODE PHONE_COUNTRY_CODE
, PHONE_CONTACT.PHONE_AREA_CODE PHONE_AREA_CODE
, PHONE_CONTACT.PHONE_NUMBER PHONE_NUMBER
, PHONE_CONTACT.PHONE_EXTENSION PHONE_EXTENSION
, FAX_CONTACT.PHONE_COUNTRY_CODE FAX_COUNTRY_CODE
, FAX_CONTACT.PHONE_AREA_CODE FAX_AREA_CODE
, FAX_CONTACT.PHONE_NUMBER FAX_NUMBER
, FAX_CONTACT.PHONE_EXTENSION FAX_EXTENSION
, FND_USER.USER_ID USER_ID
FROM HZ_PARTIES CUSTOMER_USER
, HZ_RELATIONSHIPS CUSTOMER_CMPY
, HZ_PARTIES CUSTOMER
, HZ_CONTACT_POINTS PHONE_CONTACT
, HZ_CONTACT_POINTS FAX_CONTACT
, FND_USER FND_USER
WHERE CUSTOMER_USER.PARTY_TYPE = 'PERSON'
AND CUSTOMER_USER.STATUS = 'A'
AND FND_USER.PERSON_PARTY_ID = CUSTOMER_USER.PARTY_ID
AND FND_USER.EMPLOYEE_ID IS NULL
AND FND_USER.START_DATE <= SYSDATE
AND (FND_USER.END_DATE IS NULL OR FND_USER.END_DATE >= SYSDATE)
AND CUSTOMER_CMPY.SUBJECT_TYPE = 'PERSON'
AND CUSTOMER_CMPY.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND CUSTOMER_CMPY.OBJECT_TYPE = 'ORGANIZATION'
AND CUSTOMER_CMPY.RELATIONSHIP_CODE = 'EMPLOYEE_OF'
AND CUSTOMER_CMPY.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND CUSTOMER_CMPY.STATUS(+) = 'A'
AND CUSTOMER_CMPY.START_DATE(+) <= SYSDATE
AND (CUSTOMER_CMPY.END_DATE >= SYSDATE OR CUSTOMER_CMPY.END_DATE IS NULL)
AND CUSTOMER_USER.PARTY_ID(+) = CUSTOMER_CMPY.SUBJECT_ID
AND CUSTOMER.PARTY_ID = CUSTOMER_CMPY.OBJECT_ID (+)
AND CUSTOMER.STATUS (+) = 'A'
AND EXISTS (SELECT 'X'
FROM HZ_CUST_ACCOUNTS
WHERE PARTY_ID = CUSTOMER.PARTY_ID
AND STATUS = 'A')
AND PHONE_CONTACT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND PHONE_CONTACT.OWNER_TABLE_ID(+) = CUSTOMER_USER.PARTY_ID
AND PHONE_CONTACT.CONTACT_POINT_TYPE(+) = 'PHONE'
AND PHONE_CONTACT.PHONE_LINE_TYPE(+) = 'GEN'
AND PHONE_CONTACT.PRIMARY_FLAG(+) = 'Y'
AND PHONE_CONTACT.STATUS(+) = 'A'
AND FAX_CONTACT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND FAX_CONTACT.OWNER_TABLE_ID(+) = CUSTOMER_USER.PARTY_ID
AND FAX_CONTACT.CONTACT_POINT_TYPE(+) = 'PHONE'
AND FAX_CONTACT.PHONE_LINE_TYPE(+) = 'FAX'
AND FAX_CONTACT.STATUS(+) = 'A'

Columns