Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

CURRENCY_CONVERT_SAP

Applies to: CELONIS 4.7

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. SAP stores this in CUKY format having length 5.

  • exchange_rate_type: Exchange Rate Type.

    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.

  • document_currency_column: Column for the Document Currency (Example: VBAK.WAERK) either from the header or item table.

  • 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 should be DATE.

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

  • 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 and TCURF must be same.

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

AEDAT : DATE

'800'

'001'

'USD'

Fri Feb 02 2001 00:00:00.000

'800'

'002'

'USD'

Mon Feb 22 2010 00:00:00.000

'800'

'003'

'NPR

Mon Feb 22 2010 00:00:00.000

'800'

'004'

EUR

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

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

'79899898'

'800'

'M'

'NPR'

'EUR'

1,0

1.0

'79899898'

TCURR

MANDT : STRING

KURST : STRING

FCURR : STRING

TCURR : STRING

GDATU : STRING

UKURS : FLOAT

UKURS : FLOAT

FFACT : FLOAT

'800'

'M'

'USD'

'EUR'

'79899898'

1.0

1.0

1.0

'800'

'M'

'USD'

'EUR'

79899898'

2.0

1.0

1.0

'800'

'M'

'NPR'

'EUR'

79899898'

-10.0

1.0

1.0

TCURX

CURRKEY : STRING

CURRDEC : INT

'USD'

1

'NPR'

2

'EUR'

1

Foreign Keys

EKPO.MANDT

EKKO.MANDT

Result

Column1 : FLOAT

null

null

null

8000.0

50.0

6000.0

[2] 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'

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

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

Result

Column1 : FLOAT

null

null

null

8000.0

50.0

6000.0

[3] 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

'USD'

1

'NPR'

2

'EUR'

1

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

2000.0

10.0

1000.0