Solved

ORA-20112 error on Incoming Customer Orders

  • 14 January 2020
  • 9 replies
  • 3049 views
Joy
Rank
Userlevel 3
Badge +8
  • Joy
  • Sidekick (Customer)
  • 31 replies

We are experiencing a large number of stopped Incoming Customer Orders during our scheduled file import each morning, across multiple customers with the error message: 

ORA-20112: CustomerOrder.EXIST2: The Customer Order object already exists.

These orders do not already exist, and if the user RMB > Approves the order from Incoming Customer Order, it will approve the error without any prior action needed. 

We have previously submitted this issue to IFS, and they were not able to recreate in their environment - leaving us without answers. 

I was able to recreate this error in our TEST environment, using our incoming prod orders from today, and I resubmitted a case to IFS - but looking to see if anyone else has experienced this error? 

icon

Best answer by Joy 14 February 2020, 15:39

View original

9 replies

Joy
Rank
Userlevel 3
Badge +8
Example #1

example #2:

 

Example #2

 

NickPorter
Rank
Userlevel 6
Badge +18

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

Joy
Rank
Userlevel 3
Badge +8

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.

NickPorter
Rank
Userlevel 6
Badge +18

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

 

Joy
Rank
Userlevel 3
Badge +8

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

Joy
Rank
Userlevel 3
Badge +8

@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.

Joy
Rank
Userlevel 3
Badge +8

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.

Joy
Rank
Userlevel 3
Badge +8

Thought I would circle back on this - we were seeing spikes of this error even after UPD14, even though most of the time it was less. 

We went ahead and implemented the workaround of choosing “Allow lapses in number sequences” in the Coordinator Group screen (Application Base Setup > General Data > Coordinator Groups - Enable Allow Lapses in Number Sequences checkbox), and we have not received the error since doing this.

GPIE
Rank
Userlevel 5
Badge +11

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.

 

 

Reply


Terms & ConditionsCookie settings