View: EGO_VENDOR_PEOPLE_V

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

View Text

SELECT VENDOR_USER.PARTY_ID PERSON_ID
, FND_USER.USER_NAME USER_NAME
, VENDOR_USER.PARTY_NAME PERSON_NAME
, (SELECT EMAIL_CONTACT.EMAIL_ADDRESS
FROM HZ_CONTACT_POINTS EMAIL_CONTACT
WHERE EMAIL_CONTACT.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND EMAIL_CONTACT.OWNER_TABLE_ID = VEND_CMPY.PARTY_ID
AND EMAIL_CONTACT.CONTACT_POINT_TYPE = 'EMAIL'
AND EMAIL_CONTACT.STATUS = 'A'
AND EMAIL_CONTACT.PRIMARY_FLAG = 'Y'
AND ROWNUM = 1) AS EMAIL_ADDRESS
, VENDOR.PARTY_ID COMPANY_ID
, VENDOR.PARTY_NAME COMPANY_NAME
, VENDOR_USER.PERSON_TITLE PERSON_TITLE
, VENDOR_USER.PERSON_PRE_NAME_ADJUNCT PERSON_PREFIX
, VENDOR_USER.PERSON_FIRST_NAME PERSON_FIRST_NAME
, VENDOR_USER.PERSON_MIDDLE_NAME PERSON_MIDDLE_NAME
, VENDOR_USER.PERSON_LAST_NAME PERSON_LAST_NAME
, VENDOR_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 PO_VENDORS PV
, HZ_PARTIES VENDOR_USER
, HZ_RELATIONSHIPS VEND_CMPY
, HZ_PARTIES VENDOR
, AK_WEB_USER_SEC_ATTR_VALUES SEC
, HZ_CONTACT_POINTS PHONE_CONTACT
, HZ_CONTACT_POINTS FAX_CONTACT
, FND_USER FND_USER
WHERE VENDOR_USER.PARTY_TYPE = 'PERSON'
AND VENDOR_USER.STATUS = 'A'
AND FND_USER.PERSON_PARTY_ID = VENDOR_USER.PARTY_ID
AND FND_USER.START_DATE <= SYSDATE
AND NVL(FND_USER.END_DATE
, SYSDATE) >= SYSDATE
AND VEND_CMPY.RELATIONSHIP_TYPE = 'CONTACT'
AND VEND_CMPY.RELATIONSHIP_CODE = 'CONTACT_OF'
AND VEND_CMPY.SUBJECT_ID = VENDOR_USER.PARTY_ID
AND VEND_CMPY.SUBJECT_TYPE = 'PERSON'
AND VEND_CMPY.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND VEND_CMPY.OBJECT_ID = VENDOR.PARTY_ID
AND VEND_CMPY.OBJECT_TYPE = 'ORGANIZATION'
AND VEND_CMPY.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND VEND_CMPY.STATUS = 'A'
AND VEND_CMPY.START_DATE <= SYSDATE
AND NVL(VEND_CMPY.END_DATE
, SYSDATE) >= SYSDATE
AND PV.PARTY_ID = VENDOR.PARTY_ID
AND VENDOR.STATUS = 'A'
AND SEC.WEB_USER_ID = FND_USER.USER_ID
AND SEC.ATTRIBUTE_CODE = 'ICX_SUPPLIER_ORG_ID'
AND SEC.NUMBER_VALUE = PV.VENDOR_ID
AND SEC.ATTRIBUTE_APPLICATION_ID = 177
AND PHONE_CONTACT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND PHONE_CONTACT.OWNER_TABLE_ID(+) = VENDOR_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(+) = VENDOR_USER.PARTY_ID
AND FAX_CONTACT.CONTACT_POINT_TYPE(+) = 'PHONE'
AND FAX_CONTACT.PHONE_LINE_TYPE(+) = 'FAX'
AND FAX_CONTACT.STATUS(+) = 'A'

Columns