Reconcile Cost and Usage to Invoice Detail by Invoice ID
Validates that the sum of costs for all service usage in the Cost and Usage dataset equals the total non-tax charges in the Invoice Detail dataset for a specific invoice.
- Source
- FOCUS MCP snapshot
- FOCUS editions
- v1.4
- Queries
- 1
- Mode
- Read-only
- Library snapshot (UTC)
- 2026-10-08
Reconcile Cost and Usage to Invoice Detail by Invoice ID
Not in v1.0 sample- Source
- FOCUS use-case SQL
- Status
- Not in v1.0 sample
- Results
- Not run
- Mode
- Read-only
- Library snapshot (UTC)
- 2026-10-08
InvoiceId and the separate Invoice Detail dataset containing InvoiceId and BilledCost are absent from the v1.0 sample, preventing invoice-level reconciliation.
Original FOCUS SQL
SELECT
COALESCE(ID.InvoiceId, CU.InvoiceId) AS InvoiceId,
ID.TotalBilledCost_InvoiceDetail,
CU.TotalBilledCost_CostAndUsage,
(COALESCE(ID.TotalBilledCost_InvoiceDetail, 0) - COALESCE(CU.TotalBilledCost_CostAndUsage, 0)) AS Variance
FROM (
SELECT
InvoiceId,
SUM(BilledCost) AS TotalBilledCost_InvoiceDetail
FROM InvoiceDetail
WHERE
ChargeCategory <> 'Tax'
GROUP BY
InvoiceId
) AS ID
FULL OUTER JOIN (
SELECT
InvoiceId,
SUM(BilledCost) AS TotalBilledCost_CostAndUsage
FROM CostAndUsage
WHERE
ChargeCategory <> 'Tax'
GROUP BY
InvoiceId
) AS CU
ON ID.InvoiceId = CU.InvoiceId
WHERE
COALESCE(ID.InvoiceId, CU.InvoiceId) = ?Columns used
Column names from the source use case.