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

8 comments:

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

    ReplyDelete
    Replies
    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

      SELECT
      account_number "Account Number" ,
      obj.party_name "Customer Name" ,
      sub.party_name "Contact Name" ,
      hcp_phone.phone_area_code || ' ' || hcp_phone.phone_number Phone ,
      hcp_email.email_address
      FROM
      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
      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_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
      ;

      Delete
    2. HI, I need site wise contact details , can you help me

      Delete
    3. Hi but party_id column is not there in hz_relationships table

      Delete
  2. 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

    ReplyDelete