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:
Oracle Life Sciences Argus Safety - Version 8.2.2 and laterInformation in this document applies to any platform.
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
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
Symptoms |
Changes |
Cause |
Solution |
References |