Currency Conversion Use Cases

[amit_kothari]

Introduction

A typical business intelligence application stores numerical information including amounts. Typical amount examples include, Order Amount, Invoice Amount, Shipped Amount, etc.

With the global nature of business now-a-days, end-users want to view reports in their own currency or in a global/common currency as defined by their business. This presents a unique opportunity in BI to provide amounts in converted rates either by pre-storing or by doing on-the-fly conversions while displaying reports to users.

This article discusses three use cases on how to support Currency Conversion in Incorta.

Supporting user’s default currency based on location and doing currency conversion

1) First, define an internal session variable called MyCountry:

query(_IncortaMetadata.USER.COUNTRY,
      _IncortaMetadata.USER.LOGINNAME = $user,
      _IncortaMetadata.USER.TENANTID = 47
     )

2) Use the above variable and define a second internal session variable called MyGlobalCurrency.  You can also do this by querying a table in incorta using a file which has this information:

query(
      if(
         $MyCountry = 'United States',
         'USD',
         if(
            in(
               $MyCountry,
               "Germany",
               "France",
               "Italy"
              ),
            'EUR',
            'USD'
           )
         )
     )

3) In a dashboard, define a Presentation Variable called v_currcode and default its value to $MyGlobalCurrency :

You can now create a dashboard insight formula (or in a business schema) and do the conversion on the fly:

If($v_currcode = PayablesTransactions.InvoiceHeaders.INVOICE_CURRENCY_CODE, 
   PayablesTransactions.InvoiceHeaders.INVOICE_AMOUNT,
   lookup(EBS_FIN_COMMON.GL_DAILY_RATES.CONVERSION_RATE,
          EBS_FIN_COMMON.GL_DAILY_RATES.FROM_CURRENCY,
          PayablesTransactions.InvoiceHeaders.INVOICE_CURRENCY_CODE,
          EBS_FIN_COMMON.GL_DAILY_RATES.CONVERSION_TYPE,
          'Corporate',
          EBS_FIN_COMMON.GL_DAILY_RATES.CONVERSION_DATE,
          PayablesTransactions.InvoiceHeaders.INVOICE_DATE,
          EBS_FIN_COMMON.GL_DAILY_RATES.TO_CURRENCY,
          $v_currcode,
          0) * PayablesTransactions.InvoiceHeaders.INVOICE_AMOUNT
  )

Supporting amounts in three global currencies

1. Define three internal session variables called Global1, Global2, Global3 and default the currency code in them
2. Define three fields to hold the currency conversion rates in a business schema or as a table formula using the lookup() example on gl_daily_rates table above
3. Create a small table called CurrencyTypes  in Incorta to hold the three string values Global1, Global2, Global3 in a column called type, this will serve as a prompt selector for the presentation variable which we will define in a Dashboard
4. In a Dashboard create a presentation variable called CurrType and default the value of Global1 and drag the type field from CurrencyTypes into the UI field called ‘Field’ in the  presentation variable UI. Check the screenshot above
5. Define a formula in a business schema or a insight which uses a case statement on the variable CurrType and returns the correct amount in the global currency based on the rates stored

sum(
    case(
         $CurrType = 'Global1',
         EBS_AR_SNP.AR_AGING.AMT * EBS_AR_SNP.AR_AGING.GlobalRate1 ,
         $CurrType = 'Global2',
         EBS_AR_SNP.AR_AGING.AMT * EBS_AR_SNP.AR_AGING.GlobalRate2,
         $CurrType = 'Global3',
         EBS_AR_SNP.AR_AGING.AMT.AMT * EBS_AR_SNP.AR_AGING.GlobalRate1,
         0.0
        )
   )

Supporting amounts  using currency type like functional, document, global

1. Define a internal session variables called CurrencyType and default the value to ‘Functional’
2. Define fields to hold the currency conversion rates for functional and global in a business schema or as a table formula using the lookup() example on the gl_daily_rates table above
3. Create a small table called CurrencyTypes in Incorta to hold the three string values Functional, Document, Global in a column called type, this will serve as a prompt selector for the presentation variable which we will define in a Dashboard
4. In a Dashboard create a presentation variable called CurrType and default the value of ‘Functional’ and drag the type field from CurrencyTypes into the UI field called ‘Field’ in the  presentation variable UI. Check the screenshot above
5. Define a formula in a business schema or a insight which executes a case statement on the variable CurrType and returns the correct amount in the currency type based on the rates stored

sum(
    case(
         $CurrType = 'Document',
         EBS_AR_SNP.AR_AGING.AMT * EBS_AR_SNP.AR_AGING.DocRate,
         $CurrType = 'Functional',
         EBS_AR_SNP.AR_AGING.AMT * EBS_AR_SNP.AR_AGING.FuncRate,
         $CurrType = 'Global',
         EBS_AR_SNP.AR_AGING.AMT * EBS_AR_SNP.AR_AGING.GlobalRate,
         0.0
        )
   )