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

book

Article ID: 8319

calendar_today

Updated On:

Products

CLARITY PPM FOR ITG CLARITY PPM FEDERAL Clarity PPM SaaS - Application Clarity PPM On Premise

Issue/Introduction

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

 

 

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.

Environment

CA PPM 14.4 and higher

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