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 DECEMBER 18, 2023
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.
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)))
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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |