Search This Blog

Monday, April 28, 2014

Query to List Active Serial Number for an Items

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

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

Copyright (c) All rights reserved. Presented by Suresh Vaishya