1 Reply Latest reply on Mar 18, 2013 12:41 PM by IdrisAli

    How to display Customer contact purpose details

      Hi All,

      I am using below query for getting customer contact details and I am getting all teh details except contact purpose( e.g. Acknowledgments,Invoices) fields anywhere. Which table I need to join for this? I havetried using Diagnostics also but nothing related to this.
      in attached screen shot I have got all the information except purpose field.
      Version 12.1.1


      Open a customer-> go to site etails-> communication tab>Account site contacts-> Crate new contacts-> Add a Purpose

      I want to display the purpose field in below query :

      SELECT hcas.CUST_ACCT_SITE_ID,h_contact.party_id contact_id,h_contact.party_name contact_name, hcp.phone_number, hcp.EMAIL_ADDRESS ,hp.party_id,hp.party_name --, hcp.phone_number,
      ,cust.account_name,cust.account_number,nvl2(hcas.org_id,'SITE CONTACT','ACCOUNT CONTACT') "Contact Level",
      nvl(hcas.org_id,cust.org_id) org_id
      ar.hz_parties hp,
      ar.hz_relationships hr,
      ar.hz_parties h_contact ,
      ar.hz_contact_points hcp,
      ar.hz_cust_accounts cust,
      ar.hz_cust_Account_roles hcar,
      ar.hz_cust_Acct_sites_all hcas
      hr.subject_id = h_contact.PARTY_ID
      and hr.subject_type = 'PERSON'
      and hr.object_id = hp.party_id
      and hp.party_name = :p_party_name
      and hcp.owner_table_id(+) = hr.party_id
      and cust.party_id = hp.party_id
      and hcar.cust_Account_id = cust.cust_Account_id
      and hcar.party_id = hr.party_id
      and hcar.cust_Acct_site_id=hcas.cust_Acct_site_id