Partition F0911 issues

[sat3314]

Hello All,
I am a an oracle dba trying to solve an issue that has all of us including JDE experts confusing.
We went ahead and partitioned F0911 table based on GLDGJ column.
Partition in oracle should be transparent from the JDE side.
But when we run a report through JDE, we get an error saying
------
Unable to execute - SELECT GLKCO, GLDCT, GLDOC, GLDGJ, GLJELN, GLEXTL, GLICU, GLICUT, GLDICJ, GLCO, GLANI, GLAID, GLMCU, GLOBJ, GLSBL, GLSBLT, GLLT, GLFY, GLCRCD, GLAA, GLU, GLUM, GLEXR, GLALT1, GLBCRC, GLCRRM FROM PRODSUPDTA.F0911 WHERE ( ( GLLT = :KEY1 AND GLDCT IN ( :KEY2,:KEY3,:KEY4 ) ) AND ( GLKCO = :KEY5 AND GLDGJ >= :KEY6 AND GLDGJ <= :KEY7 AND GLSBL = :KEY8 AND GLICU = :KEY9 AND GLDCT = :KEY10 ) ) ORDER BY GLKCO ASC,GLMCU ASC,GLDCT ASC,GLANI ASC,GLSBL ASC,GLICU ASC
Feb 11 17:34:35.776002 - 1552/2292 MAIN_THREAD OCI0000179 - Error - ORA-12801: error signaled in parallel query server P006, instance SERV-DB31:ajc64dev1 (1)
ORA-01722: invalid number
Feb 11 17:34:35.776004 - 1552/2292 MAIN_THREAD JDB9900401 - Failed to execute db request
Feb 11 17:34:35.776006 - 1552/2292 MAIN_THREAD Exiting JDB_SelectKeyed with Failure
--UBE--[0]-- Unable to locate table in specified datasource for section: Integrity (F0911)
Feb 11 17:34:35.870000 - 1552/2292 MAIN_THREAD Entering JDB_CloseView (VIEW = V554111B)
----------


We can run this select query with hard coded values from UTB or sqlplus or other oracle clients but not from the JDE application.
We get the invalid number error.
I am presuming that JDE is going to convert date column into julian date. Somewhere there is a conversion issue.

Has anyone come across this issue.
If so what needs to be done to resolve it.

Paritition is going to give us good benefits.
No help from JDE support on this so far.

Thanks

 

[Larry_Jones]

And your configuration is ...?

 

[wainwrr]

DOn't overlook GLICU which is also a number field and therefore susceptible.

 

[sat3314]

sorry for the late reply....

We are on oracle 10g 10.2.0.3 RAC (5 node) environment.
4 app and 4 web servers.
windows 2003 64 bit edition is our OS.

We also opened up a call with JDE support. we posted them all the logfiles and the relevenent tracing information.
They came back saying in the logfile there is line
""ORACLE DBInitCon envr=05E612D0 conn=05F427C8 SERV-DB1 A (AFTAB64@ajcprdjde) V90 Unicode=Y (srvrusers=1) 1 """

they point to V90 and say that its using an oracle 9i driver. So upgrade to 10.2.0.3 client and then test it out.
I don't think that is the problem. The client version on all our app/web/db servers are 10.2.0.1
There is something else that is going on.
The invalid number in my opinion points to the date conversion.

Please point us in the right direction.

I have attached the log file for your reference.

Thanks,

 

[samarchon]

What JDE E1 tools release are you currently on?

Thanks
Sam

 

[Chan Rana]

Ask you JDE CNC to generate debug log when you run this report. This log will generate SQL statement with values it is using to execute the query. This can give you some more info on if there is any issue with values or field.

Thanks,
Chan

 

[owlearner]

Hi Sam,

I am the CNC Admin and i work with satish. we are currently on Enterprise One 8.11 SP1 with tools rel 8.96c1.

Thanks

Aftab