2 Replies Latest reply: Jun 25, 2013 12:04 PM by SarathL RSS

    Need Customer Account Site Contact with Site Number and Site Id

    Tarun Prakash
      Hi Folks,

      I am trying to write a query to get the customer name and customer contacts (contact person,contact mail,phone number etc)

      Here is my query

      SELECT hp.party_id,hp.party_name customer_name, h_contact.party_name contact_person_name,hcp.phone_number,
      hcp.EMAIL_ADDRESS,cust.account_name,cust.account_number
      FROM
      hz_parties hp,
      hz_relationships hr,
      hz_parties h_contact ,
      hz_contact_points hcp,
      hz_cust_accounts cust
      where
      1=1
      and hr.subject_id = h_contact.PARTY_ID
      and hr.object_id = hp.party_id
      and hcp.owner_table_id(+) = hr.party_id
      and cust.party_id = hp.party_id
      and hcp.CONTACT_POINT_TYPE ='EMAIL'
      and hcp.STATUS = 'A'
      AND hp.party_name=:1

      Here the contact details are from the Account Site Contacts of Customer master

      I need the party_site_number and party_site_also in thins... But the correct linkage i don know.,

      Also anything wrong in this query??

      Any one can help me ...


      Regards,
      Tarun
        • 1. Re: Need Customer Account Site Contact with Site Number and Site Id
          Deborahcrm-Oracle
          Hello Tarun,

          You can reference the output of the following diagnostic script which includes a link to the actual SQL query used -

          Document ID: 309862.1 - 11i : Oracle Receivables Party Data Data Collection Test     

          Steps to run this report -

          Login to Oracle E-Business Suite
          Select the responsibility "Oracle Diagnostics Tool" (see Note 358831.1 for details)
          Select application "Oracle Receivables" from the "Application" list of values
          Click the "Advanced" tab
          Scroll down to group "Customers"
          Select test name "Party Data"
          Input Parameters (* required)
          Responsibility Id (LOV) * enter your Receivables responsibility
          Party Number (LOV) *



          Regards,
          Debbie
          • 2. Re: Need Customer Account Site Contact with Site Number and Site Id
            SarathL

            SELECT hp.party_name customer, hp1.party_name contact, hcsu.site_use_code

              FROM hz_cust_accounts_all hca,

                   hz_cust_acct_sites_all hcas,

                   hz_cust_site_uses_all hcsu,

                   hz_cust_account_roles hcar,

                   hz_relationships hr,

                   hz_parties hp,

                   hz_parties hp1

            WHERE hca.party_id = hp.party_id

               AND hca.cust_account_id = hcas.cust_account_id

               AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id

               AND hcsu.contact_id = hcar.cust_account_role_id

               AND hcar.party_id = hr.party_id

               AND hr.relationship_code = 'CONTACT_OF'

               AND hr.object_id = hp.party_id

               AND hr.subject_id = hp1.party_id;