Installation and Upgrade › Known Issues › Duplicate Cost Plan Code Error

Duplicate Cost Plan Code Error

This issue applies only to upgrades from a 12.x release to Release 13.1.

The duplication of a plan code within a financial plan can cause the upgrade process to fail. The plan code for each plan type within the investment must be unique. For example, you can have a cost plan with the code MasterPlan2011 and a budget plan with the same code. However, you cannot have two cost plans with code MasterPlan2011 for the same investment. The unique database constraint for this rule was added in Version 13.0.

Due to concurrency issues, there can be a data issue in previous versions. When a duplicate plan type code exists within a single financial plan, the upgrade process fails with an appropriate error message. You must correct the data issue before resuming the upgrade process. An upgrade check script runs and alerts you that this problem exists.

Run the following query to identify possible data issues before you start the upgrade:

SELECT p1.id, p1.code, p1.name 
FROM fin_plans p1, fin_plans p2 
WHERE p1.id != p2.id 
AND p1.object_id = p2.object_id 
AND p1.code = p2.code 
AND p1.plan_type_code = p2.plan_type_code

If this query returns any rows, it indicates issues with fin_plans table data.

We recommend that you rename the codes for duplicate records. Do not change the other data. A sample query is available to fix the data issues. Because this known issue deals with financial information, review the query carefully before deciding to use it. The following query appends _<internalId> to the duplicate plan code.

<!-- Oracle --> 
UPDATE fin_plans 
SET code = code || '_' || to_Char(id) 
WHERE id IN 
    ( SELECT p1.id 
                              FROM fin_plans p1, fin_plans p2 
                              WHERE p1.id != p2.id 
                AND p1.object_id = p2.object_id 
                AND p1.code = p2.code 
                AND p1.plan_type_code = p2.plan_type_code 
                ) 


<!-- Mssql --> 
UPDATE fin_plans 
SET code = code + '_' + CAST(id AS VARCHAR) 
WHERE id IN ( SELECT p1.id 
                                FROM fin_plans p1, fin_plans p2 
                                WHERE p1.id != p2.id 
                AND p1.object_id = p2.object_id 
                AND p1.code = p2.code 
                AND p1.plan_type_code = p2.plan_type_code 
)