VIEW: APPS.AR_CUSTOMER_CONTACT_FIND_V

Source

View Text - Preformatted

SELECT cust.customer_id, substrb(party.person_last_name,1,50) cont_last_name, substrb(party.person_first_name,1,40) cont_first_name, cont_point.phone_area_code || decode(cont_point.contact_point_type, 'TLX', cont_point.telex_number, cont_point.phone_number) || cont_point.phone_extension phone, cust.customer_name, cust.last_name last_name, cust.first_name first_name, cust.city, cust.state, cust.postal_code, cust.country, cust.country_code, acct_role.status, org_cont.contact_number, cust.org_per_number, cust.customer_number, cust.address1, cust.address2, cust.address3, cust.address4, cust.county, cust.province, cust.tax_reg_number, cust.taxpayer_id, cust.reference, cust.type, cust.category, cust.class, cust.sic_code, cust.customer_type, party.customer_key contact_key, cust.address_key, cust.customer_key FROM hz_cust_account_roles acct_role, hz_parties party, hz_relationships rel, hz_org_contacts org_cont, ar_customer_find_v cust, hz_contact_points cont_point, hz_cust_account_roles car WHERE acct_role.party_id = rel.party_id and acct_role.role_type = 'CONTACT' and org_cont.party_relationship_id = rel.relationship_id and rel.subject_id = party.party_id and rel.object_id = cust.party_id and acct_role.cust_account_id = cust.customer_id and (acct_role.cust_acct_site_id = cust.address_id or acct_role.cust_acct_site_id is null) and acct_role.cust_account_role_id = car.cust_account_role_id (+) and car.party_id = cont_point.owner_table_id(+) and cont_point.owner_table_name(+) = 'HZ_PARTIES' and cont_point.contact_point_type(+) not in ('EDI', 'EMAIL','WEB')

View Text - HTML Formatted

SELECT CUST.CUSTOMER_ID
, SUBSTRB(PARTY.PERSON_LAST_NAME
, 1
, 50) CONT_LAST_NAME
, SUBSTRB(PARTY.PERSON_FIRST_NAME
, 1
, 40) CONT_FIRST_NAME
, CONT_POINT.PHONE_AREA_CODE || DECODE(CONT_POINT.CONTACT_POINT_TYPE
, 'TLX'
, CONT_POINT.TELEX_NUMBER
, CONT_POINT.PHONE_NUMBER) || CONT_POINT.PHONE_EXTENSION PHONE
, CUST.CUSTOMER_NAME
, CUST.LAST_NAME LAST_NAME
, CUST.FIRST_NAME FIRST_NAME
, CUST.CITY
, CUST.STATE
, CUST.POSTAL_CODE
, CUST.COUNTRY
, CUST.COUNTRY_CODE
, ACCT_ROLE.STATUS
, ORG_CONT.CONTACT_NUMBER
, CUST.ORG_PER_NUMBER
, CUST.CUSTOMER_NUMBER
, CUST.ADDRESS1
, CUST.ADDRESS2
, CUST.ADDRESS3
, CUST.ADDRESS4
, CUST.COUNTY
, CUST.PROVINCE
, CUST.TAX_REG_NUMBER
, CUST.TAXPAYER_ID
, CUST.REFERENCE
, CUST.TYPE
, CUST.CATEGORY
, CUST.CLASS
, CUST.SIC_CODE
, CUST.CUSTOMER_TYPE
, PARTY.CUSTOMER_KEY CONTACT_KEY
, CUST.ADDRESS_KEY
, CUST.CUSTOMER_KEY
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, AR_CUSTOMER_FIND_V CUST
, HZ_CONTACT_POINTS CONT_POINT
, HZ_CUST_ACCOUNT_ROLES CAR
WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.OBJECT_ID = CUST.PARTY_ID
AND ACCT_ROLE.CUST_ACCOUNT_ID = CUST.CUSTOMER_ID
AND (ACCT_ROLE.CUST_ACCT_SITE_ID = CUST.ADDRESS_ID OR ACCT_ROLE.CUST_ACCT_SITE_ID IS NULL)
AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = CAR.CUST_ACCOUNT_ROLE_ID (+)
AND CAR.PARTY_ID = CONT_POINT.OWNER_TABLE_ID(+)
AND CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CONT_POINT.CONTACT_POINT_TYPE(+) NOT IN ('EDI'
, 'EMAIL'
, 'WEB')