Notifications

58 views

Description

Where is financial data stored with the system

For currency type dictionary fields the data is stored within the table [fx_currency_instance] table, whilst price type fields will store their data within the [fx_price] table

Exchange rate information is stored within the [fx_rate] table

 

FX_CURRENCY_INSTANCE Definition:

Column

Definition

table

The table where the record exists

field

The field where the financial data is entered

id

The “sys_id” of the associated record in the table where data is stored

currency

The currency the user used to enter the data

amount

The amount the user used to enter the data

reference_currency

This is the currency based on the system locale

reference_amount

This is the currency amount based on the system locale

 

FX_PRICE Definition:

Column

Definition

table

The table where the record exists

field

The field where the financial data is entered

id

The “sys_id” of the associated record in the table where data is stored

currency

The currency the user used to enter the data

amount

The amount the user used to enter the data

reference_currency

This is the currency based on the system locale

reference_amount

This is the currency amount based on the system locale

type

The type of currency used i.e. calculated, fixed, multiple.

*You should not manually adjust the data within these tables. These tables are maintained by the system

 

See the following documentation for further details regarding price fields and the type of currency used:

https://docs.servicenow.com/bundle/madrid-platform-administration/page/administer/currency/concept/price-fields.html

What is reference currency and why is it used?

The system allows you to enter financial data in multiple currencies and requires a single currency to store all financial data. The reference currency will contain all the financial data in a single currency and is determined by the system locale. Reference currency will be used for all calculations and aggregations performed within the system

The system locale is controlled by the system property 'glide.system.locale'. If this is blank then by default the reference currency used will be US dollars 

Please see the following documentation for further information regarding system localization:

https://docs.servicenow.com/bundle/madrid-platform-administration/page/administer/currency/concept/locales.html

 

Can the system locale be changed?

The system locale should not be changed after go live

When changing this property the existing financial data would not be updated. Any financial data inserted/updated would then use the new locale for storing the reference currency. All calculations/Aggregations within the system use the reference currency and if there are reference currency values that do not match the system locale you would see inaccurate results when performing calculations and aggregations.

Why is the SUM different to the value in the list view?

When a SUM is performed the system will use the details in the reference currency field. This will then be converted to be displayed in the user session currency

The session currency conversion will always use the latest exchange rate within the system

Example

In the example below the user has their language set to English and their country code set to Australia so their session locale is "en.au" and currency will be displayed in Australian dollars

The list view shows that there are two currency values entered in Australian dollars $100, $50 but the sum is $159.1896, not the anticipated $150

Let's look at the latest exchange rate [fx_rate] in the system which will be used for conversion

The fx_currency_instance for these associated currency fields

 

The Calculation

Step 1:

Each of the reference currency values for the associated filed in your list will be added up i.e.

34.6352 + 69.2704 = 103.9056 USD

Step 2:

Convert the summed reference amount to the user's session currency

(103.9056 / 1.1307) *  = 159.1896 (4DP) 

 

Since summed calculation always use the latest exchange rate if there is a significant difference in the exchange rate to when the record was saved and the present, then you will see these differences. Usually, the difference will not be that significant

What is single-currency mode?

Single-currency mode lets all users of the platform see currency values in the same currency.

Rate conversions will be avoided by using single-currency mode because the reference currency will match the single currency used

Please refer to the following knowledge article for further information regarding single currency:

https://docs.servicenow.com/bundle/madrid-platform-administration/page/administer/currency/concept/single-currency-mode.html

How can I switch to single-currency mode?

I Have NOT gone LIVE

To configure single-currency mode, set the following properties:

  • glide.i18n.single_currency: true
  • glide.i18n.single_currency.code: the three-letter ISO currency code
  • The system locale: glide.system.locale

Please refer to the following knowledge article for further information regarding setting single-currency mode:

https://docs.servicenow.com/bundle/madrid-platform-administration/page/administer/currency/concept/single-currency-mode.html

I Have gone LIVE

All currency and priced data must be entered within a single currency. You will need to review all fx_currency_instance and fx_price record which are not entered in your desired currency which has a value greater than 0 entered

The procedure below must be tested within a sub-production instance prior to being applied in production. It is highly recommended to perform a clone of production over a sub-production instance to test this process. Once this process has been applied you may raise a case with ServiceNow support who will assess and provide next steps

Procedure:

*It is not always possible to convert to single currency mode. Several factors are taken into consideration such as the number of currency records and usage of the instance. If you have any concerns it would be best to speak with a ServiceNow support before beginning this procedure

1) In the filter enter fx_currency_instance.LIST to navigate to the fx_currency_instance table

2) Filter out any records where the entered amount is 0 and the currency values are already entered in your desired single currency. 

i.e. https://<instance>/fx_currency_instance_list.do?sysparm_query=amount!%3D0

Take note of the id, table and fields columns. Refer to the "Where is financial data stored with the system" section to understand the data held in these fields

3) Navigate to each record discovered in step 2 and update the associated fields to hold the correct value in the currency you require

4) In the filter enter fx_price.LIST to navigate to the fx_price table

5) Filter out any records where the entered amount is 0 and the currency values are already entered in your desired single currency. 

i.e. https://<instance>/fx_price_list.do?sysparm_query=amount!%3D0

Take note of the id, table and fields columns. Refer to the "Where is financial data stored with the system" section to understand the data held in these fields

6) Navigate to each record discovered in step 5 and update the associated fields to hold the correct value in the currency you require

7) Navigate to the fx_Currency_instance.LIST and fx_price.LIST and ensure all currencies are entered in your desired currency where the amount is greater than zero

https://<instance>/fx_price_list.do?sysparm_query=amount!%3D0

https://<instance>/fx_currency_instance_list.do?sysparm_query=amount!%3D0

If you discover any records that are not stored in your desired currency perform steps 3 and 5 again respectively.

 8)  At this point, you may raise a case to ServiceNow support and they will assess and provide next steps

 

How is my Session currency value determined?

The user locale is determined by the following, in order of consideration.

  • User record in which both country and language are specified.
  • System locale set using the glide.system.locale property.
  • Browser locale.

*The browser locale is determined by the language setting within the browser being used

How do currency conversions work?

The fx_rate table will hold all exchange rates. The exchange rate value is the rate used to convert that currency to EURO

Exchange rates are gathered daily from the ECB website from a scheduled job called "ECB Exchange Rate Load".

How the reference currency was calculated

In the example below, you can see the user entered the value of 100 AUD in the field amount which was converted to 69.2603 USD for the reference currency

This record was updated at 27/05/2019 17:12. To locate the exchange rates used we need to locate the earliest fx_rate records for currencies USD/AUD which were created prior to the update time.

*Remember a currency conversion will always use the latest exchange rate in the system at the time a financial record is updated

The system would convert the AUD value to EURO then EURO to USD to set the reference currency.
 
(Entered Amount / Exchange rate for Currency entered) * Exchange rate currency for the system locale
 
(100/1.6168) * 1.1198 = 69.26026  (69.2603 rounded to 4dp)

 

Additional Information

Please see the following documentation for further information regarding currency conversions

https://docs.servicenow.com/bundle/madrid-platform-administration/page/administer/currency/concept/currency-conversions.html

On a list view, I do not see the globe icon for my currency fields, why?

A Globe icon is displayed beside the currency value that enables the value to be changed to one of the following values:

  • Value as entered by the user
  • Value in session currency
  • Value as entered and, in brackets, the value in reference currency.

The icon appears when the user’s session currency is different from the currency entered. Clicking the icon cycles through the listed displays.

 

What is the difference between Calculated, Single and Multi-currency for price types using service catalog

When accessing a service catalog item the Price field will always be displayed in the user's session currency by default:

Calculated: If the price field is using calculated then the value entered will be converted to the user's session currency using the latest exchange rate

Fixed Single: The currency will not be displayed in the user's session currency but will be displayed in the currency specified

Multi: Multiple fx_price records will be generated for each active currency in the system. What is set for the associated record will be used when a user visits this item based on their session currency. Conversions will not occur as this is not required

 

In the example below, we will order the out of the box catalog item "Apple iPad 3" using a user with their locale set as en.GB and demonstrate how each record is displayed dependent of the type used for the catalog item record

Calculated:

catalog item fx_price record: 

 Ordering the Apple IPad 3 (displayed in GBP):

 

Fixed Single:

catalog item fx_price record: 

 Ordering the Apple IPad 3 (displayed in USD):

Multi:

catalog item fx_price records:

There are now multiple fx_price records for this item linked by the parent record.

 

 Ordering the Apple IPad 3:

In this example, the associated GBP record has been amended to GBP100 and this will be used by the user when ordering the item

 

Please refer to the following documentation for further information: 

https://docs.servicenow.com/bundle/madrid-platform-administration/page/administer/currency/concept/price-fields.html

Article Information

Last Updated:2019-11-05 20:46:32
Published:2019-11-06