Follow us: Connect on YouTube Connect on YouTube Connect on YouTube


Sunday, 5 February 2017

Query to get Customer Name and Address

Below query can be handy to get customer related information.
The query will list Party Name, Number, Customer Number and their Bill To and Ship Addresses.

                     SELECT hp.party_name
     , hp.party_number
     , hca.account_number
     , hca.cust_account_id
     , hp.party_id
     , hps.party_site_id
     , hps.location_id
     , hl.address1
     , hl.address2
     , hl.address3
     , hl.state
     , hl.postal_code
     , hcsu.site_use_code
     , hcsu.site_use_id
     , hcsa.bill_to_flag
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    hca.cust_account_id = hcsa.cust_account_id
AND    hca.account_number = :customer_number
Share this article :


  1. Hi There,

    10/10 !!! Thank you for making your blogs an embodiment of perfection and simplicity. You make everything so easy to follow.

    This is the first time that I have tried a PIVOT... I followed the syntax from exactly as shown in the examples... The problem is that the field ACCOUNTNUM shows as an ERROR... I have tried using an ALIAS for the field but still it returns as an ERROR...
    What am I missing

    from [dbo].[LEDGERTRANS]
    where ACCOUNTNUM between '400000' and '999999'
    and transdate between cast('2015-02-22' as date)
    and cast('2015-03-28' as date)
    and POSTING != '19'
    ) L
    ( [0], [1] )
    ) AS pvt

    Great effort, I wish I saw it earlier. Would have saved my day :)


  2. Hello There,

    In total awe…. So much respect and gratitude to you folks for pulling off such amazing blogs without missing any points on the Query to get Customer Name and Address Kudos!

    I am facing an issue with DBMS_CHAIN execution.
    Issue :Even one of the step got Failed, remaining steps were processed without waiting for depended step SUCCESS.
    Just FYI: I created 2 chains i.e. Chain1 and Chain2 . Calling Chain2 as sub chain in Chain1.
    I attached the total script for your reference and below the run details.
    In the below run details , you can see STEP2 is in failed state but further steps(i.e. STEP3 of CHAIN1 and STEP1 , STEP2 of CHAIN2 got processed)
    Please suggest me on this(what i need to do to hold at FAILED step and how can i RERUN that FAILED step only (or) from Failed Steps).
    I read multiple articles and watched many videos about how to use this tool - and was still confused! Your instructions were easy to understand and made the process simple.

    Many Thanks,

  3. Positive site, where did u come up with the information on this posting?I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work. Walmart Customer Service

  4. Thanks for a very interesting blog. What else may I get that kind of info written in such a perfect approach? I’ve a undertaking that I am simply now operating on, and I have been at the look out for such info. Vistara customer care

  5. Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check for Never Before Offers and Discount Coupon Codes.

  6. It is therefore crucial for all businesses to choose a suitable CRM solution for their individual business needs. customer engagement and loyalty

  7. With the use of digital payment options, you can make sure that your payments are made on time. It also saves time and effort, making it easier for you to manage your finances. Some other reasons why people prefer paying bills online include safety while others argue that it is more convenient to use digital transactions.

    SSGC Online Bill For payment of the SSGC gas bill, there are a few options you have.