SPM Financials: PRB1999559: Duplicate investment records created when demand is converted to project

Md Asim Khan · ‎07-01-2026

Description

When a demand is converted to a project, or when SPM integration is enabled and an alignment project (sn_align_core_project) creates an execution entity (pm_project), two investment records are created in the sn_invst_pln_invst_investment table for the same planning item. One investment points at the original entity (e.g., dmn_demand) and the other at the newly created entity (e.g., pm_project).

This causes financial data (cost plans, benefit plans, budgets, expense lines) to be split across two investment records, leading to incorrect totals in the Financials tab, Strategic Planning Workspace, and Planning Console.

Steps to Reproduce

Scenario 1: Demand to Project Conversion

Create a demand (dmn_demand) — an investment record is created automatically
Convert the demand to a project — a second investment record is created for the pm_project
Navigate to sn_invst_pln_invst_investment_list.do and filter by the project's sys_id in funding_entity_id
Notice two investment records exist

Affected Tables

The following tables reference sn_invst_pln_invst_investment and may have records split across duplicate investments:

cost_plan (field: investment)
benefit_plan (field: investment)
sn_invst_pln_invst_budget (field: investment)
fm_expense_line (field: investment)
cost_plan_breakdown (field: investment)
cost_plan_baseline (field: investment)
benefit_plan_breakdown (field: investment)
benefit_plan_baseline (field: investment)
sn_invst_pln_invst_budget_baseline (field: investment)
sn_invst_pln_invst_investment_baseline_header (field: investment)
sn_invst_pln_invst_investment_baseline (field: investment_origin)
sn_align_core_planning_item (field: investment)

Workaround

A fix script is attached to this article. The script:

Finds all pm_project records that also have a linked demand
Checks if both a demand-based and a project-based investment exist
Keeps the older investment record.
Migrates all child records (cost plans, benefit plans, budgets, expense lines, breakdowns, baselines) to the surviving investment
Updates planning items to reference the surviving investment
Repoints the surviving investments funding_entity to the project
Deletes the duplicate investment

Important: Run the Remove_duplicate_investments.txt from the sn_invst_pln application scope to avoid cross-scope access policy errors on delete.

Set CONFIG.dryRun = true first to preview which records will be affected before executing.

arijeetdev · ‎08-04-2026

Hi All,

[Updated]After running the script in this article ,If you face issue where duplicate investment got deleted but the investment reference on demand and project still points to demand's investment then use the below code that takes care of fixing duplicates and all related issue -

// Remove duplicate investments created during demand-to-project conversion.
//
// For each explicitly selected project with a linked demand, this script:
// - Uses configurable demand and project tables; both must use the project.demand
//   relationship followed by the OOB demand-to-project conversion flow.
// - Finds the demand and project investments and keeps the older record.
// - Updates investment references on plans, budgets, expense lines, breakdowns,
//   baseline records, and planning items to the surviving investment.
// - Uses normal GlideRecord.update() calls for child records, so applicable
//   Business Rules run.
// - Does not directly invoke financial, breakdown, budget, or baseline
//   regeneration APIs.
// - Reports Budget v1 project_funding records if found but does not change them
// - Deletes the duplicate before repointing the survivor to the project. This
//   prevents the project/demand investment validation Business Rule from
//   rejecting the repoint while another investment still targets the project.
// - Verifies that the survivor targets the project and that no covered record
//   continues to reference the deleted investment.
//
// Run from the sn_invst_pln (Financials core) application scope with explicit project sys_ids and
// dryRun=true first to verify the changes. Use roll back true or record for rollback checked so that the changes done by script can be rolled back if needed.

(function fixDuplicateInvestmentsAndPlanningItems() {

    var CONFIG = {
        dryRun: true,
        demandTable: 'dmn_demand', // Put table name accordingly
        projectTable: 'pm_project', // Put table name accordingly
        projectSysIds: [
            // Add sys_ids from the configured project table as quoted, comma-separated values.
            // Example:
            // '0123456789abcdef0123456789abcdef',
            // 'fedcba9876543210fedcba9876543210'
        ]
    };
    var LOG_PREFIX = 'PRB1999559/PRB1928809: ';
    var INVESTMENT_TABLE = 'sn_invst_pln_invst_investment';
    var INVESTMENT_BASELINE_TABLE = 'sn_invst_pln_invst_investment_baseline';
    var PLANNING_ITEM_TABLE = 'sn_align_core_planning_item';
    var INVESTMENT_REFERENCE_TABLES = [
        'cost_plan',
        'benefit_plan',
        'sn_invst_pln_invst_budget',
        'fm_expense_line',
        'cost_plan_breakdown',
        'cost_plan_baseline',
        'benefit_plan_breakdown',
        'benefit_plan_baseline',
        'sn_invst_pln_invst_budget_baseline',
        'sn_invst_pln_invst_investment_baseline_header'
    ];
    var stats = {
        scanned: 0,
        eligible: 0,
        fixed: 0,
        skipped: 0,
        errors: 0
    };

    if (!CONFIG.projectSysIds.length) {
        gs.error(LOG_PREFIX + 'No project sys_ids configured. No records were changed.');
        return;
    }

    gs.info(LOG_PREFIX + 'Starting (dryRun=' + CONFIG.dryRun + ').');

    var project = new GlideRecord(CONFIG.projectTable);
    project.addQuery('sys_id', 'IN', CONFIG.projectSysIds.join(','));
    project.addNotNullQuery('demand');
    project.query();

    while (project.next()) {
        stats.scanned++;

        try {
            repairProject(project);
        } catch (error) {
            stats.errors++;
            gs.error(LOG_PREFIX + project.getValue('number') + ': ' + (error.message || error));
        }
    }

    gs.info(LOG_PREFIX + 'Completed: ' + JSON.stringify(stats));

    function repairProject(projectGr) {
        var projectId = projectGr.getUniqueValue();
        var demandId = projectGr.getValue('demand');
        var demandInvestment = getSingleInvestment(CONFIG.demandTable, demandId);
        var projectInvestment = getSingleInvestment(CONFIG.projectTable, projectId);

        if (!demandInvestment || !projectInvestment) {
            stats.skipped++;
            gs.info(LOG_PREFIX + projectGr.getValue('number') + ': duplicate investment pair not found.');
            return;
        }

        var demandCreated = new GlideDateTime(demandInvestment.getValue('sys_created_on'));
        var projectCreated = new GlideDateTime(projectInvestment.getValue('sys_created_on'));
        var keepInvestment = demandCreated.compareTo(projectCreated) <= 0 ?
            demandInvestment : projectInvestment;
        var deleteInvestment = keepInvestment.getUniqueValue() === demandInvestment.getUniqueValue() ?
            projectInvestment : demandInvestment;
        var keepId = keepInvestment.getUniqueValue();
        var deleteId = deleteInvestment.getUniqueValue();
        var projectFundingEntity = getProjectFundingEntity(projectGr);
        var budgetState = getBudgetState(projectGr);

        stats.eligible++;

        gs.info(LOG_PREFIX + projectGr.getValue('number') +
            ' | KEEP=' + keepId + ' (' + keepInvestment.getValue('sys_created_on') + ')' +
            ' | DELETE=' + deleteId + ' (' + deleteInvestment.getValue('sys_created_on') + ')');

        logBudgetState(projectGr, demandId, keepId, deleteId, budgetState);
        migrateInvestmentReferences(deleteId, keepId);
        migrateReference(INVESTMENT_BASELINE_TABLE, 'investment_origin', deleteId, keepId);
        migrateReference(PLANNING_ITEM_TABLE, 'investment', deleteId, keepId);

        if (CONFIG.dryRun)
            return;

        if (!deleteInvestment.deleteRecord())
            throw new Error('Failed to delete duplicate investment ' + deleteId);

        repointInvestmentToProject(keepInvestment, projectGr, projectFundingEntity);
        verifyRepair(projectGr, keepId, deleteId);
        stats.fixed++;
    }

    function getSingleInvestment(entityTable, entityId) {
        var investment = new GlideRecord(INVESTMENT_TABLE);
        investment.addQuery('funding_entity_table', entityTable);
        investment.addQuery('funding_entity_id', entityId);
        investment.query();

        var count = investment.getRowCount();
        if (count === 0)
            return null;
        if (count !== 1)
            throw new Error('Expected one ' + entityTable + ' investment for ' + entityId + ', found ' + count);

        investment.next();
        return investment;
    }

    function getProjectFundingEntity(projectGr) {
        var fundingEntity = new InvstInvestment().getFundingEntityGr(
            projectGr.getRecordClassName(), projectGr.getUniqueValue());
        var fundingEntityApi = new InvstFundingEntity(fundingEntity);

        if (!fundingEntityApi.isValidRecord() || !fundingEntityApi.isActive())
            throw new Error('No active funding entity for ' + projectGr.getRecordClassName());

        return fundingEntity;
    }

    function getBudgetState(projectGr) {
        var budgetV2Enabled = gs.getProperty('sn_invst_pln.enable_budget_allocation_v2') === 'true';
        return {
            v2Enabled: budgetV2Enabled,
            migrated: budgetV2Enabled && new InvestmentBudgetMigrationAPI().isBudgetMigrated(projectGr)
        };
    }

    function logBudgetState(projectGr, demandId, keepId, deleteId, budgetState) {
        var projectFundingCount = countRecords('project_funding', 'task', projectGr.getUniqueValue());
        var demandFundingCount = countRecords('project_funding', 'task', demandId);

        gs.info(LOG_PREFIX + projectGr.getValue('number') +
            ' | budgetV2Enabled=' + budgetState.v2Enabled +
            ' | budgetMigrated=' + budgetState.migrated +
            ' | keepInvestmentBudgets=' + countRecords('sn_invst_pln_invst_budget', 'investment', keepId) +
            ' | deleteInvestmentBudgets=' + countRecords('sn_invst_pln_invst_budget', 'investment', deleteId) +
            ' | projectFunding=' + projectFundingCount +
            ' | demandFunding=' + demandFundingCount);

        if ((!budgetState.v2Enabled || !budgetState.migrated) &&
                (projectFundingCount > 0 || demandFundingCount > 0)) {
            gs.warn(LOG_PREFIX + projectGr.getValue('number') +
                ': Budget v1 Project Funding records exist and require separate validation.');
        }
    }

    function migrateInvestmentReferences(fromId, toId) {
        for (var index = 0; index < INVESTMENT_REFERENCE_TABLES.length; index++) {
            migrateReference(INVESTMENT_REFERENCE_TABLES[index], 'investment', fromId, toId);
        }
    }

    function migrateReference(table, field, fromId, toId) {
        var record = new GlideRecord(table);
        record.addQuery(field, fromId);
        record.query();
        var count = record.getRowCount();

        if (!CONFIG.dryRun) {
            while (record.next()) {
                record.setValue(field, toId);
                record.update();
            }
        }

        if (count > 0)
            gs.info(LOG_PREFIX + table + '.' + field + ': ' + count + ' record(s) ' + fromId + ' -> ' + toId);
    }

    function repointInvestmentToProject(investment, projectGr, fundingEntity) {
        investment.setValue('funding_entity', fundingEntity.getUniqueValue());
        investment.setValue('funding_entity_table', projectGr.getRecordClassName());
        investment.setValue('funding_entity_id', projectGr.getUniqueValue());
        investment.setValue('name', projectGr.getClassDisplayValue() + ': ' +
            projectGr.getValue('short_description'));
        investment.setValue('owner', '');

        if (!investment.update())
            throw new Error('Failed to repoint surviving investment ' + investment.getUniqueValue());
    }

    function verifyRepair(projectGr, keepId, deleteId) {
        var survivor = new GlideRecord(INVESTMENT_TABLE);
        if (!survivor.get(keepId))
            throw new Error('Surviving investment no longer exists: ' + keepId);

        if (survivor.getValue('funding_entity_table') !== projectGr.getRecordClassName() ||
                survivor.getValue('funding_entity_id') !== projectGr.getUniqueValue()) {
            throw new Error('Surviving investment was not repointed to project ' + projectGr.getUniqueValue());
        }

        var duplicate = new GlideRecord(INVESTMENT_TABLE);
        if (duplicate.get(deleteId))
            throw new Error('Duplicate investment still exists: ' + deleteId);

        for (var index = 0; index < INVESTMENT_REFERENCE_TABLES.length; index++) {
            assertNoReference(INVESTMENT_REFERENCE_TABLES[index], 'investment', deleteId);
        }

        assertNoReference(INVESTMENT_BASELINE_TABLE, 'investment_origin', deleteId);
        assertNoReference(PLANNING_ITEM_TABLE, 'investment', deleteId);
    }

    function assertNoReference(table, field, investmentId) {
        if (countRecords(table, field, investmentId) > 0)
            throw new Error(table + '.' + field + ' still references deleted investment ' + investmentId);
    }

    function countRecords(table, field, value) {
        var aggregate = new GlideAggregate(table);
        aggregate.addQuery(field, value);
        aggregate.addAggregate('COUNT');
        aggregate.query();
        return aggregate.next() ? parseInt(aggregate.getAggregate('COUNT'), 10) : 0;
    }
})();

¯

zainabmemon · ‎08-17-2026

child table records are not migrating into keeped investment.

arijeetdev

@zainabmemon You can use the latest updated script. Hopefully this should solve issue for all child table as well