DevOps, CI/CD and Automation

Please use below SQL statement. It works well to retrieve Sales Order's Ship To Location.

-- SHIP TO NAME and SHIP TO DETAILS
select rsua.location ship_to_location,
raa.ADDRESS1 Ship_To_address1,
raa.ADDRESS2 Ship_to_Address2,
raa.address3 Ship_to_address3,
raa.country
from ra_site_uses_all rsua,
ra_addresses_all raa,
oe_order_headerS_all oha,
wsh_delivery_details wdd
where wdd.ship_to_site_use_id = rsua.SITE_USE_ID and
oha.order_number = '3022004731' and -- Sales Order Number
oha.header_id = wdd.source_header_id and
raa.address_id = rsua.address_id

It then work FINE now!

No need to use WSH_DELIVERY_DETAILS table to retrieve the Ship To Locations. Otherwise, some sales order cannot show the "Ship To Location" in the report, "<<Company Abbr.>> Order Notice Printout".

Hi fiona and all, it happened that I need to retrieve the information same as you did with a little modification.

I can retrieve the customer name from the number in ship to and bill to fields if it was referred to the same customer but I failed if it was different customer, e.g. the 1st customer who bought an item wants it to delivered to 2nd customer, so that ship to field will be filled with 2nd customer's number, (which already had relationship with the 1st customer) but the invoice must go to 1st customer, so that the bill to field will be filled with 1st customer's number.

I already tried your script but it still gave me only the number, not the customer's name (we can see it in the shipping menu but I still couldn't retrieve it).

I already read the script (OE_ORDER_HEADERS_ALL) and I found that ship_to_location (for ship to field) and invoice_to_location (for bill to field) referred to same column (LOCATION) of the same table (HZ_CUST_ACCT_SITE_USES_ALL if I remembered it right) but I still haven't found out how can they gave different number. I already got the party_field, site_id field, etc and tried to split the queries but still failed.

Can anyone help? Thanks.

Hi all, I managed to solve my problem. Here's the query in case someone need it. Thanks

SELECT party.party_name "Send to" (SELECT party.party_name
FROM hz_cust_site_uses_all bill_su,
hz_cust_acct_sites_all bill_cas,
hz_party_sites bill_ps,
hz_locations bill_loc,
hz_parties party,
oe_order_headers_all h
WHERE h.invoice_to_org_id = bill_su.site_use_id
AND bill_su.cust_acct_site_id = bill_cas.cust_acct_site_id
AND bill_cas.party_site_id = bill_ps.party_site_id
AND bill_loc.location_id = bill_ps.location_id
AND bill_cas.party_site_id = bill_ps.party_site_id
AND bill_ps.party_id = party.party_id
AND h.order_number = 'your_order_number') "Invoice to"
FROM oe_order_headers_all h,
hz_cust_site_uses_all ship_su,
hz_cust_acct_sites_all ship_cas,
hz_party_sites ship_ps,
hz_locations ship_loc,
hz_parties party
WHERE h.ship_to_org_id = ship_su.site_use_id
AND ship_cas.party_site_id = ship_ps.party_site_id
AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id
AND ship_cas.party_site_id = ship_ps.party_site_id
AND ship_ps.party_id = party.party_id
AND ship_loc.location_id = ship_ps.location_id
AND h.order_number = 'your_order_number';