Errors "ORA-01455: converting column overflows integer datatype." or "Arithmetic overflow error converting expression to data type int." when Running the 'Optimization Table Load' Process (PL_OPT_LOAD) (Doc ID 1512214.1)

Last updated on AUGUST 04, 2016

Applies to:

PeopleSoft Enterprise SCM Supply Planning - Version 8.9 to 9 [Release 8.9 to 9]
Information in this document applies to any platform.

Symptoms

The 'Optimization Table Load' process (PL_OPT_LOAD) (navigation: Supply Planning, Create Plan, Load Planning Instance) fails with one of the following errors:

Failed SQL stmt:SELECT BUSINESS_UNIT,  INV_ITEM_ID,  TO_CHAR(START_DATE,'YYYY-MM-DD') START_DATE,  TO_CHAR(END_DATE,'YYYY-MM-DD') END_DATE,  SAFETY_LIMIT,  EXCESS_LIMIT ,
VERSION  FROM PS_PL_STK_PERIODS WHERE PROBINST = 'xxx'

Error Position: 0  Return: 1455 - ORA-01455: converting column overflows integer datatype.

 

SQL error. Stmt #: 1603 Error Position: 0 Return: 8601 - [Microsoft][SQL Native Client][SQL Server]Arithmetic overflow error converting expression to
data type int.

INSERT INTO PS_PL_STK_PERIODS (PROBINST , BUSINESS_UNIT , INV_ITEM_ID , START_DATE , END_DATE , SAFETY_LIMIT , EXCESS_LIMIT , VERSION) SELECT
B.PROBINST , B.BUSINESS_UNIT , B.INV_ITEM_ID , '2007-10-12' , '2008-11-10' , C.SAFETY_LEVEL , C.EXCESS_LEVEL , 0 FROM PS_PL_BU_ITEMS B , PS_PL_ITEM_ATTRIB
C WHERE B.PROBINST = 'PLANTS1' AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.INV_ITEM_ID = C.INV_ITEM_ID AND C.SAFETY_LEVEL <> 0 AND NOT EXISTS ( SELECT
'X' FROM PS_PL_STK_PERIODS WHERE PROBINST = 'PLANTS1' AND BUSINESS_UNIT = B.BUSINESS_UNIT AND INV_ITEM_ID = B.INV_ITEM_ID) AND ('1' = '1' OR (NOT
EXISTS ( SELECT 'X' FROM PS_DP_PUB_IPMAST WHERE DP_PUBLISHNAME = ' ' AND DP_PUBLISH_DATE IS NULL AND DP_LOCATION = B.BUSINESS_UNIT AND DP_ITEMCODE =
B.INV_ITEM_ID)))

 

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