Skip to main content

Using the Currency Conversion object

Using the Currency Conversion object is a fairly simple process. Once the object is created in the data model then loaded in, the PQL operator CURRENCY_CONVERT allows us to reflect the logic of the different parameters (exchange rate between currencies, time frame, and currencies being converted). Once all values are plugged into the operator, the corresponding value can be calculated.

Error handling

Consider a situation where CompanyXYZ wants to see Sys1 results displayed in EUR. The main currency that Sys1 operates in is AUD and does not have a conversion rate to EUR. However, Sys1 does have a third currency, USD, which does have a conversion rate to EUR, and AUD also has a conversion rate to USD. The Currency Conversion object can be adjusted manually in the Celonis Platform to convert the starting currency (AUD) to the target currency (EUR). The example below includes sample code that can be used to resolve this scenario.

There may be a different situation where CompanyXYZ wants to see Sys3 results displayed in EUR. However, Sys3 currency tables eitherdon't have a currency that converts to EUR, or if there is a currency that converts to EUR, there is no path from NOK to that intermediary currency. Sys3 does not have a path to go from NOK to an intermediate currency and finally to EUR. If there is no pair of currencies to act as an intermediate between the starting currency and the desired target currency, then the data needs another extraction without any limitations to find an intermediary currency pair.

Example - Intermediary currency pair

When a currency does not have a direct conversion rate with another currency and the inverse of the combination is also not available, then the best strategy is to find an intermediary currency pair as described above. This solution is not used in the base CurrencyConversion object as it would inflate the data unnecessarily for most cases.

Below is an example of a second insert that would be added to a customer’s CurrencyConversion object when using OracleEBS to convert all currencies into AUD:

WITH "CTE_INTERMEDIARY" AS (
    SELECT 'CurrencyConversion_' || "GL_DAILY_RATES"."FROM_CURRENCY" || '_' || "GL_DAILY_RATES"."TO_CURRENCY" || '_'
       || "GL_DAILY_RATES"."CONVERSION_DATE"
       || '_EBS'                                             AS "ID",       || '_EBS'
       "GL_DAILY_RATES"."FROM_CURRENCY"                      AS "FromCurrency",
       "GL_DAILY_RATES"."TO_CURRENCY"                        AS "ToCurrency",
       CAST("GL_DAILY_RATES"."CONVERSION_DATE" AS TIMESTAMP) AS "FromDate",
       CAST(CAST("GL_DAILY_RATES"."CONVERSION_DATE" AS DATE)
            + CAST('23:59:59' AS TIME) AS TIMESTAMP)         AS "ToDate",
       "GL_DAILY_RATES"."CONVERSION_RATE"                    AS "Rate",
       'Oracle'                                              AS "SourceSystemType",
       NULL                                                  AS "SourceSystemInstance",
       "GL_DAILY_RATES"."CONVERSION_TYPE"                    AS "ExchangeRateType"
    FROM <%=DATASOURCE:ORACLE_ORACLE%>."GL_DAILY_RATES" AS "GL_DAILY_RATES"
    WHERE "GL_DAILY_RATES"."CONVERSION_TYPE" = 'Corporate' -- TODO: Parameter (ExchangeRateType = 'Corporate')
        AND "GL_DAILY_RATES"."TO_CURRENCY" = 'USD'
)
SELECT 'CurrencyConversion_' || "GL_DAILY_RATES"."FROM_CURRENCY" || '_' || 
"GL_DAILY_RATES"."TO_CURRENCY" || '_'
       || "GL_DAILY_RATES"."CONVERSION_DATE"
       || '_EBS'                                             AS "ID",
       "CTE_INTERMEDIARY"."FromCurrency"                     AS "FromCurrency",
       "GL_DAILY_RATES"."TO_CURRENCY"                        AS "ToCurrency",
       CAST("GL_DAILY_RATES"."CONVERSION_DATE" AS TIMESTAMP) AS "FromDate",
       CAST(CAST("GL_DAILY_RATES"."CONVERSION_DATE" AS DATE)
            + CAST('23:59:59' AS TIME) AS TIMESTAMP)         AS "ToDate",
       "CTE_INTERMEDIARY"."Rate" * "GL_DAILY_RATES"."CONVERSION_RATE"   AS "Rate",
       'Oracle'                                              AS "SourceSystemType",
       NULL                                                  AS "SourceSystemInstance",
       "GL_DAILY_RATES"."CONVERSION_TYPE"                    AS "ExchangeRateType"
FROM <%=DATASOURCE:ORACLE_ORACLE%>."GL_DAILY_RATES" AS "GL_DAILY_RATES"
    LEFT JOIN "CTE_INTERMEDIARY"
        ON "CTE_INTERMEDIARY"."FromDate" = CAST("GL_DAILY_RATES"."CONVERSION_DATE" AS TIMESTAMP) 
            AND "CTE_INTERMEDIARY"."ToCurrency" = "GL_DAILY_RATES"."FROM_CURRENCY"
WHERE "GL_DAILY_RATES"."CONVERSION_TYPE" = 'Corporate'
  AND "GL_DAILY_RATES"."FROM_CURRENCY" = 'USD'
  AND "GL_DAILY_RATES"."TO_CURRENCY" = 'AUD';

In this example, note that the CTE is converting all currencies into USD, then into AUD. This is not strictly necessary and if there is only one currency that is desired to be converted to the final target currency, a filter can be added on FROM_CURRENCY in the CTE. The CTE will always have the TO_CURRENCY as the intermediate currency. Then, the main insert statement will have the FROM_CURRENCY as the intermediate currency and the TO_CURRENCY as the final desired target currency.

Code explanation

Summarizing explanation of the object creation code.

The first part is specific per system, as it considers the raw system table as input. Output of the system specific section is the creation of two CTEs, CTE_DIRECT_CONVERSION and CTE_INVERSE_CONVERSION.

SAP

Required tables:

  • TCURR

  • TCURF

  • TCURX

Logic:

  1. Self-join TCURF to get all Start and End times for it.

    1. CTE_TCURF_TMP

      1. Select * from TCURF

      2. DENSE_RANK() for easier join instead of all entries

      3. ROW_NUMBER() to do the offset by validity date

    2. CTE_TCURF_CC

      1. Just converts GDATU to a readable timestamp format and adds it as VALID_START

    3. CTE_TCURF_CC_2

      1. Handles the self-join based on the DENSE_RANK as key and ROW_NUMBER as the offset to get all valid start/end time pairs

    4. CTE_TCURR_TMP

      1. Select * from TCURR

      2. DENSE_RANK() for easier join instead of all entries

      3. ROW_NUMBER() to do the offset by validity date

    5. CTE_TCURR_CC

      1. Just converts GDATU to a readable timestamp format and adds it as VALID_START

    6. CTE_TCURR_CC_2

      1. Handles the self-join based on the DENSE_RANK as key and ROW_NUMBER as the offset to get all valid start/end time pairs

  2. Build helper tables with all days since beginning of time until now() (TCURR/TCURF GDATU are not the same so this is needed).

    1. CTE_DATE_RANGE

      1. Set starting point, set end point (now()) and then timestamp every day

  3. Get TDEC value to get true position of comma for converted values.

    1. CTE_TCURX

      1. ocdm_curr_conv_tdec.png

      2. Standard is 2 so the offset is then based off of it

  4. Build the whole thing.

    1. Currency_Conversion

      1. ID: Mix of everything

      2. FromCurrency: TCURR.FCURR

      3. ToCurrency: TCURR.TCURR

      4. FromDate: TCURX ._DATE

      5. ToDate: TCURX ._DATE

      6. Rate: please find explanation below

  5. Rate calculation

    1. UKURS

      1. UKURS < 0 means we need inverse calculation (1/ABS(UKURS))

      2. UKURS > 0 means we can directly use it

    2. Multiply the TCURX.TDEC offset

    3. Divide FromCurrency Ratio TCURF.FFACT

    4. Multiply ToCurrency Ratio TCURF.TFACT

  6. CTE_DIRECT_CONVERSION

    1. ID composed based on order: "TCURR_CC_2"."FCURR" || '_' || "TCURR_CC_2"."TCURR" || '_' || "_DATE" || '_' || 'SAP' || '_' || "TCURR_CC_2"."MANDT"

    2. FromCurrency filled with actual FromCurrency value in raw table: "TCURR_CC_2"."FCURR"

    3. ToCurrency filled with actual ToCurrency value in raw table: "TCURR_CC_2"."TCURR"

    4. Rate calculation (direct - formula in point 5)

    5. Flag inserted in ‘ConversionType’ column with value ‘Direct’

    6. Filter applied: MANDT IS NOT NULL

  7. CTE_INVERSE_CONVERSION

    1. ID composed based on order: "TCURR_CC_2"."TCURR" || '_' || "TCURR_CC_2"."FCURR" || '_' || "_DATE" || '_' || 'SAP' || '_' || "TCURR_CC_2"."MANDT"

    2. FromCurrency filled with actual ToCurrency value in raw table: "TCURR_CC_2"."TCURR"

    3. ToCurrency filled with actual FromCurrency value in raw table: "TCURR_CC_2"."FCURR"

    4. Inverse rate calculation ( 1/ formula in point 5)

    5. Flag inserted in ‘ConversionType’ column with value ‘Inverse’

    6. Filter applied: MANDT IS NOT NULL

Oracle EBS

Required tables:

  • GL_DAILY_RATES

Logic:

  1. CTE_DIRECT_CONVERSION

    1. ID composed based on order: "GL_DAILY_RATES"."FROM_CURRENCY" || '_' || "GL_DAILY_RATES"."TO_CURRENCY" || '_' || "GL_DAILY_RATES"."CONVERSION_DATE" || '_EBS'

    2. FromCurrency filled with actual FromCurrency value in raw table: "GL_DAILY_RATES"."FROM_CURRENCY"

    3. ToCurrency filled with actual ToCurrency value in raw table: "GL_DAILY_RATES"."TO_CURRENCY"

    4. Rate - Direct: "GL_DAILY_RATES"."CONVERSION_RATE"

    5. Flag inserted in ‘ConversionType’ column with value ‘Direct’

    6. No filter applied

  2. CTE_INVERSE_CONVERSION

    1. ID composed based on order: "GL_DAILY_RATES"."TO_CURRENCY" || '_' || "GL_DAILY_RATES"."FROM_CURRENCY" || '_' || "GL_DAILY_RATES"."CONVERSION_DATE" || '_EBS'

    2. FromCurrency filled with actual ToCurrency value in raw table: "GL_DAILY_RATES"."TO_CURRENCY"

    3. ToCurrency filled with actual FromCurrency value in raw table: "GL_DAILY_RATES"."FROM_CURRENCY"

    4. Rate - Inverse: 1 / "GL_DAILY_RATES"."CONVERSION_RATE"

    5. Flag inserted in ‘ConversionType’ column with value ‘Inverse’

    6. No filter applied

Oracle Fusion

Required tables:

  • FscmTopModelAM_FinExtractAM_GlBiccExtractAM_DailyRateExtractPVO

Logic:

  1. CTE_DIRECT_CONVERSION

    1. ID composed based on order: "DailyRateExtractPVO"."DailyRateFromCurrency" || '_' || "DailyRateExtractPVO"."DailyRateToCurrency" || '_' || "DailyRateExtractPVO"."DailyRateConversionDate" || '_FUSION'

    2. FromCurrency filled with actual FromCurrency value in raw table: "DailyRateExtractPVO"."DailyRateFromCurrency"

    3. ToCurrency filled with actual ToCurrency value in raw table: "DailyRateExtractPVO"."DailyRateToCurrency"

    4. Rate - Direct: "DailyRateExtractPVO"."DailyRateConversionRate"

    5. Flag inserted in ‘ConversionType’ column with value ‘Direct’

    6. No filter applied

  2. CTE_INVERSE_CONVERSION

    1. ID composed based on order: "DailyRateExtractPVO"."DailyRateToCurrency" || '_' || "DailyRateExtractPVO"."DailyRateFromCurrency" || '_' || "DailyRateExtractPVO"."DailyRateConversionDate" || '_FUSION'

    2. FromCurrency filled with actual ToCurrency value in raw table: "DailyRateExtractPVO"."DailyRateToCurrency"

    3. ToCurrency filled with actual FromCurrency value in raw table: "DailyRateExtractPVO"."DailyRateFromCurrency"

    4. Rate - Inverse: 1 / "DailyRateExtractPVO"."DailyRateConversionRate"

    5. Flag inserted in ‘ConversionType’ column with value ‘Inverse’

    6. No filter applied

System Agnostic [SAP + EBS + Fusion]

This part of the code is following the previously explained one per system, and it is in common for all the systems.

Required CTEs previously created:

  • CTE_DIRECT_CONVERSION

  • CTE_INVERSE_CONVERSION

Logic:

  1. CTE_DIRECT_CONVERSION_TO

    1. Select * from CTE_DIRECT_CONVERSION

    2. Filter on ToCurrency for combination (ExchangeRateType, CurrencyKey) OR (ExchangeRateType_2, CurrencyKey_2)

  2. CTE_DIRECT_CONVERSION_FROM

    1. Select * from CTE_DIRECT_CONVERSION

    2. Filter on FromCurrency for combination (ExchangeRateType, CurrencyKey) OR (ExchangeRateType_2, CurrencyKey_2)

  3. CTE_INVERSE_CONVERSION_TO

    1. Select * from CTE_INVERSE_CONVERSION

    2. Filter on ToCurrency for combination (ExchangeRateType, CurrencyKey) OR (ExchangeRateType_2, CurrencyKey_2)

  4. CTE_INVERSE_CONVERSION_FROM

    1. Select * from CTE_INVERSE_CONVERSION

    2. Filter on FromCurrency for combination (ExchangeRateType, CurrencyKey) OR (ExchangeRateType_2, CurrencyKey_2)

  5. CTE_CONVERSION_ALL

    1. Unioning following selects:

      1. Select * from CTE_DIRECT_CONVERSION_TO

      2. Select * from CTE_DIRECT_CONVERSION_FROM excluding combinations already in CTE_DIRECT_CONVERSION_TO (ExchangeRateType not considered when excluding - in case of overlap ExchangeRateType in previous select predominate)

      3. Select * from INVERSE_CONVERSION_FROM excluding cases in CTE_DIRECT_CONVERSION_TO and CTE_DIRECT_CONVERSION_FROM (ExchangeRateType not considered when excluding - in case of overlap ExchangeRateType in previous select predominate)

      4. Select * from INVERSE_CONVERSION_TO excluding cases in CTE_DIRECT_CONVERSION_TO and CTE_DIRECT_CONVERSION_FROM and INVERSE_CONVERSION_FROM (ExchangeRateType not considered when excluding - in case of overlap ExchangeRateType in previous select predominate)