Currency Conversion Use Cases

[amit_kothari]

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

Define a internal session variable called MyCountry:

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

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'
           )
         )
     )

In a dashboard, define a Presentation variable called v_currcode and default it’s value to $MyGlobalCurrency :

We 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 does 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 of ‘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 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 does 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
        )
   )