OrderJoins

select
part.party_name cust,
soh.order_number Onum,
substr(soh.CUST_PO_NUMBER,1,6) PONum,
substr(sol.ordered_item,1,10) item,
substr(sot.attribute1,1,10) type,
sol.ship_from_org_id org,
rsu.location loc
from
oe_order_headers_all soh,
oe_order_lines_all sol,
oe_transaction_types_all sot,
org_organization_definitions ood,
hz_cust_site_uses_all rsu,
hz_cust_acct_sites_all addr,
hz_cust_accounts cust,
wsh_new_deliveries dhead,
wsh_delivery_assignments dass,
wsh_delivery_details wdd,
tk_carton_headers cart,
hz_parties part,
hz_locations loc,
hz_party_sites parts
where soh.header_id = sol.header_id
and sot.transaction_type_id = soh.order_type_id
AND ood.organization_id = sol.ship_from_org_id
AND sol.ship_to_org_id = rsu.SITE_USE_ID
AND wdd.source_header_id = soh.header_id --SOURCE_LINE_ID ALSO EXISTS
-- AND dhead.delivery_id = wdd.delivery_detail_id
and dhead.delivery_id =cart.delivery_id
and dhead.delivery_id = dass.delivery_id
and dass.delivery_detail_id = wdd.delivery_detail_id
and rsu.cust_acct_site_id = addr.cust_acct_site_id --ok
and addr.cust_account_id = cust.cust_account_id --ok
and cust.party_id = part.party_id --ok
and loc.location_id = parts.location_id --ok
and addr.party_site_id = parts.party_site_id --ok
and soh.order_number = 2000376
and rownum < 50


More


select hzp.party_name,
cust.party_id,
cust.account_number customer_number,
csu.site_use_code,
hzl.address1,
hzl.address2
from hz_cust_site_uses_all csu,
hz_cust_acct_sites_all addr,
hz_cust_accounts cust,
hz_parties hzp,
hz_locations hzl,
hz_party_sites hzps
where csu.cust_acct_site_id = addr.cust_acct_site_id
and addr.cust_account_id = cust.cust_account_id
and cust.party_id = hzp.party_id
and hzl.location_id = hzps.location_id
and addr.party_site_id = hzps.party_site_id
and csu.site_use_id = 114261