Here is another issue I recently hit with a customer and would like to share the solution with all of you.
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.
- 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.
- Take a backup of FIN_PLANS table.
- 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:
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)
- Run the Load Data Warehouse job - Full
It should complete successfully.
- 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