Archive Plan Summary Completing With "Error while populating the fact tables. purging this plan summary." and "ORA-00001: unique constraint (MSC.MSC_APCC_WORKERS_U1)" (Doc ID 2031706.1)

Last updated on DECEMBER 01, 2016

Applies to:

Oracle Advanced Planning Command Center - Version 12.2.4 and later
Information in this document applies to any platform.

Symptoms

On :  12.2.4 version, Load and Summarization ETL

After applying 12.2.4.1 VCP Cum <Patch 19549533>: When running the Archive Plan Summary with parameter "Enable Details" = Yes, it is completing with error  

ERROR
-----------------------

Error while populating the fact tables. purging this plan summary.


The issue is occurring while inserting records into msc_apcc_workers due to unique constraint violation.

If we run the following SQL statement manually from msc_phub_pkg.populate_details_fn (the statement which populates msc_appc_workers)

 select
               :p_plan_run_id,
               worker_name,
               part_name,
               fnd_global.user_id, sysdate,
               sysdate, fnd_global.user_id, fnd_global.login_id,
               fnd_global.conc_program_id, fnd_global.conc_login_id,
               fnd_global.prog_appl_id, fnd_global.conc_request_id
           from
               (select 'msc_plan_details_pkg' worker_name, 'orders' part_name from dual
               union select 'msc_plan_details_pkg' worker_name, 'pegging' part_name from dual
               union select 'msc_plan_details_pkg' worker_name, 'exception_details' part_name from dual
               )

We get the following error:

ORA-00001: unique constraint (MSC.MSC_APCC_WORKERS_U1) violated

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run Archive Plan Summary with parameter "Enable Details" = Yes
2. Note the error above

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms