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.

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.

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;

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.




