VIEW: APPS.EGO_VENDOR_PEOPLE_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'