EMERALDCUBE BLOG

Workcenter Completion Emails and Duplicate Key Errors


In a normally operating EnterpriseOne environment, Workcenter completion emails are sent to several Workcenter tables whether a UBE completes successfully or ends in error. As it turns out, there is a potential issue with Next Numbers when there are an extremely large number of Workcenter messages being sent. If there are a large volume of UBEs being submitted, the number of records stored in 3 key Workcenter tables (F01131, F01131T, and F01133) will grow so large that they will roll over the unique number used for each of these messages until the number is reused, which will cause “duplicate key” error messages, since the number being used is already in the table.

When this happens, each UBE will wait until the process of writing to the Workcenter tables is complete and with each duplicate key error, it will wait 3 seconds for the record to be inserted and then try again 2 more times, then give up. This causes each UBE to process an additional 9 seconds. For one customer I worked with, they submitted several thousand UBEs to process through a Subsystem UBE process one day per week and when the Next Number rolled over, it caused the UBEs to process 3 times longer than normal – a big issue for any customer.

Here is a sample of messages you might see in the log files for the CallObject kernel when this issue occurs:

37049/-143051056WRK:Starting jdeCallObject Thu Jul 31 12:37:33.592131jdb_exet.c6155
JDB3400009 – Failed to perform Insert for F01131T
37049/-143051056WRK:Starting jdeCallObject Thu Jul 31 12:37:33.592151b0100011.c2296
*** PPAT error. Insert to F01131T failure. ***
37049/-143051056WRK:Starting jdeCallObject Thu Jul 31 12:37:33.592751dbperfrq.c471
OCI0000178 – Unable to execute – INSERT INTO PRODDTA.F01133 (ZTSERK, ZTAN8, ZTALPH, ZTMBDS, ZTDTI, ZTCMDF, ZTSTSM, ZTDSS5, ZTMSGA, ZTAB3, ZTUPMT) VALUES (:BND1,:BND2,:BND3,:BND4,:BND5,:BND6,:BND7,:BND8,:BND9,:BND10,:BND11)
37049/-143051056WRK:Starting jdeCallObject Thu Jul 31 12:37:33.592791dbperfrq.c477
OCI0000179 – Error – ORA-00001: unique constraint (PRODDTA.F01133_PK) violated
37049/-143051056WRK:Starting jdeCallObject Thu Jul 31 12:37:33.592824jdb_drvm.c1003
JDB9900401 – Failed to execute db request
37049/-143051056WRK:Starting jdeCallObject Thu Jul 31 12:37:33.592853jdb_exet.c6155
JDB3400009 – Failed to perform Insert for F01133
37049/-143051056WRK:Starting jdeCallObject Thu Jul 31 12:37:33.592873b0100011.c2309
*** PPAT error. Insert to F01133 failure. ***
37049/-143051056WRK:Starting jdeCallObject Thu Jul 31 12:37:33.627902dbperfrq.c471
OCI0000178 – Unable to execute – INSERT INTO PRODDTA.F01131 (ZZSERK, ZZAN8, ZZALPH, ZZSFRM, ZZCCO, ZZCPH, ZZMBDS, ZZDTI, ZZUPMJ, ZZUPMT, ZZJOBN, ZZDSS5, ZZMSGP, ZZPA8, ZZMB01, ZZMB02, ZZTCNT, ZZCMPR, ZZMSG, ZZUSER, ZZPID, ZZMSGA, ZZENTT, ZZSTSM, ZZPXT1, ZZBASE, ZZSY, ZZDOCO, ZZSFXO, ZZDCTO, ZZLNID, ZZKCOO, ZZTMPI, ZZAB3, ZZLV, ZZPSRK, ZZVERS, ZZTSV, ZZEAP, ZZFRMI) VALUES (:BND1,:BND2,:BND3,:BND4,:BND5,:BND6,:BND7,:BND8,:BND9,:BND10,:BND11,:BND12,:BND13,:BND14,:BND15,:BND16,:BND17,:BND18,:BND19,:BND20,:BND21,:BND22,:BND23,:BND24,:BND25,:BND26,:BND27,:BND28,:BND29,:BND30,:BND31,:BND32,:BND33,:BND34,:BND35,:BND36,:BND37,:BND38,:BND39,:BND40)
37049/-143051056WRK:Starting jdeCallObject Thu Jul 31 12:37:33.627967dbperfrq.c477
OCI0000179 – Error – ORA-00001: unique constraint (PRODDTA.F01131_PK) violated
37049/-143051056WRK:Starting jdeCallObject Thu Jul 31 12:37:33.628001jdb_drvm.c1003
JDB9900401 – Failed to execute db request
37049/-143051056WRK:Starting jdeCallObject Thu Jul 31 12:37:33.628029jdb_exet.c6155
JDB3400009 – Failed to perform Insert for F01131

The solution we used to resolve this issue was to purge off Workcenter completion messages to a lower retention period to prevent these from rolling over and reusing the unique ID on the Workcenter completion messages.

This is easily done through EmeraldPrism, and if your UBEs are suddenly taking a lot longer than normal, and you are seeing messages like the errors above, then chances are likely that you are encountering this issue, and you should plan to purge records from the affected tables above.

Additional Oracle Knowledge Documents on Workcenter messaging:

E1: RDA: How to Disable the UBE Job Completion Message ( Doc ID 645319.1)
E1: WRKFLW: Frequently Asked Questions on External Mail ( Doc ID 1228336.1)
E1: WRKFLW: How is Work Center Message Serial Number (SERK) Generated and what to do when SERK =99999999? ( Doc ID 648112.1)
E1: WRKFLW: Frequently Asked Questions on Work Center ( Doc ID 626538.1)
E1: WRKFLW: Frequently Asked Questions on Purging Work Center Messages ( Doc ID 1329669.1)