Unique Constraint Encountered In CI_TNDR_ST_EXCP When Running PUPL Batch Process (Doc ID 2237207.1)

Last updated on JULY 22, 2024

Applies to:

Symptoms

A unique constraint error was encountered when running PUPL batch process.
The error happened while inserting a record in the CI_TNDR_ST_EXCP table.
.
The batch process creates exception rows in the table when it finds Pay
Tender Upload Staging records where Customer Id is an old Account Id and not
an Account Id but the Old Account Id belongs to different Accounts. In short,
the exception table record is there to capture this particular type of
invalid data.
.
We are running PUPL batch in 32 threads, and the batch process is
multi-threading by Account Id. This means the Account Ids for the same Old
Account Id could be processed in two separate threads.
.
The issue is that there are 2 SAs with different ACCT_ID but are referencing
the same OLD_ACCT_ID. This should be caught by the batch process and logged
as exception. However, while logging the exception, it encountered a
duplicate key error because it is possible that it is processing the 2 SAs on
separate and parallel threads.

 how to reproduce the issue:
.
1. Set-up 1 record in Pay Tender Upload Staging table. The Customer Id must
be referenced by two SAs belonging to different Account Ids.

2. Run PUPL in multiple threads. Make sure that the two records are processed
in different threads.
.
It is hard to replicate the error though because the records must be
processed in 2 separated threads at exactly the same time.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.