Commitment Report Changes

1 · Changes to the ProjectCommitments Report

The existing report already works from values and posted ledger transactions, so it is usually right. Four changes make it match the definition exactly:

1. Remove the quantity test. The report currently sets commitment to zero when ordered and invoiced quantities match, even if the values do not.
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 are netted correctly in the total but not in the Labour, Plant, Materials and Subcontract columns, so the columns can disagree with the total.
4. Every copy starts at the Purchase Order. Older copies started from Purchase Requests, whose links to Purchase Orders are unreliable. We will check every published copy and retire any still doing so.

2 · Proposed Query

One row per Purchase Order. Values only, whole order, posted only, credits netted. Guards: commitment is floored at zero with the true value kept in Commitment_Raw and an OverInvoiced flag; NULLIF protects the category apportionment from zero-value orders. The status filter placeholder awaits the answer to definition question 5.

Deployed as [BI].[Commitment_Order] (with the line level companion [BI].[Commitment_OrderLine]): no ORDER BY or parameter in the views; consumers filter by ProjectCode and sort. See the Commitment Views page for full documentation.

;WITH [Orders] AS
(
    -- 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

    WHERE POD.[POD_COSTHEADER] = @CH_CODE
    -- Pending definition question 5: exclude/flag statuses that release commitment

    GROUP BY POD.[POD_COSTHEADER], POD.[POD_ACCOUNT], POD.[POD_ORDER_NO]
),
[Posted] AS
(
    -- Everything posted against those orders: invoices positive, credits negative
    SELECT
         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 POD.[POD_COSTHEADER] = @CH_CODE

    GROUP BY 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]
    ,O.[OrderValue]
    ,ISNULL(P.[PostedValue], 0)      AS [PostedValue]
    ,ISNULL(P.[PostedValue_Labour], 0)      AS [PostedValue_Labour]
    ,ISNULL(P.[PostedValue_Plant], 0)       AS [PostedValue_Plant]
    ,ISNULL(P.[PostedValue_Materials], 0)   AS [PostedValue_Materials]
    ,ISNULL(P.[PostedValue_Subcontract], 0) AS [PostedValue_Subcontract]
    ,RAW.[Commitment_Raw]
    ,FLOORED.[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
    ,FLOORED.[Commitment] * O.[OrderValue_Labour]      / NULLIF(O.[OrderValue], 0) AS [Commitment_Labour]
    ,FLOORED.[Commitment] * O.[OrderValue_Plant]       / NULLIF(O.[OrderValue], 0) AS [Commitment_Plant]
    ,FLOORED.[Commitment] * O.[OrderValue_Materials]   / NULLIF(O.[OrderValue], 0) AS [Commitment_Materials]
    ,FLOORED.[Commitment] * O.[OrderValue_Subcontract] / NULLIF(O.[OrderValue], 0) AS [Commitment_Subcontract]

FROM [Orders] O

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

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

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

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

ORDER BY PL.[SUNAME], O.[OrderNumber]

3 · Column Mapping (old report → new query)

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 the new query.

4 · Test Run Findings (project 446459, 10 Sep 2026)

• Purchase Order 920438 (the original worked example) is now fully invoiced. Order £18,492.48, posted £18,492.48, commitment zero. Further invoices have posted since Barry's June document, so zero is the correct answer. The order level sum handled the auto-split lines cleanly.
• The old report used for comparison is a Purchase Request based copy. Its output includes PR_Number, PR_Total and PRD_PROJECT columns, which the ProjectCommitments RDL does not produce. Live evidence for change 4.
• The OverInvoiced flag caught five orders. Two look like real problems, posted at roughly double the order value: 925549 (order £34,348.62, posted £66,979.81) and 921853 (order £4,697.75, posted £9,160.61), likely duplicate matching or a missing credit. The other three are small hire extension overruns (−£124, −£92.30, −£57). The old report quietly netted these negatives off project commitment.
• Rounding fix needed. POD_NETT is stored as float, producing values like 7170.19999999999. Every money output in the query needs ROUND(..., 2). Sunbelt 918055's £0.01 commitment is a real 1p difference, not float noise.
• Consistency checks passed. Category columns sum to the commitment total on every row; orders with nothing posted show full commitment (the LEFT JOIN working).
• Old versus new totals will differ by design. The old report emits NULL on quantity matched lines and negatives on over-invoiced ones, netting them off the project total; the new report floors at zero per order, so its total is slightly higher and more honest.