Tuesday, December 24, 2013

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

Customer's phone, email and URL attached to customer directly.

Phone, Email and URL attached to customer's address


  1. hi sanjai..thanks for the queries, can i get email and phone in a single row?

    1. I am not sure about your exact requirements but you may try something like the query given below. If you remove condition on primary_flag and customer has M phones and N email addresses then the query will return M * N rows

      account_number "Account Number" ,
      obj.party_name "Customer Name" ,
      sub.party_name "Contact Name" ,
      hcp_phone.phone_area_code || ' ' || hcp_phone.phone_number Phone ,
      apps.hz_cust_accounts hca ,
      apps.hz_parties obj ,
      apps.hz_relationships rel ,
      apps.hz_contact_points hcp_phone ,
      apps.hz_contact_points hcp_email ,
      apps.hz_parties sub
      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_phone.owner_table_id (+)
      AND hcp_phone.owner_table_name (+) = 'HZ_PARTIES'
      AND hcp_phone.contact_point_type (+)= 'PHONE'
      AND hcp_phone.primary_flag (+) = 'Y'
      AND rel.party_id = hcp_email.owner_table_id (+)
      AND hcp_email.owner_table_name (+) = 'HZ_PARTIES'
      AND hcp_email.contact_point_type (+)= 'EMAIL'
      AND hcp_email.primary_flag (+) = 'Y'
      AND hca.account_number = :Account_Number

  2. yahoo mail support || yahoo helpline || yahoo phone number || yahoo support
    yahoo phone number

  3. Thank you, this was helpful. It would be nice to add a query to get contact phone details to customer contacts attached at customer address level