VIEW: APPS.ASO_PVT_CUSTOMER_CONT_FIND_V

Source

View Text - Preformatted

SELECT hcont.party_id party_id, hcont.party_number Registry_Id, hca.cust_account_id cust_account_id, hcont.person_last_name cont_last_name, hcont.person_first_name cont_first_name, hcont.party_name cont_full_name, phon.phone_country_code || phon.phone_area_code || phon.phone_number || phon.phone_extension cont_phone, phon.phone_country_code || ' ' || phon.phone_area_code || ' ' || phon.phone_number || ' ' || phon.phone_extension cont_phone_disp, phon.primary_flag primary_phone_flag, hcont.customer_key contact_key, hrel.party_id party_relationship_id, hcust.party_id cust_party_id, hcust.party_name customer_name, hcust.person_last_name last_name, hcust.person_first_name first_name, hcust.status status, hca.account_number account_number, hcust.tax_reference tax_reg_number, hcust.jgzz_fiscal_code taxpayer_id, hca.orig_system_reference reference, hcust.party_type customer_type, hcust.customer_key customer_key FROM hz_relationships hrel, hz_parties hcont, hz_cust_accounts hca, hz_parties hcust, hz_contact_points phon WHERE hcont.party_type = 'PERSON' AND hrel.subject_id = hcont.party_id and hrel.object_id = hcust.party_id and hrel.subject_table_name = 'HZ_PARTIES' and hrel.object_table_name = 'HZ_PARTIES' and hca.party_id(+) = hcust.party_id and hrel.party_id = phon.owner_table_id(+) and phon.owner_table_name(+) = 'HZ_PARTIES' and phon.contact_point_type(+) = 'PHONE' and hcont.status = 'A' and phon.status(+) = 'A' and hcust.status = 'A' and hca.status(+) = 'A' and hrel.status = 'A'

View Text - HTML Formatted

SELECT HCONT.PARTY_ID PARTY_ID
, HCONT.PARTY_NUMBER REGISTRY_ID
, HCA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, HCONT.PERSON_LAST_NAME CONT_LAST_NAME
, HCONT.PERSON_FIRST_NAME CONT_FIRST_NAME
, HCONT.PARTY_NAME CONT_FULL_NAME
, PHON.PHONE_COUNTRY_CODE || PHON.PHONE_AREA_CODE || PHON.PHONE_NUMBER || PHON.PHONE_EXTENSION CONT_PHONE
, PHON.PHONE_COUNTRY_CODE || ' ' || PHON.PHONE_AREA_CODE || ' ' || PHON.PHONE_NUMBER || ' ' || PHON.PHONE_EXTENSION CONT_PHONE_DISP
, PHON.PRIMARY_FLAG PRIMARY_PHONE_FLAG
, HCONT.CUSTOMER_KEY CONTACT_KEY
, HREL.PARTY_ID PARTY_RELATIONSHIP_ID
, HCUST.PARTY_ID CUST_PARTY_ID
, HCUST.PARTY_NAME CUSTOMER_NAME
, HCUST.PERSON_LAST_NAME LAST_NAME
, HCUST.PERSON_FIRST_NAME FIRST_NAME
, HCUST.STATUS STATUS
, HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER
, HCUST.TAX_REFERENCE TAX_REG_NUMBER
, HCUST.JGZZ_FISCAL_CODE TAXPAYER_ID
, HCA.ORIG_SYSTEM_REFERENCE REFERENCE
, HCUST.PARTY_TYPE CUSTOMER_TYPE
, HCUST.CUSTOMER_KEY CUSTOMER_KEY
FROM HZ_RELATIONSHIPS HREL
, HZ_PARTIES HCONT
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HCUST
, HZ_CONTACT_POINTS PHON
WHERE HCONT.PARTY_TYPE = 'PERSON'
AND HREL.SUBJECT_ID = HCONT.PARTY_ID
AND HREL.OBJECT_ID = HCUST.PARTY_ID
AND HREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HCA.PARTY_ID(+) = HCUST.PARTY_ID
AND HREL.PARTY_ID = PHON.OWNER_TABLE_ID(+)
AND PHON.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND PHON.CONTACT_POINT_TYPE(+) = 'PHONE'
AND HCONT.STATUS = 'A'
AND PHON.STATUS(+) = 'A'
AND HCUST.STATUS = 'A'
AND HCA.STATUS(+) = 'A'
AND HREL.STATUS = 'A'