Clarity

Expand all | Collapse all

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

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

    Broadcom Employee
    Posted Sep 28, 2017 12:07 PM

    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



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

    Broadcom Employee
    Posted Sep 28, 2017 12:19 PM