Reservations Completed For An Item Or List Of Items With Sales Order And Other Details.sql

SELECT
	iop.organization_code org
  , ir.reservation_id
  , dfa.header_id
  , dfa.source_order_number
  , ir.back_to_back_flag
  , ir.staged_flag
  , esi.item_number
  , ir.reservation_quantity
  , ir.creation_date
FROM
	fusion.inv_reservations      ir
  , fusion.inv_org_parameters    iop
  , fusion.egp_system_items_b    esi
  , fusion.doo_fulfill_lines_all dfa
  , fusion.doo_headers_all       dha
WHERE
	iop.organization_id              =ir.organization_id
	AND dfa.source_order_number      = '&Source_order_number'
	AND dha.submitted_flag           = 'Y'
	AND dfa.header_id                = dha.header_id
	AND esi.inventory_item_id        =ir.inventory_item_id
	AND esi. organization_id         =ir.organization_id
	AND ir.source_fulfillment_line_id=dfa.fulfill_line_id
	AND dfa.inventory_item_id        =ir.inventory_item_id
	AND ir.demand_source_type_id     ='2'
	/*demand_source_type_id is 2 for sales order as per the seeded data of application*/
ORDER BY
	dfa.source_order_number
;