View: HZ_CONTACT_FIND_V

Product:	AR - Receivables
Description:
Implementation/DBA Data:	APPS.HZ_CONTACT_FIND_V

View Text

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'

Columns