Skip to main content

Celonis Product Documentation

CURRENCY_CONVERT_SAP
Description

This operator converts the document currency of SAP tables using the internal tables TCURR, TCURF and TCURX to perform the conversion.

The currency conversion varies based on three different cases explained below.

  • Case 1: Input Currency = Converted Currency Even though both currencies are the same, the amount of decimal places needs to be taken into account. The conversion is calculated as follows: Converted_Value = Value * (TCURX.CURRDEC ? TDEC : 1)

  • Case 2: Input Currency ≠ Converted Currency, TCURR.UKURS < 0 If the exchange rate is < 0, SAP uses the inverse of that rate, meaning that UKURS = -0.003 is interpreted as 1/0.003 . This is the reason why we have to make a difference between cases with positive and negative exchange rates in the calculations. We also need to take into account that the conversion factors might be different for the FROM and the TO currency. Therefore, we divide the FROM currency by the FROM factor and multiply the result by the TO factor. Converted_Value = (Value * TDEC * TFACT) / (|UKURS| * FFACT)

  • Case 3: Input Currency ≠ Converted Currency, TCURR.UKURS > 0 Here as well, we need to take into account that the conversion factors might be different for the FROM and the TO currency. Therefore, we divide the FROM currency by the FROM factor and multiply the result by the TO factor. Converted_Value = (Value * TDEC * TFACT * |UKURS|) / (FFACT)

TDEC = POW(10, 2 - TCURX.CURRDEC)

The helper tables TCURF, TCURR and, TCURX need to be integrated from SAP. TFACT and FFACT should be integrated as FLOAT. DATE Column: GDATU should be in the same format as it is stored in the TCURF and TCURR table in SAP i.e. inverted date format as a STRING.

Syntax
 CURRENCY_CONVERT_SAP ( to_currency, exchange_rate_type, mandt_column, document_currency_column, document_date_column, amount_column [, source_system_main_table_column ])

  • to_currency: Currency to which conversion needs to be made: It can either be a constant of type STRING. SAP stores this in CUKY format having length 5. Alternatively, the target currency can also be an input column which was aggregated to a single string constant, e.g. by using the currency key WAERS from the company code table T001 in SAP.

  • exchange_rate_type: Exchange Rate Type, must be a constant of type STRING. Different popular values and what they mean:

    • B - Bank Selling Rate.

    • G - Bank Buying Rate.

    • M - Average Rate.

    • EURO - Used for exchange rates between EU member nation currencies (Example: DEM:FRF).

    • EURX - Used for exchange rates between an EU member nation currency and a non-member nation currency (Example: DEM:USD)

  • mandt_column: Column for the client id (Example: VBAK.MANDT). Either from header or item table, must be of type STRING.

  • document_currency_column: Column for the Document Currency (Example: VBAK.WAERK) either from the header or item table, must be of type STRING.

  • document_date_column: Column for the Date of the record (Example: VBAP.ERDAT) either from the header or item table. Data type of this column must be DATE.

  • amount_column: Column for the Amount to be converted (Example: VBAP.NETWR) usually from the item table. The data type needs to be FLOAT

  • source_system_main_table_column: Optional column specifying the source system for the table for which currency conversion needs to be done. The name of this column and the source system column in TCURR, TCURF, and TCURX must be same. The column must be of type STRING.

Examples

[1]

Example of currency conversion to EUR for the SAP P2P process with EKKO header table and EKPO item table. The date field used is EKKO.AEDAT and the converted column is EKPO.NETWR:

Query

Column1 

         CURRENCY_CONVERT_SAP ( 'EUR' , 'M' , "EKKO"."MANDT" , "EKKO"."WAERS" , "EKKO"."AEDAT" , "EKPO"."NETWR" )

Input

Output

EKKO

MANDT : string

EBELN : string

WAERS : string

BUKRS : string

AEDAT : date

'800'

'001'

'USD'

'WEST'

Fri Feb 02 2001 00:00:00.000

'800'

'002'

'USD'

'WEST'

Mon Feb 22 2010 00:00:00.000

'800'

'003'

'NPR'

'WEST'

Mon Feb 22 2010 00:00:00.000

'800'

'004'

'EUR'

'WEST'

Mon Feb 22 2010 00:00:00.000

'800'

'005'

'KRW'

'WEST'

Mon Feb 22 2010 00:00:00.000

EKPO

MANDT : string

EBELN : string

EBELP : int

NETWR : float

'800'

'001'

1

100.0

'800'

'001'

2

200.0

'800'

'001'

3

300.0

'800'

'002'

1

400.0

'800'

'003'

1

500.0

'800'

'004'

1

600.0

'800'

'005'

1

1230000.0

T001

MANDT : string

BUKRS : string

WAERS : string

'800'

'EAST'

'EUR'

'800'

'WEST'

'EUR'

TCURF

MANDT : string

KURST : string

FCURR : string

TCURR : string

TFACT : float

FFACT : float

GDATU : string

'800'

'M'

'USD'

'EUR'

1.0

1.0

'79899898'

'800'

null

'USD'

'EUR'

1.0

1.0

'79899878'

'800'

'M'

'NPR'

'EUR'

1.0

1.0

'79899878'

'800'

'M'

'KRW'

'EUR'

1.0

1000.0

'79899898'

TCURR

MANDT : string

KURST : string

FCURR : string

TCURR : string

GDATU : string

UKURS : float

TFACT : float

FFACT : float

'800'

'M'

'USD'

'EUR'

'79899898'

1.0

1.0

1.0

'800'

'M'

'USD'

'EUR'

'79899878'

2.0

1.0

1.0

'800'

'M'

'NPR'

'EUR'

'79899878'

-10.0

1.0

1.0

'800'

'M'

'KRW'

'EUR'

'79899898'

0.6

1.0

1000.0

TCURX

CURRKEY : string

CURRDEC : int

'USD'

1

'NPR'

2

'EUR'

1

Foreign Keys

EKPO.MANDT

EKKO.MANDT

EKPO.EBELN

EKKO.EBELN

T001.MANDT

EKKO.MANDT

T001.BUKRS

EKKO.BUKRS

Result

Column1 : float

null

null

null

8000.0

50.0

6000.0

738.0

[2]

Example of currency conversion for the SAP P2P process with EKKO header table, EKPO item table and T001 company code text table as input for the to_currency parameter. T001 is used to retrieve the to_currency value from column WAERS. Since to_currency needs to be a single row constant, a PU function is applied to aggregate the expression.

Query

Column1 

         CURRENCY_CONVERT_SAP ( PU_FIRST ( CONSTANT ( ) , "T001"."WAERS" ) , 'M' , "EKKO"."MANDT" , "EKKO"."WAERS" , "EKKO"."AEDAT" , "EKPO"."NETWR" )

Input

Output

EKKO

MANDT : string

EBELN : string

WAERS : string

BUKRS : string

AEDAT : date

'800'

'001'

'USD'

'WEST'

Fri Feb 02 2001 00:00:00.000

'800'

'002'

'USD'

'WEST'

Mon Feb 22 2010 00:00:00.000

'800'

'003'

'NPR'

'WEST'

Mon Feb 22 2010 00:00:00.000

'800'

'004'

'EUR'

'WEST'

Mon Feb 22 2010 00:00:00.000

'800'

'005'

'KRW'

'WEST'

Mon Feb 22 2010 00:00:00.000

EKPO

MANDT : string

EBELN : string

EBELP : int

NETWR : float

'800'

'001'

1

100.0

'800'

'001'

2

200.0

'800'

'001'

3

300.0

'800'

'002'

1

400.0

'800'

'003'

1

500.0

'800'

'004'

1

600.0

'800'

'005'

1

1230000.0

T001

MANDT : string

BUKRS : string

WAERS : string

'800'

'EAST'

'EUR'

'800'

'WEST'

'EUR'

TCURF

MANDT : string

KURST : string

FCURR : string

TCURR : string

TFACT : float

FFACT : float

GDATU : string

'800'

'M'

'USD'

'EUR'

1.0

1.0

'79899898'

'800'

null

'USD'

'EUR'

1.0

1.0

'79899878'

'800'

'M'

'NPR'

'EUR'

1.0

1.0

'79899878'

'800'

'M'

'KRW'

'EUR'

1.0

1000.0

'79899898'

TCURR

MANDT : string

KURST : string

FCURR : string

TCURR : string

GDATU : string

UKURS : float

TFACT : float

FFACT : float

'800'

'M'

'USD'

'EUR'

'79899898'

1.0

1.0

1.0

'800'

'M'

'USD'

'EUR'

'79899878'

2.0

1.0

1.0

'800'

'M'

'NPR'

'EUR'

'79899878'

-10.0

1.0

1.0

'800'

'M'

'KRW'

'EUR'

'79899898'

0.6

1.0

1000.0

TCURX

CURRKEY : string

CURRDEC : int

'USD'

1

'NPR'

2

'EUR'

1

Foreign Keys

EKPO.MANDT

EKKO.MANDT

EKPO.EBELN

EKKO.EBELN

T001.MANDT

EKKO.MANDT

T001.BUKRS

EKKO.BUKRS

Result

Column1 : float

null

null

null

8000.0

50.0

6000.0

738.0

[3]

Example of currency conversion to EUR for the SAP O2C process with VBAK header table and VBAP item table. The date field used is VBAP.ERDAT and the converted column is VBAP.NETWR:

Query

Column1 

         CURRENCY_CONVERT_SAP ( 'EUR' , 'M' , "VBAK"."MANDT" , "VBAK"."WAERK" , "VBAP"."ERDAT" , "VBAP"."NETWR" )

Input

Output

TCURF

MANDT : string

KURST : string

FCURR : string

TCURR : string

TFACT : float

FFACT : float

GDATU : string

'800'

'M'

'USD'

'EUR'

1.0

1.0

'79899898'

'800'

null

'USD'

'EUR'

1.0

1.0

'79899878'

'800'

'M'

'NPR'

'EUR'

1.0

1.0

'79899878'

'800'

'M'

'KRW'

'EUR'

1.0

1000.0

'79899898'

TCURR

MANDT : string

KURST : string

FCURR : string

TCURR : string

GDATU : string

UKURS : float

TFACT : float

FFACT : float

'800'

'M'

'USD'

'EUR'

'79899898'

1.0

1.0

1.0

'800'

'M'

'USD'

'EUR'

'79899878'

2.0

1.0

1.0

'800'

'M'

'NPR'

'EUR'

'79899878'

-10.0

1.0

1.0

'800'

'M'

'KRW'

'EUR'

'79899898'

0.6

1.0

1000.0

TCURX

CURRKEY : string

CURRDEC : int

'USD'

1

'NPR'

2

'EUR'

1

VBAK

MANDT : string

VBELN : string

WAERK : string

'800'

'001'

'USD'

'800'

'002'

'USD'

'800'

'003'

'NPR'

'800'

'004'

'EUR'

VBAP

MANDT : string

VBELN : string

POSNR : int

ERDAT : date

NETWR : float

'800'

'001'

1

Fri Feb 02 2001 00:00:00.000

100.0

'800'

'001'

2

Fri Feb 02 2001 00:00:00.000

200.0

'800'

'001'

3

Fri Feb 02 2001 00:00:00.000

300.0

'800'

'002'

1

Mon Feb 22 2010 00:00:00.000

400.0

'800'

'003'

1

Mon Feb 22 2010 00:00:00.000

500.0

'800'

'004'

1

Mon Feb 22 2010 00:00:00.000

600.0

Foreign Keys

VBAP.MANDT

VBAK.MANDT

VBAP.VBELN

VBAK.VBELN

Result

Column1 : float

null

null

null

8000.0

50.0

6000.0

[4]

Example of currency conversion to EUR for TMP_VBAK_VBAP table with multiple source systems. The column name of the source system in TMP_VBAK_VBAP, TCURR and TCURF tables must be same:

Query

Column1 

         CURRENCY_CONVERT_SAP ( 'EUR' , 'M' , "TMP_VBAK_VBAP"."MANDT" , "TMP_VBAK_VBAP"."WAERK" , "TMP_VBAK_VBAP"."ERDAT" , "TMP_VBAK_VBAP"."NETWR" , "TMP_VBAK_VBAP"."SOURCE_SYSTEM" )

Input

Output

TCURF

MANDT : string

KURST : string

FCURR : string

TCURR : string

TFACT : float

FFACT : float

GDATU : string

SOURCE_SYSTEM : string

'800'

'M'

'USD'

'EUR'

1.0

1.0

'79899898'

'UK'

'800'

null

'USD'

'EUR'

1.0

1.0

'79899878'

'US'

'800'

'M'

'NPR'

'EUR'

1.0

1.0

'79899878'

'US'

TCURR

MANDT : string

KURST : string

FCURR : string

TCURR : string

GDATU : string

UKURS : float

TFACT : float

FFACT : float

SOURCE_SYSTEM : string

'800'

'M'

'USD'

'EUR'

'79899898'

1.0

1.0

1.0

'US'

'800'

'M'

'USD'

'EUR'

'79899878'

2.0

1.0

1.0

'UK'

'800'

'M'

'NPR'

'EUR'

'79899878'

-10.0

1.0

1.0

'US'

TCURX

CURRKEY : string

CURRDEC : int

SOURCE_SYSTEM : string

'USD'

3

'US'

'NPR'

3

'US'

'EUR'

3

'US'

TMP_VBAK_VBAP

_CASE_KEY : int

MANDT : string

VBELN : string

POSNR : string

WAERK : string

ERDAT : date

NETWR : float

VBTYP : string

SOURCE_SYSTEM : string

1

'800'

'001'

'001'

'USD'

Fri Feb 02 2001 00:00:00.000

100.0

'C'

'US'

2

'800'

'001'

'001'

'USD'

Mon Feb 22 2010 00:00:00.000

100.0

'C'

'UK'

3

'800'

'001'

'001'

'NPR'

Mon Feb 22 2010 00:00:00.000

100.0

'C'

'US'

4

'800'

'001'

'001'

'EUR'

Mon Feb 22 2010 00:00:00.000

100.0

'C'

'UK'

Result

Column1 : float

null

200.0

1.0

100.0

[5]

Example of currency conversion with zero FFACT values. The result table will contain NULL values because of division by zero.

Query

Column1 

         CURRENCY_CONVERT_SAP ( 'EUR' , 'M' , "EKKO"."MANDT" , "EKKO"."WAERS" , "EKKO"."AEDAT" , "EKPO"."NETWR" )

Input

Output

EKKO

MANDT : string

EBELN : string

WAERS : string

BUKRS : string

AEDAT : date

'800'

'001'

'USD'

'WEST'

Fri Feb 02 2001 00:00:00.000

'800'

'002'

'USD'

'WEST'

Mon Feb 22 2010 00:00:00.000

'800'

'003'

'NPR'

'WEST'

Mon Feb 22 2010 00:00:00.000

'800'

'004'

'EUR'

'WEST'

Mon Feb 22 2010 00:00:00.000

'800'

'005'

'KRW'

'WEST'

Mon Feb 22 2010 00:00:00.000

EKPO

MANDT : string

EBELN : string

EBELP : int

NETWR : float

'800'

'001'

1

100.0

'800'

'001'

2

200.0

'800'

'001'

3

300.0

'800'

'002'

1

400.0

'800'

'003'

1

500.0

'800'

'004'

1

600.0

'800'

'005'

1

1230000.0

T001

MANDT : string

BUKRS : string

WAERS : string

'800'

'EAST'

'EUR'

'800'

'WEST'

'EUR'

TCURF

MANDT : string

KURST : string

FCURR : string

TCURR : string

TFACT : float

FFACT : float

GDATU : string

'800'

'M'

'USD'

'EUR'

0.0

0.0

'79899898'

'800'

null

'USD'

'EUR'

0.0

0.0

'79899878'

'800'

'M'

'NPR'

'EUR'

0.0

0.0

'79899878'

'800'

'M'

'KRW'

'EUR'

0.0

0.0

'79899898'

TCURR

MANDT : string

KURST : string

FCURR : string

TCURR : string

GDATU : string

UKURS : float

TFACT : float

FFACT : float

'800'

'M'

'USD'

'EUR'

'79899898'

1.0

1.0

1.0

'800'

'M'

'USD'

'EUR'

'79899878'

2.0

1.0

1.0

'800'

'M'

'NPR'

'EUR'

'79899878'

-10.0

1.0

1.0

'800'

'M'

'KRW'

'EUR'

'79899898'

0.6

1.0

1000.0

TCURX

CURRKEY : string

CURRDEC : int

'USD'

1

'NPR'

2

'EUR'

1

Foreign Keys

EKPO.MANDT

EKKO.MANDT

EKPO.EBELN

EKKO.EBELN

T001.MANDT

EKKO.MANDT

T001.BUKRS

EKKO.BUKRS

Result

Column1 : float

null

null

null

null

null

6000.0

null

See also: