1 2 Previous Next 21 Replies Latest reply on Nov 26, 2018 7:09 AM by Anrich-Ankit

    Get customer contact information (query) given an account_number

    Oraclian10

      Hey guys, how are you?
      I got a problem: i've searching/trying to get the information of a custumer using the different tables (i.e hz_contacts_points) without success
      I want a query that shows me(with an account_number) all the information about that customer (phone/s, email, address/addresses, and so on) because i want to check if that customer has an empty field.
      Can you help me? I found several queries but no one helped me :/

       

      Thanks

        • 1. Re: Get customer contact information (query) given an account_number
          Anrich-Ankit

          Hi User,

           

          Below query should give you the required fields.

           

          SELECT hp.party_name customer_name, hca.cust_account_id customer_id,

                 hcsu.LOCATION customer_site_name,

                 hcas.cust_acct_site_id customer_site_id, hcsu.site_use_code site_type,

                 hl.city, hl.province, hl.postal_code, hcas.attribute5 repair_site,

                 NVL ((SELECT distinct phone_number

          FROM hz_contact_points

          WHERE status = 'A'

          AND primary_flag = 'Y'

          and owner_table_name='HZ_PARTY_SITES'

          AND contact_point_type = 'PHONE'

          AND owner_table_id = hcas.cust_acct_site_id

          and rownum=1),

          NULL

          ) PHONE_NUMBER,

                 NVL ((SELECT distinct email_address

          FROM hz_contact_points

          WHERE status = 'A'

          AND primary_flag = 'Y'

          and owner_table_name='HZ_PARTY_SITES'

          AND contact_point_type = 'EMAIL'

          AND owner_table_id = hcas.cust_acct_site_id

          and rownum=1),

          NULL

          ) EMAIL,

                 hcas.status site_status,

                 DECODE (hcas.attribute5, 'PUP', 'Y', 'N') usage_type,

                 hca.status account_status

            FROM apps.hz_cust_accounts hca,

                 apps.hz_cust_acct_sites_all hcas,

                 apps.hz_cust_site_uses_all hcsu,

                 apps.hz_cust_acct_sites_all hcas2,

                 apps.hz_cust_site_uses_all hcsu2,

                 apps.hz_parties hp2,

                 apps.hz_party_sites hps2,

                 apps.hz_locations hl2,

                 apps.hz_parties hp,

                 apps.hz_party_sites hps,

                 apps.hz_locations hl

          WHERE hca.cust_account_id = hcas.cust_account_id

             AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id

             AND hcas.party_site_id = hps.party_site_id

             AND hps.location_id = hl.location_id

             AND hps.party_id = hp.party_id

             AND hca.cust_account_id = hcas2.cust_account_id

             AND hcsu2.cust_acct_site_id = hcas2.cust_acct_site_id

             AND hcas2.party_site_id = hps2.party_site_id

             AND hps2.location_id = hl2.location_id

             AND hps2.party_id = hp2.party_id

             AND hcsu.site_use_code = 'SHIP_TO'

             AND hcsu2.site_use_code = 'BILL_TO'

             AND hcsu2.primary_flag = 'Y'

             AND hcas.status = 'A'

             AND hcsu.status = 'A'

             AND hcsu2.status = 'A'

             AND hps.status = 'A'

             AND hca.status = 'A'

           

          Regards,

           

          • 2. Re: Get customer contact information (query) given an account_number
            Oraclian10

            Thank you for replying!
            Im sorry, it does not give me the number account :/
            What should i do?

            • 3. Re: Get customer contact information (query) given an account_number
              Anrich-Ankit

              Hi,

               

              Number account or account number.

               

              Does this query return any output for you.

               

              Regards,

              • 4. Re: Get customer contact information (query) given an account_number
                Oraclian10

                Your query just give me column with "Account_status".
                I wanted a query that allows me to put a number account and after executing it get all the contact information about it (phone, address, email and so on)
                Do you understand me? :/

                • 5. Re: Get customer contact information (query) given an account_number
                  Anrich-Ankit

                  Hi,

                   

                  I hope below query helps you.

                   

                  SELECT hp.party_name customer_name,hca.account_number, hca.cust_account_id customer_id,

                         hcsu.LOCATION customer_site_name,

                         hcas.cust_acct_site_id customer_site_id, hcsu.site_use_code site_type,

                         hl.city, hl.province, hl.postal_code, hcas.attribute5 repair_site,

                         NVL ((SELECT DISTINCT phone_number

                                          FROM hz_contact_points

                                         WHERE status = 'A'

                                           AND primary_flag = 'Y'

                                           AND owner_table_name = 'HZ_PARTY_SITES'

                                           AND contact_point_type = 'PHONE'

                                           AND owner_table_id = hcas.cust_acct_site_id

                                           AND ROWNUM = 1),

                              NULL

                             ) phone_number,

                         NVL ((SELECT DISTINCT email_address

                                          FROM hz_contact_points

                                         WHERE status = 'A'

                                           AND primary_flag = 'Y'

                                           AND owner_table_name = 'HZ_PARTY_SITES'

                                           AND contact_point_type = 'EMAIL'

                                           AND owner_table_id = hcas.cust_acct_site_id

                                           AND ROWNUM = 1),

                              NULL

                             ) email,

                         hcas.status site_status,

                         DECODE (hcas.attribute5, 'PUP', 'Y', 'N') usage_type,

                         hca.status account_status

                    FROM apps.hz_cust_accounts hca,

                         apps.hz_cust_acct_sites_all hcas,

                         apps.hz_cust_site_uses_all hcsu,

                         apps.hz_cust_acct_sites_all hcas2,

                         apps.hz_cust_site_uses_all hcsu2,

                         apps.hz_parties hp2,

                         apps.hz_party_sites hps2,

                         apps.hz_locations hl2,

                         apps.hz_parties hp,

                         apps.hz_party_sites hps,

                         apps.hz_locations hl

                  WHERE hca.cust_account_id = hcas.cust_account_id

                     AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id

                     AND hcas.party_site_id = hps.party_site_id

                     AND hps.location_id = hl.location_id

                     AND hps.party_id = hp.party_id

                     AND hca.cust_account_id = hcas2.cust_account_id

                     AND hcsu2.cust_acct_site_id = hcas2.cust_acct_site_id

                     AND hcas2.party_site_id = hps2.party_site_id

                     AND hps2.location_id = hl2.location_id

                     AND hps2.party_id = hp2.party_id

                     AND hcsu.site_use_code = 'SHIP_TO'

                     AND hcsu2.site_use_code = 'BILL_TO'

                     AND hcsu2.primary_flag = 'Y'

                     AND hcas.status = 'A'

                     AND hcsu.status = 'A'

                     AND hcsu2.status = 'A'

                     AND hps.status = 'A'

                     AND hca.status = 'A'

                     AND hca.account_number=NVL(:1,hca.account_number)

                  • 6. Re: Get customer contact information (query) given an account_number
                    Oraclian10

                    Dude, i think there is something wrong because when i try to execute it, i put the number account and it give me empty rows.. and thats not correct.. that account exist on EBS :/

                    • 7. Re: Get customer contact information (query) given an account_number
                      Anrich-Ankit

                      Please provide me the output of below query.

                       

                      SELECT account_number,cust_account_id,status  from apps.hz_cust_accounts;

                       

                      Check if all the sites are active for that account as this query returns only active sites. otherwise runt the query like this. Should return the output even if account or sites are not active.

                       

                      SELECT hp.party_name customer_name,hca.account_number, hca.cust_account_id customer_id,

                             hcsu.LOCATION customer_site_name,

                             hcas.cust_acct_site_id customer_site_id, hcsu.site_use_code site_type,

                             hl.city, hl.province, hl.postal_code, hcas.attribute5 repair_site,

                             NVL ((SELECT DISTINCT phone_number

                                              FROM hz_contact_points

                                             WHERE status = 'A'

                                               AND primary_flag = 'Y'

                                               AND owner_table_name = 'HZ_PARTY_SITES'

                                               AND contact_point_type = 'PHONE'

                                               AND owner_table_id = hcas.cust_acct_site_id

                                               AND ROWNUM = 1),

                                  NULL

                                 ) phone_number,

                             NVL ((SELECT DISTINCT email_address

                                              FROM hz_contact_points

                                             WHERE status = 'A'

                                               AND primary_flag = 'Y'

                                               AND owner_table_name = 'HZ_PARTY_SITES'

                                               AND contact_point_type = 'EMAIL'

                                               AND owner_table_id = hcas.cust_acct_site_id

                                               AND ROWNUM = 1),

                                  NULL

                                 ) email,

                             hcas.status site_status,

                             DECODE (hcas.attribute5, 'PUP', 'Y', 'N') usage_type,

                             hca.status account_status

                        FROM apps.hz_cust_accounts hca,

                             apps.hz_cust_acct_sites_all hcas,

                             apps.hz_cust_site_uses_all hcsu,

                             apps.hz_cust_acct_sites_all hcas2,

                             apps.hz_cust_site_uses_all hcsu2,

                             apps.hz_parties hp2,

                             apps.hz_party_sites hps2,

                             apps.hz_locations hl2,

                             apps.hz_parties hp,

                             apps.hz_party_sites hps,

                             apps.hz_locations hl

                      WHERE hca.cust_account_id = hcas.cust_account_id

                         AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id

                         AND hcas.party_site_id = hps.party_site_id

                         AND hps.location_id = hl.location_id

                         AND hps.party_id = hp.party_id

                         AND hca.cust_account_id = hcas2.cust_account_id

                         AND hcsu2.cust_acct_site_id = hcas2.cust_acct_site_id

                         AND hcas2.party_site_id = hps2.party_site_id

                         AND hps2.location_id = hl2.location_id

                         AND hps2.party_id = hp2.party_id

                         AND hcsu.site_use_code = 'SHIP_TO'

                         AND hcsu2.site_use_code = 'BILL_TO'

                         AND hcsu2.primary_flag = 'Y'

                         AND hca.account_number=NVL(:1,hca.account_number)

                      • 8. Re: Get customer contact information (query) given an account_number
                        Oraclian10

                        Dude, after executing this query: SELECT account_number,cust_account_id,status  from apps.hz_cust_accounts;
                        It says that the accoount is "active" (The number account is there)

                         

                        But when i try to run the second one, it shows me empty rows.. What is going on?

                        • 9. Re: Get customer contact information (query) given an account_number
                          Anrich-Ankit

                          Provide the output of below query.

                           

                          SELECT hca.account_number, hca.cust_account_id customer_id,

                                 hcsu.LOCATION customer_site_name,

                                 hcas.cust_acct_site_id customer_site_id, hcsu.site_use_code site_type,hca.status,hcsu.status,hcas.status

                            FROM apps.hz_cust_accounts hca,

                                 apps.hz_cust_acct_sites_all hcas,

                                 apps.hz_cust_site_uses_all hcsu

                          WHERE hca.cust_account_id = hcas.cust_account_id

                             AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id

                             AND hca.account_number = ' ' ;

                          • 10. Re: Get customer contact information (query) given an account_number
                            Oraclian10

                            Empty rows.. it does not show anything but the name of the columns :/

                            • 11. Re: Get customer contact information (query) given an account_number
                              Anrich-Ankit

                              That means you have the only account created in the system but no site. Hence returning no rows.  To confirm that run below three select statement

                               

                              select   hca.account_number, hca.cust_account_id from apps.hz_cust_accounts hca  WHERE   hca.account_number = 'your account number'

                                

                                 select   hcas.cust_acct_site_id from hz_cust_acct_sites_all hcas WHERE   cust_account_id = ' cust_account_id' ( Account id from first query)

                                

                                 select  *  from  apps.hz_cust_site_uses_all where cust_account_id = ' cust_account_id' ( Account id from first query)

                              • 12. Re: Get customer contact information (query) given an account_number
                                Oraclian10

                                I tried the 3 queries. These 2 queries are working

                                select   hca.account_number, hca.cust_account_id from apps.hz_cust_accounts hca  WHERE   hca.account_number = 'your account number'

                                  

                                select   hcas.cust_acct_site_id from hz_cust_acct_sites_all hcas WHERE   cust_account_id = ' cust_account_id' ( Account id from first query)

                                 

                                 

                                This one is not working:

                                select  *  from  apps.hz_cust_site_uses_all where cust_account_id = ' cust_account_id' ( Account id from first query)

                                 

                                So, what should in oder to get the phone, address, email for that number account? :/

                                • 13. Re: Get customer contact information (query) given an account_number
                                  Anrich-Ankit

                                  Yes,


                                  1. Check and try to create the uses of the site. (If not create either bill to or ship to and try to run the first query)

                                   

                                  2. If contact points are already created on the front end, then you can find out with below query.

                                   

                                  For Phone number

                                   

                                  SELECT DISTINCT phone_number

                                                          FROM hz_contact_points

                                                         WHERE status = 'A'

                                                           AND primary_flag = 'Y'

                                                           AND owner_table_name = ( 'HZ_PARTY_SITES')

                                                           AND contact_point_type = 'PHONE'

                                                           AND owner_table_id = cust_acct_site_id( Output from the second query)

                                                           AND ROWNUM = 1;

                                   

                                  For Email

                                   

                                  SELECT DISTINCT email_address

                                                          FROM hz_contact_points

                                                         WHERE status = 'A'

                                                           AND primary_flag = 'Y'

                                                           AND owner_table_name = 'HZ_PARTY_SITES'

                                                           AND contact_point_type = 'EMAIL'

                                                           AND owner_table_id = cust_acct_site_id( Output from the second query)

                                                           AND ROWNUM = 1

                                   

                                  These queries will work only if the contacts are created at the site level.

                                  • 14. Re: Get customer contact information (query) given an account_number
                                    Oraclian10

                                    Dude, i dont know how to do that: "Check and try to create the uses of the site."

                                     

                                    I tried to run the first query but i got an error.. i do not what to do.. i only know that there is a PROCEDURE that brings all the information about the customers. I cannot ceate anything, read-only pemission

                                    1 2 Previous Next