Reconcile Multi-Currency Settlement using Lineage IDs
Demonstrates how to use PaymentCurrencyInvoiceDetailId to reconcile granular records (denominated in the billing currency) against the aggregate records used for payment (denominated in the payment currency). Resolves the ‘Divergent Grain’ problem where billing and payment currencies are represented at different aggregation levels.
- Source
- FOCUS MCP snapshot
- FOCUS editions
- v1.4
- Queries
- 1
- Mode
- Read-only
- Library snapshot (UTC)
- 2026-10-08
Reconcile Multi-Currency Settlement using Lineage IDs
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
The query reads the v1.4 Invoice Detail dataset (InvoiceId, PaymentCurrencyInvoiceDetailId, PaymentCurrencyBilledCost), none of which exist in the v1.0 cost-and-usage sample.
Original FOCUS SQL
SELECT PaymentCurrencyInvoiceDetailId, SUM(BilledCost) AS TotalBilled_BillingCurrency, SUM(PaymentCurrencyBilledCost) AS TotalBilled_PaymentCurrency, SUM(PaymentCurrencyBilledCost) / NULLIF(SUM(BilledCost), 0) AS EffectiveExchangeRate FROM InvoiceDetail WHERE InvoiceId = ? GROUP BY PaymentCurrencyInvoiceDetailId ORDER BY PaymentCurrencyInvoiceDetailId
Columns used
Column names from the source use case.