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

[Md Asim Khan]

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

1. Create a demand (dmn_demand) — an investment record is created automatically
2. Convert the demand to a project — a second investment record is created for the pm_project
3. Navigate to sn_invst_pln_invst_investment_list.do and filter by the project's sys_id in funding_entity_id
4. 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:

1. Finds all pm_project records that also have a linked demand
2. Checks if both a demand-based and a project-based investment exist
3. Keeps the older investment record.
4. Migrates all child records (cost plans, benefit plans, budgets, expense lines, breakdowns, baselines) to the surviving investment
5. Updates planning items to reference the surviving investment
6. Repoints the surviving investments funding_entity to the project
7. 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]

Hi All, 

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 - 

(function fixDuplicateInvestmentsAndPlanningItems() {

    var CONFIG = {
        investmentQuery: '',
        dryRun: false
    };
    var INVESTMENT_TABLE = 'sn_invst_pln_invst_investment';
    var PLANNING_ITEM_TABLE = 'sn_align_core_planning_item';
    var FUNDING_ENTITY_TABLE = 'sn_invst_pln_invst_funding_entity';
    var CHILD_TABLES = ['cost_plan', 'benefit_plan', 'sn_invst_pln_invst_budget', 'project_funding'];

    var fixed = 0;
    gs.info('PRB1928809: Starting duplicate investment fix...');

    var project = new GlideRecord('pm_project');
    project.addNotNullQuery('demand');
    project.query();

    while (project.next()) {
        try {
            var projectId = project.getUniqueValue();
            var demandId = project.getValue('demand');

            var demandInv = findInvestment('dmn_demand', demandId);
            if (!demandInv) continue;
            var projectInv = findInvestment('pm_project', projectId);
            if (!projectInv) continue;

            var demandCreated = new GlideDateTime(demandInv.getValue('sys_created_on'));
            var projectCreated = new GlideDateTime(projectInv.getValue('sys_created_on'));
            var keepInv = demandCreated.compareTo(projectCreated) <= 0 ? demandInv : projectInv;
            var deleteInv = keepInv === demandInv ? projectInv : demandInv;
            var keepId = keepInv.getUniqueValue();
            var deleteId = deleteInv.getUniqueValue();

            gs.info('PRB1928809: Project ' + project.getValue('number') +
                    ' | KEEP=' + keepId + ' (' + keepInv.getValue('sys_created_on') + ')' +
                    ' | DELETE=' + deleteId + ' (' + deleteInv.getValue('sys_created_on') + ')');

            if (CONFIG.dryRun) continue;
            // Fixed (correct order):
           migrateChildren(deleteId, keepId);
           updatePlanningItems(deleteId, keepId);
           deleteInv.deleteRecord();             // ← DELETE FIRST: remove the duplicate
           migrateToProject(keepInv, project);   // ← NOW repoint succeeds: no duplicate
           fixed++;
        } catch (e) {
            gs.error('PRB1928809: Error on project ' + project.getValue('number') + ': ' + e);
        }
    }
    gs.info('PRB1928809: Completed. Fixed=' + fixed);

    function findInvestment(table, id) {
        var inv = new GlideRecord(INVESTMENT_TABLE);
        inv.addQuery('funding_entity_table', table);
        inv.addQuery('funding_entity_id', id);
        if (CONFIG.investmentQuery) inv.addEncodedQuery(CONFIG.investmentQuery);
        inv.query();
        return inv.next() ? inv : null;
    }

    function migrateChildren(fromId, toId) {
        for (var i = 0; i < CHILD_TABLES.length; i++) {
            var gr = new GlideRecord(CHILD_TABLES[i]);
            gr.addQuery('investment', fromId);
            gr.query();
            var count = 0;
            while (gr.next()) {
                gr.setValue('investment', toId);
                gr.update();
                count++;
            }
            if (count > 0) gs.info('PRB1928809: Migrated ' + count + ' from ' + CHILD_TABLES[i]);
        }
    }

    function updatePlanningItems(deleteId, keepId) {
        var pi = new GlideRecord(PLANNING_ITEM_TABLE);
        pi.addQuery('investment', deleteId);
        pi.query();
        var count = 0;
        while (pi.next()) {
            pi.setValue('investment', keepId);
            pi.update();
            count++;
        }
        if (count > 0) gs.info('PRB1928809: Updated ' + count + ' planning items: ' + deleteId + ' -> ' + keepId);
    }

    function migrateToProject(inv, proj) {
        var fe = new GlideRecord(FUNDING_ENTITY_TABLE);
        fe.addQuery('entity_table', proj.getRecordClassName());
        fe.addQuery('active', true);
        fe.query();
        if (!fe.next()) throw new Error('No funding entity for ' + proj.getRecordClassName());
        inv.setValue('funding_entity', fe.getUniqueValue());
        inv.setValue('funding_entity_table', proj.getRecordClassName());
        inv.setValue('funding_entity_id', proj.getUniqueValue());
        inv.setValue('name', proj.getClassDisplayValue() + ': ' + proj.getValue('short_description'));
        inv.setValue('owner', '');
        inv.update();
    }
})();