VIEW: APPS.HZ_CONTACT_FIND_V

Source

View Text - Preformatted

SELECT acct_role.cust_account_id, cont_party.person_last_name cont_last_name, cont_party.person_first_name cont_first_name, cont_party.party_name cont_full_name, phon.phone_area_code|| decode(phon.contact_point_type,'TLX',phon.telex_number, phon.phone_number)|| phon.phone_extension phone, cust_party.party_name customer_name, cust_party.party_name dummy_name, cust_party.person_last_name last_name, cust_party.person_first_name first_name, hl.city, hl.state, hl.postal_code, ter.description country, hl.country country_code, acct_role.status, org_contact.contact_number, cust_party.party_number, hca.account_number customer_number, hl.address1, hl.address2, hl.address3, hl.address4, hl.county, hl.province, cust_party.tax_reference tax_reg_number, cust_party.jgzz_fiscal_code taxpayer_id, hca.orig_system_reference reference, hca.customer_type type, cust_party.category_code category, hca.customer_class_code class, cust_party.sic_code, cust_party.party_type customer_type, cont_party.customer_key, hl.address_key, cust_party.customer_key, hca.account_name, hps.party_site_number FROM hz_cust_account_roles acct_role, hz_relationships rel, hz_org_contacts org_contact, hz_parties cont_party, hz_cust_accounts hca, hz_cust_acct_sites_all hcs, hz_parties cust_party, hz_party_sites hps, hz_locations hl, fnd_territories_tl ter, hz_contact_points phon WHERE acct_role.party_id = rel.party_id and acct_role.role_type = 'CONTACT' and org_contact.party_relationship_id = rel.relationship_id and rel.subject_id = cont_party.party_id and rel.object_id = hca.party_id and rel.subject_table_name = 'HZ_PARTIES' and rel.object_table_name = 'HZ_PARTIES' and acct_role.cust_account_id = hca.cust_account_id and acct_role.cust_acct_site_id = hcs.cust_acct_site_id (+) and acct_role.cust_account_id = hcs.cust_account_id (+) and hca.party_id = cust_party.party_id (+) and hcs.party_site_id = hps.party_site_id (+)and hps.location_id = hl.location_id (+) and hl.country = ter.territory_code (+) and nvl(ter.language, USERENV('LANG')) = USERENV('LANG') and acct_role.party_id = phon.owner_table_id (+) and nvl(phon.owner_table_name, 'HZ_PARTIES') = 'HZ_PARTIES'

View Text - HTML Formatted

SELECT ACCT_ROLE.CUST_ACCOUNT_ID
, CONT_PARTY.PERSON_LAST_NAME CONT_LAST_NAME
, CONT_PARTY.PERSON_FIRST_NAME CONT_FIRST_NAME
, CONT_PARTY.PARTY_NAME CONT_FULL_NAME
, PHON.PHONE_AREA_CODE|| DECODE(PHON.CONTACT_POINT_TYPE
, 'TLX'
, PHON.TELEX_NUMBER
, PHON.PHONE_NUMBER)|| PHON.PHONE_EXTENSION PHONE
, CUST_PARTY.PARTY_NAME CUSTOMER_NAME
, CUST_PARTY.PARTY_NAME DUMMY_NAME
, CUST_PARTY.PERSON_LAST_NAME LAST_NAME
, CUST_PARTY.PERSON_FIRST_NAME FIRST_NAME
, HL.CITY
, HL.STATE
, HL.POSTAL_CODE
, TER.DESCRIPTION COUNTRY
, HL.COUNTRY COUNTRY_CODE
, ACCT_ROLE.STATUS
, ORG_CONTACT.CONTACT_NUMBER
, CUST_PARTY.PARTY_NUMBER
, HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER
, HL.ADDRESS1
, HL.ADDRESS2
, HL.ADDRESS3
, HL.ADDRESS4
, HL.COUNTY
, HL.PROVINCE
, CUST_PARTY.TAX_REFERENCE TAX_REG_NUMBER
, CUST_PARTY.JGZZ_FISCAL_CODE TAXPAYER_ID
, HCA.ORIG_SYSTEM_REFERENCE REFERENCE
, HCA.CUSTOMER_TYPE TYPE
, CUST_PARTY.CATEGORY_CODE CATEGORY
, HCA.CUSTOMER_CLASS_CODE CLASS
, CUST_PARTY.SIC_CODE
, CUST_PARTY.PARTY_TYPE CUSTOMER_TYPE
, CONT_PARTY.CUSTOMER_KEY
, HL.ADDRESS_KEY
, CUST_PARTY.CUSTOMER_KEY
, HCA.ACCOUNT_NAME
, HPS.PARTY_SITE_NUMBER
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONTACT
, HZ_PARTIES CONT_PARTY
, HZ_CUST_ACCOUNTS HCA
, HZ_CUST_ACCT_SITES_ALL HCS
, HZ_PARTIES CUST_PARTY
, HZ_PARTY_SITES HPS
, HZ_LOCATIONS HL
, FND_TERRITORIES_TL TER
, HZ_CONTACT_POINTS PHON
WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONTACT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = CONT_PARTY.PARTY_ID
AND REL.OBJECT_ID = HCA.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND ACCT_ROLE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND ACCT_ROLE.CUST_ACCT_SITE_ID = HCS.CUST_ACCT_SITE_ID (+)
AND ACCT_ROLE.CUST_ACCOUNT_ID = HCS.CUST_ACCOUNT_ID (+)
AND HCA.PARTY_ID = CUST_PARTY.PARTY_ID (+)
AND HCS.PARTY_SITE_ID = HPS.PARTY_SITE_ID (+)AND HPS.LOCATION_ID = HL.LOCATION_ID (+)
AND HL.COUNTRY = TER.TERRITORY_CODE (+)
AND NVL(TER.LANGUAGE
, USERENV('LANG')) = USERENV('LANG')
AND ACCT_ROLE.PARTY_ID = PHON.OWNER_TABLE_ID (+)
AND NVL(PHON.OWNER_TABLE_NAME
, 'HZ_PARTIES') = 'HZ_PARTIES'