Dynamics F&O exchange rate and currency conversion
In Dynamics Finance and Operations allows for multiple currencies to be in play. In this article, I am going to describe how to do currency conversion for a company that transacts in multiple currencies wants to report on both the transaction currency, and a single reporting currency. In this case, our reporting currency is AUD.
In a lot of cases, the data tables in F&O will provide two currencies. If it's amount we're talking about, it might give us both the transaction currency (amountcur) and the company's accounting currency (amountmst). There are some problems we might encounter:
- the data table doesn't give you one or the other (i.e. it only has
amountcur, or onlyamountmst) - the accounting currency for the company in question is different from the reporting currency we want
- (e.g. the transaction is in GBP, the company entity is in England, their accounting currency is GBP, but headquarters is in Australia)
There are a few prerequisites:
- a currency code
- a date of transaction (so that we can pick up the right exchange rate at the right time)
- an exchange rate reference table
- we need a composite table made of some base data tables
We would only do this if it was not possible to join onto another tables which has the currency conversion already on it. Getting the converted currency "as printed" into the data tables is always the most reliable way of getting currency conversions.
Exchange rate reference table
We need to make a reference table for exchange rates. This script will create this view. The value that we want to get out of this view is exchangerate.
CREATE OR ALTER view fno.exchangerate AS
SELECT
ercp.fromcurrencycode -- take this currency...
, base.exchangerate / 100 AS exchangerate -- and multiply it by this rate...
, ercp.tocurrencycode -- to get this currency
, base.validfrom
, base.validto
, datediff(day, base.validfrom, base.validto ) AS windowduration -- duration of time window in which the rate is valid
FROM dbo.exchangerate AS base
LEFT JOIN dbo.exchangeratecurrencypair AS ercp ON base.exchangeratecurrencypair = ercp.recid
Fetch the exchange rate
We need to do this CROSS APPLY on the base table to fetch the exchange rate. Examples to follow.
The conversion we're looking for is from AUD to the target currency. When joining onto the base table, there might be multiple matches for the currency pair and the date of validity. However, we must only have one match. Here, I assume that the rate with the narrowest window and most recent valid dates is the one that we want.
Note that the actual column names for currencyid and transdate may vary from table to table.
OUTER APPLY (
SELECT TOP 1 -- select the first one out of this list (shortest window, most recent)
*
FROM fno.exchangerate AS er
WHERE 1=1
AND er.fromcurrencycode = 'AUD' -- from AUD
AND base.currencyid = er.tocurrencycode -- to target currency
AND er.validfrom <= base.transdate -- within the window of validity
AND base.transdate <= er.validto
ORDER BY
er.windowduration ASC -- shortest window duration
, er.validfrom DESC -- most recent validfrom
) AS er
When data table has transaction currency amount only
In this case, we need to fetch the reporting currency amount. An example here is projpostedtranstable.
CREATE OR ALTER view fno.projpostedtranstable AS
SELECT
currencyid
, transdate
, COALESCE(er.exchangerate, 1) AS exchangerate
, totalsalesamountcur
, base.totalsalesamountcur / COALESCE(er.exchangerate, 1) AS totalsalesamountmst -- divide by exchange rate to get reporting currency
FROM dbo.projpostedtranstable AS base
OUTER APPLY (
SELECT TOP 1 -- select the first one out of this list (shortest window, most recent)
*
FROM fno.exchangerate AS er
WHERE 1=1
AND er.fromcurrencycode = 'AUD' -- from AUD
AND base.currencyid = er.tocurrencycode -- to target currency
AND er.validfrom <= base.transdate -- within the window of validity
AND base.transdate <= er.validto
ORDER BY
er.windowduration ASC -- shortest window duration
, er.validfrom DESC -- most recent validfrom
) AS er
When data table has accounting currency amount only
CREATE OR ALTER VIEW fno.projtransposting AS
SELECT
currencyid
, projtransdate
, amountmst
, base.amountmst * COALESCE(er.exchangerate, 1) AS amountcur -- multiply by exchange rate to get transaction currency
FROM dbo.projtransposting AS base
OUTER APPLY (
SELECT TOP 1 -- select the first one out of this list (shortest window, most recent)
*
FROM fno.exchangerate AS er
WHERE 1=1
AND er.fromcurrencycode = 'AUD' -- from AUD
AND base.currencyid = er.tocurrencycode -- to target currency
AND er.validfrom <= base.projtransdate -- within the window of validity
AND base.projtransdate <= er.validto
ORDER BY
er.windowduration ASC -- shortest window duration
, er.validfrom DESC -- most recent validfrom
) AS er