Commitment Views (BI schema)

← Back to CVR Index Definition Page → Report Changes Page → Line Level Design Page → Reconciliation Page →

The Two Views

Commitment is defined once, in two database views on the Focalpoint SQL Server, and consumed everywhere: the rebuilt ProjectCommitments report, the CVR feed (issue 18), and any future point in time work (issue 29). One definition, one source of truth. Full commented SQL is on the Report Changes page (order level) and the Line Level Design page.

[BI].[Commitment_Order]

Grain: one row per Purchase Order. The headline commitment figures: order value, posted value (with Labour/Plant/Materials/Subcontract splits), Commitment_Raw (true value, may be negative), Commitment (floored at zero), OverInvoiced flag, and category commitment columns apportioned from the order's cost centre mix so they always sum to the total.

[BI].[Commitment_OrderLine]

Grain: one row per Purchase Order Line, plus one 'Unallocated' row per order with excess. The display version: line descriptions, cost centres and line keys, with posted value capped at each line's value and misallocated money shown on the Unallocated row. Each order's rows sum exactly to [BI].[Commitment_Order]'s floored commitment. The base view is [BI].[Commitment_OrderLine_Base]; it also exposes LineOverInvoicedAmount (the excess invoiced above each line's value, 0 otherwise; per-order it sums to the Unallocated row's posted value) plus the two flags OrderOverInvoiced / OrderLineOverInvoiced.

Design Decisions

Decision Rationale

Values only, never quantities Commitment is money, not units. Focalpoint's quantities are unreliable (lump sum lines, auto-splits). The old report's quantity gate (POD_QTYORD = POD_QTYINVD forcing zero) hid over-invoices; there is no quantity reference anywhere in either view.

Whole order truth, line level display Order totals survive Focalpoint's auto-split corruption; line values do not. So commitment is only ever asserted at order level, and the line view is presentation over the top, with the cap and pool mechanism keeping lines honest.

Posted only, credits netted at source Only ledger transactions (DET_LEDGER = 'PL', types INV/CRN) discharge commitment; the register is invisible by design (matching is what links and posts). The CRN sign flip happens once, in the posted aggregation, so every downstream column inherits correct credit handling. This kills the old report's category credit bug.

Cap and pool (line view) A line's posted value is capped at the line's value: a line cannot be invoiced for more than it is worth. Excess above the cap is misallocated by definition and pools into the order's Unallocated row, which nets it against the order's remaining commitment. Capped lines plus the Unallocated row always equal everything genuinely posted.

Floor at zero, flag the overrun Commitment never goes negative. The order view keeps the true value in Commitment_Raw; the line view implements the floor via "net off the smaller of excess and remaining". Both carry OverInvoiced = Y where posted exceeds ordered. Nothing is hidden, nothing distorts the project total.

Category split by apportionment (order view) Category commitment is apportioned from the order's own cost centre mix, so columns sum to the total by construction. The PostedValue_* columns are categorised by the invoice's cost centre and can legitimately disagree with the order's mix; they report what was invoiced as Labour, not what Labour commitment was released.

ROUND(…, 2) on all money POD_NETT is stored as float; unrounded output produced values like 7170.19999999999.

No ORDER BY, no parameter Views allow neither. Consumers filter WHERE [ProjectCode] = @CH_CODE and sort by Supplier, OrderNumber, LineNumber with LineKey as tiebreak.

LineKey in the output Focalpoint reuses line numbers (912364 has two line 2s; 920438, 914382, 913556 and 923899 all have duplicate line 1s), so LineKey (POD_PRIMARY) is the only unique row identity. NULL on Unallocated rows.

Status filter pending Placeholder in the Lines/Orders source awaiting definition question 5: which Purchase Order statuses release commitment. Becomes a WHERE clause when Finance answer.

Display names aliased in the proc, not the view The view keeps its internal names (OrderValue, PostedValue) so the SSRS report dataset is untouched. The Excel-facing proc [BI].[Commitment_OrderLine] aliases them for the CVR tab: OrderValue → OrderAmount, PostedValue → CappedInvoicedAmount (capped at the line value), and selects LineOverInvoicedAmount after Commitment. So CappedInvoicedAmount + LineOverInvoicedAmount = total invoiced on the line.

Decision	Rationale
Values only, never quantities	Commitment is money, not units. Focalpoint's quantities are unreliable (lump sum lines, auto-splits). The old report's quantity gate (`POD_QTYORD = POD_QTYINVD` forcing zero) hid over-invoices; there is no quantity reference anywhere in either view.
Whole order truth, line level display	Order totals survive Focalpoint's auto-split corruption; line values do not. So commitment is only ever asserted at order level, and the line view is presentation over the top, with the cap and pool mechanism keeping lines honest.
Posted only, credits netted at source	Only ledger transactions (`DET_LEDGER = 'PL'`, types INV/CRN) discharge commitment; the register is invisible by design (matching is what links and posts). The CRN sign flip happens once, in the posted aggregation, so every downstream column inherits correct credit handling. This kills the old report's category credit bug.
Cap and pool (line view)	A line's posted value is capped at the line's value: a line cannot be invoiced for more than it is worth. Excess above the cap is misallocated by definition and pools into the order's Unallocated row, which nets it against the order's remaining commitment. Capped lines plus the Unallocated row always equal everything genuinely posted.
Floor at zero, flag the overrun	Commitment never goes negative. The order view keeps the true value in `Commitment_Raw`; the line view implements the floor via "net off the smaller of excess and remaining". Both carry `OverInvoiced` = Y where posted exceeds ordered. Nothing is hidden, nothing distorts the project total.
Category split by apportionment (order view)	Category commitment is apportioned from the order's own cost centre mix, so columns sum to the total by construction. The `PostedValue_*` columns are categorised by the invoice's cost centre and can legitimately disagree with the order's mix; they report what was invoiced as Labour, not what Labour commitment was released.
ROUND(…, 2) on all money	`POD_NETT` is stored as float; unrounded output produced values like 7170.19999999999.
No ORDER BY, no parameter	Views allow neither. Consumers filter `WHERE [ProjectCode] = @CH_CODE` and sort by `Supplier, OrderNumber, LineNumber` with `LineKey` as tiebreak.
LineKey in the output	Focalpoint reuses line numbers (912364 has two line 2s; 920438, 914382, 913556 and 923899 all have duplicate line 1s), so `LineKey` (`POD_PRIMARY`) is the only unique row identity. NULL on Unallocated rows.
Status filter pending	Placeholder in the Lines/Orders source awaiting definition question 5: which Purchase Order statuses release commitment. Becomes a WHERE clause when Finance answer.
Display names aliased in the proc, not the view	The view keeps its internal names (`OrderValue`, `PostedValue`) so the SSRS report dataset is untouched. The Excel-facing proc `[BI].[Commitment_OrderLine]` aliases them for the CVR tab: `OrderValue → OrderAmount`, `PostedValue → CappedInvoicedAmount` (capped at the line value), and selects `LineOverInvoicedAmount` after Commitment. So `CappedInvoicedAmount + LineOverInvoicedAmount = total invoiced on the line`.

Example Records for the Nuances

All from project 446459 (Maidenhead Grid), run 10 Sep 2026. Values will move as new invoices post.

1. The auto-split order — Purchase Order 920438 (Microll Engineering)

Focalpoint split one £18,492.48 line into £11,095.49 and £7,396.99 on a part invoice, and misallocated the postings (£14,793.98 was matched against the £11,095.49 line). The order is in reality fully invoiced.

Row	Ordered	Posted (shown)	Commitment
Line 1	11,095.49	11,095.49 (capped from 14,793.98)	0.00
Line 2	7,396.99	3,698.50	3,698.49
Unallocated invoices	—	3,698.49	−3,698.49
Order total	18,492.48	18,492.48	0.00

The old report showed £3,698.49 phantom commitment on this order. Both lines carry POD_LINE_NUMBER 1, which is why LineKey exists.

2. Genuinely over-invoiced — Purchase Order 925549 (PT Contractors)

£66,979.81 posted against a £34,348.62 order: the full value invoice was matched to the £1,717.43 retention line, and £32,631.19 was also posted to line 1, a suspected duplicate. Both lines show zero commitment (capped), the Unallocated row shows the £32,631.19 excess with zero commitment (there is nothing left to net; the floor), and every row is flagged OverInvoiced = Y. In [BI].[Commitment_Order], Commitment_Raw = −32,631.19. Referred to Finance.

3. Misallocation across lines, order fine — Purchase Order 914382 (Cable Services)

Two lines, invoices swapped: £7,030 posted against the £7,000 line and £1,958.45 against the £1,988.45 line. Line view: £30 excess pools to the Unallocated row and nets off the £30 shown on the under-posted line. Order total: zero commitment, flag N. The old report showed £30 phantom commitment.

4. The zero floor on a small overrun — Purchase Order 918053 (Apex)

Hire ran over: £1,272 posted against a £1,148 order. Old report showed commitment of −£124, quietly reducing the project total. Now: all lines capped, Unallocated row carries the £124 excess with zero commitment, flag Y, project total unaffected.

5. Value beats quantity by a penny — Purchase Order 918055 (Sunbelt)

Line ordered at £1,508.35, invoiced £1,508.34. The old quantity gate called it finished; the value rule shows the true £0.01 commitment. Trivial money, but proof the calculation is value based.

6. Credits reopen commitment — Purchase Order 922465 (Celtic Recycling)

Order description says it best: "£73,150 less £60,140 credit". Invoices net of credits leave £4,336.67 posted against the £13,010 order, so £8,673.33 commitment remains. The CRN sign flip at source is what makes this work everywhere; the old report netted credits correctly in its total but added them the wrong way in its category columns (911788 line 1: Transaction_Total £1,798 against Transaction_Plant £2,294).

7. Retention lines — Purchase Order 912364 (PT Contractors)

The £166,656.56 retention line correctly shows full commitment (nothing posted against it). But note the same order's line 1: £389.50 ordered, £38,950.13 posted, roughly 100×, a suspected keying error, and invoices are sometimes matched to retention lines (921853). The cap and pool design absorbs all of it: the order's Unallocated row nets £38,560.63 and the order lands on the true £2,732,727.93.

Consumers and Verification

Consumers: the rebuilt ProjectCommitments report (line view), the CVR commitment tab feed (issue 18), and any summary reporting (order view). All filter by ProjectCode and sort themselves.
Verified 10 Sep 2026 on project 446459: both views agree with each other, and every difference from the old report (£42,177.52 total) reconciles to a named defect. See the Reconciliation page and spreadsheet.
Watch: performance on large projects (the posted aggregation covers the whole transaction table pre-filter), and the open questions on the Fix Detail page (statuses, orphaned invoice links, order number column on transactions).