Currency conversion with SAP HANA SQLScript in transformation routines

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.

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

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