Unique Constraint error when submitting for approval cost plans migrated from version 7.5.3

Document ID : KB000051713
Last Modified Date : 14/02/2018
Show Technical Document Details



After upgrade to 8.1x or 12.x, cost plans that were migrated from 7.5.3 are erroring out with a unique constraint error when submitting for approval.

This is because the cost plans do not have a sub group by and the system erroneously inserts a ':' into the PLAN_DETAIL_2_KEY column of the fin_cost_plan_details table which is where the sub group by is stored.

This causes the system to think that there is a detail row for the subgroup by and it tries to insert a row for it in the table and end s up being non-unique.

Following error in the sql trace
NDE:PSTM@11e9e2b: FAIL: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00001: unique constraint (FIN_COST_PLAN_DETAILS_U1) violated
This is because the following insert causes a unique constraint because the system thinks there is a value in the PLAN_DETAIL_2_KEY column of the fin_cost_plan_details table.

Actually there is ':' which is not a value, it should have a value of NULL.

Steps to Recreate:

  1. Create cost plans with no subgroup (you can group by the transaction class or any other group).

  2. Upgrade to 8.1x or higher.

  3. Go to the UI and select a cost plan.

  4. Click the submit for approval button.

  5. Enter the required fields and click on submit for approval button.

Expected Results: Cost plan should be submitted successfully.

Actual Results: Error 'ODF-0015: Value must be unique' displayed.



Update the ':' in the PLAN_DETAIL_2_KEY column of the fin_cost_plan_details table to NULL by running the following update statement:

update fin_cost_plan_details
where PLAN_DETAIL_2_KEY = ':'


This issue has been documented as CLRT-55995 and is assigned to development for review. If you are experiencing this problem and the workaround above does not significantly help, please contact CA Clarity Technical Support.


clrt-55995, PLAN_DETAIL_2_KEY, fin_cost_plan_details, clarity8open, clarity12open, unique constraint, ODF-0015, Value must be unique, FIN_COST_PLAN_DETAILS_U1.