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

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

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

 

 

Environment:
CA PPM 14.4 and higher
Cause:

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.

Resolution:

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)
commit
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:

https://communities.ca.com/message/242010790-tech-tip-load-data-warehouse-fails-with-error-financial-plans-has-invalid-start-or-end-periods