Load Data Warehouse job fails with error "Financial plans has invalid start or end periods."


Article ID: 8319


Updated On:


Clarity PPM SaaS Clarity PPM On Premise


When running Load Data Warehouse job, an error is thrown:
Financial plans has invalid start or end periods.




Orphans Cost plans exist with no fiscal periods on them.

As a fix for CLRT-78721, a Data Integrity Check for FIN_PLANS START_PERIOD_ID and END_PERIOD_ID was added in Data Warehouse.
Seeing that error message means that you failed the check and orphans exist.


CA PPM 14.4 and higher


1. Retrieve all the cost plans with query:

select f.id, f.name as cost_plan, f.code, f.object_code, f.plan_type_code, f.start_period_id, f.end_period_id,i.code project_code, i.is_active, i.name Project_name
from fin_plans f, inv_investments i where i.id =f.object_id and  (f.start_period_id NOT IN (select id from biz_com_periods) or end_period_id NOT IN (select id from biz_com_periods))
order by Project_name

Save the results in an Excel sheet with headers.
2. Take a backup of FIN_PLANS table.

3. Run the query to update the cost plans to a valid period. You may use this query which will set them to the earliest existing fiscal period:
update fin_plans
set start_period_id = (select min(id) from biz_com_periods), end_period_id =(select min(id) from biz_com_periods)
where start_period_id NOT IN (select id from biz_com_periods)
or end_period_id NOT IN (select id from biz_com_periods)
4. Run the Load Data Warehouse job - Full
It should complete successfully. 
5. Get back to the projects containing those cost plans as per the results in step 1 - correct or delete the plans as appropriate. You can also leave them as they are if projects/costs no longer in use.

Additional Information

Communities Post that refers to this error message where you could discuss the error and the results: