Search This Blog

Loading...

Tuesday, May 20, 2008

Query to get Customer Related information for a Sales Order

Here is another handy query to get Customer related information for a sales order.
The query will list SHIP TO and BILL TO Address for a customer.

SELECT ooh.order_number
, hp_bill.party_name
, hl_ship.address1 ||Decode(hl_ship.address2,NULL,'',chr(10))
||hl_ship.address2||Decode(hl_ship.address3,NULL,'',chr(10))
||hl_ship.address3||Decode(hl_ship.address4,NULL,'',chr(10))
||hl_ship.address4||Decode(hl_ship.city,NULL,'',chr(10))
||hl_ship.city ||Decode(hl_ship.state,NULL,'',',')
||hl_ship.state ||Decode(hl_ship.postal_code,'',',')
||hl_ship.postal_code ship_to_address
, hl_bill.address1 ||Decode(hl_bill.address2,NULL,'',chr(10))
||hl_bill.address2||Decode(hl_bill.address3,NULL,'',chr(10))
||hl_bill.address3||Decode(hl_bill.address4,NULL,'',chr(10))
||hl_bill.address4||Decode(hl_bill.city,NULL,'',chr(10))
||hl_bill.city ||Decode(hl_bill.state,NULL,'',',')
||hl_bill.state ||Decode(hl_bill.postal_code,'',',')
||hl_bill.postal_code bill_to_address
, ooh.transactional_curr_code currency_code
, mp.organization_code
, ooh.fob_point_code
, ooh.freight_terms_code
, ooh.cust_po_number
FROM oe_order_headers_all ooh
, hz_cust_site_uses_all hcs_ship
, hz_cust_acct_sites_all hca_ship
, hz_party_sites hps_ship
, hz_parties hp_ship
, hz_locations hl_ship
, hz_cust_site_uses_all hcs_bill
, hz_cust_acct_sites_all hca_bill
, hz_party_sites hps_bill
, hz_parties hp_bill
, hz_locations hl_bill
, mtl_parameters mp
WHERE 1 = 1
AND header_id = :p_header_id
AND ooh.ship_to_org_id = hcs_ship.site_use_id
AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
AND hca_ship.party_site_id = hps_ship.party_site_id
AND hps_ship.party_id = hp_ship.party_id
AND hps_ship.location_id = hl_ship.location_id
AND ooh.invoice_to_org_id = hcs_bill.site_use_id
AND hcs_bill.cust_acct_site_id = hca_bill.cust_acct_site_id
AND hca_bill.party_site_id = hps_bill.party_site_id
AND hps_bill.party_id = hp_bill.party_id
AND hps_bill.location_id = hl_bill.location_id
AND mp.organization_id(+) = ooh.ship_from_org_id


Please do let me know if this queries are helping and I will post more of such queries.

Note: The query is not tested completely. If any problem found with the above query please let me know and I will try to fix them.

8 Comments:

Lakki Reddy Sreehari Reddy said...

This query is very helpfull. why because,to relate AR tables with some other module tables, its little bit difficult.
Thank U Suresh.

if u have PLSQL program to create non stanrd jobs (standard also) ,
Please send me that program.
Its very helpfull.

MYID : sreeharireddy.l@gmail.com

Suresh Vaishya said...

Sreehari,
Thanks for your feedback.
Are you looking for creating conc. program using APIs or calling request?

Suresh

Lakki Reddy Sreehari Reddy said...

Hi suresh,
Thanks for your replay.
I want to upload components,operation,resources for a assembly into wip_job_schedule_interface and wip_job_dtls_interface.

If u have any packahe for this requirement,Please forward.
My ID : sreeharireddy.l@gmail.com

Lakki Reddy Sreehari Reddy said...

Hi suresh,
Sorry,i forgeten to tell onething.
here i am passing ROUTING name as parameter.

Suresh Vaishya said...

sorry buddy .. I have no code for this.

Rama Krishna Mateti said...

Hi Suresh,

How are you doing?
Can you please give me the query which will list all the AR Invoices for the Customers assoicated with the Customer Information like Ship-To, Bill-To....Address....bla..bla....
Thanks in advance.

Suresh Vaishya said...

Rama,
you can slightly modify this query to meet your requirement. The oe_order_headers_all needs to be replaced with ra_customer_trx_all.
The transaction table has bill_to_customer_id and ship_to_customer_id using which you can then bill to and ship to address.
Let me know if you still have issue.

sap support packs said...

This post explain query to get customer related information for sales order. The query is easy to understand and write. Just get the logic and you will understand what's working. Read it carefully as its very useful.

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