FAQ Serial Numbers

 

1. What are the forms/programs?

Inventory > On-hand, Availability > Serial Numbers = INVITSNU.fmb
Inventory > On-hand, Availability > Generate serial numbers = Serial number generation (INV_SERIAL_NUMBER_PUB.GENERATE_SERIALS)

2. What tables?

1) MTL_SERIAL_NUMBERS: Serial numbers, this stores the serial numbers with a serial status noting if the serial is in stores, the current organization and subinventory, as well as mark ids that note if the serial number is locked..

2) MTL_SERIAL_NUMBERS_INTERFACE: Interface serial number table, this is the serial interface table used to note serial numbers that relate to transactions in the interface table (MTL_TRANSACTIONS_INTERFACE). See the Manufacturing Open Interface for more details.

3) MTL_SERIAL_NUMBERS_TEMP: Pending serial number table, this is the serial pending, temporary table used behind the scenes when transacting serial numbers. This goes hand-in-hand with the transaction temporary table (MTL_MATERIAL_TRANSACTIONS_TEMP).

4) MTL_UNIT_TRANSACTIONS: Unit transactions, this is the table used to track the history of serial numbers. Each unit transaction relates to a history record (MTL_MATERIAL_TRANSACTIONS) that outlines the transaction. The tables are joined by transaction_id. 

3. What is the difference between the various levels of serial uniqueness?

Here is an explanation of the uniqueness from the Inventory User's Guide page 5-162:

You use the Organization Parameters window to choose a type of serial number uniqueness for your organization. You can choose to enforce uniqueness Within inventory items, Within an organization, or Across organizations. The three levels for serial uniqueness are cumulative the definitions are as follows: 

• Within Inventory Items: 
Once you assign a serial number to a particular item you cannot assign the same serial number to the same item regardless of the organization. For example if you assign serial number SN100 to item A, you cannot assign serial number SN100 to any other instance of item A in any organization. This also includes CTO items derrived from base model A. However you could receive item B with serial number SN100 in any organization. 

• Within Organizations: 
In addition to the restrictions Within Inventory Items control, the same serial number cannot exist twice within the same organization. For example if you assign SN100 to item A, you will not be able to receive item B with the serial number SN100 in the same organization. However, you could receive item B with the serial number SN100 in any other organization. 

• Across Organizations: 
In addition to the restrictions Within Organizations, you cannot assign the same serial number to any other item regardless of the organization. For example if you assign SN100 to item A you will not be able to receive item B with the serial number SN100 in any organization. If you assign Across Organization uniqueness to any organization it restricts the serial generation in all other organizations. If one organization dictates Across Organizations, all other organizations must do so.

 

4.. What do the serial status numbers mean?

When one reviews the serial number table (MTL_SERIAL_NUMBERS) the column 'CURRENT_STATUS' indicates the status of a serial number. The column is populated with a number. For example, 3 means resides in stores. This means that the serial number has an associated onhand quantity and should be available to transact.

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;

 

5.What is a mark id?

The serial number table (MTL_SERIAL_NUMBERS) holds a list of existing serial numbers in the Oracle Applications. When a serial number is being used by a transaction, mark ids are often populated until the transactions are complete. The mark ids act as a lock on the serial number and are meant to only be temporary until the transaction processes. The mark id columns are GROUP_MARK_ID, LINE_MARK_ID, LOT_LINE_MARK_ID. When the mark ids are populated, other transactions cannot use the serials. The Transact Move Order form is an example form that locks serials when they are allocated using the mark ids.
In the example of a move order allocation and assuming no lot control, the serial (MTL_SERIAL_NUMBERS) mark ids (GROUP_MARK_ID, LINE_MARK_ID, LOT_LINE_MARK_ID) match the pending transaction (MTL_MATERIAL_TRANSACTIONS_TEMP) transaction temp id (TRANSACTION_TEMP_ID).


6.What is the difference between serial generation 'At Receipt' vs. 'At Sales Order Issue'?

At inventory receipt 
Create and assign serial numbers when you 
receive the item. Thereafter, for any material 
transaction, you must provide a serial number 
for each unit. 

At sales order issue 
Create and assign serial numbers when you 
issue (ship) the item against a sales order. If 
you select this option, serial numbers are required 
at ship confirm. If you receive an item 
on an RMA (return material authorization), 
you must specify the same serial numbers you 
created at sales order issue. All other material 
transactions for this item bypass serial number 
information. 

No control 
Serial number control not established for this 
item. All material transactions involving this 
item bypass serial number information. 

Predefined 
Assign predefined serial numbers when you 
receive the item. Thereafter, for any material 
transaction, you must provide a serial number 
for each unit.

The serial generation of an item is also called the serial control code. One can also look up the serial control codes for an item via SQL. The list of items in the system is maintained in the table MTL_SYSTEM_ITEMS_B. The column SERIAL_NUMBER_CONTROL_CODE will list the current value of the serial generation. 
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;

7.How to track a serial number across organizations?
After transferring a serial number between organizations, it only appears in the current organization. The Onhand > Serial number query screen displays the current state of the serial number. It queries the serial number table (MTL_SERIAL_NUMBERS). The serial number table will only have one record for the serial number and item combination. The organization of the serial number is maintained in a column called, CURRENT_ORGANIZATION_ID. This maintains only the last organization to have the serial number. If the serial number changes organizations, the serial number will show only in the new organization. If a serial number is shipped to a customer, the serial is visible with status 'issued out of stores' in the last organization that owned the serial. 

To see the history of the serial number, one would look at the transaction history using the Transactions > Material Transactions form. You can query by item or serial number and see all related transactions. The form queries any transaction that touched the serial number. Unlike the serial number form that queried the serial's current state from MTL_SERIAL_NUMBERS, the material transaction screen queries against all transactions (MTL_MATERIAL_TRANSACTIONS) and looks at the serial number history table (MTL_UNIT_TRANSACTIONS).

Example -- The following query could be useful looking for all transactions of a given serial number -- Note that two queries are joined because the transaction history relates through the lot table if the item is lot controlled:
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'
/

8.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
Read more about descriptive flexfields (DFF) also called attributes in the user's guide.

 

References:

http://snroracle.blogspot.com/2014/02/faq-serial-numbers.html