Serial number is not available when doing any inventory transaction but shows in on hand quantity interface

source

I was facing problem in one of my client site that oracle shows on hand quantity for one item in on hand quantity screen with serial numbers but when user is going to transact it either by sub inventory transfer or by Miscellaneous issue it shows me on hand� zero on transactional form

I have investigated it in detail and find different notes on Metalink for the solution and fusion of these notes is solution

First of all get Inventory Item id , serial number and organization id from on hand quantity interface by using examine option.

Step 1

Run the following query to check exact status of that serial numbers

SELECT SUBSTR (inventory_item_id, 1, 9) “Item Id”,
 SUBSTR (serial_number, 1, 20) “Serial Number”,
SUBSTR (current_organization_id, 1, 9) “Organization Id”,
SUBSTR (lot_number, 1, 18) “Lot Number”,
SUBSTR (DECODE (current_status,
1, ‘Defined but not used’,
3, ‘Resides in Stores’,
4, ‘Out of Stores’,
5, ‘Intransit’,
6, ‘Invalid’,
NULL, ‘Verify Serial Number’,
current_status
 ),
1,
25
) “Status”,
SUBSTR (current_subinventory_code, 1, 25) “Subinv”,
 SUBSTR (current_locator_id, 1, 9) “Locator”,
 SUBSTR (group_mark_id, 1, 15) “Group Mark Id”,
SUBSTR (line_mark_id, 1, 15) “Line Mark Id”,
 SUBSTR (lot_line_mark_id, 1, 15) “Lot Line Mark Id”, group_mark_id,
 line_mark_id, lot_line_mark_id
FROM mtl_serial_numbers
WHERE inventory_item_id = ‘&EnterItemID’
AND serial_number = ‘&EnterSerialNumber’
AND current_organization_id = ‘&EnterOrgID’

Above query shows me the status �Resides in Stores� but this serial number is not displayed in transactional forms

Step 2

Then run the following query

SELECT i.inventory_item_id itemname, s.inventory_item_id itemid,

s.serial_number serialno, s.current_organization_id org,
s.current_subinventory_code sub, s.current_status stat,
 s.group_mark_id gmid, s.line_mark_id lmid, s.lot_line_mark_id
 FROM mtl_system_items i, mtl_serial_numbers s
WHERE inventory_item_id = ‘&EnterItemID’
AND serial_number = ‘&EnterSerialNumber’
AND current_organization_id = ‘&EnterOrgID’
AND s.current_organization_id = i.organization_id
AND s.inventory_item_id = i.inventory_item_id;

Check the value of following columns

1.�group_mark_id
This column is Group identifier (used by Oracle Inventory for ensuring that serial numbers used by one user’s session do not become available incorrectly to another user)

2.�line_mark_id
This column Used by Oracle Inventory for ensuring that serial numbers used by one user’s session do not become available incorrectly to another user. The column is used by Oracle Inventory’s serial number marking and unmarking routines

3.�lot_line_mark_id

Identifier used in serial number marking and unmarking routines

Step 3

If value of above mentioned column is not blank / null then run following query

UPDATE mtl_serial_numbers
SET group_mark_id = NULL,
line_mark_id = NULL,
 lot_line_mark_id = NULL
WHERE inventory_item_id = ‘&EnterItemID’
AND serial_number = ‘&EnterSerialNumber’��
AND current_organization_id = ‘&EnterOrgID’
AND current_status = 3

A serial number can have one of following statuses
1.�Defined but not used
2.�Resides in stores
3.�Issued out of stores
4.�Resides in transit

I used status = 3 in above query as I was looking for only those serial numbers which Resides in stores but not available to me for transaction

Step 4

Commit the record and then check are you able for transaction or not