SQL Error ‘[Microsoft][SQL Server Native Client 11.0]Numeric value out of range (SQLSTATE 22003)’ on Item/Product Availability Page (Doc ID 2284765.1)

Last updated on JULY 17, 2017

Applies to:

PeopleSoft Enterprise SCM Inventory - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

Users are getting a fatal SQL error

'[Microsoft][SQL Server Native Client 11.0]Numeric value out of range (SQLSTATE 22003)'

when attempting to search for a certain item on the Item/Product Availability Page.

The issue appears to be the field ATP_D_WO_VW.SCHED_NBR has only 3 digits long maximum, but it is pulling that field from IN_DEMAND. SCHED_LINE_NBR that has 6 digits long maximum.

As a result, when SCHED_NBR > 999, the following error shows up in the logs:

ERROR
-----------------------

 Return: 8601 - [Microsoft][SQL Server Native Client 11.0]Numeric value out of range (SQLSTATE 22003) 0

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1) Navigate to Inventory > Manage Inventory > Review Inventory Balance Info >
Item/Product Availability.

2) Enter the SetID, Item ID and Business Unit of any item on ATP_D_WO_VW
having a SCHED_NBR > 999.
SetID = SHARE
Product ID = i.e. V10026
Patch Kit details:
Item ID = i.e. V10026
Business Unit = INVMI (Cycling Accessories DC West)
In rest all option are left default

3) As result no error appears
Then remove the flags and save both

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms