Query to List Active Serial Number for an Items

Below query can be used to display active serial number for Item, Organization combination

/* Formatted on 7/30/2014 6:55:59 PM (QP5 v5.115.810.9015) */
SELECT msi.segment1 item_number,
       msn.serial_number,
       msn.current_status_name,
       msn.status_code,
       msn.current_subinventory_code,
       ml.concatenated_segments
FROM mtl_serial_numbers_all_v msn,
     mtl_system_items_b msi,
     mtl_item_locations_kfv ml,
     mtl_parameters mp
WHERE     msi.inventory_item_id = msn.inventory_item_id
      AND mp.organization_code = msn.organization_code
      AND ml.inventory_location_id = msn.current_locator_id
      AND msn.current_status = 3
      AND mp.organization_id = msi.organization_id
      AND mp.organization_code = :org_code
      AND msi.segment1 = :item