My Oracle Support Banner

Periodic Reports DLP Version Failing When Using BIP Due To PK_USER_CASES_REVISION Constraint (Doc ID 2919341.1)

Last updated on JANUARY 10, 2023

Applies to:

Oracle Argus Safety - Version 8.2.2 and later
Information in this document applies to any platform.


Periodic Reports DLP Version fails when using BIP due to PK_USER_CASES_REVISION constraint

When generating a PBRER/DSUR using BIP and going against DLP, user receives a unique constraint error PK_USER_CASES_REVISION.

User sees that the PKG_AGG_RPT is failing when executing:

User investigated and found that rpt_agg_cs_cases is populated from cfg_adv_con_case_list.

The cfg_adv_cond_case_list is not populating as it previously did for DLP case series.
Specifically, last_update_time used to be populated from case_master. So if 2 records for same case_id were populated for 2 case series, the last_update_time would be consistent for both.

DLP periodic reports generates multiple case series (e.g.interval and cumulative) for a report and dlp_revision_number and last_update_time prior to 8231 upgrade were the same if the case id was in both case series.


Since the case series are created separately, same case_id/dlp_revision now have different last_update_time values.
This is then pushed into RPT_AGG_CS_CASES for both series.

For BIP Periodic Reports, the PKG_AGG_RPT.P_POPULATE_DLP then tries to insert into USER_CASES_REVISION using above mentioned SQL statement. But since last_update_time is now different for each case series, 2 rows with same case_id are returned and when inserting into USER_CASES_REVISION, we receive the PK_USER_CASES_REVISION unique constraint violation error.

To verify this user disabled the ARGUS_APP.TRG_CFG_ADV_COND_CASE_LIST trigger and ran the same report. When this trigger is disabled the report generates successfully.

Since user cannot run the system with that trigger disabled, user needs a long term solution.

Name of the referenced trigger has been corrected to TRG_CFG_ADV_COND_CASE_LIST_LUT


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

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