/*Query to find SHIP_TO_LOCATION_ID from SO's SHIP_TO_LOCATION*/
SELECT location_id SHIP_TO_LOCATION_ID /*wsh_delivery_details.ship_to_location_id*/
FROM hz_locations
WHERE location_id IN
(
SELECT location_id
FROM hz_party_sites hps ,
hz_cust_acct_sites_all hcas
WHERE hcas.party_site_id = hps.party_site_id
AND hcas.cust_acct_site_id IN
(
SELECT cust_acct_site_id
FROM hz_cust_site_uses_all
WHERE location = 140912 /*:ORDER.SHIP_TO_LOCATION*/
AND site_use_code = 'SHIP_TO' ) );
(OR)
SELECT location_id ship_to_location_id
FROM hz_locations
WHERE location_id IN
(
SELECT location_id
FROM hz_party_sites hps ,
hz_cust_acct_sites_all hcas
WHERE hcas.party_site_id = hps.party_site_id
AND hcas.cust_acct_site_id IN
(
SELECT cust_acct_site_id
FROM hz_cust_site_uses_all
WHERE site_use_id = 140912 /*:ORDER.SHIP_TO_ORG_ID*/
AND site_use_code = 'SHIP_TO' ) );
