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

Last updated on DECEMBER 04, 2023

Applies to:

Symptoms

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.

With introduction of trigger ARGUS_APP.TRG_CFG_ADV_COND_CASE_LIST, CFG_AD_COND_CASE_LIST.LAST_UPDATE_TIME is now populated with GSS_UTIL.GMT.

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

Changes

Cause

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