Commitments Fix Detail — NG Bailey CVR

← Back to CVR Index Email Summary Page →

Status (09 Sep 2026): Working detail behind the Finance email. Captures what is wrong with the current commitment calculations and what the fix needs to cover. Tracker issue 15 (also resolves 24 and 33). Focalpoint system changes are out of scope; the fix is at report level.

The Report Being Fixed

ProjectCommitments (SSRS)
URL: http://sql-reports/Reports/report/Freedom/FreedomFinance/SSRS Reports/ProjectCommitments
RDL copy: Customers\NG Bailey\_context\SSRS Commitments\ProjectCommitments.rdl
Parameter: project number (CH_CODE). One row per PO line, grouped by project, sorted by supplier, order, line.

Source tables: POP_DETAIL (PO lines), PL_ACCOUNTS (suppliers), CST_COSTHEADER (projects), SL_PL_NL_DETAIL (ledger transactions, filtered to DET_LEDGER = 'PL' and types INV / CRN, matched to PO lines via DET_ORDER_LINK = POD_PRIMARY).

Current commitment per line:

CASE WHEN POD_QTYORD = POD_QTYINVD THEN 0
     ELSE ISNULL(POD_NETT, 0) - ISNULL(SumOfDET_NETT, 0) END

How the Two Current Calculations Differ

	Existing ProjectCommitments report	Focalpoint / CVR feed
Method	Value based per PO line: line value minus posted invoices against that line, but forced to zero when quantity ordered equals quantity invoiced.	Quantity based per PO line: unit cost × quantity not yet invoiced.
Worked example (PO 920438)	£9,246.24 (correct, but only because the invoice happened to be linked to the line carrying the inflated value).	£12,944.74 (wrong; inherits the misallocated values on the auto-split lines).
Verdict	Right idea, usually right answer. Fragile: quantity gate, line level matching, credit handling in category columns.	Cannot be trusted where lines have been auto-split or values misallocated. Not fixable at report level; this is FP internals.

Existing ProjectCommitments report

Focalpoint / CVR feed

Method

Value based per PO line: line value minus posted invoices against that line, but forced to zero when quantity ordered equals quantity invoiced.

Quantity based per PO line: unit cost × quantity not yet invoiced.

Worked example (PO 920438)

£9,246.24 (correct, but only because the invoice happened to be linked to the line carrying the inflated value).

£12,944.74 (wrong; inherits the misallocated values on the auto-split lines).

Verdict

Right idea, usually right answer. Fragile: quantity gate, line level matching, credit handling in category columns.

Cannot be trusted where lines have been auto-split or values misallocated. Not fixable at report level; this is FP internals.

Assessment Against the Three Rules

Rule Existing ProjectCommitments report today

1. Values only Close. The subtraction is value based, but the quantity gate (POD_QTYORD = POD_QTYINVD forces zero) keeps one foot in the quantity world. A line that is quantity complete but under-invoiced by value shows zero commitment.

2. Whole order, not lines Breaks. Calculates line by line via DET_ORDER_LINK = POD_PRIMARY. Identical to order level on clean POs; inherits FP's mess on split or misallocated lines. Right answer on the worked example is luck, not design.

3. Posted only, credits netted Half right. Correctly uses only posted ledger transactions (sound timing basis). Credits netted correctly in the total column but added the wrong way in the category columns. Credits and invoices with no PO link are invisible.

Rule	Existing ProjectCommitments report today
1. Values only	Close. The subtraction is value based, but the quantity gate (`POD_QTYORD = POD_QTYINVD` forces zero) keeps one foot in the quantity world. A line that is quantity complete but under-invoiced by value shows zero commitment.
2. Whole order, not lines	Breaks. Calculates line by line via `DET_ORDER_LINK = POD_PRIMARY`. Identical to order level on clean POs; inherits FP's mess on split or misallocated lines. Right answer on the worked example is luck, not design.
3. Posted only, credits netted	Half right. Correctly uses only posted ledger transactions (sound timing basis). Credits netted correctly in the total column but added the wrong way in the category columns. Credits and invoices with no PO link are invisible.

Fix List

# Fix Current behaviour Required change Status

1 Remove the quantity gate POD_QTYORD = POD_QTYINVD forces commitment to zero regardless of values. Delete the quantity test. Completion is determined by value: commitment is simply order value minus posted invoices. Agreed

2 Aggregate at order level Line value minus invoices matched to that specific line. Vulnerable to FP auto-splits and misallocation (issue 24). Group by PO: sum the order's line values, sum all posted transactions against the order, subtract once. Keep line detail for display only. Agreed

3 Correct credit note signs in category columns Total column negates CRN values; Labour / Plant / Materials / Subcontract columns add them as positives, so categories drift from the total. Apply the same CRN sign handling to every column. Agreed

4 Rework the category split PO line category from POD_COSTCENTRE prefix, invoice category independently from DET_COSTCENTRE prefix. Mismatched coding means categories do not reconcile to the total. Derive the split from the PO lines' cost centres and apportion the order level commitment. Exact apportionment method to be designed. Agreed

5 Handle unrecognised cost centres Cost centres outside LAB / PLA / MAT / SUB appear in the total but in no category column. Add an Other category so every column set reconciles to the total. Agreed

6 Surface unlinked invoices and credits Transactions with no PO link (invoice without PO, credits matched to the supplier account) are excluded entirely (issues 15 and 19). Show them, likely as an exceptions section rather than netting them into commitment. Treatment to be confirmed by Finance. Finance to confirm

7 Over-invoiced orders Invoiced value above order value produces a negative that quietly reduces project commitment (issue 20 makes this possible). Proposed: floor commitment at zero per order and flag the order as over-invoiced in an exceptions column. Finance to confirm

8 All copies start at Purchase Order, not Purchase Request The report was originally built starting from Purchase Requests, and some published copies are believed to still start from the PR. The report is published in different areas. PR to PO links are unreliable (POs increased without a PR), so PR based copies cannot be trusted. Find every published copy of the report, confirm each starts from the PO (POP_DETAIL) and ignores Purchase Requests entirely, and retire or correct any that still start from the PR. Agreed

9 Order status filter No status filter. Cancelled, closed or descoped PO lines report commitment indefinitely (issue 21 workarounds make this common). Exclude cancelled and closed orders, or flag them. Rule to be confirmed by Finance. Finance to confirm

#	Fix	Current behaviour	Required change	Status
1	Remove the quantity gate	`POD_QTYORD = POD_QTYINVD` forces commitment to zero regardless of values.	Delete the quantity test. Completion is determined by value: commitment is simply order value minus posted invoices.	Agreed
2	Aggregate at order level	Line value minus invoices matched to that specific line. Vulnerable to FP auto-splits and misallocation (issue 24).	Group by PO: sum the order's line values, sum all posted transactions against the order, subtract once. Keep line detail for display only.	Agreed
3	Correct credit note signs in category columns	Total column negates CRN values; Labour / Plant / Materials / Subcontract columns add them as positives, so categories drift from the total.	Apply the same CRN sign handling to every column.	Agreed
4	Rework the category split	PO line category from `POD_COSTCENTRE` prefix, invoice category independently from `DET_COSTCENTRE` prefix. Mismatched coding means categories do not reconcile to the total.	Derive the split from the PO lines' cost centres and apportion the order level commitment. Exact apportionment method to be designed.	Agreed
5	Handle unrecognised cost centres	Cost centres outside LAB / PLA / MAT / SUB appear in the total but in no category column.	Add an Other category so every column set reconciles to the total.	Agreed
6	Surface unlinked invoices and credits	Transactions with no PO link (invoice without PO, credits matched to the supplier account) are excluded entirely (issues 15 and 19).	Show them, likely as an exceptions section rather than netting them into commitment. Treatment to be confirmed by Finance.	Finance to confirm
7	Over-invoiced orders	Invoiced value above order value produces a negative that quietly reduces project commitment (issue 20 makes this possible).	Proposed: floor commitment at zero per order and flag the order as over-invoiced in an exceptions column.	Finance to confirm
8	All copies start at Purchase Order, not Purchase Request	The report was originally built starting from Purchase Requests, and some published copies are believed to still start from the PR. The report is published in different areas. PR to PO links are unreliable (POs increased without a PR), so PR based copies cannot be trusted.	Find every published copy of the report, confirm each starts from the PO (`POP_DETAIL`) and ignores Purchase Requests entirely, and retire or correct any that still start from the PR.	Agreed
9	Order status filter	No status filter. Cancelled, closed or descoped PO lines report commitment indefinitely (issue 21 workarounds make this common).	Exclude cancelled and closed orders, or flag them. Rule to be confirmed by Finance.	Finance to confirm

Verification Plan

Rebuild the worked example: PO 920438 on project 446459 must show £9,246.24 by design, not by luck.
Run old and new reports side by side across a set of live projects and explain every difference.
Check the category columns sum to the total on every project, including ones with credit notes.
User verification with Elena and the finance team before it becomes the single source of truth (per issue 15's estimate: 5 to 7 days plus user verification).

Open Questions (not yet raised anywhere)

Which report feeds the CVR template? Barry's document implies the template loads from a "summary of costs on the project" that agrees with Focalpoint's wrong number, not from ProjectCommitments. Confirm before the fixed report becomes the source of truth (issue 18).
Order status. Now question 5 on the definition email: "Are there any Purchase Order statuses that release the commitment?" Whatever statuses Finance name become a filter in the fixed report.
Retentions. Should retention lines stay in commitment until certified? Question cut from an early email draft, still unanswered.
Over-invoicing floor. Where invoiced value exceeds order value, should commitment show zero (with an exception flag) or negative? Deliberately deferred to the changes conversation.
Timing check with Elena. Issue 27 (batched versus transacted differences) is probably the register versus posted gap. Walk through examples with Elena to confirm.
Orphaned invoice check (low priority). Confirm Focalpoint never deletes or re-keys a Purchase Order line during an auto-split, which would orphan matched invoices. One query: count PL INV/CRN transactions whose DET_ORDER_LINK has no matching POD_PRIMARY. Zero expected; any orphans behave like unlinked invoices (commitment not released) and do not change the report design.

Related Tracker Issues

Issue	Relevance
15	The core fix: commitment report as the single source of truth. Also covers the blind spot where an invoice exists without a PO.
18	Once 15 is fixed and verified, load the commitment output into the CVR automatically.
19	Credits match to the supplier account, not the PO, so they never release commitment. Closed as a process issue, but the report design should tolerate it.
20	Invoices can be approved above PO value without uplifting the PO. Accepted behaviour; the report must cope with invoiced value exceeding order value.
24	Auto-split of PO lines after part invoicing. Fixable at report level via order level aggregation.
27	Timing differences between batched and transacted items. Likely the register versus posted gap; to confirm with Elena.
29	Point in time commitment reporting. Deferred until 15 is done; `POP_DETAIL` is modified in place so it would need snapshots.
33	A specific example of Focalpoint commitment not matching the true value. Resolved by the corrected report from issue 15.