SPM Financials: Fix Cost Plan / Actual Cost mismatches with dry-run

[arijeetdev]

SPM Financials: Fix Cost Plan Actual OR Actual Cost mismatches with dry-run

Problem statement

A common ServiceNow SPM financials issue is when a project shows incorrect or zero actual costs in the Financials tab / Portfolio Planning Workspace, even though fm_expense_line records exist.

Typical symptoms:

• cost_plan.total_actual_cost does not equal the sum of processed fm_expense_line amounts.
• pm_project.work_cost  does not equal the sum of cost_plan_breakdown rows where breakdown_type = task.
• cost_plan_breakdown rows have stale breakdown_type = requirement actuals or duplicated task-level rows.
• Task-level planned cost breakdowns are missing or out of sync with requirement-level planned cost.
• Project  EAC Is incorrect even after clicking "Calculate completion estimates" related link.
• EAC on the financials grid and widgets differs in the new Project workspace financials tab.
• Actuals on the financials grid and widgets differs in the new Project workspace financials tab.
• SPW financials grid value for actual, budget , planned cost on breakdown rows does not matches with record page financials value for the same timescope.
• Discrepancy in financials value when workspace data is compared with classical UI.

This script rebuilds task-level planned and actual cost rollups for a project , using existing breakdown_type = requirement rows as the source of truth.

Root cause

The SPM financial rollup chain is split into two pipelines:

Planned cost

cost_plan
  -> (existing) cost_plan_breakdown (breakdown_type = requirement) with planned cost
      -> PPMCostRollupManager
        -> cost_plan_breakdown (breakdown_type = task) with planned cost
          -> task planned totals

Actual cost

fm_expense_line (state = processed)
  -> PPMFundManager.updateActualsForTask() / updateActualsForCostPlan()
    -> cost_plan_breakdown (breakdown_type = requirement) with actuals
      -> cost_plan_breakdown (breakdown_type = task) with actuals
        -> ExpenseLinesHelper.setTaskActualCosts()
          -> pm_project.work_cost 

The mismatch usually happens when one of those layers is updated and the downstream layers are not refreshed, for example:

• Custom cost types, source_id/source_table changes, or top_task changes.
• Duplicate cost_plan_breakdown rows from past data corruption / partial rollbacks.
• Fiscal period changes without rebuilding breakdowns.
• Processed expense lines are deleted.
• Expense lines are imported.
• Processed expense lines are reparented with a different cost plan.
• The amount on a processed expense line is changed.

Any of the above can leave the cost_plan_breakdown task- and requirement-level rows out of sync with the expense-line source of truth. The script below rebuilds those breakdown layers.

Before you run this script

1. Back up the affected project(s)
2. resetRequirementTypeActualsToZero — Zeros out actual on all cost_plan_breakdown records of type 'requirement' linked to the task.
3. Test in a non-production instance first.
4. The script is intended to be run by an admin user as a Fix Script , from Scripts - Background can get timed out if large number of records present.
5. The script is a data-correction workaround, not a product code fix. If the symptom is reproducible on a clean instance, open a new PRB / case.

Configuration

Edit the variables at the top of the script:

Variable	Purpose
DRY_RUN	true = only logs what would be changed; false = applies the fix.
inCorrPrj	Array of affected sys_id values. Keep the list small.

How to use

1. Copy the script below into Scripts - Background or a Fix Script.
2. Set DRY_RUN = true and put project sys id  inCorrPrj array for the affected project
3. Run the script and read the gs.info output.
4. If the preview looks correct, set DRY_RUN = false and run again.

The script

// ============================================================================
// Reset & recalculate task actuals for projects with stale/corrupted actuals
// ============================================================================
// DRY_RUN = true  -> read-only. Logs what WOULD change, writes nothing.
// DRY_RUN = false -> applies the fix.
// ============================================================================
var DRY_RUN = true;
var LOG_PREFIX = '[ActualsFix]' + (DRY_RUN ? '[DRY_RUN] ' : ' ');
var inCorrPrj = ['70b35d7953227a10b678730330e5e6b5'];

var log = function (msg) { gs.info(LOG_PREFIX + msg); };
var logErr = function (msg) { gs.error(LOG_PREFIX + msg); };

var resetTaskBreakdowns = function (sys_id, table) {
    var taskRef = new GlideRecord(table);
    if (!taskRef.get(sys_id)) {
        logErr('Record not found: ' + table + ' ' + sys_id + '. Skipping.');
        return;
    }
    log('Processing ' + table + ' ' + sys_id + ' (' + taskRef.getDisplayValue() + ')');

    log('STEP 1/3: Reset requirement-type breakdown actuals to zero');
    resetRequirementTypeActualsToZero(sys_id);

    log('STEP 2/3: Re-drive breakdown actuals from processed expense lines');
    resetTaskBreakdownsActual(sys_id);

    log('STEP 3/3: Update task-level actual costs and cost plan totals');
    updateTaskAndCpRecordActuals(sys_id);

    log('Finished ' + table + ' ' + sys_id);
};

var resetRequirementTypeActualsToZero = function (sys_id) {
    var grBreakdown = new GlideRecord('cost_plan_breakdown');
    grBreakdown.addQuery('breakdown_type', 'requirement');
    grBreakdown.addQuery('task', sys_id);
    grBreakdown.query();

    var count = 0, skipped = 0;
    while (grBreakdown.next()) {
        var oldActual = grBreakdown.getValue('actual');
        if (parseFloat(oldActual || 0) === 0) {   // already zero, nothing to do
            skipped++;
            continue;
        }
        log('  Breakdown ' + grBreakdown.getUniqueValue() + ': actual ' + oldActual + ' -> 0');
        if (!DRY_RUN) {
            grBreakdown.setValue('actual', 0);
            grBreakdown.update();
        }
        count++;
    }
    log('  Step 1 summary: ' + count + ' requirement breakdown(s) '
        + (DRY_RUN ? 'would be reset' : 'reset') + ', ' + skipped + ' already zero');
};

var resetTaskBreakdownsActual = function (sys_id) {
    var gr = new GlideRecord('fm_expense_line');
    gr.addQuery('task', sys_id);
    gr.query();

    var total = 0, withCostPlan = 0, orphan = 0;
    var fundManager = DRY_RUN ? null : new PPMFundManager();

    while (gr.next()) {
        total++;
        var hasCostPlan = !gr.cost_plan.nil();
        if (hasCostPlan) withCostPlan++;
        else {
            orphan++;
            log('  Orphan expense line (no cost plan): ' + gr.getUniqueValue()
                + ', amount=' + gr.getValue('amount'));
        }

        if (!DRY_RUN) {
            fundManager.updateActualsForTask(gr);
            if (hasCostPlan) {
                fundManager.updateActualsForCostPlan(gr);
            }
        }
    }
    log('  Step 2 summary: ' + total + ' expense line(s) '
        + (DRY_RUN ? 'would be processed' : 'processed')
        + ' (' + withCostPlan + ' with cost plan, ' + orphan + ' orphan)');
};

var updateTaskAndCpRecordActuals = function (taskId) {
    var expenseLinesGr = new GlideRecord('fm_expense_line');
    expenseLinesGr.addQuery('task', taskId);
    expenseLinesGr.addQuery('state', 'processed');
    expenseLinesGr.query();

    var ok = 0, failed = 0, cpUpdated = 0;
    while (expenseLinesGr.next()) {
        var lineId = expenseLinesGr.getUniqueValue();

        if (DRY_RUN) {
            log('  Would update task actuals for expense line ' + lineId
                + (expenseLinesGr.cost_plan.nil() ? ' (no cost plan)' : ', cost plan ' + expenseLinesGr.getValue('cost_plan')));
            ok++;
            continue;
        }

        try {
            var helper = new ExpenseLinesHelper(expenseLinesGr);
            helper.setTaskActualCosts();

            if (!expenseLinesGr.cost_plan.nil()) {
                var costPlan = new CostPlan(expenseLinesGr.cost_plan.getRefRecord());
                if (costPlan.updateTotalActualCost()) {
                    costPlan.update();
                    cpUpdated++;
                }
            }
            ok++;
        } catch (e) {
            failed++;
            logErr('  Failed on expense line ' + lineId + ': ' + (e.message || JSON.stringify(e)));
        }
    }
    log('  Step 3 summary: ' + ok + ' processed expense line(s) '
        + (DRY_RUN ? 'would be updated' : 'updated')
        + (DRY_RUN ? '' : ', ' + cpUpdated + ' cost plan(s) updated, ' + failed + ' failed'));
};

// ----------------------------- main -----------------------------------------
log('=== Start. Mode: ' + (DRY_RUN ? 'DRY RUN (no changes will be saved)' : 'LIVE (changes WILL be saved)')
    + ' | Projects: ' + inCorrPrj.length + ' ===');

var prj = new GlideRecord('pm_project');
prj.addQuery('sys_id', 'IN', inCorrPrj);
prj.query();

var found = 0;
while (prj.next()) {
    found++;
    resetTaskBreakdowns(prj.getUniqueValue(), 'pm_project');
}
if (found !== inCorrPrj.length) {
    logErr('Requested ' + inCorrPrj.length + ' project(s) but found ' + found + '. Check the sys_ids.');
}
log('=== Done. Projects processed: ' + found
    + (DRY_RUN ? '. DRY RUN only: set DRY_RUN = false to apply.' : '.') + ' ===');

Important notes

1. Dry run first. Never run with DRY_RUN = false on the first attempt.
2. Task-type breakdowns are deleted and recreated. Existing breakdown_type = task cost_plan_breakdown rows are removed, then regenerated from the requirement-level planned cost and from the processed fm_expense_line actuals. Any direct manual changes to task-type breakdown rows will be lost.
3. Requirement breakdowns are the source of truth. This script does not recreate breakdown_type = requirement rows. It only rolls up from existing requirement breakdowns into breakdown_type = task breakdowns.
4. Actual fix zeros and rebuilds requirement actuals. The script sets cost_plan_breakdown.actual to 0 for all requirement rows, then reprocesses every fm_expense_line through PPMFundManager. This makes the requirement-level actuals match the expense lines.

Related PRBs

These PRBs describe the same or related symptoms:

• PRB2003945 / KB1318242 — Cost plan breakdowns of type requirement duplicated when resource plan date changes generate duplicate events.
• PRB2033720 — Duplicate cost plan breakdown records created when concurrent threads run find-or-create in PPMRollupRecordFinder.
• PRB1941521 — Task-type breakdowns have wrong rolled-up actuals when the budget allocation attribute is set to cost_type.
• PRB2075948 — Actuals in project currency don't match.
• PRB2118696 — Rollups break and data becomes inconsistent when an admin updates processed expense lines.
• PRB2079959 — Investment not stamped on requirement-type breakdown when expense lines are bulk-imported.
• PRB2079651 — Budget value reverted because of a negative expense line.
• PRB2086216 — Inconsistent total planned cost rollup from cost plans to program.
• PRB2124361 — Actuals in Project Workspace incorrect in detailed mode.
• PRB2126607 — Project Workspace financial widgets show incorrect actuals and forecast for fiscal-year scopes.
• PRB2115120 — Project Financial Diagnostics doesn't repair stale investment-currency actuals and investment budget corruption.