SAP Business One Exchange Rates: Multicurrency and Analysis with SQL - featured image

Publications

SAP Business One Exchange Rates: Multicurrency and Analysis with SQL

Hector Pincheira

Hector Pincheira

CIO | CTO
Digital Strategy & Technology Innovation

items

If your company imports supplies, exports products or invoices in dollars, SAP Business One exchange rates are not an accounting detail: they are a variable that defines your real margin. Every foreign currency invoice recorded at the wrong rate distorts your financial statements, inflates or hides profits and, as a result, leads you to make decisions on numbers that do not exist. Mastering multi-currency is therefore not optional for anyone running an industrial or commercial operation connected to the world.

In this tutorial I show you, step by step, how to configure multi-currency correctly, how to automate the daily rate upload and how to audit your exchange differences with ready-to-run SQL queries. This is not accounting theory: it is the procedure that lets you close a month without surprises and look at your currency exposure with data, not intuition.

SAP Business One exchange rates - currency analysis
Currency management has a direct impact on your company’s real margin.

Why exchange rates in SAP Business One define your margin

Imagine your company buys raw materials in dollars and sells in local currency. If you record a vendor invoice with an outdated exchange rate, the inventory cost is valued incorrectly. As a result, the margin you see in your profitability reports is contaminated at the source. On top of that, when you pay that invoice weeks later, the gap between the recording rate and the payment rate generates an exchange difference that, if left uncontrolled, shows up as a «phantom» loss or gain in your results.

For example, a company moving 200 thousand dollars a month in purchases can see its accounting profit swing by six-figure amounts simply because of currency treatment. Properly configured multi-currency therefore stops being an accounting topic and becomes a management control tool. This way, you regain the ability to tell how much you earned through management and how much came from the exchange rate.

Prerequisites before configuring multi-currency

Before touching the configuration, check these points. First, you need superuser permissions or authorization in Administration > System Initialization. You also need clarity about your local currency (the system one) and your eventual system currency for consolidation. Next, confirm with your accountant which foreign currencies will be used and under what policy the rates will be loaded: manually, by file or via an external service.

However, the most important point is governance: define who is responsible for loading the rate every day. Most multi-currency errors are not technical, they are process errors. This way, if nobody loads the day’s dollar, the system uses the last available rate and drags that error into every document issued.

Step-by-step exchange rate configuration

Follow this sequence to get multi-currency operational with no gaps.

Step 1: Define the local currency and the foreign currencies

Go to Administration > Setup > Financials > Currencies. There you define each currency with its ISO code (USD, EUR, etc.), its name and its decimals. Consequently, every currency your company uses must exist here before it can be used in any document.

Step 2: Enable multi-currency for customers and vendors

In the business partner master data, the Currency field determines which currency is invoiced. You have three options: local currency, a specific currency or «All Currencies». For example, a foreign vendor is best assigned a fixed currency; a customer operating in several currencies, on the other hand, gets «All». This way, the document automatically inherits the right currency.

SAP Business One exchange rates - cost calculation and exchange differences
Every mismanaged exchange difference translates into fictitious profit.

Step 3: Load the daily rate

The heart of the system is the Administration > Exchange Rates and Indexeswindow. When you open SAP Business One, if the day’s rates are missing, the system warns you. The rate is stored in the ORTTtable, with the key fields Currency, Rate and RateDate. Every multi-currency audit therefore starts by querying this table.

SQL queries to audit your exchange rates

Now comes the real management value: stop trusting that «someone loaded the rate» and verify it with data. Below you will find four queries, each in its two versions depending on your database.

Query 1: Check the loaded rates for a currency

First, this query shows the rates for the last 30 days so you can spot days with no load.

SQL Server

-- USD rates loaded in the last 30 days
SELECT T0.RateDate, T0.Currency, T0.Rate
FROM ORTT T0
WHERE T0.Currency = 'USD'
  AND T0.RateDate >= DATEADD(DAY, -30, GETDATE())
ORDER BY T0.RateDate DESC;

SAP HANA

-- USD rates loaded in the last 30 days
SELECT T0."RateDate", T0."Currency", T0."Rate"
FROM ORTT T0
WHERE T0."Currency" = 'USD'
  AND T0."RateDate" >= ADD_DAYS(CURRENT_DATE, -30)
ORDER BY T0."RateDate" DESC;

Query 2: Documents issued in foreign currency

It is also worth reviewing which invoices were issued in foreign currency and at what rate they were recorded. The DocRate field stores the document exchange rate, while DocTotalFC is the total in foreign currency.

SQL Server

-- Facturas de venta en moneda extranjera (ultimo mes)
SELECT T0.DocNum, T0.DocDate, T0.CardCode, T0.DocCur,
       T0.DocRate, T0.DocTotalFC AS Total_Moneda_Ext,
       T0.DocTotal AS Total_Local
FROM OINV T0
WHERE T0.DocCur <> (SELECT MainCurncy FROM OADM)
  AND T0.DocDate >= DATEADD(MONTH, -1, GETDATE())
ORDER BY T0.DocDate DESC;

SAP HANA

-- Facturas de venta en moneda extranjera (ultimo mes)
SELECT T0."DocNum", T0."DocDate", T0."CardCode", T0."DocCur",
       T0."DocRate", T0."DocTotalFC" AS "Total_Moneda_Ext",
       T0."DocTotal" AS "Total_Local"
FROM OINV T0
WHERE T0."DocCur" <> (SELECT "MainCurncy" FROM OADM)
  AND T0."DocDate" >= ADD_MONTHS(CURRENT_DATE, -1)
ORDER BY T0."DocDate" DESC;

Query 3: Your portfolio exposure by currency

This is the query every manager should have at hand. It shows you how much open balance you have in each currency, both in foreign currency and in its local equivalent.

SQL Server

-- Exposicion de cuentas por cobrar por moneda
SELECT T0.DocCur AS Moneda,
       COUNT(*) AS Facturas_Abiertas,
       SUM(T0.DocTotalFC - T0.PaidFC) AS Saldo_Moneda_Ext,
       SUM(T0.DocTotal - T0.PaidToDate) AS Saldo_Local
FROM OINV T0
WHERE T0.DocStatus = 'O'
  AND T0.DocCur <> (SELECT MainCurncy FROM OADM)
GROUP BY T0.DocCur
ORDER BY Saldo_Local DESC;

SAP HANA

-- Exposicion de cuentas por cobrar por moneda
SELECT T0."DocCur" AS "Moneda",
       COUNT(*) AS "Facturas_Abiertas",
       SUM(T0."DocTotalFC" - T0."PaidFC") AS "Saldo_Moneda_Ext",
       SUM(T0."DocTotal" - T0."PaidToDate") AS "Saldo_Local"
FROM OINV T0
WHERE T0."DocStatus" = 'O'
  AND T0."DocCur" <> (SELECT "MainCurncy" FROM OADM)
GROUP BY T0."DocCur"
ORDER BY "Saldo_Local" DESC;

Query 4: Estimate the latent exchange difference

Finally, this query revalues your open balance at the most recent exchange rate and compares it with each invoice’s original rate. This way, you anticipate the exchange difference that will hit your results when those documents are collected or revalued.

SQL Server

-- Diferencia cambiaria latente sobre saldos abiertos
SELECT T0.DocNum, T0.CardCode, T0.DocCur,
       (T0.DocTotalFC - T0.PaidFC) AS Saldo_ME,
       T0.DocRate AS Tasa_Original,
       T1.Rate AS Tasa_Actual,
       ((T0.DocTotalFC - T0.PaidFC) * T1.Rate)
       - ((T0.DocTotalFC - T0.PaidFC) * T0.DocRate) AS Dif_Cambiaria_Local
FROM OINV T0
INNER JOIN ORTT T1
  ON T1.Currency = T0.DocCur
  AND T1.RateDate = (SELECT MAX(RateDate) FROM ORTT
                     WHERE Currency = T0.DocCur)
WHERE T0.DocStatus = 'O'
  AND T0.DocCur <> (SELECT MainCurncy FROM OADM)
ORDER BY Dif_Cambiaria_Local DESC;

SAP HANA

-- Diferencia cambiaria latente sobre saldos abiertos
SELECT T0."DocNum", T0."CardCode", T0."DocCur",
       (T0."DocTotalFC" - T0."PaidFC") AS "Saldo_ME",
       T0."DocRate" AS "Tasa_Original",
       T1."Rate" AS "Tasa_Actual",
       ((T0."DocTotalFC" - T0."PaidFC") * T1."Rate")
       - ((T0."DocTotalFC" - T0."PaidFC") * T0."DocRate") AS "Dif_Cambiaria_Local"
FROM OINV T0
INNER JOIN ORTT T1
  ON T1."Currency" = T0."DocCur"
  AND T1."RateDate" = (SELECT MAX("RateDate") FROM ORTT
                       WHERE "Currency" = T0."DocCur")
WHERE T0."DocStatus" = 'O'
  AND T0."DocCur" <> (SELECT "MainCurncy" FROM OADM)
ORDER BY "Dif_Cambiaria_Local" DESC;
SAP Business One exchange rates - multi-currency and currency exposure
Knowing your exposure by currency means getting ahead of exchange rate risk.

Variations and real-world use cases

These queries are a starting point, not a destination. For example, you can adapt Query 3 toward OPCH instead of OINV to measure your exposure in accounts payable to foreign vendors. Likewise, you can turn Query 1 into a Formatted Search that alerts you when the day’s rate has not been loaded, integrating it with the system’s alert management.

Another frequent case is consolidation. If your group runs a system currency different from the local one, the SysRate and DocTotalSy fields let you replicate these analyses at a consolidated level. This way, the same approach serves both daily operations and corporate reporting.

Best practices and common mistakes

First, automate the rate upload. Leaving the daily rate to one person’s memory is the number one cause of currency errors. Therefore, consider loading the rates through the Data Transfer Workbench or a scheduled service that inserts them into ORTT every morning.

Second, do not manually edit the rate inside a document unless there is a real accounting reason. Every manual edit breaks traceability. Also, train the team to distinguish between the document currency and the business partner currency, because that is where most of the confusion starts. However, the most expensive mistake is ignoring exchange differences until closing: by then, they have already contaminated several months of results.

As a final best practice, run Query 4 every week. This way, you turn the exchange difference into a metric you monitor, not a surprise you discover.

Official SAP Resources

To go deeper, check SAP’s official documentation on the exchange rates and indexes window and the definition of currencies on the help.sap.com. There you will find the detail of the system fields and the revaluation policies recommended for period-end closing.

Conclusion

Getting SAP Business One exchange rates right is not an accounting formality: it is regaining control over your real margin. When multi-currency is well governed and audited with SQL, you stop looking at profits inflated by the luck of a rate and start deciding on true numbers. As a result, your company gains something no system gives you by default: clarity.

If you lead an operation that buys or sells in foreign currency, I invite you to run Query 3 in your environment today. The result will tell you, in less than a minute, how much of your business is exposed to the exchange rate. And that is the first step to managing it.

Want more practical tutorials like this one, with queries ready for your SAP Business One? Join the Círculo Estratégico at hectorpincheira.com and take your management to the next level.

Most Read

QlikSense Hierarchy: parent-child chart of accounts expanded by levels
QlikSense Hierarchy: the Chart of Accounts That Doesn't Add Up
QlikSense Sum TOTAL: share of each family over the total
QlikSense Sum TOTAL: the Percentage That Changes When You Filter
SAP Business One Inventory Revaluation - featured image of the tutorial
SAP Business One Inventory Revaluation: Fix Wrongly Loaded Costs
Empty chamber with wooden benches: the counterweight that required being present to be used
The Veto You Had to Say in Person: Your Decisions With No Counterweight
SAP Business One customizations - featured image of the pre-upgrade audit tutorial
SAP Business One Customizations: Pre-Upgrade Audit
SAP Business One Customer Concentration - featured image of the tutorial
SAP Business One Customer Concentration: Measure Your Risk with SQL

RELATED ARTICLES