3 Replies Latest reply on Jan 9, 2019 5:36 PM by Royal Cascade

    Query to get SalesRep Name of a Customer

    Royal Cascade

      Hello Gurus

       

      (oracle ebs r 12) I am trying to write a query to get Sales rep (Bill To, Ship To) for customers, i got primary_salesrep_id in hz_cust_site_uses_all table. Now the challenge to get name. I look into jtf_rs_salesreps, but this table is not updated for all names.

      then i connect it to PER_ALL_PEOPLE_F thru employee_number, still i cannot get all names. seems like tables are not updated. Name column shows null in it.

       

      i get behind oracle java pages and get the query. it uses a table ra_salesreps. when i query this table, it didnt even exists. Can someone please help me get Sales rep Name thru Primary _salesrep_id.

       

      Thanks

        • 1. Re: Query to get SalesRep Name of a Customer
          Royal Cascade

          i have this query. can someone help me adding sales rep name in it. when i add i am not joining them correctly and number of records becomes low or high

          SELECT

          hcsu.ORG_ID

          , hp.party_name

          --,hp.PARTY_ID

          , hp.party_number

          , hca.account_number

          , hca.account_name

          --, hca.cust_account_id

          --, hps.party_site_id

          --, hps.location_id

          , hl.address1

          , hl.address2

          , hl.address3

          , hl.city

          , hl.state

          , hl.country

          , hl.postal_code

          , hcsu.site_use_code Purpose

          , hps.PARTY_SITE_NUMBER

          ,hca.CUSTOMER_CLASS_CODE

          ,hcsu.PRIMARY_SALESREP_ID

           

           

           

           

          FROM hz_parties hp

          , hz_party_sites hps

          , hz_locations hl

          , hz_cust_accounts_all hca

          , hz_cust_acct_sites_all hcsa

          , hz_cust_site_uses_all hcsu

           

           

          WHERE hp.party_id = hps.party_id

          AND hps.location_id = hl.location_id

          AND hp.party_id = hca.party_id

          AND hcsa.party_site_id = hps.party_site_id

          AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id

          --and hcsa.CUST_ACCT_SITE_ID = HCSU.SITE_USE_ID

           

           

          and hca.account_number = 8383

           

           

          ORDER BY to_number(hp.party_number),

          hp.party_name,

          hca.account_number ;

          • 2. Re: Query to get SalesRep Name of a Customer
            Royal Cascade

            Can someone help me merging these two queries (above one for customer and following for salesrep name, primary_salesrep_id is the connection) but following query bring so many rows of different Site_use_id from HCSU table. I need just unique sales rep name, with the above query.

             

            Can someone help me nest these queries.

             

            select jrd.resource_name as salesperson_name, hcsu.site_use_id

            from

            HZ_CUST_SITE_USES_ALL HCSU

            ,jtf_rs_salesreps jrs

            ,jtf_rs_defresources_v jrd

            where HCSU.PRIMARY_SALESREP_ID = 100015040

            and HCSU.PRIMARY_SALESREP_ID  =  jrs.salesrep_id

            and HCSU.ORG_ID = JRS.ORG_ID

            and  jrs.resource_id  =  jrd.resource_id ;

            • 3. Re: Query to get SalesRep Name of a Customer
              Royal Cascade

              I fixed the query. Here is the correct query in case it help someone else

              select b.*, a.resource_name

              from jtf_rs_resource_extns_vl a

              , (SELECT

              1. hcsu.ORG_ID "Org Number"

              , hp.party_name "Customer Name", hp.party_number "Customer ID", hca.account_name "Customer Account Name", hca.account_number "Customer Account Number",hca.CUSTOMER_CLASS_CODE "Customer Account Class Code", hl.state, hl.country,hcsu.site_use_code Purpose, hps.PARTY_SITE_NUMBER,hcsu.PRIMARY_SALESREP_ID, jrs.resource_idFROM hz_parties hp, hz_party_sites hps, hz_locations hl, hz_cust_accounts_all hca, hz_cust_acct_sites_all hcsa, hz_cust_site_uses_all hcsu, jtf_rs_salesreps jrs WHERE hp.party_id = hps.party_idAND hps.location_id = hl.location_idAND hp.party_id = hca.party_idAND hcsa.party_site_id = hps.party_site_idAND hcsu.cust_acct_site_id = hcsa.cust_acct_site_idand hca.CUST_ACCOUNT_ID =hcsa.CUST_ACCOUNT_IDand JRS.SALESREP_ID   = hcsu.PRIMARY_SALESREP_IDand hcsu.STATUS = 'A'

              ORDER BY to_number(hp.party_number),

              1. hp.party_name,
              2. hca.account_number

              , hps.PARTY_SITE_NUMBER) b

              where a.resource_id = b.resource_id