Load DWH - Full fails with error "Financial plans has invalid start or end periods."

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Error message when running Load DWH - Full Load:

Financial plans has invalid start or end periods.

Environment

Any Supported Clarity Release

Cause

Some plans have an invalid start or end period that does not match a valid fiscal period

Resolution

Run this query to identify any bad plans:

    SELECT 
    fp.id AS plan_id, 
    fp.name AS plan_name, 
    fp.code AS plan_code, 
    fp.plan_type_code,
    i.code AS investment_code, 
    i.name AS investment_name,
    fp.start_period_id,
    fp.end_period_id
FROM fin_plans fp
JOIN inv_investments i ON fp.object_id = i.id
WHERE (fp.start_period_id IS NOT NULL AND fp.start_period_id NOT IN (SELECT id FROM biz_com_periods))
   OR (fp.end_period_id IS NOT NULL AND fp.end_period_id NOT IN (SELECT id FROM biz_com_periods));

Option 1:

Update or delete the cost plans through the UI. Some plans can typically just be deleted and regenerated if they are still needed.

1. In Clarity, navigate to an affected project
2. Go to the Financial Plans tab - Cost Plans.
3. Look for the plan named  with the corresponding name from your list
4. If the plan opens successfully:
   • Edit the Start Period and End Period drop-downs to select valid, active fiscal periods.
   • Click Save.
   • Alternatively, if this plan is obsolete, you can simply delete it
5. Repeat this for the remaining projects in your list.

Option 2:

If the update/delete UI method is not working for these projects, apply the below SQL fix for the broken references. Once the database is updated with a valid period, the UI should load successfully again.

1. Run this query against your Clarity database  to grab a valid, active fiscal period ID:
    SELECT id, period_name, start_date, end_date
   FROM biz_com_periods
   WHERE is_active = 1
   AND ROWNUM <= 5
   ORDER BY start_date DESC;
       
2. Note down the id from the first row of the results (for example 5000000).
3. Using the valid ID you found in step 1, run the following statements to fix the corrupted plans.(Make sure to replace 5000000 with the actual ID you found in Step 1):
   -- Fix the broken start periods

   UPDATE fin_plans

   SET start_period_id = 5000000 

   WHERE start_period_id NOT IN (SELECT id FROM biz_com_periods);

    

   -- Fix the broken end periods

   UPDATE fin_plans

   SET end_period_id = 5000000 

   WHERE end_period_id NOT IN (SELECT id FROM biz_com_periods);

    

   COMMIT;
4. Go back into Clarity and navigate to the project you've opened.
5. Click on the Financials tab, it should now load correctly
6. You can now safely delete the cost plans via the UI