E9.2 F00166 Unique Constraint on Insert

[Patrickjk]

Database: Oracle 12.2

Issue:
1) R41413 - Cycle Count Update was copied to custom R5741413 and slightly modified.
2) R5741413 is run manually from the menu by the user.
3) The UBE intermittently violates the unique constraint for table F00166 when inserting Work Center messaging.

This has become more of an issue over the past month. Has anyone else run into this issue? Any ideas how to permanently resolve the issue?

Based on what I see in the logs it appears to have something to do with caching.

Here is a portion of the log:

Mon Nov 8 12:17:06.242400 dbperfrq.c360
OCI0000178 - Unable to execute - INSERT INTO PRODDTA.F00166 (GTOBNM, GTTXKY, GTMOSEQN, GTMODOCTP, GTMODL01, GTMOAUTHOR, GTCDT, GTMOSTATUS, GTMOEFDTFR, GTMOEFDTTO, GTMOREVDT, GTMOCPYLINK, GTMOLNGP, GTMCU, GTCO, GTAN8, GTSHAN, GTAN8V, GTMOEMPL, GTDOCO, GTDCTO, GTITM, GTLITM, GTAITM, GTMOCALLNUM, GTMOOPPNUM, GTMOCAMPNUM, GTMOCOMPET, GTVEHN, GTAID, GTNUMB, GTDRAW, GTMOCATCD1, GTMOCATCD2, GTMOCATCD3, GTMOCATCD4, GTMOCATCD5, GTMOCATCD6, GTMOCATCD7, GTMOCATCD8, GTMOCATCD9, GTMOCATCD10, GTMOCATCD11, GTMOCATCD12, GTMOCATCD13, GTMOCATCD14, GTMOCATCD15, GTMOCATCD16, GTMOCATCD17, GTMOCATCD18, GTMOCATCD19, GTMOCATCD20, GTMOCATCD21, GTMOCATCD22, GTMOCATCD23, GTMOCATCD24, GTMOCATCD25, GTMOCATCD26, GTMOCATCD27, GTMOCATCD28, GTMOCATCD29, GTMOCATCD30, GTMODATE1, GTMODATE2, GTMODATE3, GTMODATE4, GTMODATE5, GTMOMATH01, GTMOMATH02, GTMOMATH03, GTMOMATH04, GTMOMATH05, GTMOFALPH1, GTMOFALPH2, GTMOFALPH3, GTMOFALPH4, GTMOFALPH5, GTPID, GTJOBN, GTUSER, GTUPMJ, GTUPMT, GTSERK, GTMOFMN1, GTMOFMN2, GTMOFMN3, GTMOFMN4, GTMOACTVID, GTMOFSTR1, GTMOFSTR2, GTMOFSTR3, GTMOFSTR4) 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,:BND41,:BND42,:BND43,:BND44,:BND45,:BND46,:BND47,:BND48,:BND49,:BND50,:BND51,:BND52,:BND53,:BND54,:BND55,:BND56,:BND57,:BND58,:BND59,:BND60,:BND61,:BND62,:BND63,:BND64,:BND65,:BND66,:BND67,:BND68,:BND69,:BND70,:BND71,:BND72,:BND73,:BND74,:BND75,:BND76,:BND77,:BND78,:BND79,:BND80,:BND81,:BND82,:BND83,:BND84,:BND85,:BND86,:BND87,:BND88,:BND89,:BND90,:BND91,:BND92)

Mon Nov 8 12:17:06.242556 dbperfrq.c366
OCI0000179 - Error - ORA-00001: unique constraint (PRODDTA.F00166_PK) violated

Mon Nov 8 12:17:06.242596 jdb_drvm.c1000
JDB9900401 - Failed to execute db request

Mon Nov 8 12:17:06.242635 jdb_exet.c4319
JDB3400009 - Failed to perform Insert for F00166

Mon Nov 8 12:17:06.242686 jdeppat.c187
writeF00166Record : InsertTable failed for F00166, JDB error=0

Mon Nov 8 12:17:06.266778 zdrv.cpp406
Terminating Z driver

Mon Nov 8 12:17:06.266899 zdrv.cpp423
Calling freeSession

Mon Nov 8 12:17:06.389487 jdecache.c1627
CAC0001025 - Application code leaked 1 caches which were detected when freeing environment PDNA92 (EnvHandle 09cad498) for user D779. Please refer to the debug log for details. If there are not enough details in the debug log to determine the cause, set Output=FILE in the [DEBUG] section of the JDE.INI and watch for this error message again. Or, set logCacheLeaksAtSignoff=TRUE in the [DEBUG] section of the JDE.INI and look for CAC0001036 error messages. This message (CAC0001025) will only appear with debug tools code or when debug logging is turned on.

Mon Nov 8 12:17:06.418902 ipcmisc.c348
API ipcSawUnregisterProcV1 : process 3579205 unregistered in entry 59

Any help would be greatly appreciated.

Thanks.

Patrick

 

[JohnDanter2]

That table is linked to media objects not the WC
WC tables are all F01131 F01131/m type names

So check your MOs

 

[Patrickjk]

You are correct. However, if you look at the code in R41413 (Custom R5741413 as mentioned previously) it calls F4114EndDoc in Group Section "F4141 Cycle Count Update" Event Rules "End Document", so it is inserting into WC table F01131 and tables F00165 and F00166.