Oracle Serial Number

Here is a list of common serial number statuses and ids:

Status Codes (ID and Description)
---------------------
1 Defined but not used
3 Resides in stores
4 Issued out of stores
5 Resides in intransit
6 Pending status 
7 Resides in receiving
8 Resides in WIP 

You can get a full list of the codes with the following SQL:
SELECT
   lookup_type,
   lookup_code,
   meaning
FROM mfg_lookups
WHERE lookup_type = 'SERIAL_NUM_STATUS'
ORDER BY lookup_type, lookup_code;


Here is a list of common serial control code ids and there descriptions:

Control Codes (ID and Description)
---------------------
1 No serial number control
2 Predefined serial numbers
5 Dynamic entry at inventory receipt
6 Dynamic entry at sales order issue 
You can get a full list of the codes with the following SQL
SELECT
   lookup_type,
   lookup_code,
   meaning
FROM mfg_lookups
WHERE lookup_type = 'MTL_SERIAL_NUMBER'
ORDER BY lookup_type, lookup_code;


Here is a version of the query from R12.1 - The actual query is against the view RCV_TRX_INT_SERIALS_V but the SQL below shows the underlying tables: (Receiving Transaction Form)

SELECT rss.serial_num
FROM rcv_serials_supply rss, rcv_shipment_lines rsl
WHERE rss.shipment_line_id = rsl.shipment_line_id
AND upper(serial_num) LIKE '&yourserial')
AND rss.shipment_line_id = &yourshiplineid
AND (rss.lot_num IS NULL OR rss.lot_num = '&YourLot')
AND EXISTS
(SELECT 1
FROM mtl_serial_numbers msn
WHERE msn.serial_number = rss.serial_num
AND msn.current_status = 5
AND (msn.group_mark_id IS NULL
OR msn.group_mark_id = -1)
AND (msn.line_mark_id IS NULL
OR msn.line_mark_id = -1)
AND (msn.lot_line_mark_id IS NULL
OR msn.lot_line_mark_id = -1)
AND msn.inventory_item_id = rsl.item_id
)
ORDER BY rss.serial_num;

INV: Restrict Receipt of Serials?

Here is a query that might help you look for serials that were received again after being shipped. You might add additional logic to check for the existence of a customer install base:
a. This one gives the list of serials shipped then received back:
select mut.serial_number, mut.organization_id
from mtl_unit_transactions mut, mtl_material_transactions mmt
where ((mut.transaction_source_type_id = 11 -- inventory transaction
and mmt.transaction_type_id = 42) -- misc. receipt
or (mut.transaction_source_type_id = 3 -- account transaction
and mmt.transaction_type_id = 40) -- account receipt
or (mut.transaction_source_type_id = 6 -- account alias transaction
and mmt.transaction_type_id = 41)) -- account alias receipt
and mut.transaction_id = mmt.transaction_id
and exists
(select 1 from mtl_unit_transactions mut2
where mut2.transaction_id != mut.transaction_id
and mut2.transaction_date < mut.transaction_date
and mut2.transaction_source_type_id IN (2,8) -- sales/internal order
and mut.serial_number = mut2.serial_number
);

b. This gives a summary count by status and organization of serials shipped and received back:
select msn.current_status, msn.current_organization_id, count(*)
from mtl_serial_numbers msn
where msn.serial_number IN
(select mut.serial_number, mut.organization_id
from mtl_unit_transactions mut, mtl_material_transactions mmt
where ((mut.transaction_source_type_id = 11 -- inventory transaction
and mmt.transaction_type_id = 42) -- misc. receipt
or (mut.transaction_source_type_id = 3 -- account transaction
and mmt.transaction_type_id = 40) -- account receipt
or (mut.transaction_source_type_id = 6 -- account alias transaction
and mmt.transaction_type_id = 41)) -- account alias receipt
and mut.transaction_id = mmt.transaction_id
and exists
(select 1 from mtl_unit_transactions mut2
where mut2.transaction_id != mut.transaction_id
and mut2.transaction_date < mut.transaction_date
and mut2.transaction_source_type_id IN (2,8) -- sales/internal order
and mut.serial_number = mut2.serial_number
))
group by msn.current_status, msn.current_organization_id;


How to track a serial number across organizations
prompt
accept YourSerial DEFAULT 'JBP10000' prompt 'Please enter your serial (Default JBP10000): '
prompt
break on serial_number

select c.serial_number, a.transaction_id, a.transaction_type_id, a.transaction_quantity, 'No Lot'
from MTL_MATERIAL_TRANSACTIONS a, MTL_UNIT_TRANSACTIONS c
where a.TRANSACTION_ID = c.TRANSACTION_ID
  and c.serial_number like '&YourSerial'
UNION
select c.serial_number, a.transaction_id, a.transaction_type_id, a.transaction_quantity, b.lot_number
from MTL_MATERIAL_TRANSACTIONS a, MTL_TRANSACTION_LOT_NUMBERS b, MTL_UNIT_TRANSACTIONS c
where a.TRANSACTION_ID = b.TRANSACTION_ID
  and b.SERIAL_TRANSACTION_ID   = c.TRANSACTION_ID
  and c.serial_number like '&YourSerial'
/


How to enter serial number descriptions (or Descriptive Flexfields / DFF)?

You can enter descriptions for serial numbers using descriptive flexfields.
There are two that would be helpful: Serial Attributes or Serial numbers
Serial attributes apper in the onhand > serial number screen in the Serial Info tab with the column name of Attributes. This is similar to the serial entry screens that label the field attributes as well.  The Serial numbers descriptive flexfield is visible upon placing the cursor in the descriptive flexfield field usually labled with two brackets like [] also called mug-handles.

To maintain descriptive flexfields for serial numbers:
Navigate to Inventory > Setup > Flexfield > Descriptive > Segments
Query Serial Attributes or Serial numbers
You could then setup descriptions for the serials

Performance Issues with Serial Numbers
For example, one customer found this query to be especially slow working with serials in the Transact Move Order form.  They found the query by running a trace on the process using Help > Diagnostics > Trace.  Selecting one serial was taking over a minute as there were 3 million available serial numbers in MTL_SERIAL_NUMBERS. Here is the SQL that had bad performance:
SELECT MSN.SERIAL_NUMBER
FROM
MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP MSNT WHERE
  MSNT.TRANSACTION_TEMP_ID = DECODE(:B5 , 1, :B4 , :B3 ) AND
  MSN.CURRENT_ORGANIZATION_ID = :B2 AND MSN.INVENTORY_ITEM_ID = :B1 AND
  MSN.SERIAL_NUMBER BETWEEN MSNT.FM_SERIAL_NUMBER AND
  NVL(MSNT.TO_SERIAL_NUMBER, MSNT.FM_SERIAL_NUMBER) AND
  LENGTH(MSN.SERIAL_NUMBER) = LENGTH(MSNT.FM_SERIAL_NUMBER)

References:
http://sairamgoudmalla.blogspot.com/2015/03/oracle-serial-number.html