[BI].[Commitment_Order]

Purpose

Grain: one row per Purchase Order. This view is the single source of truth for commitment, implementing the agreed definition: Commitment = Total Purchase Order Value − Total Posted Invoices against that Purchase Order (net of credits). It replaces the calculation inside the ProjectCommitments SSRS report and will feed the CVR (issue 18) and any summary reporting. The line level companion [BI].[Commitment_OrderLine] is presentation over the same logic; each order's rows there sum exactly to this view's figure.

Views cannot hold an ORDER BY or a parameter, so consumers filter WHERE [ProjectCode] = @CH_CODE and sort themselves. Resolves tracker issues 15, 24 and 33.

What Was Wrong Before

The old ProjectCommitments report (SSRS, http://sql-reports/Reports/report/Freedom/FreedomFinance/SSRS Reports/ProjectCommitments) calculated per Purchase Order line:

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

Four changes were agreed to make it match the definition:

1. Remove the quantity test. The report set commitment to zero when ordered and invoiced quantities matched, even if the values did not, which hid over-invoices.
2. Calculate per whole order, not per line. Line level matching inherits Focalpoint's auto-split errors; order level totals are immune to them.
3. Correct credit note handling in the category columns. Credits were netted correctly in the total but added as positives in the Labour, Plant, Materials and Subcontract columns.
4. Every published copy starts at the Purchase Order. Older copies started from Purchase Requests, whose links to Purchase Orders are unreliable (confirmed live: the first comparison run used a PR based copy). Check every published copy and retire any still doing so.

Design Decisions

The View

As documented before deployment (the deployed view in the BI schema is authoritative; project filter and ORDER BY removed, ROUND to be applied throughout as per the rounding decision):

;WITH [Orders] AS
(
    -- Grain: one row per Purchase Order. Sum the lines, so auto-split errors cancel out
    SELECT
         POD.[POD_COSTHEADER]        AS [ProjectCode]
        ,POD.[POD_ACCOUNT]           AS [SupplierCode]
        ,POD.[POD_ORDER_NO]          AS [OrderNumber]
        ,SUM(ISNULL(POD.[POD_NETT], 0))  AS [OrderValue]
        ,SUM(CASE WHEN POD.[POD_COSTCENTRE] LIKE 'LAB%' THEN ISNULL(POD.[POD_NETT], 0) ELSE 0 END) AS [OrderValue_Labour]
        ,SUM(CASE WHEN POD.[POD_COSTCENTRE] LIKE 'PLA%' THEN ISNULL(POD.[POD_NETT], 0) ELSE 0 END) AS [OrderValue_Plant]
        ,SUM(CASE WHEN POD.[POD_COSTCENTRE] LIKE 'MAT%' THEN ISNULL(POD.[POD_NETT], 0) ELSE 0 END) AS [OrderValue_Materials]
        ,SUM(CASE WHEN POD.[POD_COSTCENTRE] LIKE 'SUB%' THEN ISNULL(POD.[POD_NETT], 0) ELSE 0 END) AS [OrderValue_Subcontract]

    FROM [POP_DETAIL] POD
    -- Pending definition question 5: exclude/flag statuses that release commitment

    GROUP BY POD.[POD_COSTHEADER], POD.[POD_ACCOUNT], POD.[POD_ORDER_NO]
),
[Posted] AS
(
    -- Grain: one row per Purchase Order with postings. Invoices positive, credits negative.
    -- The line link (DET_ORDER_LINK = POD_PRIMARY) is used only to find which order a
    -- transaction belongs to, then immediately grouped to order level.
    SELECT
         POD.[POD_COSTHEADER]        AS [ProjectCode]
        ,POD.[POD_ORDER_NO]          AS [OrderNumber]
        ,SUM(CASE WHEN DET.[DET_TYPE] = 'CRN' THEN -1 * DET.[DET_NETT] ELSE DET.[DET_NETT] END) AS [PostedValue]
        ,SUM(CASE WHEN DET.[DET_COSTCENTRE] LIKE 'LAB%' THEN CASE WHEN DET.[DET_TYPE] = 'CRN' THEN -1 * DET.[DET_NETT] ELSE DET.[DET_NETT] END ELSE 0 END) AS [PostedValue_Labour]
        ,SUM(CASE WHEN DET.[DET_COSTCENTRE] LIKE 'PLA%' THEN CASE WHEN DET.[DET_TYPE] = 'CRN' THEN -1 * DET.[DET_NETT] ELSE DET.[DET_NETT] END ELSE 0 END) AS [PostedValue_Plant]
        ,SUM(CASE WHEN DET.[DET_COSTCENTRE] LIKE 'MAT%' THEN CASE WHEN DET.[DET_TYPE] = 'CRN' THEN -1 * DET.[DET_NETT] ELSE DET.[DET_NETT] END ELSE 0 END) AS [PostedValue_Materials]
        ,SUM(CASE WHEN DET.[DET_COSTCENTRE] LIKE 'SUB%' THEN CASE WHEN DET.[DET_TYPE] = 'CRN' THEN -1 * DET.[DET_NETT] ELSE DET.[DET_NETT] END ELSE 0 END) AS [PostedValue_Subcontract]

    FROM [SL_PL_NL_DETAIL] DET

    JOIN [POP_DETAIL] POD
        ON POD.[POD_PRIMARY] = DET.[DET_ORDER_LINK]

    WHERE DET.[DET_LEDGER] = 'PL'
      AND DET.[DET_TYPE] IN ('INV', 'CRN')
      AND DET.[DET_BATCH_FLAG] = 0   -- posted only: exclude matched-but-unposted batch rows

    GROUP BY POD.[POD_COSTHEADER], POD.[POD_ORDER_NO]
)
SELECT
     CH.[CH_CODE]                    AS [ProjectCode]
    ,CH.[CH_NAME]                    AS [ProjectName]
    ,CH.[CH_USER1]                   AS [ProjectManager]
    ,PL.[SUNAME]                     AS [Supplier]
    ,O.[SupplierCode]
    ,O.[OrderNumber]
    ,ROUND(O.[OrderValue], 2)        AS [OrderValue]
    ,ROUND(ISNULL(P.[PostedValue], 0), 2)      AS [PostedValue]
    ,ROUND(ISNULL(P.[PostedValue_Labour], 0), 2)      AS [PostedValue_Labour]
    ,ROUND(ISNULL(P.[PostedValue_Plant], 0), 2)       AS [PostedValue_Plant]
    ,ROUND(ISNULL(P.[PostedValue_Materials], 0), 2)   AS [PostedValue_Materials]
    ,ROUND(ISNULL(P.[PostedValue_Subcontract], 0), 2) AS [PostedValue_Subcontract]
    -- The true value: negative when the order is over-invoiced
    ,ROUND(RAW.[Commitment_Raw], 2)  AS [Commitment_Raw]
    -- The headline figure: floored at zero
    ,ROUND(FLOORED.[Commitment], 2)  AS [Commitment]
    ,CASE WHEN RAW.[Commitment_Raw] < 0 THEN 'Y' ELSE 'N' END AS [OverInvoiced]

    -- Category split: order level commitment apportioned by the order's cost centre mix,
    -- so the categories always sum to the total by construction
    ,ROUND(FLOORED.[Commitment] * O.[OrderValue_Labour]      / NULLIF(O.[OrderValue], 0), 2) AS [Commitment_Labour]
    ,ROUND(FLOORED.[Commitment] * O.[OrderValue_Plant]       / NULLIF(O.[OrderValue], 0), 2) AS [Commitment_Plant]
    ,ROUND(FLOORED.[Commitment] * O.[OrderValue_Materials]   / NULLIF(O.[OrderValue], 0), 2) AS [Commitment_Materials]
    ,ROUND(FLOORED.[Commitment] * O.[OrderValue_Subcontract] / NULLIF(O.[OrderValue], 0), 2) AS [Commitment_Subcontract]

FROM [Orders] O

LEFT JOIN [Posted] P
    ON  P.[OrderNumber] = O.[OrderNumber]
    AND P.[ProjectCode] = O.[ProjectCode]

JOIN [CST_COSTHEADER] CH
    ON CH.[CH_CODE] = O.[ProjectCode]

JOIN [PL_ACCOUNTS] PL
    ON PL.[SUCODE] = O.[SupplierCode]

-- Named calculations, written once and reused (aliases cannot be referenced in the same SELECT)
CROSS APPLY (SELECT O.[OrderValue] - ISNULL(P.[PostedValue], 0) AS [Commitment_Raw]) RAW
CROSS APPLY (SELECT CASE WHEN RAW.[Commitment_Raw] < 0 THEN 0 ELSE RAW.[Commitment_Raw] END AS [Commitment]) FLOORED

Column Mapping (old report → view)

The old column names are bound into the SSRS layout and the CVR template pivots. Apply this mapping either by renaming in the report update or with a facade view over [BI].[Commitment_Order].

Verification: Reconciliation Against the Old Report

Both the old RDL query and this calculation were run against the live Focalpoint database on 10 Sep 2026 for project 446459 (Maidenhead Grid). Every difference reconciles exactly to a known defect in the old calculation.

Additional findings from the run

• Order 925549 (PT Contractors) looks over-invoiced overall: £66,979.81 posted against a £34,348.62 order. The full order value invoice was matched to the retention line and £32,631.19 was also posted to line 1, a possible duplicate invoice. Commitment_Raw = −32,631.19, flagged Y. Needs Finance review.
• Order 921853 (PT Contractors): the £4,697.75 full value invoice was matched to the tiny retention line; posted £9,160.61 against a £4,697.75 order. Commitment_Raw = −4,462.86, flagged Y. Needs Finance review.
• Order 912931 (Sunbelt): over-invoiced by £92.30, hidden by the old quantity gate, flagged by the view.
• The credit note category bug, live: order 911788 line 1 showed Transaction_Total £1,798 against Transaction_Plant £2,294 on the old report, £496 of disagreement in one row. Impossible in the view: the CRN sign flip happens once, at source.
• Invoices matched to retention lines (921853, 925549) are a matching hygiene issue worth raising alongside the report fix.

Change Detection: the ReviewStatus Gate (Issues 11 / 28)

Delivered 11 Sep 2026 as stored procedures (a view cannot take a date parameter): [BI].[usp_Commitment_Order] and [BI].[usp_Commitment_OrderLine], each taking @SinceDate and @CH_CODE. They return the matching view's output plus one column, ReviewStatus: 'Needs Review' if anything changed on the Purchase Order since @SinceDate, otherwise 'Previously Reviewed'. The report slicer defaults to 'Needs Review'. The flag is decided at order level and stamped on every row (including Unallocated rows in the line level proc), so a changed order pulls through whole.

An order is 'Needs Review' if either:

• It was added after @SinceDate, from the order's entry timestamp (POD_DATE_PUTIN, copied to every line, so MAX per order = the date the order was added).
• It has a posted invoice or credit in a purchase period that was still open at @SinceDate, the period fence below.

The period fence

Focalpoint does not record when a transaction posted: no posting timestamp exists anywhere (confirmed: ledger batches leave no dated record; DET_DATE_PUTIN is entry, not posting, so a row entered in one refresh cycle and posted in a later one would slip past any date filter). Two facts, proven against live data on 10 Sep 2026, make the accounting period a reliable fence instead:

• Fact 1: a transaction can only post into a purchase period that is open.
• Fact 2: running Period End (the roll) seals a period to the minute. Last arrivals per period stop minutes before the roll (period 5: 10:09 vs the 10:27 roll; period 6: 10:46 vs 10:55) and nothing has ever arrived after one.

Therefore every posting made after @SinceDate must sit in a period still open on @SinceDate. [BI].[PERIOD_CHANGE] (Freedom's dated log of every roll) gives the period that was open at any date; SYS_YEAR converts the letter year codes (C current, L last, O older, N next) to real years; both sides become one comparable number (year × 100 + period, e.g. 202607) and the fence is a single >= comparison. PL/PL pairing throughout: the row's purchase year/period against the purchase roll log, never the nominal ledger, which rolls on different dates. The then-open period also returns some pre-@SinceDate postings, a small accepted over-catch.

• What the gate catches: new orders, all posted invoice and credit activity (including batch rows whenever they eventually post), and auto-splits (they happen at matching, which posts).
• Known blind spot, accepted: a Purchase Order value amendment with no posting. Order book edits are not dateable (POD_LAST_ACTION is an action code, not a date) and issue 21 says amendments are rare.
• The 'O' quirk is harmless here: all old years collapse to one, but they always sit far below the fence, which is the correct outcome. Never reuse the year join to date old transactions.
• Validated 11 Sep 2026 on 446459 with @SinceDate = 3 Sep: exactly four orders flagged (920518, 923899 and 912364 via period 7 postings; 931092 added on the 3rd), identical verdicts at order and line level, no false positives or misses.

Open Questions

• Purchase Order statuses. Definition question 5: which statuses release commitment? The answer becomes a filter in the Orders source and also suppresses stale OverInvoiced rows on completed orders.
• 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 view becomes the source of truth for the CVR (issue 18).
• Retentions. Should retention lines stay in commitment until certified? Currently they do (nothing posted means full commitment), which looks right, but needs Finance's confirmation.
• Over-invoicing display. Floor at zero with the flag is the deployed behaviour; confirm Finance are happy or whether they want the negative shown.
• Timing check with Elena. Issue 27 (batched versus transacted differences) is probably the register versus posted gap. Walk through examples to confirm.
• Orphaned invoice links. 2,706 posted PL transactions company-wide point at Purchase Order line keys that no longer exist, so Focalpoint does sometimes delete or re-key lines. Their commitment never releases. Confirmed: there is no order number column on the transaction table to rescue them (only DET_ORDER_LINK), so they remain a known limitation. Needs sizing by year and value.
• Line keys are reused. Order 913556 has invoice and credit pairs from 2016 attached to line keys belonging to a 2025 order, so deleted keys can be reallocated and old transactions can point at the wrong lines, not just missing ones. The pairs seen net to zero, so no commitment damage yet, but worth a wider check for non-zero cases.
• Performance. The Posted aggregation covers the whole transaction table before the consumer's project filter applies. Test the report response on a large project.
• Align the deployed views with the procs. The procs (v2) apply ROUND(…, 2) to every money output, with Commitment_Raw rounded before the OverInvoiced test (float source columns produced values like 7170.19999999999 and risked flagging exactly-invoiced orders). The deployed views do not have the rounds yet.

Related Tracker Issues