Search This Blog

Friday, March 6, 2009

Query to get Customer Name, Number and Address

Below query can be handy to get customer related information.
The query will list Party Name, Number, Customer Number and there 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


P.S. The query is not completely tested. Please let me know if you find any problem

5 Comments:

vvvv said...

Dear Suresh,

How to create Back orders in Order Management? Can u plz help me on this. bcoz i dont have knowledge on this. if u have any documents(screenshots), plz send it to my mail id..
byvenkat@gmail.com

Thanks and Regards,
venkat

Suresh Vaishya said...

Emailed you the steps.

Anonymous said...

Dear Suresh,
I came across your query for BILL-TO and SHIP-TO.God Bless.Nice Job.Thank you so much.And will you plz let me know
How to create Back orders in Order Management? . if u have any documents(screenshots),can u plz send it to my mail id..
ibrahim.owaisi@gmail.com

Regards,
Mohammed Ibrahim.

mohtesham said...

hi there can you please help to join hz_parties with PO to get the buyer information???

Suresh Vaishya said...

Can you please provide more information on what exactly you need. Do you need buyer name or vendor_name or anything else?

Suresh

Copyright (c) All rights reserved. Presented by Suresh Vaishya