[OM] OE_ORDER_HEADERS_ALL -> SOLD_TO_ORG_ID and SHIP_TO_ORG_ID

At the Order Header table, SOLD_TO_ORG_ID refers to customer_id whereas SHIP_TO_ORG_ID is the ORGANIZATION_ID from OE_SHIP_TO_ORGS_V. From the SHIP_TO_ORG_ID field, we can trace back the actual ship-to address for a particular order.

Before we can use OE_SHIP_TO_ORGS_V view in sql, we have to first initialize the view. However, we can skip it by going direct to the tables.

--to get actual ship to address for a Sales Order
select hl.*
from
apps.hz_cust_site_uses_all cs,
apps.hz_cust_acct_sites_all cas,
apps.hz_party_sites ps,
apps.hz_locations hl
where
hl.location_id=ps.location_id and
ps.party_site_id=cas.party_site_id and
cas.cust_acct_site_id=cs.cust_acct_site_id and
hl.country='IN' and
hl.state='JAMMU AND KASHMIR' and
cs.site_use_code='SHIP_TO' and
cs.org_id=123
order by cs.site_use_id desc

--to get order base on ship to address
-- cs.site_use_id equals oe_order_headers_all.ship_to_org_id
select * from apps.oe_order_headers_all where ship_to_org_id in (
select cs.site_use_id
from
apps.hz_cust_site_uses_all cs,
apps.hz_cust_acct_sites_all cas,
apps.hz_party_sites ps,
apps.hz_locations hl
where
hl.location_id=ps.location_id and
ps.party_site_id=cas.party_site_id and
cas.cust_acct_site_id=cs.cust_acct_site_id and
hl.country='IN' and
hl.state='JAMMU AND KASHMIR' and
cs.site_use_code='SHIP_TO' and
cs.org_id=123 )
and order_type_id=2345