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

[arijeetdev]

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

Problem statement

A common ServiceNow SPM financials issue is when a project or demand 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 / dmn_demand.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.

This script rebuilds task-level planned and actual cost rollups for a project or a demand, 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 / dmn_demand.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) / demand(s). The script deletes breakdown_type = task cost_plan_breakdown rows and rewrites actual on breakdown_type = requirement rows.
2. Task-type breakdowns are deleted and recreated. Existing breakdown_type = task rows are removed, then regenerated from the breakdown_type = requirement rows (planned cost) and from fm_expense_line records (actual cost). Any manual edits made directly to task-type breakdown rows will be lost.
3. Test in a non-production instance first.
4. The script is intended to be run as a Fix Script or in Scripts - Background by an admin user.
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.
TABLE	pm_project or dmn_demand.
SYS_IDS	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 SYS_IDS to the affected project/demand sys_id values.
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

var DRY_RUN = true;
var TABLE = 'pm_project'; // use 'dmn_demand' for demands
var SYS_IDS = ['35c600505301561042c7730330e5e6c8']; // replace with affected sys_ids

function resetRequirementTypeActualsToZero(sys_id) {
    gs.info('Zeroing requirement breakdown actuals for task ' + sys_id);
    var grBreakdown = new GlideRecord('cost_plan_breakdown');
    grBreakdown.addQuery('breakdown_type', 'requirement');
    grBreakdown.addQuery('task', sys_id);
    grBreakdown.query();
    while (grBreakdown.next()) {
        grBreakdown.setValue('actual', 0);
        grBreakdown.update();
    }
}

function resetTaskBreakdownsCost(sys_id, table, taskRef) {
    var gr = new GlideAggregate('cost_plan_breakdown');
    gr.addQuery('breakdown_type', 'requirement');
    gr.addNotNullQuery('cost_plan');
    gr.addQuery('task', sys_id);
    gr.groupBy('fiscal_period');
    gr.groupBy('expense_type');
    gr.query();

    while (gr.next()) {
        var util = new PPMCostRollupManager(gr.getValue('fiscal_period'), gr.getValue('expense_type'));
        if (table == 'pm_project') {
            util.updateProjectEstimatedCostByFiscalPeriod(taskRef.getUniqueValue());
        } else if (table == 'dmn_demand') {
            util.updateDemandEstimatedCostByFiscalPeriod(taskRef.getUniqueValue());
        }
    }
}

function resetTaskBreakdownsActual(sys_id) {
    var gr = new GlideRecord('fm_expense_line');
    gr.addQuery('task', sys_id);
    gr.query();
    while (gr.next()) {
        var fundManager = new PPMFundManager();
        if (!gr.cost_plan.nil()) {
            fundManager.updateActualsForCostPlan(gr);
        }
        fundManager.updateActualsForTask(gr);
    }
}

function updateTaskAndCpRecordActuals(taskId) {
    var expenseLinesGr = new GlideRecord('fm_expense_line');
    expenseLinesGr.addQuery('task', taskId);
    expenseLinesGr.addQuery('state', 'processed');
    expenseLinesGr.query();
    while (expenseLinesGr.next()) {
        try {
            var helper = new ExpenseLinesHelper(expenseLinesGr);
            helper.setTaskActualCosts();

            var costPlan = new CostPlan(expenseLinesGr.cost_plan.getRefRecord());
            var updated = costPlan.updateTotalActualCost();
            if (updated) costPlan.update();
        } catch (e) {
            gs.error('Error updating task actuals for ' + taskId + ': ' + (e.message || e));
        }
    }
}

function resetTaskBreakdowns(sys_id, table) {
    var taskRef = new GlideRecord(table);
    if (!taskRef.get(sys_id)) {
        gs.warn('Record not found: ' + table + ' ' + sys_id);
        return;
    }

    if (DRY_RUN) {
        var taskBreakdowns = new GlideRecord('cost_plan_breakdown');
        taskBreakdowns.addQuery('breakdown_type', 'task');
        taskBreakdowns.addQuery('task', sys_id);
        taskBreakdowns.query();

        var reqBreakdowns = new GlideRecord('cost_plan_breakdown');
        reqBreakdowns.addQuery('breakdown_type', 'requirement');
        reqBreakdowns.addQuery('task', sys_id);
        reqBreakdowns.query();

        var expenseLines = new GlideRecord('fm_expense_line');
        expenseLines.addQuery('task', sys_id);
        expenseLines.query();

        gs.info('[DRY RUN] Would reset planned/actual breakdowns for ' + table + ' ' + sys_id +
            ' (task breakdowns: ' + taskBreakdowns.getRowCount() +
            ', requirement breakdowns: ' + reqBreakdowns.getRowCount() +
            ', expense lines: ' + expenseLines.getRowCount() + ')');
        return;
    }

    resetRequirementTypeActualsToZero(sys_id);

    var grDel = new GlideRecord('cost_plan_breakdown');
    grDel.addQuery('breakdown_type', 'task');
    grDel.addQuery('task', sys_id);
    grDel.deleteMultiple();

    resetTaskBreakdownsCost(sys_id, table, taskRef);
    resetTaskBreakdownsActual(sys_id);
    updateTaskAndCpRecordActuals(sys_id);
}

for (var i = 0; i < SYS_IDS.length; i++) {
    resetTaskBreakdowns(SYS_IDS[i], TABLE);
}

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.
5. Scope. The script operates on a single pm_project or dmn_demand record. If child project tasks also have their own expense lines, run the script for each child sys_id or adjust the query to include descendants.

Related cases and PRBs

These cases describe the same or related symptoms:

• CSTASK1543676 — Actual costs not shown in portfolio planning workspace.
• CSTASK1524920 — Expense lines and cost plans out of sync.
• CSTASK1493068 / PRB2003945 / KB1318242 — Duplicate cost plan breakdowns.
• CSTASK1541990 — Difference between project actual cost and cost plan actual cost.
• CSTASK1532786 — Expense lines add up to 0 but project actual cost is $0.02.
• CSTASK1539415 — Expense line associated with generic resource internal OPEX instead of role-based.
• CSTASK1545300 — Inconsistent total planned cost rollup from cost plans to program.
• CSTASK1361083 — Total planned cost on program greater than cost plans.
• CSTASK1537249 — Duplicate cost plan breakdown issue not resolved.
• CSTASK1496364 — Project cost plan migrated as internal after resource plan migration.
• CSTASK1545115 — Duplicate investment cost plan breakdowns.
• CSTASK1545092 — Budget decreasing because of negative expense line.