3 Replies Latest reply on Apr 22, 2014 10:00 AM by 1002933

    Sql query for Customer Account number, Customer name and Customer Site name, operating unit name group by customer name.

    1002933

      Hi All,

       

      Need sql query for the below requirement, Kindly suggest.

      We are using Apps R12.1.3. Database 11g.

       

      Customer Account number, Customer name and Customer Site name, operating unit name group by customer name.

      If the customer has number of sites then also it should view all the detail by keeping customer name common to that site.

       

      Below is the query found on net, but the Site_name is coming null. Kindly check if there is any problem with the query.

       

      SELECT  --  hl.orig_system_reference

               --,hl.country

               --,hl.address1

               --,hl.address2

               --,hl.address3

               --,hl.address4

               --,hl.city

               --,hl.postal_code

               --,hl.state

               --,hl.province

               --,hl.county

               --,hl.content_source_type

               hp.party_number

               ,hp.party_name

               ,hca.account_number

               ,hou.NAME operting_unit_name

               --,hl.actual_content_source actual_content_source

               ,hps.party_site_number

               ,hps.party_site_name

               --,hps.identifying_address_flag

               ,hps.status

               --,hps.created_by_module

               --,hps.actual_content_source site_actual_content_source

               --,hcasa.orig_system_reference site_orig_system_reference

               ,hcasa.status         acct_site_status

               ,bill_to_flag

         FROM  ar.hz_locations        hl

              ,ar.hz_party_sites      hps

              ,ar.hz_cust_acct_sites_all  hcasa

              ,ar.hz_parties     hp

              ,ar.hz_cust_accounts   hca

              ,hr_operating_units hou

         WHERE hcasa.party_site_id     = hps.party_site_id

         AND   hps.location_id         = hl.location_id

         AND   hp.party_id             = hca.party_id

         AND   hp.party_id             = hps.party_id

         AND   hca.cust_account_id     = hcasa.cust_account_id

         AND   hcasa.org_id            = hou.organization_id

         AND   hp.party_type           ='ORGANIZATION'

         AND   hps.status              = 'A'

         AND   hcasa.status            = 'A'

         AND   hp.status               = 'A'

         AND   hca.status              = 'A'

         order by party_number, party_site_number

       

      Thanks & Regards,

      Afzal.

        • 2. Re: Sql query for Customer Account number, Customer name and Customer Site name, operating unit name group by customer name.
          VimalRM

          Hi Afzal,

           

          You can try this Query.

           

          *************************************************************************************************************************************

          select  A.party_number,A.party_name,d.account_number,

                  A.operting_unit_name,A.party_site_number,

                  A.party_site_name,A.status,A.acct_site_status,A.bill_to_flag

                  from

          (select hp.party_id,hp.party_number,hp.party_name,

                  hou.NAME operting_unit_name,hps.party_site_number,

                  hps.party_site_name,hps.status,hcasa.cust_account_id,

                  hcasa.status acct_site_status,bill_to_flag

              from

                  hz_party_sites hps,

                  hz_cust_acct_sites_all hcasa,

                  hz_parties hp,

                  hz_locations hl,

                  hr_operating_units hou

            where hps.party_site_name is not null

              and hps.party_site_id   =   hcasa.party_site_id(+)

              and hps.party_id        =   hp.party_id

              and hps.location_id     =   hl.location_id

              and hcasa.org_id        =   hou.organization_id(+)

              and hp.party_type       =   'ORGANIZATION'

              and hps.status              = 'A'

              --and hcasa.status            = 'A'

              and hp.status               = 'A'

              ) a

          left outer join

              hz_cust_accounts d

              on  a.party_id          =   d.party_id

              and a.cust_account_id   =   d.cust_account_id

              and d.status            = 'A'

          by party_number, party_site_number

           

           

          Thanks & Regards,

          Vimal

           

          1 person found this helpful
          • 3. Re: Sql query for Customer Account number, Customer name and Customer Site name, operating unit name group by customer name.
            1002933

            Hi Vimal,

             

            Thanks for the query.

            Actually this is for one of my friend, I modified below query found on net and its working fine.

             

             

            SELECT hca.account_number customer_number, hp.party_name customer_name,

             

                   (SELECT hcpa.currency_code

                      FROM hz_cust_profile_amts hcpa

                     WHERE hcpa.cust_account_id = hca.cust_account_id

                       AND hcpa.site_use_id IS NULL) " CURRENCY",

                   hp.attribute1 " Customer Credit Limit",

                   hp.attribute1 " Customer Order Limit",

                   (SELECT meaning

                      FROM ar_lookups

                     WHERE lookup_type = 'CUSTOMER CLASS'

                       AND lookup_code = hca.customer_class_code)

                                                                "Customer Classification",

                   (SELECT hcpc.NAME

                      FROM hz_cust_profile_classes hcpc,

                           hz_customer_profiles hcp

                     WHERE hcpc.profile_class_id = hcp.profile_class_id

                       AND hcp.cust_account_id = hca.cust_account_id

                       AND hcp.site_use_id IS NULL) "Customer Profile ",

                   (SELECT NAME

                      FROM ra_terms

                     WHERE term_id = hca.payment_term_id) "Customer Payment Terms",

                   (SELECT meaning

                      FROM ar_lookups

                     WHERE lookup_type = 'CUSTOMER_CATEGORY'

                       AND lookup_code = hcasa.customer_category_code)

                                                                      "Customer Category",

                   hps.party_site_number site_number,

                   (SELECT hcpa.trx_credit_limit

                      FROM hz_cust_profile_amts hcpa

                     WHERE hcpa.cust_account_id =

                                                 hca.cust_account_id

                       AND hcpa.site_use_id IS NULL) " site Credit Limit",

                   (SELECT hcpa.overall_credit_limit

                      FROM hz_cust_profile_amts hcpa

                     WHERE hcpa.cust_account_id = hca.cust_account_id

                       AND hcpa.site_use_id IS NULL) " Site Order Limit",

                   (SELECT hcpc.NAME

                      FROM hz_cust_profile_classes hcpc,

                           hz_customer_profiles hcp,

                           hz_cust_site_uses_all hcsua

                     WHERE hcpc.profile_class_id = hcp.profile_class_id

                       AND hcp.cust_account_id = hca.cust_account_id

                       AND hcp.site_use_id = hcsua.site_use_id

                       AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id

                       AND hcsua.site_use_code = 'BILL_TO') "Customer Site Profile ",

                   (SELECT rt.NAME

                      FROM hz_customer_profiles hcp,

                           ra_terms rt,

                           hz_cust_site_uses_all hcsua

                     WHERE hcp.standard_terms = rt.term_id

                       AND hcp.cust_account_id = hca.cust_account_id

                       AND hcp.site_use_id = hcsua.site_use_id

                       AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id

                       AND hcsua.site_use_code = 'BILL_TO') "Site Payemnt Terms",

                   (SELECT DISTINCT hcasa.attribute1

                               FROM hz_cust_site_uses_all hcsua

                              WHERE hcsua.cust_acct_site_id =

                                           hcasa.cust_acct_site_id)

                                                                   " Customer Site Class",

                   (SELECT address1

                      FROM hz_locations

                     WHERE location_id = hps.location_id) address1,

                   (SELECT address2

                      FROM hz_locations

                     WHERE location_id = hps.location_id) address2,

                   (SELECT address3

                      FROM hz_locations

                     WHERE location_id = hps.location_id) address3,

                   (SELECT address4

                      FROM hz_locations

                     WHERE location_id = hps.location_id) address4,

                   (SELECT city

                      FROM hz_locations

                     WHERE location_id = hps.location_id) city,

                   (SELECT postal_code

                      FROM hz_locations

                     WHERE location_id = hps.location_id) postal_code,

                   (SELECT state

                      FROM hz_locations

                     WHERE location_id = hps.location_id) state,

                   /*(SELECT ftt.territory_short_name

             

                      FROM fnd_territories_tl ftt, hz_locations hl

             

                     WHERE hl.country = ftt.territory_code

                       AND hl.location_id = hps.location_id) country,*/

             

             

             

                         ,(SELECT hou.Name from hr_operating_units hou

             

                   where hcasa.org_id = hou.organization_id)Operating_unit

                    ,hcasa.org_id--see the last column to know ur org_id

             

              FROM hz_parties hp,

             

                   hz_party_sites hps,

                   hz_cust_accounts_all hca,

                   hz_cust_acct_sites_all hcasa

            WHERE hp.party_id = hps.party_id

               AND hp.party_id = hca.party_id

               AND hcasa.party_site_id = hps.party_site_id

               AND hca.cust_account_id = hcasa.cust_account_id

               AND hca.account_number NOT LIKE '%Unapplied%'

               and hcasa.org_id = 145--give ur org_id here


            Regards,

            Afzal.