Skip to main content

Currency conversion objects

A currency conversion object is a specialized helper object within the Data Model. Like other helper objects, it is technical in nature, not business-facing, and sits outside the process flow. Its sole purpose is to provide reusable, time-dependent exchange rate logic that can be applied dynamically in KPIs and analyses across systems and processes.

Using the CURRENCY_CONVERT operator, monetary values can be converted dynamically during analysis rather than being stored as pre-converted values. The currency conversion object defines:

  • Source and target currencies

  • Exchange rates

  • Validity periods for those rates

  • Exchange rate types

  • System-specific configurations

Key capabilities of currency conversion objects

  • Supports multiple systems and multiple exchange rate types.

  • Allows time-dependent exchange rates.

  • Enables conversion into different target currencies.

  • Keeps currency conversion independent of process logic.

  • Lets analysts choose the target currency in Studio, within the set defined by the data engineer.

Using currency conversion objects

Currency conversion object allows Celonis to dynamically convert values from one currency to another using defined exchange rates and time frames. If a direct conversion is unavailable, intermediate currencies can be used to complete the calculation. This ensures consistent, flexible reporting across systems and regions, even when source currencies differ or conversion paths are complex.

Error handling

When a direct conversion from a source currency to a target currency is unavailable, the Currency Conversion object can use an intermediate currency to complete the conversion:

  • Example 1: AUD → EUR

    • No direct AUD→EUR rate exists

    • AUD→USD and USD→EUR rates exist, so the conversion can use USD as an intermediary

  • Example 2: NOK → EUR

    • No available conversion path exists through any intermediate currency

    • In this case, additional data extraction or adjustments are required to provide a valid conversion path

Example code - Intermediary currency pair in Oracle EBS

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

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)

Related topics