Database Optimization job fails on CMN_CAPTIONS_NLS_PROCESSED_U1

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Steps to Reproduce:
    1. Navigate to Modern UX > Reporting module > Data Providers sub-module
    2. Copy 'Missing Time' Data Provider. Let us name this as 'demo'
    3. Publish the copied Data Provider
    4. Run the 'Database Optimization' job:
        a. 'Caption Limit' should be 10000
    5. Run the following query:
        select *
        from cmn_captions_nls_processed
        where id = (
            select id
            from cmn_captions_nls
            where language_code = 'en'
            and table_name = 'DPR_DATA_PROVIDER'
            and name = 'demo'
        )
    6. If the query returns a result proceed to the next step. Otherwise go to step 4. This needs to be done till the query returns a single record
    7. Edit the same Data Provider again
    8. Make no changes. Just publish it back
    9. Run 'Database Optimization' job again, again with 'Caption Limit' parameter set to 10000

Expected Results:
    * The job completes

Actual Results:
    * The job fails with the following error in the bg-ca.log:
        Oracle DB:
            Error message: ORA-00001: unique constraint (NIKU.CMN_CAPTIONS_NLS_PROCESSED_U1) violated
        Postgres DB:
            ERROR: duplicate key value violates unique constraint "cmn_captions_nls_processed_u1"

Environment

Clarity 16.4.1, 16.4.2.

Cause

DE209332

Resolution

DE209332 is under review.

Workaround:

Reschedule the job with Delete Orphaned Captions setting unchecked