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:
- Create cost plans with no subgroup (you can group by the transaction class or any other group).
- Upgrade to 8.1x or higher.
- Go to the UI and select a cost plan.
- Click the submit for approval button.
- 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:
set PLAN_DETAIL_2_KEY = NULL
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.