Commit fails in P4205

Hi all (Oracle) database experts out there

One of my clients went live this weekend on an XE upgrade from B7321(alpha!). All's going quite well, except we are getting a multitude of records (about 200 per day) in the F41021wf as a result of failed commitments during ship confirmation (ie. we think it is a result of ship confirm as the PID in the F41021wf table in nearly all cases points to EP4205 (and is blank in all other cases)) but the user ID in the table is not necessarily the ID of the person doing the ship confirmation)...

All clients are connected through Citrix; our data base is Oracle 8.1 and all system are running OW XE Sp17.1.

Has anybody encountered this?
Any ideas as to what is causing this? Is it related to a particular process (even though we cannot find a common thread)? Or is this a database issue (cursor related?)
And how can we prevent this from happening?

Any suggestions are greatly appreciated.

Rgds,






Sef van den Nieuwelaar
Australia
B732 on NT, XE on NT, B732/A73 on AS400, B733 on NT

* Coming to a European City near you soon *

Dear Sef,

We have faced almost similar situation when we first went live. I think =
JDE
does have an explanation for the F41021 commit failure issue... Please =
refer
to JDE and also the following attachment for relevant issues.

<<OWrollback.doc>>=20

Most truly,
Anandan Sathamoorthy (Ext: 613/651)
Systems Analyst, Unico Technology Berhad

multitude
ship
to
table is
and
http://www.jdelist.com/cgi-bin/wwwthreads/showflat.pl?Cat=3D&Board=3DOW&=
Number



Anandan

Hi Sef,

Why do you suspect Oracle? My first suspicion would be a change in how OW processes things (a bug). You didn't mention users reporting errors in the Ship Confirm process which makes me think that its not the interactive application at fault. Even though PID says its P4205 as you know JDE programmers sometimes get lazy and don't change the Program ID in copied code. So here's a couple of questions:

Question 1: Is the client using R42500/R47500 to process InBound Ship Confirm transactions?

Question 2: Do they have any custom UBEs involved in the Ship Confirm process?

Regards,

Larry Jones
[email protected]
OneWorld XE, SP 15.1
HPUX 11, Oracle SE 8.1.6
Mfg, Distribution, Financials

Hi Larry

Thanks for your reply and comments.
It is not nessecarily an Oracle database problem. However during the first few days of go-live we had a few other issues that turned out to be database related.

You are right about sloppy programming. We are seeing on occasion UPMJ and TDAY in F41021WF being populated with yesterday's date and with a time stamp of several hours ago. Again we are not sure why this is happening.

We are currently not using transporation nor any interoperability. However the P4205 program (and N4200790) are modified in a number of places and a custom made application is called as part of the ship confirm process.

However the issue still occurs intermittently (my favourite type of problems, the ones you can't reliably duplicate... NOT) and as I said we haven't found a common thread as yet.

Currently we monitor the F41021WF file on a regular basis and try and roll back all commitments and Qty on hand every night before all the batch and integrity jobs run.

A second issue we are seeing, which may be related, is that in about 20 cases thusfar a Customer Service person is entering a sales order and after keying a number of lines successfully, receives an error message that no base price is setup. In all case, bar one, we cancelled the transaction and after re-connecting to Citrix all was well again. However in one case we just bypassed the line in error and accepted the other lines on the order. In this case NO F4211 records were written, but a sales order header (F4201) was. Also commitments for all stock were still recorded in the F41021.

We are continuing our quest...

Rgds,

Sef van den Nieuwelaar
Australia
B732 on NT, XE on NT, B732/A73 on AS400, B733 on NT

* Coming to a European City near you soon *

Sef,

Have you checked JDE.LOG and JDEDEBUG.LOG when you get this error?

In JDE.LOG check if the commit failure is on table F4501, if so then you should check the average cost field on the Item Master for the items you're processing. If the average cost stored is a big negative number, then that may be the problem. You'll have to go to the System Constants for the Branch/Plant you're working on. If you have the "Update Average Cost On Line" check box on, and if for those items you've set up the average cost as zero, the average cost will incorrectly calculate this value. You might also want to check UDC 40/AV, because you can remove P4205 from this table if you don't need to calculate the average cost at ship confirm or uncheck the "Update Average Cost On Line" check box in the System Constants.

Remember, this is valid only if in the JDE.LOG the table that did not commit was F4501.

Good luck,

Jorge
[email protected]

Hi Jorge

We were able to recapture a couple of JDE logs. We have had instances where an insert into F4115 failed and another one where an insert into F5506 (an allocation related custom table) failed. In other instances we just had the simple message that commit failed without any explanation.
We have not seen any costing related messages.

We are trying to capture more logs, but since the users are scrattered all over the country and the jde.log is reset everytime the users re-connect to Citrix, we haven't recovered that many. Also turning on the JDEDEBUG log for some of the users hasn't been a success as Murphy's law says that no error will occur whilst the debug log is running.

I have also heard of issues where the F49211, SO tag file, causes this problem but again we haven't been able to prove a relationship.

Today (Day 6 of being live) we only had a handful of ship confirmations fail, but the issue continues to puzzle us. All we can do is to run the roll back UBE and keep on searching.

Rgds,

Sef van den Nieuwelaar
Australia
B732 on NT, XE on NT, B732/A73 on AS400, B733 on NT

* Coming to a European City near you soon *

Sef,

Check two work tables: F47131 and F47132. They are work tables used by ship confirm. These two tables should be empty after ship confirm. However, when ship confirm fails, there are leftover junkies in them.

Give it a try and good luck.

MG
B7332, Win2000/SQL2000, SP13.3

G
[email protected]

Sef,

came across the below in the Knowledge Garden and thought of your issue:
----------------------------------------------------
Title: Commit Failed - Invalid Cursor Error
Abstract: Commit Failed and Invalid Cursor (Oracle error) can occur in applications that have Transaction Processing turned on.

Product OneWorld
Suite Distribution
Release B73.3.2 and later
Document ID ODS-01-0069
Date Created 05/15/2001
Date Reviewed 05/15/2001
Date Revised 08/07/2001

--------------------------------------------------------------------------------

Issue
Commit Failed and Oracle error: (OCI0000179 - Error - ORA-01001: invalid cursor) in jde.log. This has been seen in the Purchase Order Entry (P4310) and other applications. Commit Failed only occurs in applications with Transaction Processing turned on. The issue is caused when Oracle forfeits the cursors created in the previous connection when OneWorld middleware creates a new connection.

Resolution
Add the following to the JDE INI on both the client machine and the server:

[DB SYSTEM SETTINGS]
OracleServerHandleReuse=0

Client machine needs to be on SP11.1 or higher. Server needs to be at SP16 or higher.

NOTE: Both client and server need to be at the same SP level. If logic is processed on the server then both client and server need to be at SP16 or higher.



Larry Jones
[email protected]
OneWorld XE, SP 15.1
HPUX 11, Oracle SE 8.1.6
Mfg, Distribution, Financials

Hi Larry

Thank you very much for the tip (I will add it to the JDE.INI enhancement document in the tips and traps section). I did not come accross this document when perusing the KG, but the description looks very promising as in the JDE.LOG we do see the same Oracle error messages:

OCI0000057 - Error - ORA-01001: invalid cursor

OCI0000179 - Error - ORA-00001: unique constraint (PRODDTA.F4115_1) violated

We will put the extra line in the JDE.INI asap and I'll keep you informed on the progress.

Thanks again,


Sef van den Nieuwelaar
Australia
B732 on NT, XE on NT, B732/A73 on AS400, B733 on NT

* Coming to a European City near you soon *