[BI].[Commitment_OrderLine]

← Back to CVR Index Commitment Definition → [BI].[Commitment_Order] →

Fundamental change, 10 Sep 2026: posted only is now enforced. The transaction table holds matched-but-unposted batch rows (DET_BATCH_FLAG = 1; ~£2.2m in flight when found). Counting them released commitment at entry while actuals recognise at posting, leaving the value in neither bucket. This view now filters DET_BATCH_FLAG = 0 in the PostedPerLine CTE: commitment releases only at posting. Full rationale on the Commitment_Order page.

Purpose

Grain: one row per Purchase Order Line, plus one 'Unallocated' row per order with excess. The order level view [BI].[Commitment_Order] gives the correct commitment, but users need to see what is actually on each order: the line descriptions, the cost centres, and what is still to come. This view keeps one row per Purchase Order Line so the report reads honestly, while each order's rows sum exactly to the order level figure. It is the dataset for the rebuilt ProjectCommitments report layout.

Views cannot hold an ORDER BY or a parameter, so consumers filter WHERE [ProjectCode] = @CH_CODE and sort by Supplier, OrderNumber, LineNumber with LineKey as the tiebreak.

The Rules (Cap and Pool)

Each line keeps its own row with description, cost centre, ordered value and the invoices Focalpoint linked to it.
A line's posted value is capped at the line's value. A line cannot be invoiced for more than it is worth, so a Line row can never show negative commitment.
The excess above the cap is misallocated by definition, so it pools into one extra row per order: Unallocated invoices. That row nets against the order's remaining commitment, but never more than the lines are showing, which is what implements the zero floor here.
Order totals reconcile exactly to [BI].[Commitment_Order]: order value minus everything posted, floored at zero, with OverInvoiced = Y where posted exceeds ordered. Capped lines plus the Unallocated row always equal everything genuinely posted, so no money vanishes.

Worked Example: Purchase Order 920438

The auto-split order (issue 24, the original worked example). Focalpoint split one £18,492.48 line into £11,095.49 and £7,396.99 on a part invoice and misallocated the postings: £14,793.98 was matched against the £11,095.49 line. The order is in reality fully invoiced.

Row	Ordered	Posted (shown)	Commitment
Line 1	11,095.49	11,095.49 (capped from 14,793.98)	0.00
Line 2	7,396.99	3,698.50	3,698.49
Unallocated invoices	—	3,698.49	−3,698.49
Order total	18,492.48	18,492.48	0.00

The user sees the line detail they are used to, sees that £3,698.49 of invoicing could not be attributed to the line it was posted against, and the order total is the true order level number. The old report showed £3,698.49 phantom commitment on this order. Both lines carry POD_LINE_NUMBER 1, which is why LineKey exists.

Example Records for the Nuances

All from project 446459 (Maidenhead Grid), run 10 Sep 2026. Values will move as new invoices post.

1. Genuinely over-invoiced order — 925549 (PT Contractors)

£66,979.81 posted against a £34,348.62 order: 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. Both Line rows show zero commitment (capped), the Unallocated row shows the £32,631.19 excess with zero commitment (nothing left to net; the floor), and every row is flagged Y. Referred to Finance, along with 921853 (same pattern, £9,160.61 posted against £4,697.75).

2. Misallocation across lines, order fine — 914382 (Cable Services)

Invoices swapped between two lines: £7,030 posted against the £7,000 line and £1,958.45 against the £1,988.45 line. The £30 excess pools to the Unallocated row (commitment −30, flag N) and nets off the £30 shown on the under-posted line. Order total: zero.

3. The zero floor on a small overrun — 918053 (Apex)

Hire ran over: £1,272 posted against a £1,148 order. All lines capped, the Unallocated row carries the £124 excess with zero commitment, flag Y. The old report carried this as commitment of −£124, quietly reducing the project total.

4. Value beats quantity by a penny — 918055 (Sunbelt)

Line ordered at £1,508.35, invoiced £1,508.34. The old quantity gate called it finished; the value rule shows the true £0.01 commitment.

5. Credits reopen commitment — 922465 (Celtic Recycling)

"£73,150 less £60,140 credit": invoices net of credits leave £4,336.67 posted against the £13,010 order, so £8,673.33 commitment remains. The CRN sign flip at source makes this work; a line whose credits exceed its invoices can legitimately show commitment above its line value, and the order total stays correct.

6. Retention lines and a keying suspect — 912364 (PT Contractors)

The £166,656.56 retention line correctly shows full commitment (nothing posted against it). Line 1 ordered £389.50 but has £38,950.13 posted, roughly 100×, a suspected decimal keying error. The Unallocated row nets the £38,560.63 excess and the order lands on the true £2,732,727.93 (the old report overstated it by that amount).

Points to Note

The Unallocated row is the only row type allowed negative commitment. It needs clear labelling in the layout ("invoiced elsewhere on this order"), not read as an error. Whether it amounts to an overpayment depends on the order's flag: N means tidy-up, Y means investigate.
A Line row can still show residual commitment that was in reality invoiced but posted against a sibling line. The Unallocated row is where that truth surfaces.
Focalpoint reuses line numbers (912364 has two line 2s; 920438, 914382, 913556 and 923899 all have duplicate line 1s), so LineKey (POD_PRIMARY) is the only unique row identity. NULL on Unallocated rows. The Unallocated row's LineNumber of 999999 sorts it to the bottom of its order.
All money outputs are ROUND(…, 2): POD_NETT is stored as float.
Validated 10 Sep 2026 on project 446459: no Line row negative, all eleven Unallocated rows correct, the five over-invoiced orders floored and flagged, and every order's rows sum to [BI].[Commitment_Order]'s figure.
Change detection (delivered 11 Sep 2026): [BI].[usp_Commitment_OrderLine] (@SinceDate, @CH_CODE) returns this view's output plus ReviewStatus: 'Needs Review' if the order was added after @SinceDate or has posted invoice/credit activity in a purchase period still open at @SinceDate (the period fence), otherwise 'Previously Reviewed'. The verdict is order level and stamped on every row including the Unallocated row, so a changed order pulls through whole. Validated on 446459: identical flags to the order level proc. Full design and proof on the Commitment_Order page.

The View

As documented before deployment (the deployed view in the BI schema is authoritative):

;WITH [Lines] AS
(
    -- Grain: one row per Purchase Order Line (key: POD_PRIMARY)
    SELECT
         POD.[POD_COSTHEADER]        AS [ProjectCode]
        ,POD.[POD_ACCOUNT]           AS [SupplierCode]
        ,POD.[POD_ORDER_NO]          AS [OrderNumber]
        ,POD.[POD_LINE_NUMBER]       AS [LineNumber]
        ,POD.[POD_DETAIL]            AS [ItemDescription]
        ,POD.[POD_COSTCENTRE]        AS [CostCentre]
        ,POD.[POD_PRIMARY]           AS [LineKey]
        ,ISNULL(POD.[POD_NETT], 0)   AS [LineValue]

    FROM [POP_DETAIL] POD
    -- Pending definition question 5: exclude/flag statuses that release commitment
),
[PostedPerLine] AS
(
    -- Grain: one row per Purchase Order Line with at least one posted invoice or credit.
    -- Invoices positive, credits negative.
    SELECT
         DET.[DET_ORDER_LINK]        AS [LineKey]
        ,SUM(CASE WHEN DET.[DET_TYPE] = 'CRN' THEN -1 * DET.[DET_NETT] ELSE DET.[DET_NETT] END) AS [LinePosted]

    FROM [SL_PL_NL_DETAIL] DET

    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 DET.[DET_ORDER_LINK]
),
[LineCalc] AS
(
    -- Grain: one row per Purchase Order Line. Joins each line to its posted total and
    -- splits that posted money into the believable part and the misallocated part.
    SELECT
         L.[ProjectCode]
        ,L.[SupplierCode]
        ,L.[OrderNumber]
        ,L.[LineNumber]
        ,L.[LineKey]
        ,L.[ItemDescription]
        ,L.[CostCentre]
        ,L.[LineValue]
        ,ISNULL(P.[LinePosted], 0)   AS [LinePosted]
        -- Posted capped at the line's value: a line cannot be invoiced for more than it is worth
        ,CASE WHEN ISNULL(P.[LinePosted], 0) > L.[LineValue] THEN L.[LineValue]
              ELSE ISNULL(P.[LinePosted], 0) END AS [CappedPosted]
        -- Posted above the line's value: misallocated, pooled into the order's Unallocated row
        ,CASE WHEN ISNULL(P.[LinePosted], 0) > L.[LineValue] THEN ISNULL(P.[LinePosted], 0) - L.[LineValue]
              ELSE 0 END AS [Excess]

    FROM [Lines] L

    LEFT JOIN [PostedPerLine] P
        ON P.[LineKey] = L.[LineKey]
),
[OrderCalc] AS
(
    -- Grain: one row per Purchase Order. Order level truth (value vs posted) plus the
    -- pooled excess and the remaining line commitment the Unallocated row must net against.
    SELECT
         [ProjectCode]
        ,[SupplierCode]
        ,[OrderNumber]
        ,SUM([LineValue])                  AS [OrderValue]
        ,SUM([LinePosted])                 AS [OrderPosted]
        ,SUM([Excess])                     AS [OrderExcess]
        ,SUM([LineValue] - [CappedPosted]) AS [RemainingAfterLines]

    FROM [LineCalc]

    GROUP BY [ProjectCode], [SupplierCode], [OrderNumber]
)

-- Output: one row per Purchase Order Line ('Line') plus one 'Unallocated' row per order
-- with excess. Each order's rows sum to the order level commitment, floored at zero.
SELECT
     CH.[CH_CODE]                        AS [ProjectCode]
    ,CH.[CH_NAME]                        AS [ProjectName]
    ,CH.[CH_USER1]                       AS [ProjectManager]
    ,PL.[SUNAME]                         AS [Supplier]
    ,LC.[SupplierCode]
    ,LC.[OrderNumber]
    ,LC.[LineNumber]
    ,LC.[LineKey]
    ,'Line'                              AS [RowType]
    ,LC.[ItemDescription]
    ,LC.[CostCentre]
    ,ROUND(LC.[LineValue], 2)            AS [OrderValue]
    ,ROUND(LC.[CappedPosted], 2)         AS [PostedValue]
    -- Line commitment using posted capped at the line's max value, so never negative
    ,ROUND(LC.[LineValue] - LC.[CappedPosted], 2) AS [Commitment]
    ,CASE WHEN OC.[OrderValue] - OC.[OrderPosted] < 0 THEN 'Y' ELSE 'N' END AS [OverInvoiced]

FROM [LineCalc] LC

JOIN [OrderCalc] OC
    ON  OC.[OrderNumber] = LC.[OrderNumber]
    AND OC.[ProjectCode] = LC.[ProjectCode]

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

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

UNION ALL

-- Unallocated rows: one per Purchase Order where more was posted against a line
-- than the line is worth. Shows the misallocated money the capped lines exclude,
-- and nets it against the order's remaining commitment.
SELECT
     CH.[CH_CODE]                        AS [ProjectCode]
    ,CH.[CH_NAME]                        AS [ProjectName]
    ,CH.[CH_USER1]                       AS [ProjectManager]
    ,PL.[SUNAME]                         AS [Supplier]
    ,OC.[SupplierCode]
    ,OC.[OrderNumber]
    -- Dummy line number so the row always sorts to the bottom of its order
    ,999999                              AS [LineNumber]
    ,NULL                                AS [LineKey]
    ,'Unallocated'                       AS [RowType]
    ,'Unallocated invoices applied to order' AS [ItemDescription]
    ,''                                  AS [CostCentre]
    -- Nothing was ordered on this row; it exists only to carry posted money
    ,0                                   AS [OrderValue]
    -- The pooled excess: posted value stripped off over-posted lines by the cap.
    -- Capped lines + this row = everything genuinely posted against the order.
    ,ROUND(OC.[OrderExcess], 2)          AS [PostedValue]
    -- Nets the excess off the lines' commitment, but never more than the lines
    -- are showing, so the order total lands on the order level figure floored at
    -- zero. The only row type allowed a negative commitment.
    ,ROUND(-1 * CASE WHEN OC.[OrderExcess] > OC.[RemainingAfterLines] THEN OC.[RemainingAfterLines]
                     ELSE OC.[OrderExcess] END, 2) AS [Commitment]
    ,CASE WHEN OC.[OrderValue] - OC.[OrderPosted] < 0 THEN 'Y' ELSE 'N' END AS [OverInvoiced]

FROM [OrderCalc] OC

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

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

-- Clean orders produce no Unallocated row at all
WHERE OC.[OrderExcess] > 0