Clarity Upgrade fails on duplicates in CAP_SCENARIO_MEMBERS

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

When upgrading to or through Clarity 16.3.1, it fails with error message in logs on table CAP_SCENARIO_MEMBERS :

5/14/25 7:30 AM (ExecTask) Process - table: CAP_SCENARIO_MEMBERS.xml
5/14/25 7:30 AM (ExecTask) 
5/14/25 7:30 AM (ExecTask) SQL Text:
5/14/25 7:30 AM (ExecTask) 
5/14/25 7:30 AM (ExecTask) ALTER TABLE CAP_SCENARIO_MEMBERS ADD MODIFIED_STATUS VARCHAR ( 1 ) NULL
5/14/25 7:30 AM (ExecTask) 
5/14/25 7:30 AM (ExecTask) java.sql.BatchUpdateException: error occurred during batching: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
5/14/25 7:30 AM (ExecTask) 
5/14/25 7:30 AM (ExecTask) ALTER TABLE CAP_SCENARIO_MEMBERS ADD PUBLISH_STATUS VARCHAR ( 100 ) NULL
5/14/25 7:30 AM (ExecTask) 
5/14/25 7:30 AM (ExecTask)     at oracle.jdbc.driver.OracleStatement.executeLargeBatch(OracleStatement.java:4659)CREATE UNIQUE INDEX CAP_SCENARIO_MEMBERS_U1 ON CAP_SCENARIO_MEMBERS ( SCENARIO_ID, OBJECT_ID ) TABLESPACE INDX_SMALL
5/14/25 7:30 AM (ExecTask) 

Environment

Clarity 16.3.1

Cause

Duplicate records in table CAP_SCENARIO_MEMBERS

Resolution

1. Run the below query to identify the duplicates:

select SCENARIO_ID, OBJECT_ID, count(*) from CAP_SCENARIO_MEMBERS
group by SCENARIO_ID, OBJECT_ID
having count(*) >1

2. Check tables cap_scenarios to identify the scenario and object

3. To remove the wrong scenario(s), connect to Classic UI 

4. Open the idea or project- go to Scenario tab - pick the exact Scenario name and click delete.

This is the preferred solution.

Additional Information

For more information on the records, check database tables:

• cap_scenarios
• cap_scenario_assignments
• cap_scenario_resources
• cap_scenario_roles
• cap_scenario_tasks
• cap_scenario_team
• cap_scenario_members