NextLytics Blog

Currency conversion with SAP HANA SQLScript in transformation routines

Written by Chris | Jan 14, 2021 3:12:58 PM

Often in SAP BW HANA, the currency must be converted during the loading processes. For example, in a corporation with companies in different countries, the local currency is converted into the currency of the corporation.

Meanwhile it is possible to implement many typical requirements of the ETL process with SQLScript. HANA users are supported by predefined functions. In this article we will take a closer look at currency conversion. You can find more information on areas of application for SQLScript in our whitepaper.

Advantages of SQLScript

By using SQLScript instead of ABAP you benefit from Code Pushdown. Calculations are performed on the database level. This prevents that the data is first loaded from the database to the ABAP server in order to then carry out the calculations there.

As a result you can achieve better performance. SQLScript allows you to access the data of the HANA database directly and avoid unnecessary transfers between the database and the application server. You can save a lot of time in this way, especially when doing calculations with large amounts of data.

Requirements

To perform currency conversion in SQLScript, the following currency tables must be available in HANA:

  • TCURR - Exchange Rates
  • TCURV - Exchange rate types for currency translation
  • TCURF - Conversion Factors
  • TCURN - Quotations
  • TCURX - Decimal Places in Currencies

Parameters of the CONVERT_CURRENCY function

The SQLScript function CONVERT_CURRENCY is used to perform the currency conversion. The parameters of this function are shown below. The Reference column indicates whether the parameter can refer to a column in the data table or whether a fixed constant must be specified.

Parameter

Reference

Description

AMOUNT

Yes

Sum to be converted

SOURCE_UNIT

Yes

Currency to be converted

TARGET_UNIT

Yes

Target currency

REFERENCE_DATE

Yes

Key date for exchange rate determination

SCHEMA

No

Database schema of the conversion tables

CLIENT

Yes

Client in which the conversion is performed

ERROR_HANDLING

No

Defines the error handling. For example, when a currency is unknown. The following options are possible:

fail on error - the conversion aborts with an error (default value)

set to null - NULL is returned as the result

keep unconverted - the original value is output

CONVERSION_TYPE

No

Defines the used exchange rate type

 

Besides the standard TCUR* tables, you can also use your own tables. You must define these using the following parameters.

Parameter

Reference

Description

PRECISIONS_TABLE

No

Decimal Places in Currencies (default TCURX)

CONFIGURATION_TABLE

No

Exchange rate types for currency translation (default TCURV)

NOTATION_TABLE

No

Quotations (defaultTCURN)

RATES_TABLE

No

Exchange Rates (default TCURR)

PREFACTORS_TABLE

No

Conversion Factors (default TCURF)

Increase the performance of your BW with SQLScript

Coding Examples

At this point we present some examples to illustrate the concept. In the first example, the document currency is converted into the local currency Euro. The key date is set manually.

outTab =
SELECT calmonth, comp_code, doc_currcy,
'EUR' AS loc_currcy,
recordmode,
deb_cre_dc,
CONVERT_CURRENCY( AMOUNT => deb_cre_dc,
SOURCE_UNIT => doc_currcy,
SCHEMA => 'SAPABAP1',
TARGET_UNIT => 'EUR',
REFERENCE_DATE => '2020-08-10',
CLIENT => '100',
CONVERSION_TYPE => 'EURX')
AS deb_cre_lc,
record, SQL__PROCEDURE__SOURCE__RECORD
FROM :inTab;

ERRORTAB= SELECT * FROM :ERRORTAB;

Alternatively, you can use the current date as the key date:

outTab =
SELECT calmonth, comp_code, doc_currcy,
'EUR' AS loc_currcy,
recordmode,
deb_cre_dc,
CONVERT_CURRENCY( AMOUNT => deb_cre_dc,
SOURCE_UNIT => doc_currcy,
SCHEMA => 'SAPABAP1',
TARGET_UNIT => 'EUR',
REFERENCE_DATE => current_date,
CLIENT => '100',
CONVERSION_TYPE => 'EURX')
AS deb_cre_lc,
record, SQL__PROCEDURE__SOURCE__RECORD
FROM :inTab;

ERRORTAB= SELECT * FROM :ERRORTAB;

You can also use variables in the currency conversion. In the following example, the current date is used as the key date for the currency conversion:

DECLARE lv_date nvarchar(8);
SELECT to_nvarchar (current_date,'YYYYMMDD')INTO lv_date FROM DUMMY;

outTab =
SELECT calmonth, comp_code, doc_currcy,
'EUR' AS loc_currcy,
recordmode,
deb_cre_dc,
CONVERT_CURRENCY( AMOUNT => deb_cre_dc,
SOURCE_UNIT => doc_currcy,
SCHEMA => 'SAPABAP1',
TARGET_UNIT => 'EUR',
REFERENCE_DATE => :lv_date,
CLIENT => '100',
CONVERSION_TYPE => 'EURX')
AS deb_cre_lc,
record, SQL__PROCEDURE__SOURCE__RECORD
FROM :inTab;

ERRORTAB= SELECT * FROM :ERRORTAB;

Alternatively, you can also use the month of the respective data set as the key date:

outTab =
SELECT calmonth, comp_code, doc_currcy,
'EUR' AS loc_currcy,
recordmode,
deb_cre_dc,
CONVERT_CURRENCY( AMOUNT => deb_cre_dc,
SOURCE_UNIT => doc_currcy,
SCHEMA => 'SAPABAP1',
TARGET_UNIT => 'EUR',
REFERENCE_DATE => CONCAT(calmonth, '01'),
CLIENT => '100',
CONVERSION_TYPE => 'EURX')
AS deb_cre_lc,
record, SQL__PROCEDURE__SOURCE__RECORD
FROM :inTab;

ERRORTAB= SELECT * FROM :ERRORTAB;

Summary

As you can see, the currency conversion can be done very easily in SQLScript. This way you can fully exploit the performance advantages of the HANA database. The predefined function also allows many configuration options.

Do you have questions about SQLScript? Or do you want to convert your transformation routines to SQLScript and are looking for experienced developers with SQLScript know-how? Please do not hesitate to contact us.

 

Further information can be found in the book "SQLScript for SAP HANA" (SAP PRESS, ISBN 978-3-8362-7408-1) by Jörg Brandeis