Reconcile Multi-Currency Settlement using Lineage IDs

FOCUS Versions

v1.4

Context

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.

FOCUS SQL Query

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