# ERP Foreign Currency Revaluation SQL Generator

> Source: https://netray.co/tools/erp-africa-fx-revaluation-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generates SQL that picks the latest closing rate on or before the revaluation date, revalues open foreign currency items and summarises unrealised gain or loss by currency.

## Category

developer

## Region

Africa

## Audience

- ERP developer
- Finance controller
- Database administrator
- Financial analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Open items table or view

**Key:** open_table

**Type:** text

**Default:** open_items

**Help:** Generic name. Replace with your open item table or a view over AR and AP.

#### Exchange rate table

**Key:** rate_table

**Type:** text

**Default:** fx_rates

**Help:** Generic name. Must have from_currency, to_currency, rate_date and rate.

#### Base (functional) currency

**Key:** base

**Type:** text

**Default:** ZAR

#### Revaluation date

**Key:** reval_date

**Type:** date

**Default:** 2026-09-30

#### Ledger type

**Key:** ledger

**Type:** select

**Options:**

- AR
- AP

**Default:** AP

**Blocks:**

#### Open items at the closing rate

**Body:** -- Revalue open {{ledger}} items to {{base}} at the latest rate on or before {{reval_date}}
-- 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_table}} oi
JOIN {{rate_table}} r
  ON r.from_currency = oi.currency
 AND r.to_currency = '{{base}}'
 AND r.rate_date = (
       SELECT MAX(r2.rate_date)
       FROM {{rate_table}} r2
       WHERE r2.from_currency = oi.currency
         AND r2.to_currency = '{{base}}'
         AND r2.rate_date <= DATE '{{reval_date}}'
     )
WHERE oi.ledger = '{{ledger}}'
  AND oi.status = 'OPEN'
  AND oi.currency <> '{{base}}'
ORDER BY oi.currency, oi.doc_no;

#### Summary by currency

**Body:** -- 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_table}} oi
JOIN {{rate_table}} r
  ON r.from_currency = oi.currency
 AND r.to_currency = '{{base}}'
 AND r.rate_date = (
       SELECT MAX(r2.rate_date)
       FROM {{rate_table}} r2
       WHERE r2.from_currency = oi.currency
         AND r2.to_currency = '{{base}}'
         AND r2.rate_date <= DATE '{{reval_date}}'
     )
WHERE oi.ledger = '{{ledger}}'
  AND oi.status = 'OPEN'
  AND oi.currency <> '{{base}}'
GROUP BY oi.currency
ORDER BY oi.currency;

#### Currencies with no rate on file

**Body:** -- Open currencies that have no rate on or before the revaluation date
SELECT DISTINCT oi.currency
FROM {{open_table}} oi
WHERE oi.ledger = '{{ledger}}'
  AND oi.status = 'OPEN'
  AND oi.currency <> '{{base}}'
  AND NOT EXISTS (
        SELECT 1
        FROM {{rate_table}} r
        WHERE r.from_currency = oi.currency
          AND r.to_currency = '{{base}}'
          AND r.rate_date <= DATE '{{reval_date}}'
      );

**Notes:** Table and column names are generic placeholders, not those of a specific ERP. Map them to your schema. DATE 'yyyy-mm-dd' literal syntax works in PostgreSQL, Oracle and MySQL, in SQL Server use CAST('yyyy-mm-dd' AS date). Check whether your ERP stores rates as units of foreign currency per base or the inverse, and for a receivable the difference sign means the opposite of a payable. This is a reconciliation aid, not a posting routine. Accounting treatment should follow your reporting framework.

## Content

**Intro:** African manufacturers often hold payables and receivables in dollars, euros and several local currencies. At month end someone has to value the open items at the closing rate and explain the unrealised gain or loss. This generator writes the SQL for that check so you can compare it with what your ERP revaluation run posts.

**Sections:**

#### How to use it

**Body:** Enter the names of your open items table and rate table, the base currency, the revaluation date and the ledger type. The generator produces three queries. The first lists each open item with its booked rate, the closing rate and the difference. The second summarises by currency. The third lists currencies with no rate on file, which is the most common cause of a missing line in a revaluation run. Run them in a read only reporting environment.

#### The logic behind the query

**Body:** For each open foreign currency item the query multiplies the open amount in foreign currency by the closing rate and by the rate at which it was booked, then takes the difference. The closing rate is the latest rate dated on or before the revaluation date, found with a MAX(rate_date) subquery. This avoids failures on weekends or bank holidays where no rate was entered. The base currency items are excluded because they do not revalue.

#### Reading the output

**Body:** A positive difference on a payable means you owe more in base currency than when you booked it, which is a loss. On a receivable, a positive difference is a gain. Compare the currency summary with the ERP revaluation journal. If they differ, check rate type, rate source, items in dispute, partly paid documents and whether the ERP revalues at document or account level. Currencies with big swings, such as the Nigerian naira or Ghanaian cedi in volatile periods, deserve a line by line review.

#### Common mistakes

**Body:** Mistakes include mixing quote directions, using average rather than closing rates, revaluing items already settled, and forgetting intercompany balances. Another is assuming a rate exists for every day. Always run the missing rate query first. If your group reports under IFRS, follow your accounting policy on monetary items, and ask your auditor when in doubt.

**Cta:** If month-end revaluation needs a faster explanation than exporting to Excel, Netray can build a read only variance view over your ERP finance tables.

**Faq:**

#### Does this post journals?

**Answer:** No. It is a read only query to check and explain revaluation. Posting should be done by the revaluation function of your ERP, with the right accounts, periods and approvals.

#### Why use the latest rate on or before the date?

**Answer:** Rate tables often have gaps for weekends and holidays. The MAX(rate_date) subquery picks the most recent available rate so the query still returns a value, which mirrors how most closing rate policies work.

#### How do I handle SQL Server?

**Answer:** Replace the DATE literal with CAST of a string to date, and check any reserved words in column names. The rest of the logic is standard SQL and works in most databases.

#### What about hyperinflation or devalued currencies?

**Answer:** This query only handles rate revaluation. Hyperinflation accounting and large devaluations need separate treatment under your reporting framework, so involve your finance and audit teams.

## Search terms

foreign currency revaluation SQL, unrealised FX gain loss query, month end revaluation SQL ERP, open items closing rate SQL, multi currency ERP africa, naira cedi kwacha shilling revaluation, latest exchange rate on or before date SQL, AP AR revaluation query

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json