ORA-20112 error on Incoming Customer Orders

example #2:

Are you sure that there are not multiple lines in the file for each order?

For example, in your last image I see A664403 being created, and I then see it try to create the same one again and get an error a few Messages later.

A few other questions for clarity:

Was this working until a certain point and then started to become a problem, or has it always been like this?

What exactly is the scheduled file import process that you have in place, can you provide any more specifics?

Are there any custom views or custom objects involved on the import side or just standard IFS structures?

Nick

Hi @NickPorter - 

Thanks for your reply!

In the second example regarding A664403, I actually imported that set of orders on purpose in duplicate - because I wanted to differentiate error ORA-20112 from error ORA-20110, because both mention information regarding the order already exists. 

This seems to be an issue since we upgraded from IFS v7.5 to v9. 

The file import process basically involves scripts that pull the mapped xml files from a file transfer directory our EDI server - to a file transfer directory on our IFS PROD server, from there IFS picks them up based on the Connect setup/background jobs.

The xml mapping and import utilizes standard IFS logic and views.

I suspect that either the structure of the file or the xml transformation process (if there is one in the application server) is different in 9 than it was in 7.5.  You’ll notice that the API/method reporting the error is different between the manual load duplicate and the system-driven one (ExternalCustomerOrder.CUST_ORD_FOUND when you import manually vs CustomerOrder.EXIST2 from the automated load process). 

When importing it manually you might be bypassing that transformation or structure difference.

This suggests to me that each mechanism may actually be using a different call to perform the load, hence a variation in the file layout requirement or a possible transformation needs to be adjusted to support the auto-load, but it still works for the manual import process.

I know this doesn’t fully answer your question, but hopefully it helps point you to the right place.

Nick

@NickPorter - thank you! I will take this back to our team and discuss. 

@NickPorter - did also realize some custom fields being populated in this process, which I will also review with our team. I believe it is still considered “standard” because we are using the standard mapping fields to include this information - we are just using the field in a manner other than intended by IFS. So, it’s not custom code… However, thank you for pointing me in that direction as well. 

Hopefully I will be able to provide an update soon.

I wanted to provide the answer that we received from IFS and after internal review:

There were no issues in our mapping, customizations present that were causing an issue, or a problem with our transformation process. 

IFS Support said that they’ve had another customer report the same issue, and they were looking into possible solutions. They said they believe it is happening because we are processing a high volume of orders at one time.

When I heard back after the above, they said that they came across some patches that were done in UPD 5 and UPD 6, and we are now on UPD 14 - so we should have it in our code. They also suggested a workaround of choosing “Allow lapses in number sequences” in the Coordinator Group screen (Application Base Setup > General Data > Coordinator Groups). The drawback is that you may have gaps in the order numbers (A#s). I tried the workaround in our TEST environment, and ran about 900 orders through, in batches of 200-280, to mirror our standard process, and the error did present on the first batch after the setting change, but in not as high volume… but then it did NOT present at all on the second, third or fourth import batch. I also noticed that the order numbers stayed pretty sequential with the workaround.

Since our update (UPD 14), we have noticed the issue less - but glad to know the workaround is available to try.  

We might also consider breaking up our import jobs, if needed.

All,

I’ve been working on this issue (with IFS assistance) as the Coordinator Group fix didn’t work for us (but it would be useful to know how your Coordinator Groups are configured, i.e. assuming you have more than one, did you tick them all, etc?). Furthermore, our error message is slightly different…

Yours is ORA-20112: CustomerOrder.EXIST2

Ours is ORA-20112: ExtCustSched.FND_RECORD_EXIST: The Ext Cust Sched already exist

...but I believe that it is a related issue, i.e. it is due to out of sync sequence numbers. The cause of our errors are as follows…

Oracle sequence CUST_SCHED_MESSAGE_SEQ contains an auto indexing number that feeds the column Message ID in Incoming Customer Schedules. If you bring in an XML EDI message, which must have a MESSAGE_ID field, it fails with ORA-20112 if the incoming MESSAGE_ID is greater than the next value in CUST_SCHED_MESSAGE_SEQ. To test this, I run two queries...

select cust_sched_message_seq.nextval from dual;

select max(message_id) from ext_cust_sched_tab;

If I increase the CUST_SCHED_MESSAGE_SEQ value to a number greater than the incoming MESSAGE_ID, the error stops.

I then worried how I would keep these in sync, e.g. if I run the same incoming file several times with the same MESSAGE_ID for testing purposes, the issue would arise again as the MESSAGE_ID would be static and the sequence next value would increment. I discovered that the CUST_SCHED_MESSAGE_SEQ value can be set much higher than your incoming MESSAGE_ID range, maybe 1,000,000 more for example - I can then bring in MESSAGE_ID values from 1 to any number under the sequence value.

In summary:

• MESSAGE_ID in incoming XML becomes DOC_NO in Incoming Schedules

• MESSAGE_ID in incoming XML must be less than CUST_SCHED_MESSAGE_SEQ

• As long as MESSAGE_ID < CUST_SCHED_MESSAGE_SEQ, same XML can be repeated for testing

• If testing by repeating the same MESSAGE_ID causes MESSAGE_ID to be greater than CUST_SCHED_MESSAGE_SEQ then error will occur

I thought I would share this for anyone else that comes along and has related ORA-20112 issues.