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.