- Post History
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
50m ago - edited 12m ago
SPM Financials:
Calculations & Rollup Flow
A complete reference for Project and Demand financials — source-of-truth tables, metric formulas, breakdown types, rollup hierarchy, and common Q&A.
Every financial field on a Project or Demand is a cached aggregate, not a live query. It is recalculated from one of three source tables when underlying records change.
Capex & Opex
cost_plancost_plan_breakdown
One cost plan per named estimate. Breakdowns store one row per fiscal period.
benefit_planbenefit_plan_breakdown
One benefit plan per expected return stream. Breakdowns per fiscal period.
fm_expense_line
Only lines with state = 'processed' count. Draft, submitted, and pending-approval lines are excluded.
sn_invst_pln_invst_budgetproject_funding (legacy)
Approved funding. Separate from planned cost — managed independently.
What is an Investment?
An Investment (sn_invst_pln_investment) is the record through which a Demand or Project participates in portfolio financial planning — receiving budget from a portfolio manager and being tracked in SPW. One Investment per work item.
funding_entity_id → pm_project / dmn_demand. The work item has no back-reference field. To find the Investment for a demand, query sn_invst_pln_investment where funding_entity_id = demand.sys_id.Budget v2 vs Budget v1 (Legacy)
| v1 — Legacy | v2 — Current | |
|---|---|---|
| Source table | project_funding |
sn_invst_pln_invst_budget |
| Budget granularity | Yearly — one record per year | Per fiscal period × expense type or cost type |
| Distributed into | cost_plan_breakdown.budget per fiscal period and expense type |
cost_plan_breakdown.budget per fiscal period, split by expense type (opex/capex) or cost type — controlled by the budget_allocation_attribute property |
sn_invst_pln_financial_migration.is_budget_migrated first. The flag lives on the migration record linked to the work item, not on the work item itself.| Stream | Source → Path | Work item field |
|---|---|---|
| 1 — Planned Cost | cost_plan_breakdown (value, fiscal_period) → cost_plan (by expense type or cost type, per property) |
estimated_costplanned_capital / planned_operating |
| 2 — Planned Benefit | benefit_plan_breakdown (value, fiscal_period) → benefit_plan |
estimated_benefit |
| 3 — Actual Cost | fm_expense_line (state = 'processed') → task/project → cost_plan_breakdown (actual field) |
actual_cost |
| 4 — Budget | Portfolio manager approves in SPW → sn_invst_pln_invst_budget → cost_plan_breakdown task type, per fiscal period |
budget_cost |
| 5 — Actual Benefit | Realized benefit tracked per fiscal period → benefit_plan_breakdown (actual_benefit field) |
actual_benefit |
What triggers each level
| Transition | Trigger |
|---|---|
| Breakdown → cost_plan | Business rule on cost_plan_breakdown insert / update / delete → CostPlan.updateTotalPlannedCost() |
| cost_plan → work item | Business rule on cost_plan save recalculates estimated_cost, planned_capital, planned_operating |
| benefit_plan → work item | Business rule on benefit_plan save recalculates estimated_benefit |
| Expense line → cost_plan_breakdown → work item | Business rule fires when fm_expense_line.state transitions to or from 'processed' → updates actual field on matching breakdown → rollup recalculates actual_cost on work item |
Capex vs Opex Classification
Expense type is a plain string field (capex or opex) stored on resource_type_definition. Cost type is derived from the resource_type_definition reference field on the cost plan — each resource_type_definition record represents one cost type, and carries an expense type string of either capex or opex. Multiple cost types can share the same expense type.
The system property sn_invst_pln.budget_allocation_attribute (values: expense_type / cost_type) controls how cost plan breakdowns are generated: by expense type (one capex + one opex row per fiscal period) or by individual cost type (one row per resource_type_definition per fiscal period).
| resource_type_definition.budget_allocation_attribute | Rolls up to |
|---|---|
capex |
Planned Capital |
opex |
Planned Operating |
All breakdowns live in a single table (cost_plan_breakdown) and are distinguished by the breakdown_type field.
Source: fm_expense_line (state = 'processed' only)
Only lines in the processed state count toward Actual Cost. Submitted or pending-approval timesheets are not included.
| Source of expense lines | Notes |
|---|---|
| Timesheets | Hours × rate from the work item's rate_model. No rate model = $0 actuals even with approved timesheets. |
| Expense reports | Direct non-labor costs |
| External integrations | Jira, Workday, ERP feeds |
| Manual entry | Ad-hoc cost recording |
Critical Difference: Project Actuals vs Demand Actuals
| Work item | Actuals source | Link to cost plan |
|---|---|---|
| pm_project | Expense lines on the project or its tasks | Can be traced to cost plan breakdowns |
| dmn_demand | Expense lines on demand tasks only (dmn_demand_task) |
No direct linkage between expense lines and demand cost plans. Cost plans on a demand are planning artifacts. Actuals come in independently via tasks. |
SUM(cost_plan_breakdown.value)SUM(benefit_plan_breakdown.value)Total Planned Cost − Planned Benefit(Planned Benefit − Total Planned Cost) / Total Planned Cost × 100Actuals (past FPs) + Planned Cost (current & future FPs)Actuals (past FPs) + Planned Cost (current & future FPs till end)SUM(sn_invst_pln_invst_budget.amount) per fiscal periodΣ [ (benefit_t − cost_t) / (1 + i)^t ]
where i = Discount Rate % / 100NPV = 0 (solved iteratively)Two sides of the same work
When the PPM–SPW integration is enabled, every financial work item has two representations:
| Side | Record | Who uses it |
|---|---|---|
| Planning item | sn_align_core_planning_item(subtypes: sn_align_core_project, sn_align_core_demand) |
Portfolio Manager in SPW — sees financials, allocates budget, monitors forecasts |
| Execution item | pm_project / dmn_demand |
Project/Demand Manager — manages tasks, timesheets, cost plans, resources |
The sn_invst_pln_investment record bridges both sides — it aggregates the financial picture from the execution item and exposes it to the planning side. Each cost_plan_breakdown row carries a planning_item reference field that ties it directly to its SPW counterpart.
How financial data syncs
| Financial data | Condition |
|---|---|
| Planned cost, Capex, Opex | When no cost plan exists on the investment, planning item cost fields sync directly from investment. When cost plans exist, they are the source of truth. |
| Actual cost, Actual Capex/Opex | Business rule on investment save syncs work_cost, capex_work_cost, opex_work_cost to the planning item's actual_cost, actual_capex, actual_opex |
| Planned benefit, Actual benefit | Same pattern — when no benefit plan exists, planning item benefit fields sync from investment directly |
| Budget | Portfolio manager allocates budget in SPW; distributed into task type breakdowns per fiscal period × expense type or cost type |
Without integration vs with integration
| Integration OFF | Integration ON (com.sn_align_snow_int) |
|
|---|---|---|
| Planning item type | Standalone sn_align_core_planning_item — not backed by a PPM execution item |
sn_align_core_project or sn_align_core_demand — planning item is directly linked to a PPM execution item |
| Financial sync | Investment fields push directly to planning item on save | Dedicated work-item business rules handle the sync, keeping execution-side cost plans and actuals in lock-step with the planning item |
planning_item field on breakdown |
Blank | Populated — each breakdown row is tied to its SPW planning item |
sn_align_core_project or sn_align_core_demand, the generic investment → planning item sync is skipped. Work-item-specific business rules take over to avoid double-syncing.What controls whether financials are available on a planning item
Financials are available on a planning item regardless of integration state. Whether integration is active is determined by an entry in sn_align_cmn_int_integrations_setup for the planning item's table — if present, execution-side cost plans and actuals drive the financials; if absent, they sync directly from the investment record on save.
Portfolio Manager — SPW
Demand / Project Manager
What is a Baseline?
A baseline is a point-in-time snapshot of financial data, taken when a project or demand reaches an approved state. It freezes the current cost plans, benefit plans, and budget so that actual vs baseline comparisons can be made later as the project evolves.
Baselines do not affect live calculations. They are read-only reference copies stored in parallel tables.
Baseline Tables
| Live table | Baseline snapshot table |
|---|---|
cost_plan |
cost_plan_baseline |
cost_plan_breakdown |
cost_plan_breakdown_baseline |
benefit_plan |
benefit_plan_baseline |
benefit_plan_breakdown |
benefit_plan_breakdown_baseline |
sn_invst_pln_invst_budget |
sn_invst_pln_invst_budget_baseline |
pm_project / dmn_demand |
pm_project_baseline / dmn_demand_baseline |
For investments, the baseline is grouped under a Baseline Header (sn_invst_pln_invst_investment_baseline_header) — a named, timestamped record that groups all snapshot rows belonging to a single baseline event. A work item can have multiple baseline headers over its lifetime (initial, re-baseline, final).
What is captured in a baseline?
- Cost plan breakdowns: Every
cost_plan_breakdown.valueper fiscal period, plus the budget field and Capex/Opex classification at that point in time. - Benefit plan breakdowns: Every
benefit_plan_breakdown.valueper fiscal period. - Budget: The approved budget amounts per fiscal period from
sn_invst_pln_invst_budget. - Multicurrency variants: Snapshot tables include project-currency and demand-currency fields so baseline comparisons work across currencies.
fm_expense_line records with state = 'processed' count. Submitted timesheets that are not yet approved remain in a pre-processed state and are excluded from the rollup.dmn_demand_task. There is no direct linkage between fm_expense_line and a demand's cost plan. Cost plans on a demand are planning artifacts only — they do not generate or absorb actuals.sn_invst_pln_financial_migration.is_budget_migrated for the work item. If true, use sn_invst_pln_invst_budget. Otherwise use project_funding. Never sum across both tables for the same record.cost_plan_breakdown distinguished by breakdown_type. Requirement breakdowns track planned cost at the cost plan level — one row per cost plan, per fiscal period — used for cost estimation. Task breakdowns aggregate at the project/demand level and are the target where budget allocation distributes approved funds, one row per fiscal period.dmn_demand pointing to its Investment. Query sn_invst_pln_investment where funding_entity_id = <demand_sys_id>.- 53 Views
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Fantastic summary! Thank you!