Currency conversion rules

Edit online

In IBM® Envizi ESG Suite assignment rules, you can configure a special CURRENCYCONVERT SQL function to convert currencies.

The function is declared as CURRENCYCONVERT(target_currency, source_currency, field, conversion_rate_date). The following table lists the input parameters:
Table 1. Currency conversion function input parameters
Input parameter Type Description Default and special input values Supported account item fields
target_currency Integer This can be a specific ID or an account item field (C1 to C40) if the ID is coming from a drop-down list in the account style. Target currency, which can be a specific Envizi ESG Suite internal currency ID, or an account item field (C_1 to C_40) if the ID comes from a drop-down list in the account style. For the internal currency ID list, see Envizi ESG Suite currency IDs.
For example:
  • 114 - USD
  • 10 - AUD
  • 39 - EUR
  • 57 - JPY
NULL
Defaults to USD 114
-1
Defaults to the location’s currency based on the location’s region
-2
Defaults to the organization’s default reporting currency.
 C_1 to C_40
source_currency Integer Source currency to be used in the conversion, where the value needs to be an Envizi ESG Suite internal currency ID. For the internal currency ID list, see Envizi ESG Suite currency IDs.
For example:
  • 114 - USD
  • 10 - AUD
  • 39 - EUR
  • 57 - JPY
NULL
Defaults to the location’s currency based on the location’s region
-1
Defaults to the location’s currency based on the location’s region, same as NULL
-2
Defaults to the organization’s default reporting currency.
 C_1 to C_40
field Money Spend or cost value to be converted, typically from user input. Not applicable C_1 to C_40
conversion_rate_date Datetime Date used to determine the exchange rate based on the source currency ID for the specified month.
  • Only the currency exchange rate that is tied to the last day of the month is used. For example, if the input date is 2025-Feb-24, then the rate for 2025-Feb-28 is used.
  • If no exchange rate is found for the month, the rate defaults to the latest currency exchange rate that is available based on the source currency ID. For example, if the input date is 2025-Feb-24 but no rate is available for 2025-Feb-28, then the latest available rate on 2025-Jan-31 is used.
Not applicable
Start_Period
End_Period
Note:
  • If you have a custom exchange rate preloaded into the platform, the function always prioritizes the custom exchange rate, over the default Envizi ESG Suite rate for any month.
  • The function ignores the Associate Single or Historical currency flag, and behaves as if the flag is set to Historical.

Example use cases

The C-fields that are used in the examples in the following table are representative options only. You can adjust the C-fields to any other C-field as required by your use case. However, the C_20 field is used universally in data types to account for total cost and rolls up under total cost in standard dashboards and reports.

Table 2. Use cases and account style rule examples
Scenario number Sample use case Rule description Account style rule example
1 In scope 3 account styles, convert local values to USD. A user captures local spend or cost in the total cost field C_20. The rule converts the amount from the local currency, which is determined by the location region where the account exists, to USD and stores it in the target field at C_1. 
 C_1 = CURRENCYCONVERT(114, -1, C_20, End_Period)
or 
 C_1 = CURRENCYCONVERT(114, NULL, C_20, End_Period)
2 User needs the ability to capture cost where the currency entered might not match the currency of the location’s region.

For example, useful for international spend accounts where multiple currencies are captured in a single account.

C_20 scenario where the user chooses a source currency code from a drop-down list at C_11 and captures a cost amount in the source currency at C_10. The rule converts the cost amount to the default local currency of the location and stores inC_20. 
C_20 = CURRENCYCONVERT( -1, C_11, C_10, End Period)
The C_11 source currency field must be configured to have the Envizi ESG Suite internal currency ID in the drop-down list values. For information about the list of available currency IDs, see the currency codes and IDs page.
3 Useful for spend-based scope 3 account styles where users want to be able to select the input currency. Uses the rate from the end of the period. User chooses a source currency code from a drop-down list at C_11 and captures a cost amount in the source currency C_10. The rule converts the cost amount to the default local currency of the location by using the end period rate and stores it in C_1. 
C_1 = CURRENCYCONVERT(114, C_11, C_10, End Period)

The C_11 source currency field must be configured to have the Envizi ESG Suite internal currency ID in the drop-down list values. For information about the list of available currency IDs, see the currency codes and IDs page.
4 As Scenario 3 but using the latest available rate. User chooses a source currency code from a drop-down list at C_11 and captures a cost amount in the source currency C_10. The rule converts the cost amount to the default local currency of the location, by using the latest available rate, and stores it in C_1. 
C_1 = CURRENCYCONVERT(114, C_11, C_10, NULL)