Introduction: This query will fetch you the party and location details for a sales order. The input parameter passed is ‘Sales order number’which will get processed and provide you with the mentioned information.
How do we solve:
SELECT
hl.last_update_date,
hp.party_name,
hl.address1,
hl.address2,
hl.address3,
hl.city, –hl.state,
CASE
WHEN hl.country = ‘CA’ THEN hl.province
ELSE hl.state
END
state, –V2.1.0
hl.postal_code,
hl.postal_plus4_code,
hl.country,
hca.account_number,
hps.PARTY_SITE_NUMBER
FROM
apps.hz_cust_site_uses_all hcsu,
apps.hz_cust_acct_sites_all hcas,
apps.hz_party_sites hps,
apps.hz_locations hl,
apps.hz_parties hp,
apps.hz_cust_accounts hca,
apps.oe_order_headers_all h
WHERE
1 = 1
AND hca.party_id = hp.party_id
AND hca.cust_account_id = hcas.cust_account_id
AND hp.party_id = hps.party_id
AND hps.party_site_id = hcas.party_site_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hps.location_id = hl.location_id
–AND hcsu.site_use_code = ‘SHIP_TO’
AND h.order_number = p_sales_order_number
AND h.ship_to_org_id = hcsu.site_use_id;
Conclusion: This query will fetch you the party and location details for a sales order
