View: HZ_CUSTOMER_PARTY_FIND_V

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

View Text

SELECT CA.CUST_ACCOUNT_ID
, ' '||HP.PARTY_NAME||' '
, HP.PARTY_NAME
, HP.PERSON_FIRST_NAME
, HP.PERSON_LAST_NAME
, HL.ADDRESS1
, HL.CITY
, HL.STATE
, HL.POSTAL_CODE
, TER.DESCRIPTION COUNTRY
, HL.COUNTRY COUNTRY_CODE
, CA.ACCOUNT_NUMBER
, HP.PARTY_NUMBER
, CA.STATUS
, HP.TAX_REFERENCE
, HP.JGZZ_FISCAL_CODE TAXPAYER_ID
, CA.ORIG_SYSTEM_REFERENCE
, CA.CUSTOMER_TYPE
, HP.CATEGORY_CODE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('CUSTOMER CLASS'
, CA.CUSTOMER_CLASS_CODE)
, HP.SIC_CODE
, HPS.PARTY_SITE_NUMBER
, HL.ADDRESS2
, HL.ADDRESS3
, HL.ADDRESS4
, HL.COUNTY
, HL.PROVINCE
, HP.CUSTOMER_KEY
, HL.ADDRESS_KEY
, HP.PARTY_TYPE
, HCS.CUST_ACCT_SITE_ID
, HP.PARTY_ID
, HP.PERSON_PRE_NAME_ADJUNCT
, HP.PERSON_MIDDLE_NAME
, HP.PERSON_NAME_SUFFIX
, DECODE(HP.PARTY_TYPE
, 'ORGANIZATION'
, HP.ORGANIZATION_NAME_PHONETIC
, NULL) ORGANIZATION_NAME_PHONETIC
, CA.ACCOUNT_NAME
, HPS.IDENTIFYING_ADDRESS_FLAG
, HPS.PARTY_SITE_ID
, DECODE(HP.PARTY_TYPE
, 'PERSON'
, PER_PROF.PERSON_NAME_PHONETIC
, NULL) PERSON_NAME_PHONETIC
FROM HZ_CUST_ACCOUNTS CA
, HZ_PARTIES HP
, HZ_PERSON_PROFILES PER_PROF
, HZ_CUST_ACCT_SITES HCS
, HZ_PARTY_SITES HPS
, HZ_LOCATIONS HL
, FND_TERRITORIES_TL TER
WHERE CA.PARTY_ID = HP.PARTY_ID
AND CA.CUST_ACCOUNT_ID = HCS.CUST_ACCOUNT_ID (+)
AND HP.PARTY_ID = PER_PROF.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 TER.LANGUAGE(+) = USERENV('LANG') UNION ALL SELECT -1
, HP.PARTY_NAME
, HP.PARTY_NAME
, HP.PERSON_FIRST_NAME
, HP.PERSON_LAST_NAME
, HP.ADDRESS1
, HP.CITY
, HP.STATE
, HP.POSTAL_CODE
, TER.DESCRIPTION COUNTRY
, HP.COUNTRY COUNTRY_CODE
, NULL
, HP.PARTY_NUMBER
, HP.STATUS
, HP.TAX_REFERENCE
, HP.JGZZ_FISCAL_CODE TAXPAYER_ID
, HP.ORIG_SYSTEM_REFERENCE
, NULL
, HP.CATEGORY_CODE
, NULL
, HP.SIC_CODE
, NULL
, HP.ADDRESS2
, HP.ADDRESS3
, HP.ADDRESS4
, HP.COUNTY
, HP.PROVINCE
, HP.CUSTOMER_KEY
, NULL
, HP.PARTY_TYPE
, -1
, HP.PARTY_ID
, HP.PERSON_PRE_NAME_ADJUNCT
, HP.PERSON_MIDDLE_NAME
, HP.PERSON_NAME_SUFFIX
, DECODE(HP.PARTY_TYPE
, 'ORGANIZATION'
, HP.ORGANIZATION_NAME_PHONETIC
, NULL) ORGANIZATION_NAME_PHONETIC
, NULL
, 'Y'
, -1
, DECODE(HP.PARTY_TYPE
, 'PERSON'
, PER_PROF.PERSON_NAME_PHONETIC
, NULL) PERSON_NAME_PHONETIC
FROM HZ_PARTIES HP
, HZ_PERSON_PROFILES PER_PROF
, FND_TERRITORIES_TL TER
WHERE HP.PARTY_ID = PER_PROF.PARTY_ID(+)
AND HP.COUNTRY = TER.TERRITORY_CODE (+)
AND TER.LANGUAGE(+) = USERENV('LANG')

Columns