Nika_Hadzhikidi

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

Discussion created by Nika_Hadzhikidi Employee on Sep 28, 2017
Latest reply on Sep 28, 2017 by Nika_Hadzhikidi

Hello everyone,

 

Here is another issue I recently hit with a customer and would like to share the solution with all of you.

 

 

Issue:

 

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.

 

 

Workaround:

  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.

  1. Take a backup of FIN_PLANS table.

 

  1. 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

  1. Run the Load Data Warehouse job - Full

It should complete successfully. 

  1. 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.

 

 

Hope this helps -Nika

Outcomes