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

Pages

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.city
     , hl.state
     , hl.country
     , 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 :

2 comments:

  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 https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx 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

    SELECT ACCOUNTNUM, [0] AS DEBIT_TRANS, [1] AS CREDIT_TRANS
    FROM
    (
    SELECT ACCOUNTNUM, CREDITING, AMOUNTMST
    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
    PIVOT
    (
    SUM(AMOUNTMST)
    FOR ACCOUNTNUM IN
    ( [0], [1] )
    ) AS pvt
    ORDER BY pvt.ACCOUNTNUM;

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


    Thanks,

    ReplyDelete
  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,
    Anil

    ReplyDelete