Currency conversion objects

A currency conversion object is a specialized helper object within the Data Model that provides reusable, time-dependent exchange rate logic. By deploying the CURRENCY_CONVERT operator, calculate dynamic monetary conversions directly within Studio KPIs and analyses rather than storing rigid, pre-converted values in your data pipeline.

In global processes like Accounts Payable or Order-to-Cash, transactions frequently occur across multiple regional currencies (such as EUR, USD, and AUD). Implementing currency conversion objects ensures your financial metrics remain standardized and unified against fluctuating market rates, allowing global procurement managers to accurately identify cross-border leakage and track true process costs.

Key capabilities of currency conversion objects

Supports multiple transactional source systems and diverse exchange rate types simultaneously.
Maintains time-dependent exchange rates to protect historical analysis precision.
Enables dynamic on-the-fly conversion into different target reporting currencies.
Decouples currency calculation logic from core transactional process transformations.
Analysts in Studio select their preferred target reporting currency from the pre-configured database set defined by the data engineer.

Using currency conversion objects

Use the currency conversion object to dynamically convert transaction values from one currency to another using your defined exchange rates and target time frames. When a direct rate conversion is unavailable, the system evaluates available multi-stage paths using an intermediate currency to complete the calculation. This ensures consistent, flexible reporting across systems and regions, even when source currencies differ or conversion paths are complex.

Intermediary currency pathing

When a direct conversion path between a source and target currency does not exist in your ERP rates table, the currency conversion object automatically evaluates available multi-stage paths using an intermediate currency:

Example (Valid Intermediary Path): If you need to convert Australian Dollars to Euros (AUD → EUR) but lack a direct rate, Celonis checks for intersecting pairs. If valid AUD → USD and USD → EUR rates exist, the system passes the calculation through US Dollars as the intermediary currency.
Example (Path Failure): If converting Norwegian Krone to Euros (NOK → EUR) yields no overlapping intermediate pairs, the transaction value evaluates as null. You must extract supplementary rate files or insert manual adjustment rows to resolve the gap.

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, the best strategy is to define an intermediary currency pair. This configuration is omitted from the base CurrencyConversion object by design to avoid unnecessary data inflation for standard installations.

Below is an example of a second insert block added to a customer’s CurrencyConversion object when using Oracle EBS to convert all regional 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",
       "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'
        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 architectural rules

The data transformation pipeline utilizes a system-specific extraction layer that feeds two standard Common Table Expressions (CTEs): CTE_DIRECT_CONVERSION and CTE_INVERSE_CONVERSION.

SAP Transformation Logic

Required source tables: TCURR (Exchange Rates), TCURF (Exchange Rate Factors), and TCURX (Decimal Places).

TCURF Validity Alignment: Self-join the exchange rate factors table (TCURF) to isolate valid start (VALID_START) and end time pairs using the chronological GDATU timestamp entries.
TCURR Rate Matching: Partition and sequence the exchange rates table (TCURR) using a DENSE_RANK() rule to handle historical validity intervals cleanly without generating duplicate data rows.
Time-Bound Date Tables: Construct target date helper frameworks covering your complete transactional timeline up to the current system date via now(). This bridges variations between TCURR and TCURF updates.
Decimal Precision Configuration: Query the currency decimals table (TCURX) to set the explicit position of the decimal comma. If a currency is omitted from this matrix, apply the global platform standard of two decimal places.
Rate Value Matrix: Calculate target fields using the standard mathematical factors. If the source rate field UKURS drops below zero, apply an inverse evaluation rule (1 / ABS(UKURS)). Factor the final value by the matching decimal scale offset, divided by the source ratio factor (TCURF.FFACT), and multiplied by the target factor (TCURF.TFACT).

Oracle EBS Transformation Logic

Required source tables: GL_DAILY_RATES.

Direct Conversion Mapping (CTE_DIRECT_CONVERSION): Construct the composite technical identity key using the expression sequence FROM_CURRENCY || '_' || TO_CURRENCY || '_' || CONVERSION_DATE || '_EBS' to trace original cross-border invoice distributions cleanly. Map your raw currency keys directly to the Rate destination and insert the hardcoded value Direct into the ConversionType column flag.
Inverse Rate Calculation (CTE_INVERSE_CONVERSION): Invert your identifier key pattern to mirror the target-to-source direction. Calculate the reciprocal exchange metric via 1 / CONVERSION_RATE and stamp the row with the Inverse string flag in the type column.

Oracle Fusion Transformation Logic

Required source tables: FscmTopModelAM_FinExtractAM_GlBiccExtractAM_DailyRateExtractPVO.

Direct Conversion Mapping (CTE_DIRECT_CONVERSION): Compile the entity ID using the system suffix _FUSION combined with your primary extraction parameters. Extract the DailyRateConversionRate field directly into the target schema and mark the structural column as Direct.
Inverse Rate Calculation (CTE_INVERSE_CONVERSION): Construct the inverse composite reference ID. Convert the raw transactional records via 1 / DailyRateConversionRate and set the operational field property metadata to Inverse.

System Agnostic Normalization Framework

This shared target module executes sequentially across all consolidated source systems to combine your ERP data feeds into unified relational tables.

Directional Destination Filtering: Populate target filters on both incoming direct components and inversion models. Evaluate currency combinations strictly against your active master criteria data keys (such as ExchangeRateType and CurrencyKey).
Data Consolidation (CTE_CONVERSION_ALL): Run an administrative UNION operation to stitch your transformation outputs together. Prioritize direct target records; exclude structural overlaps from inverse blocks where the primary exchange metadata profiles overlap to maintain data cleanliness.