SQL Objects
Remote Server: SQL-DIMENSIONS.BAILEY.PRI
These objects live on SQL-DIMENSIONS.BAILEY.PRI, the Focalpoint database server where the source tables live. All commitment logic is defined here, once, and every consumer reads it.
| Object | Type | Purpose | Consumed By |
|---|---|---|---|
| [BI].[Commitment_OrderLine_Base] | View | The commitment logic, defined once. | ProjectCommitments-v2 report [BI].[usp_Commitment_OrderLine] |
| [BI].[usp_Commitment_OrderLine] | Stored procedure | Consumes [BI].[Commitment_OrderLine_Base] and adds ReviewStatus based on the CVR's last refresh date parameter. |
CVR Excel Workbooks |
Local Server: SQL021
The server the CVR workbooks connect to (SQ021.Networks.local, database FgcAccountsLive). It holds no commitment logic of its own; it passes the workbook's call through to the remote server.
| Object | Type | Purpose | Consumed By |
|---|---|---|---|
| [BI].[Commitment_OrderLine] | Synonym | Consumes [BI].[usp_Commitment_OrderLine] on the remote server and exposes it locally for the CVR to execute. | CVR Excel Workbooks |
SSRS Report
The rebuilt report is published as ProjectCommitments-v2 and reads [BI].[Commitment_OrderLine_Base] directly. To the reader it is the same report, with the same three coloured sections and the same project number prompt. Four things are new.
| Addition | Detail |
|---|---|
| Unallocated column | Added to each of the three sections (Purchase Orders, Transactions, Commitments). This is where misallocated invoicing now surfaces instead of distorting the real lines. |
| Flag columns | Order Over Invoiced and Order Line Over Invoiced, at the far right, straight from the view. |
| Line Over Invoiced Amount | The amount invoiced on a line above the line's value. Per order it sums to the Unallocated line's invoiced amount. |
| Formatting | Grey shading on Unallocated lines and red fills on the flags, matching the CVR worksheet exactly. |
Excel CVR Template Updates
Each CVR template gains a Commitments worksheet, refreshed by the existing front-sheet button alongside the project summary. Nothing about the existing sheets changes.
| Component | Type | Purpose |
|---|---|---|
| Commitments worksheet | Worksheet | Holds the Commitments table and its slicers. |
| Commitments Connection | OLEDB connection | Executes the [BI].[Commitment_OrderLine] synonym with the project number and the last refresh date parameter. |
| Slicers | Table slicers | Filter the grid by ReviewStatus, Supplier and OrderOverInvoiced. After every refresh the ReviewStatus slicer defaults to Needs Review, so by default the QS only sees what changed since the last review. |
| RefreshData macro | VBA (Module1) | Extends the existing button: captures the previous refresh stamp, passes it to the proc as the since-date, refreshes both connections, resets the slicer and stamps the new refresh time last, so a failed refresh does not move the stamp. |
| Conditional formatting | Formatting rules | Grey for Unallocated rows, red fills where an order or line is over-invoiced. |
How a Line is Marked for Review
Every row on the Commitments worksheet carries a ReviewStatus of Needs Review or Previously Reviewed. When the refresh button is pressed, the CVR passes its last refresh date to the proc, which works out what could have changed since:
| Condition | ReviewStatus |
|---|---|
| The Purchase Order was added since the last refresh. | Needs Review |
| An invoice or credit was posted against the Purchase Order in an accounting period that was still open at the last refresh. | Needs Review |
| Everything else: no change possible since the last refresh. | Previously Reviewed |
Why accounting periods rather than dates:
- Focalpoint does not record when a transaction posted, so "what posted since date X" cannot be asked directly.
- A posting can only ever land in an open accounting period. So anything posted after the last refresh must sit in a period that was still open at that moment.
- [BI].[PERIOD_CHANGE] is a dated log of period rolls. From it the proc finds the period that was open at the last refresh; that period and every later one counts as "could have changed", and every earlier period was already sealed.
- The rule is deliberately cautious: everything in a still-open period stays flagged Needs Review, even if it was looked at last time. A change can never be missed; at worst a row is flagged twice.
Changes to Commitment Logic from Project Commitments Report v1
| Change | Report v1 | New Logic |
|---|---|---|
| Where the logic lives | The calculation was SQL embedded in the report itself. | Defined once in [BI].[Commitment_OrderLine_Base]; the report and the CVR read the same logic. |
| Values, not quantities | A line whose quantities tallied (ordered = invoiced) showed zero commitment even when the values did not, hiding over-invoices. | Quantities play no part. A line is finished when invoiced to its full value. |
| Batch-flagged invoices | Reduced commitment as soon as they were matched, before posting. | Posted only. A batched invoice stays in commitment until it posts. |
| Credit notes | Netted correctly in the total but added the wrong way round in the category (Labour / Plant / Materials / Subcontract) columns. | The sign flip happens once, at source, so every column nets credits correctly. |
| Negative commitment | An over-invoiced order showed negative commitment, quietly reducing the project total. | Commitment floors at zero and the order is flagged Over Invoiced. The invoiced amount stays visible. |
| Misallocated invoicing | Focalpoint line splits and mismatched invoices created phantom commitment on individual lines. | A line's invoiced value is capped at the line's value; the excess moves to the report's Unallocated line, so order totals are true. |
Unallocated Example 1: Project 446459, PO 920438 (Misallocated Invoicing)
This order was raised as a single £18,492.48 line. When a part invoice was matched, Focalpoint itself split the Purchase Order line in two (£11,095.49 and £7,396.99), with both rows still carrying line number 1; that is the "1 (split)" row below. The invoicing was then matched unevenly: £14,793.98 against the £11,095.49 line. The order is in reality fully invoiced.
| Line Number | Order Amount | Capped Invoiced Amount | Commitment | Line Over Invoiced Amount | Order Line Over Invoiced | Order Over Invoiced |
|---|---|---|---|---|---|---|
| 1 | 11,095.49 | 11,095.49 | 0.00 | 3,698.49 | Yes | No |
| 1 (split) | 7,396.99 | 3,698.50 | 3,698.49 | 0.00 | No | No |
| Unallocated | 0.00 | 3,698.49 | −3,698.49 | 0.00 | No | No |
| Order total | 18,492.48 | 18,492.48 | 0.00 |
Why the Unallocated line: £14,793.98 was posted against the first line, £3,698.49 more than it is worth. Because every line floors at zero, the over-posted line shows commitment of 0 rather than −3,698.49, and the split line is still showing £3,698.49 of commitment that has in truth already been invoiced. The Unallocated line carries that £3,698.49 excess and nets it off, landing the order on its true commitment of zero. Without the Unallocated line capturing the excess, the order would show £3,698.49 of phantom commitment.
What the old report showed for this order: £3,698.49 of commitment on a fully invoiced order. The old report worked from quantities and had no Unallocated line:
| Line Number | Old Report Commitment | Why |
|---|---|---|
| 1 | 0.00 | Quantities tallied (ordered = invoiced), so the quantity gate zeroed the line. The £3,698.49 over-invoice was hidden. |
| 1 (split) | 3,698.49 | Quantities did not tally, so the line showed its remaining value as commitment. |
| Order total | 3,698.49 | Phantom commitment. Nothing was left to spend on this order. |
Unallocated Example 2: Project 446459, PO 925549 (Genuinely Over-Invoiced)
A £34,348.62 order with £66,979.81 posted against it: 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.
| Line Number | Order Amount | Capped Invoiced Amount | Commitment | Line Over Invoiced Amount | Order Line Over Invoiced | Order Over Invoiced |
|---|---|---|---|---|---|---|
| 1 | 32,631.19 | 32,631.19 | 0.00 | 0.00 | No | Yes |
| 2 (retention) | 1,717.43 | 1,717.43 | 0.00 | 32,631.19 | Yes | Yes |
| Unallocated | 0.00 | 32,631.19 | 0.00 | 0.00 | No | Yes |
| Order total | 34,348.62 | 66,979.81 | 0.00 |
Why the Unallocated line, and why its commitment is zero: the retention line has £34,348.62 posted against a £1,717.43 value, so the £32,631.19 excess moves to the Unallocated line. But unlike example 1, the other lines are already fully invoiced: there is no commitment left to net off. The Unallocated line therefore shows the excess in its invoiced column and zero commitment; this is the floor at zero. The overpayment stays visible through the Order Over Invoiced flag on every row rather than dragging the project's commitment negative.
What the old report showed for this order: zero, with the £32,631.19 overpayment completely invisible. The old report worked from quantities and had no Unallocated line or flags:
| Line Number | Old Report Commitment | Why |
|---|---|---|
| 1 | 0.00 | Quantities tallied, so the quantity gate zeroed the line. |
| 2 (retention) | 0.00 | Quantities tallied, so the quantity gate zeroed the line. The £32,631.19 posted above the line's value was hidden. |
| Order total | 0.00 | The order looked cleanly finished. Nothing signalled that £66,979.81 had been posted against a £34,348.62 order. |
The new report arrives at the same zero commitment, but the overpayment is now on display: in the Unallocated line's invoiced amount, the Line Over Invoiced Amount on the retention line, and the flags.