Why a Line Level Version
The order level calculation 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 version keeps one row per Purchase Order line so the report reads honestly, while the totals still reconcile exactly to the order level figure.
Deployed as [BI].[Commitment_OrderLine], alongside the order level [BI].[Commitment_Order]. Full documentation of both views, the design decisions and the nuance examples is on the Commitment Views page.
The Rules
- 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. You cannot invoice a line for more than it is worth, so a line can never show negative commitment.
- The excess above the cap is clearly misallocated, so it pools into one extra row per order: Unallocated invoices. That row nets against the order's remaining commitment.
- Order totals reconcile exactly to the order level query: order value minus everything posted, floored at zero, with the OverInvoiced flag where posted exceeds ordered.
Worked Example: Purchase Order 920438
| 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. Nothing hidden, nothing corrupted.
Points to Note
- The Unallocated row carries a negative commitment. That is honest (it is the correction) but needs clear labelling in the layout so users read it as "invoiced elsewhere on this order", not as an error.
- A line can still show residual commitment that was in reality invoiced but posted against a sibling line. The Unallocated row is where that truth surfaces.
- A line whose credits exceed its invoices shows commitment above its line value. The order total remains correct.
- All money outputs are rounded to two decimal places in the query (
POD_NETTis stored as float).
Proposed Query
;WITH [Lines] AS
(
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
WHERE POD.[POD_COSTHEADER] = @CH_CODE
-- Pending definition question 5: exclude/flag statuses that release commitment
),
[PostedPerLine] AS
(
-- Posted invoices and credits per Purchase Order line: 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')
GROUP BY DET.[DET_ORDER_LINK]
),
[LineCalc] AS
(
-- Cap each line's posted value at the line's value; pool the excess
SELECT
L.[ProjectCode]
,L.[SupplierCode]
,L.[OrderNumber]
,L.[LineNumber]
,L.[LineKey]
,L.[ItemDescription]
,L.[CostCentre]
,L.[LineValue]
,ISNULL(P.[LinePosted], 0) AS [LinePosted]
,CASE WHEN ISNULL(P.[LinePosted], 0) > L.[LineValue] THEN L.[LineValue]
ELSE ISNULL(P.[LinePosted], 0) END AS [CappedPosted]
,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
(
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]
)
-- Detail rows: one per Purchase Order line
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] AS [LineKey]
,'Line' AS [RowType]
,LC.[ItemDescription]
,LC.[CostCentre]
,ROUND(LC.[LineValue], 2) AS [OrderValue]
,ROUND(LC.[CappedPosted], 2) AS [PostedValue]
,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 order where posted value exceeded a line's value
SELECT
CH.[CH_CODE] AS [ProjectCode]
,CH.[CH_NAME] AS [ProjectName]
,CH.[CH_USER1] AS [ProjectManager]
,PL.[SUNAME] AS [Supplier]
,OC.[SupplierCode]
,OC.[OrderNumber]
,999999 AS [LineNumber]
,NULL AS [LineKey]
,'Unallocated' AS [RowType]
,'Unallocated invoices applied to order' AS [ItemDescription]
,'' AS [CostCentre]
,0 AS [OrderValue]
,ROUND(OC.[OrderExcess], 2) AS [PostedValue]
,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]
WHERE OC.[OrderExcess] > 0
-- Deployed as a VIEW, so no ORDER BY here (views do not allow it).
-- Consumers sort by [Supplier], [OrderNumber], [LineNumber], with [LineKey] as the
-- tiebreak: Focalpoint reuses line numbers, so LineKey is the only unique row identity.