ERP Foreign Currency Revaluation SQL Generator

-- Revalue open AP items to ZAR at the latest rate on or before 2026-09-30
-- Table and column names are generic. Map them to your ERP.
SELECT oi.doc_no,
       oi.partner_id,
       oi.currency,
       oi.open_amount_fc,
       oi.booked_rate,
       r.rate AS closing_rate,
       oi.open_amount_fc * oi.booked_rate AS base_booked,
       oi.open_amount_fc * r.rate AS base_at_close,
       oi.open_amount_fc * r.rate - oi.open_amount_fc * oi.booked_rate AS reval_difference
FROM open_items oi
JOIN fx_rates r
  ON r.from_currency = oi.currency
 AND r.to_currency = 'ZAR'
 AND r.rate_date = (
       SELECT MAX(r2.rate_date)
       FROM fx_rates r2
       WHERE r2.from_currency = oi.currency
         AND r2.to_currency = 'ZAR'
         AND r2.rate_date <= DATE '2026-09-30'
     )
WHERE oi.ledger = 'AP'
  AND oi.status = 'OPEN'
  AND oi.currency <> 'ZAR'
ORDER BY oi.currency, oi.doc_no;

-- Net unrealised difference by currency
SELECT oi.currency,
       COUNT(*) AS open_items,
       SUM(oi.open_amount_fc) AS open_fc,
       SUM(oi.open_amount_fc * oi.booked_rate) AS base_booked,
       SUM(oi.open_amount_fc * r.rate) AS base_at_close,
       SUM(oi.open_amount_fc * r.rate - oi.open_amount_fc * oi.booked_rate) AS unrealised_difference
FROM open_items oi
JOIN fx_rates r
  ON r.from_currency = oi.currency
 AND r.to_currency = 'ZAR'
 AND r.rate_date = (
       SELECT MAX(r2.rate_date)
       FROM fx_rates r2
       WHERE r2.from_currency = oi.currency
         AND r2.to_currency = 'ZAR'
         AND r2.rate_date <= DATE '2026-09-30'
     )
WHERE oi.ledger = 'AP'
  AND oi.status = 'OPEN'
  AND oi.currency <> 'ZAR'
GROUP BY oi.currency
ORDER BY oi.currency;

-- Open currencies that have no rate on or before the revaluation date
SELECT DISTINCT oi.currency
FROM open_items oi
WHERE oi.ledger = 'AP'
  AND oi.status = 'OPEN'
  AND oi.currency <> 'ZAR'
  AND NOT EXISTS (
        SELECT 1
        FROM fx_rates r
        WHERE r.from_currency = oi.currency
          AND r.to_currency = 'ZAR'
          AND r.rate_date <= DATE '2026-09-30'
      );