Cost Plan Forecast Totals Not Matching CIT Grid List Forecast Totals

Products

• Clarity PPM SaaS

Issue/Introduction

Cost plans created on an investment do not have forecast cost totals for a particular year,
however when reviewing the CIT grid, the forecast cost does display.

Cause

'Forecast cost' is stored in the table/column: FIN_FINANCIALS.odf_nk_forecast_cst

It is stored as a binary format as many different time periods (months, weeks, etc.) within a single database field.

There is another field FIN_FINANCIALS.odf_nk_forecast_cst = 1 
which indicates that slicing the record for the investment instance was invalid and was never resliced
and that forcing a Time Slicing Sync job instance does not resolve the issue nor change this value to null/0.

The job that cracks these TSVs/per-period metrics is the Time Slicing Sync job however in a case study
where this was reported, it is unknown how it came to this state.

Resolution

The only fix is to re-save the financial plan, e.g. by making a  momentary name change to the POR cost plan) in the UI.

This will need to be done for the remaining discrepancies on the investment instances affected. 
The following query is used to find investments whose financials are missing or out-of-sync with its associated forecast cost plan plan-of-record.

SELECT
    ii.code,
    ii.name,
    map.primary_object_instance_code    AS investment_type,
    ff.id                               AS fin_financials_id,
    ff.odf_ss_forecast_cost,
    ff.forecast_cst_total,
    ff.forecast_cst_start,
    ff.forecast_cst_finish,
    p.id                                AS plan_id,
    p.name                              AS plan_name,
    p.is_plan_of_record,
    p.plan_type_code,
    ff.created_date                     AS financials_created_date,
    ii.last_updated_date                AS investment_last_updated,
    ff.last_updated_date                AS financials_last_updated
FROM  inv_investments              ii
JOIN  odf_object_instance_mapping  map  ON  map.primary_object_instance_id    = ii.id
                                        AND map.secondary_object_instance_code = 'financials'
JOIN  fin_financials               ff   ON  ff.id = map.secondary_object_instance_id
LEFT JOIN fin_plans                p    ON  p.object_id         = ii.id
                                        AND p.is_plan_of_record = 1
                                        AND p.plan_type_code    = 'FORECAST'
WHERE (
    ff.odf_ss_forecast_cost IS NULL
    OR ff.odf_ss_forecast_cost NOT IN (
        SELECT DISTINCT prj_object_id FROM odf_sl_forecast_cost_m
    )
)
AND   ff.forecast_cst_total >= 0
ORDER BY map.primary_object_instance_code, ii.code

Once the change/edit is made on the cost plan plan-of-record of the affected investment instance,
it will reflect properly and no longer show up in the results when the query is reran.