1. Run the following
queries in order from SQL*Plus
2. Supply this data to
Oracle Support Service when logging TARs against Serial Number issues.
Query
1.
SQL>Select
ORGANIZATION_ID "ORG_ID" ,
ORGANIZATION_CODE "ORG Code",
decode (SERIAL_NUMBER_TYPE , 1,'1 = uniq in
item',
2,'2
= uniq in org',
3,'3 = uniq across org',
null, 'NULL')
"Serial Number Type",
decode (SERIAL_NUMBER_GENERATION , 1 , ' 1
= At org lev',
2 , '2 = At Item lev',
null , 'Null') "Serial
Generation", nvl
(AUTO_SERIAL_ALPHA_PREFIX,
'NULL') "Alpha Prefix",
nvl (START_AUTO_SERIAL_NUMBER, 'NULL')
"Org Serial"
From
MTL_PARAMETERS
Order
by ORGANIZATION_ID;
Query
2. Supply the Item name when prompted.
SQL>Select
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SEGMENT1,
decode (SERIAL_NUMBER_CONTROL_CODE, 1,
'1 = No Serial Control',
2, '2 =
Predefined serial Number',
5,
'5 = Dynamic entry at INV RCPT',
6, '6 = Dynamic entry at Sale order issue
',
NULL, 'Null' ) "Serial Ctrl
CODE" ,
START_AUTO_SERIAL_NUMBER,
AUTO_SERIAL_ALPHA_PREFIX
From
MTL_SYSTEM_ITEMS_B
Where
SEGMENT1 = '&item_name'
Query
3. Supply the Item name and Serial Number when prompted.
SQL>Select
MSI.SEGMENT1,
MSI.INVENTORY_ITEM_ID,
MSN.CURRENT_ORGANIZATION_ID ,
MSN.CURRENT_SUBINVENTORY_CODE,
MSN.CURRENT_LOCATOR_ID ,
MSN.SERIAL_NUMBER,
MSN.PARENT_ITEM_ID ,
MSN.PARENT_SERIAL_NUMBER ,
decode
(MSN.CURRENT_STATUS, 1 , ' 1 = Defined
but not used',
3 , '3 = Resides in
stores',
4 , '4 = Issued out of stores',
5 , '5 = Resides in intransit',
6,
'6 = Pending ',
null,
'Null' ) "Current Status" ,
MSN.REVISION ,
NVL(msn.LOT_NUMBER,-9999) "Lot" ,
NVL(msn.GROUP_MARK_ID,-9999)
"GROUP_MARK_ID" ,
NVL(MSN.LINE_MARK_ID, -9999)
"LINE_MARK_ID",
NVL(MSN.LOT_LINE_MARK_ID, -9999)
"LOT_LINE_MARK_ID" ,
NVl(MSN.COST_GROUP_ID, -9999)
"COST_GROUP_ID"
From MTL_SYSTEM_ITEMS_B MSI , MTL_SERIAL_NUMBERS
MSN
Where
MSI.SEGMENT1 = '&item_name'
and MSN.SERIAL_NUMBER = '&serial_number'
and MSI.organization_ID =
MSN.current_organization_ID
and MSI.INVENTORY_ITEM_ID =
MSN.INVENTORY_ITEM_ID;
