Oracle Apps Inventory Queries

Oracle Apps Inventory Queries

--Reservation Qty for SKU

select SUM(mtr.reservation_quantity)
from
mtl_reservations mtr ,
oe_order_lines_all ool,
oe_order_headers_all ooh
where mtr.DEMAND_SOURCE_LINE_ID = ool.line_id
and ool.header_id = ooh.header_id
and ool.ship_from_org_id = mtr.organization_id
and mtr.inventory_item_id =1
and ooh.order_number = 316

--Reservation Qty for Order
select SUM(mtr.reservation_quantity)
from
mtl_reservations mtr ,
oe_order_lines_all ool,
oe_order_headers_all ooh
where mtr.DEMAND_SOURCE_LINE_ID = ool.line_id
and ool.header_id = ooh.header_id
and ool.ship_from_org_id = mtr.organization_id
and ooh.order_number = 316


--Order to HZ (Customer Location)
select ooh.ship_to_org_id,ooh.sold_to_org_id ,hp.party_name,hca.party_id,
hca.account_number,hcsu.site_use_code,hcsu.location,hcsu.primary_flag,
hcsu.bill_to_site_use_id,--hpsu.site_use_type,hps.party_site_number,hps.party_site_id,
hps.location_id,hpsu.primary_per_type
,hl.address1,hl.address2,hl.address3,hl.address4,hl.city,hl.state,hl.postal_code,hl.county
FROM
oe_order_headers_all ooh,
hz_cust_accounts_all hca,
hz_parties hp,hz_party_sites hps,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu,
hz_locations hl
where
ooh.sold_to_org_id = hca.cust_account_id
and hca.party_id = hp.party_id
and hca.party_id = hps.party_id
and hca.cust_account_id = hcas.cust_account_id
and hps.party_site_id = hcas.party_site_id
and hcas.cust_acct_site_id = hcsu.cust_acct_site_id
and hl.location_id = hps.location_id
and ooh.order_number = 351
order by hps.party_site_id