Serial Number Related Query

This query will let us know the corresponding Sales Order number associated with a given serial number installed in Installed Base.

-- get sales order associated with serial number
SELECT msn.serial_number,
  ool.line_id,
          ooh.order_number,
         ool.inventory_item_id
FROM     mtl_serial_numbers      msn,
          oe_order_lines_all      ool,
          oe_order_headers_all    ooh,
          mtl_system_items        msi,
          wip_discrete_jobs       wdj,
          wip_entities            we
WHERE    msn.serial_number = '&Serial_Number'
AND       ool.header_id = ooh.header_id
AND       wdj.source_line_id = ool.line_id
AND       we.wip_entity_id = wdj.wip_entity_id
AND       wdj.wip_entity_id = msn.original_wip_entity_id
AND       msi.inventory_item_id = msn.inventory_item_id
AND       msi.organization_id = ool.ship_from_org_id
AND       msn.current_status = 4
UNION
SELECT   wdd.serial_number,
          ool.line_id,
          ooh.order_number,
          ool.inventory_item_id
FROM     wsh_delivery_details    wdd,
          oe_order_lines_all      ool,
          oe_order_headers_all    ooh
WHERE    wdd.serial_number = '&Serial_Number'
AND       ool.header_id = ooh.header_id
AND       wdd.source_line_id = ool.line_id
AND       wdd.serial_number IS NOT NULL