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

Posted by Sanjai Misra at 8:33 PM
Labels:

9 comments:

  1. Gayathri RamamurthyWednesday, November 2, 2016 at 2:47:00 PM CDT

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

    ReplyDelete
    Replies
    1. Sanjai MisraThursday, November 3, 2016 at 2:43:00 PM CDT

      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. mahadevThursday, June 11, 2020 at 1:56:00 AM CDT

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

      Delete
    3. NiharikaTuesday, May 25, 2021 at 10:51:00 AM CDT

      Hi but party_id column is not there in hz_relationships table

      Delete
  2. Oracle wizardTuesday, August 1, 2017 at 10:52:00 AM CDT

    11i or r12? or are they same?

    ReplyDelete
  3. UnknownFriday, September 15, 2017 at 12:57:00 PM CDT

    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
  4. Martin KellyMonday, May 13, 2019 at 7:03:00 AM CDT

    This blog is friendly and significant Blog of Microsoft Office Customer Service phone number. Thank you

    ReplyDelete
  5. AnonymousThursday, March 6, 2025 at 7:39:00 AM CST

    The last query gives the data but some of them are not visible from the Application frontend under the Customer Account Contacts. Can you please guide us why it was not showing? I took the OAF Page query from about Page too and that query also showing the data but same data not visible in the frontend form.

    ReplyDelete

Subscribe to: Post Comments (Atom)