E9.2 IBM Db2 exception value out of range

[alfredorz]

Hi JDE Friends,

I'm reaching out regarding an issue encountered when Receipt Routing PO (P43250) where sometimes the lines do not close to status 999 as expected. We have IBM Db2 database.

In the debug log, we've identified that the issue lies with is for user decimal separator configuration. When the user has a comma (",") configured as their decimal separator, as SELECT SQL operation fails with the error "Numeric value out of range". However, when the decimal separator is set to a dot, works fine.

We are on version 9.2.6.3 tools, we've come across Bug 18686466 in Oracle Support, titled "Multiple Partial Receipts (P4312) and Incorrect Purchase Order Status (Doc ID 1677865.1)". However, after update tools, the problem persists.

As a temporary workaround, we've been execute a database update, I'm considering implementing an Orchestrator solution with LEX to address this issue more efficiently.

I would greatly aprpreciate any insights or suggestions on how to resolve this issue.

Thanks a lot!
Best regards.

 

[alfredorz]

This is the log:

SELECT  *  FROM DEVDTA920P/F43121  WHERE  ( PRMATC = '1' AND PRDOCO = 3568108.000000 AND PRDCTO = 'OP' AND PRKCOO = '00242' AND PRSFXO = '000' AND PRLNID >= 1000.000000 AND PRLNID <= 1000000.000000 )  ORDER BY PRMATC ASC,PRDOCO ASC,PRDCTO ASC,PRKCOO ASC,PRSFXO ASC,PRLNID ASC,PRNLIN ASC,PRDOC ASC
Entering DBPerformRequest
ODBC:I DBPerformRequest req=0000022C45A8EC20,con=0000022C48A1C040,env=0000022C2EE5A4F0,dbc=0000022C2EF38970,spid=0 SMDIDB30 M (JDE@Business Data - TEST)
ODBC[jdbodbc.c,3908] wSQLExecute failure. rc = -1
ODBC[jdbodbc.c,3908] STMT:00 [22003][30160] [IBM][System i Access ODBC Driver]Column 7: Numeric value out of range.
ODB0000163 - wSQLExecute failure. rc=-1
ODB0000164 - STMT:00 [22003][30160][2] [IBM][System i Access ODBC Driver]Column 7: Numeric value out of range.
Exiting DBPerformRequest
JDB9900401 - Failed to execute db request
Exiting JDB_SelectKeyed with Failure
JDB3300020 - Fetch not allowed. Prior successful Select operation required for this request.
Exiting Fetch with FailureJUN 21 01:20:48.581000 - 61124/62540 WRK:Starting jdeCallObject            **********************************************************************************
*** Start dumping data structure for business function GetUserPrefDecimalSeparator
**********************************************************************************
OUT->[  1] <Item>: cSuppressErrorMessage            <type>: CHAR             <Value>: [1]
OUT->[  2] <Item>: szDataItem                       <type>: STRING           <Value>: []
OUT->[  3] <Item>: cEverestEventPoint01             <type>: CHAR             <Value>: [,]
**********************************************************************************
*** End dumping data structure for business function GetUserPrefDecimalSeparator
**********************************************************************************
Entering jdeCloseDictionary
Exited jdeCloseDictionary with DDType 0
Return value is 0 for GetUserPrefDecimalSeparator. (BSFNLevel = 3)
Entering AddObjectCache
Entering jdeTrackBusinessFunctionUsage
Exiting jdeTrackBusinessFunctionUsage
Entering JDB_ClearSelection (hRequest 0000022c484f2320)
Exiting JDB_ClearSelection with Success
Entering JDB_SetSelection (hRequest 0000022c484f2320)
Exiting JDB_SetSelection With Success
Entering JDB_SelectKeyed (hRequest 0000022c484f2320)
ODBC:I DBInitRequest(new) req=0000022C45A8EC20,con=0000022C48A1C040,env=0000022C2EE5A4F0,dbc=0000022C2EF38970,spid=0 SMDIDB30 M (JDE@Business Data - TEST)
SELECT  *  FROM DEVDTA920P/F43121  WHERE  ( PRMATC = '1' AND PRDOCO = 3568108.000000 AND PRDCTO = 'OP' AND PRKCOO = '00242' AND PRSFXO = '000' AND PRLNID >= 1000.000000 AND PRLNID <= 1000000.000000 )  ORDER BY PRMATC ASC,PRDOCO ASC,PRDCTO ASC,PRKCOO ASC,PRSFXO ASC,PRLNID ASC,PRNLIN ASC,PRDOC ASC
Entering DBPerformRequest
ODBC:I DBPerformRequest req=0000022C45A8EC20,con=0000022C48A1C040,env=0000022C2EE5A4F0,dbc=0000022C2EF38970,spid=0 SMDIDB30 M (JDE@Business Data - TEST)
ODBC[jdbodbc.c,3908] wSQLExecute failure. rc = -1
ODBC[jdbodbc.c,3908] STMT:00 [22003][30160] [IBM][System i Access ODBC Driver]Column 7: Numeric value out of range.
ODB0000163 - wSQLExecute failure. rc=-1
ODB0000164 - STMT:00 [22003][30160][2] [IBM][System i Access ODBC Driver]Column 7: Numeric value out of range.
Exiting DBPerformRequest
JDB9900401 - Failed to execute db request
Exiting JDB_SelectKeyed with Failure
JDB3300020 - Fetch not allowed. Prior successful Select operation required for this request.
Exiting Fetch with Failure

 

[alfredorz]

If very strange, because if I execute the query direcly in database return results:
SELECT * FROM PDDTA920P.F43121 WHERE ( PRMATC = '1' AND PRDOCO = 3568108.000000 AND PRDCTO = 'OP' AND PRKCOO = '00242' AND PRSFXO = '000' AND PRLNID >= 1000.000000 AND PRLNID <= 1000000.000000 ) ORDER BY PRMATC ASC,PRDOCO ASC,PRDCTO ASC,PRKCOO ASC,PRSFXO ASC,PRLNID ASC,PRNLIN ASC,PRDOC ASC;

 

[eydeak]

We had a similar issue for a new foundation environment we set up last year. Our CNC team worked with Oracle support to track it down. I think it was related to the way the decimal separator was defined in an iSeries configuration file.

 

[alfredorz]

Solved! It's a Oracle bug, there are to apply ESU JN20775