As at 23 Sep 2026 (evening). All 17 division templates rolled out and file-verified. The master template is hardened so copies produce no external links. Definition agreed with Paul Richmond (18 Sep meeting; see Paul Meeting Notes). Email pack (3 PDFs) in CVR\_output\Email Artifacts, covering email drafted. Remaining: live refresh test on the NG Bailey network, OneDrive sync stability confirmation, then QS handover. Caution: OneDrive repeatedly restored old file versions during multi-machine work on 23 Sep — work on ONE machine at a time and never restore old copies by hand while a rollout is in progress.
Done
- Commitment definition agreed shape (values-only, whole-order, posted-only, credits netted, floored at zero) — Finance sign-off pending. See Definition page.
- Single source of truth: view
[BI].[Commitment_OrderLine_Base]+ proc[BI].[Commitment_OrderLine](ReviewStatus change-detection via the period fence). - Cap-and-pool + Unallocated row, order-level and line-level over-invoiced flags, and
LineOverInvoicedAmount(per-line excess shown on the line). - "Amount" naming aliased in the proc for the CVR tab:
OrderAmount,CappedInvoicedAmount,LineOverInvoicedAmount(view keeps internal names, so the report is untouched). - ProjectCommitments report relinked to the view and published as ProjectCommitments-v2 (Unallocated column per section, two flag columns, Line Over Invoiced Amount).
- Project Transactions (issue 26): view
[BI].[ProjectTransactions_Base]+ proc[BI].[ProjectTransactions](ReviewStatus fence validated, usesYEAR_PURCHASE_CODE). Report relinked and published as Project Transactions-v2. - CVR Excel tabs: Commitments and Transactions tabs built in the master template (connections, slicers, total row, CF, description boxes, RefreshData merged).
- 17 division templates pulled to CVR\WorkingCVRs\<number>.xlsm and validated (406 and 604 carry extra division sheets).
- View hardened and redeployed (18 Sep):
[BI].[ProjectTransactions_Base]now has ROUND(…, 2) on the three money columns (matching the commitment views) and the posted-only filterISNULL(gl.DET_BATCH_FLAG, 0) = 0(batched ledger rows can never appear; removes only 745 historic stray rows database-wide, none on live projects). Verified on 446459: totals unchanged at 1,269,332.85 / 2,021,086.53, timesheets and journals intact. The proc was not changed. - Rollout complete: all 17 division templates built and verified (23 Sep). Done by CVR_Rollout.ps1 (PowerShell drives Excel externally; the VBA control workbook is retired — in-process VBA dies silently on this workload). Each template: two tabs placed before Instructions, references local, tables emptied, credentials embedded and verified, RefreshData replaced, refresh dates baselined to 01/01/1900, zero references to Template.xlsm proven by direct file inspection. Hard-won design notes live as comments in the script.
- Master template hardened (23 Sep): the two cross-sheet formulas on the tabs (the cause of every external-link problem) replaced with values that
RefreshDatawrites; credentials embedded in all three master connections. Copies of the master now produce link-free, credential-complete templates by construction. - Transactions vs main sheet reconciliation (18 Sep, project 446459): the tab's data rows sum to £1,269,332.85 against the main sheet's £1,269,332.88 (float dust only) and sales tie exactly. The reported £780,774.13 was a misread: the column sum included the table's Total row, whose cached SUBTOTAL (−£488,558.72) was saved while the ReviewStatus slicer was on Needs Review. The −£488,558.72 is the genuine net of the 17 current-period rows (dominated by two nominal credit journals: JCA August 2026 −£518,256.00 and a PTC credit transfer −£37,094.05). Main sheet source confirmed:
dbo.FGC_ProjectSummaryreadsCST_COSTCENTRE's running totals (cc_actual_cost), LAB% / SUB% / everything-else-as-Materials (Plant sits inside Materials); those totals agree withCST_DETAILon this project. No batched invoices on 446459 (allDET_BATCH_FLAG = 0), so batching was not involved. Rule of thumb: when reconciling, exclude the table's Total row and clear slicers first.
In Progress
- Rollout live-fire test. All 17 division templates were built and file-verified on 23 Sep (see Done). Remaining: open one on the NG Bailey network, press the refresh button, confirm both tabs fill for a real project. Then the QS handover.
Next / Open
- Write the "Batched Transactions" Discussion Point for the Finance-facing Definition page (now unblocked): a matched-but-unposted invoice sits in neither commitment nor actuals until it posts — currently a £3.8M window (816 batched PL invoices) database-wide.
- Business feedback (Scotland CVR meeting, via Jamie Thompson): (1) tabs must refresh only with the main sheet, not auto-update — already met (button-driven, refresh-on-open off). (2) Wants Transactions and Commitments visible together (e.g. two pivots on one tab) — new feature to spec tightly, guard against scope creep.
- Add a "Batched Transactions" Discussion Point to the Commitments Definition page — held until the batched investigation is done.
- Order-level proc
usp_Commitment_Orderflag naming not yet aligned with the split flags.
Key Locations
- Templates + rollout: CVR\WorkingCVRs\ (17 numbered files,
Template.xlsmmaster,CVR_Rollout.xlsmcontrol workbook). - SQL source: _temp\cmd-ngbailey-*.txt (view, proc, transactions, rollout macro).
- RDLs: CVR\_output\ProjectCommitments-v2.rdl and Project Transactions-v2.rdl.
- Report server:
http://sql-reports/reports/report/Freedom/IT/(ProjectCommitments-v2, Project Transactions-v2). Also in CVR\_context\urls-connections.md. - Credentials: SQL login
fgc_accounts_projects(password embedded in the connections). VBA projects are not locked.
Worked Example (reference)
PO 912364 Unallocated £38,560.63: invoice PTCO15879 (£38,950.13) was matched to line 1 (worth only £389.50). Capped at £389.50, the £38,560.63 excess pools to the Unallocated row and the line is flagged over-invoiced. A Focalpoint re-match to the correct line would clear it.