Customer's Phone, Email and URL

Contact point such as Phone, Email or UL can be attached to a customer at multiple levels such as Customer itself, Customer's Address or through a customer's contact. Today again i had to write queries to extract customer contact information, so i added these query here.

Customer contacts and their phone, email, URL

select account_number "Account Number" , obj.party_name "Customer Name" , sub.party_name "Contact Name" , hcp.contact_point_type || ': ' || DECODE(hcp.contact_point_type, 'EMAIL', hcp.email_address , 'PHONE', hcp.phone_area_code || ' ' || hcp.phone_number , 'WEB' , hcp.url , 'Unknow contact Point Type ' || hcp.contact_point_type ) "How to Contact" from apps.hz_cust_accounts hca , apps.hz_parties obj , apps.hz_relationships rel , apps.hz_contact_points hcp , apps.hz_parties sub where hca.party_id = rel.object_id and hca.party_id = obj.party_id and rel.subject_id = sub.party_id and rel.relationship_type = 'CONTACT' and rel.directional_flag = 'F' and rel.party_id = hcp.owner_table_id and hcp.owner_table_name = 'HZ_PARTIES' ;

Customer's phone, email and URL attached to customer directly.
select account_number "Account Number" , hp.party_name "Customer Name" , hcp.contact_point_type || ': ' || DECODE(hcp.contact_point_type, 'EMAIL', hcp.email_address , 'PHONE', hcp.phone_area_code || ' ' || hcp.phone_number , 'WEB' , hcp.url , 'Unknow contact Point Type ' || hcp.contact_point_type ) "How to Contact" from apps.hz_cust_accounts hca , apps.hz_parties hp , apps.hz_contact_points hcp where hca.party_id = hp.party_id and hp.party_id = hcp.owner_table_id and hcp.owner_table_name = 'HZ_PARTIES' ;

Phone, Email and URL attached to customer's address

select account_number "Account Number" , hp.party_name "Customer Name" , loc.address1 || decode(loc.address2, null, null, ', ' || loc.address2) || decode(loc.address3, null, null, ', ' || loc.address3) || decode(loc.address4, null, null, ', ' || loc.address4) || ' ' || loc.city || ', ' || loc.state || ' ' || loc.postal_code "Address" , hcp.contact_point_type || ': ' || DECODE(hcp.contact_point_type, 'EMAIL', hcp.email_address , 'PHONE', hcp.phone_area_code || ' ' || hcp.phone_number , 'WEB' , hcp.url , 'Unknow contact Point Type ' || hcp.contact_point_type ) "How to Contact" from apps.hz_cust_accounts hca , apps.hz_parties hp , apps.hz_cust_acct_sites_all cas , apps.hz_contact_points hcp , apps.hz_party_sites hps , apps.hz_locations loc where hca.cust_account_id = cas.cust_account_id and hca.party_id = hp.party_id and hcp.owner_table_id = cas.party_site_id and hcp.owner_table_name = 'HZ_PARTY_SITES' and cas.party_site_id = hps.party_site_id and hps.location_id = loc.location_id ;