VIEW: APPS.HZ_CUSTOMER_PARTY_SITE_FIND_V

Source

View Text - Preformatted

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, 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, hp.organization_name_phonetic, ca.account_name, hps.identifying_address_flag FROM hz_cust_accounts ca, hz_parties hp, 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 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') union 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, hp.organization_name_phonetic, null, 'Y' FROM hz_parties hp, fnd_territories_tl ter WHERE hp.country = ter.territory_code (+) and nvl(ter.language,USERENV('LANG')) = USERENV('LANG')

View Text - HTML Formatted

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
, 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
, HP.ORGANIZATION_NAME_PHONETIC
, CA.ACCOUNT_NAME
, HPS.IDENTIFYING_ADDRESS_FLAG
FROM HZ_CUST_ACCOUNTS CA
, HZ_PARTIES HP
, 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 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') UNION 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
, HP.ORGANIZATION_NAME_PHONETIC
, NULL
, 'Y'
FROM HZ_PARTIES HP
, FND_TERRITORIES_TL TER
WHERE HP.COUNTRY = TER.TERRITORY_CODE (+)
AND NVL(TER.LANGUAGE
, USERENV('LANG')) = USERENV('LANG')