Friday, May 7, 2010

Query fetching the customer information based on order number ( R12 - Order to Cash Cycle )

<-- Customer details of an order -->


Below query gives you the info about the Customer and its associated Sites based on order number.

I have tested this query in R12.1.1. 

SELECT
     h.order_number
    ,h.sold_to_org_id bill_cust_account_id
    ,h.ship_to_org_id ship_to_site_use_id
    ,h.invoice_to_org_id bill_to_site_use_id
    ,hp.party_name "Customer Name"
    ,hca.account_name
    ,hca.org_id
    ,hcasab.orig_system_reference      BILL_TO_ORIG_REF
    ,hpb.status                        BILL_TO_STATUS
    ,'ADDRESS1 - '||bill_loc.address1||','||CHR(10)||
     'ADDRESS2 - '||bill_loc.address2||','||CHR(10)||
     'ADDRESS3 - '||bill_loc.address3||','||CHR(10)||
     'CITY     - '||bill_loc.city||','||CHR(10)||
     'POSTAL CD- '||bill_loc.postal_code||','||CHR(10)||
     'COUNTRY  - '|| bill_loc.country  BILL_TO_ADDRESS
    ,hcasas.orig_system_reference      SHIP_TO_ORIG_REF
    ,hps.status                        SHIP_TO_STATUS
    ,'ADDRESS1 - '||ship_loc.address1||','||CHR(10)||
     'ADDRESS2 - '||ship_loc.address2||','||CHR(10)||
     'ADDRESS3 - '||ship_loc.address3||','||CHR(10)||
     'CITY     - '||ship_loc.city||','||CHR(10)||
     'POSTAL CD- '||ship_loc.postal_code||','||CHR(10)||
     'COUNTRY  - '|| ship_loc.country  SHIP_TO_ADDRESS
FROM oe_order_headers_all h
    ,hz_parties hp
    ,hz_cust_accounts hca
    ,hz_cust_acct_sites_all hcasab
    ,hz_cust_acct_sites_all hcasas
    ,hz_cust_site_uses_all hzsuab
    ,hz_cust_site_uses_all hzsuas
    ,hz_party_sites hps
    ,hz_party_sites hpb
    ,hz_locations bill_loc
    ,hz_locations ship_loc
WHERE 1 =1
AND hp.party_id             = hca.party_id
AND hca.CUST_ACCOUNT_ID     = h.sold_to_org_id
AND hcasab.cust_account_id  = hca.cust_account_id
AND hcasas.cust_account_id  = hca.cust_account_id
AND hpb.location_id         = bill_loc.location_id
AND hps.location_id         = ship_loc.location_id
AND hcasab.party_site_id    = hpb.party_site_id
AND hcasas.party_site_id    = hps.party_site_id
AND hcasab.cust_acct_site_id= hzsuab.cust_acct_site_id
AND hcasas.cust_acct_site_id= hzsuas.cust_acct_site_id
AND h.ship_to_org_id        = hzsuas.site_use_id
AND h.invoice_to_org_id     = hzsuab.site_use_id
AND h.order_number          = '&order_number';

4 Responses to “Query fetching the customer information based on order number ( R12 - Order to Cash Cycle )”

JATR said...
July 23, 2010 at 2:10 PM

Awesome; worked with no editing. Thanks so much!


kŕáńtí said...
February 26, 2013 at 10:50 PM

how it works
{
,hz_party_sites hps
,hz_party_sites hpb
,hz_locations bill_loc
,hz_locations ship_loc
}
same tables with different alias names? what is the logic can you explain me brief


Unknown said...
May 28, 2020 at 9:26 AM

Very good and useful query. Serves as a great foundation on which to build many other queries. Thanks


Unknown said...
June 9, 2020 at 9:52 PM

anybody plz sort out this clearly,plz make me clear


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.