To ger Order customer info

-- Customer name


SELECT hzp.party_name "Customer Name"
FROM hz_cust_accounts_all hca
,hz_parties hzp,
oe_order_headers_all oeh
WHERE oeh.sold_to_org_id = hca.cust_account_id
AND hca.party_id = hzp.party_id

-- Customer City

SELECT hl.city
FROM hz_cust_accounts_all hca
,hz_cust_acct_sites_all hcas
,hz_party_sites hps
,hz_parties hzp,
hz_locations hl,
oe_order_headers_all oeh
WHERE oeh.sold_to_org_id = hca.cust_account_id
AND hca.party_id = hzp.party_id
AND hca.cust_account_id= hcas.cust_account_id
and hps.party_site_id= hcas.PARTY_SITE_ID
and hl.location_id= hps.location_id
-- and oeh.ordeR_number=
and hps.identifying_address_flag='Y'

-- ship to Location

SELECT hzsu.LOCATION
FROM hz_cust_accounts_all hca,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hzsu,
oe_order_lines_all oel
WHERE oel.ship_to_org_id = hzsu.site_use_id
AND hca.cust_account_id= hcas.cust_account_id
AND hcas.CUST_ACCT_SITE_ID= hzsu.CUST_ACCT_SITE_ID
AND hzsu.SITE_USE_CODE='SHIP_TO'

--Bill To Location




-- To get order total

select OE_TOTALS_GRP.get_ordeR_total(oeh.HEADER_ID,null,'ALL') order_total
from oe_order_headers_all oeh



-- To find on hand quantity