Currency Conversion in HANA based on Fiscal Period

Background

Whenever there is a requirement for Currency Conversion, the time reference plays a dominant role for determining exchange rates. Exchange rates vary on daily basis and which exchange rate should be applied for currency conversion is always a question.

BW – Currency Conversion Types

People who are familiar with BW, must be knowing Currency Conversion Types. In currency conversion types we have option to specify ‘Time Reference’ . With ‘Time Reference’ we have flexibility to set it as fixed or variable. A variable time reference can be determined by using start  or end of fiscal year, fiscal period, calendar year, month etc based on time reference infoObjects like 0FISCYEAR, 0FISCPER, 0CALYEAR, 0CALQUARTER, 0CALMONTH, 0CALWEEK and 0CALDAY.

HANA

There is no standard functionality available as Currency Conversion Type in HANA eclipse based modelling. While performing currency conversion, there is option to provide Conversion Date but not any other time attribute like fiscal period, fiscal year or calmonth. However, we have option to use input parameter for specifying conversion date.

Currency Conversion based on User selected Fiscal Period

Here in our example, we will build a model to have conversion date based on fiscal period. We will set Conversion date as last day of fiscal period for which user has executed the view.

The input parameter used for conversion date can be derived from procedure,allowing us to have flexibility to apply any custom logic to determine that date.

High Level Approach

1. Write a stored procedure to get last day of fiscal period for which user has executed the view. (depending upon your requirement, you can have your own logic to get first day/ last day of fiscal period or year or calmonth).

2. Create input parameter with type ‘Derived from Procedure/scalar Function’ based on above created procedure

3. User this input parameter to specify ‘Conversion Date’ in currency conversion

Prerequisites

While using this option you need to replicate the standard table into SAP HANA that are TCURR, TCURC, TCURX, TCURF, TCURT, TCURV .

SPtep by Step Design

Step 1. Create a graphical calculation view or in a procedure write SQL script to determine last day of fiscal period using system table M_FISCAL_CALENDAR.

Here, we will create graphical view to get last date of user selected fiscal period. This will have 2 input parameters – Fiscal Period and Fiscal Variant.

(a) Apply filters to get only data that corresponds to user entered fiscal period

(b) User Rank node to determine last date of selected fiscal period

(c) Create Calculated column to have fiscal period in SAP format (YYYYPPP)

(d) Check output of the view.

DATE field shows last date for the fiscal period 001.2016

Step 2. Create Stored procedure to read DATE as Conversion Date.

Step 3. Create input parameter in the main calculation view where currency conversion is to be performed.

Note – Don’t forget to map this procedure’s input parameter to the calculation view’s input parameter created for user entry.

Step 4. Create calculated column with currency conversion.

Note – the fields highlighted will have values based on your landscape.

With above setup, the currency conversion will consider Conversion Date as last day of user entered fiscal period.

This is just an approach which can be used for any custom logic to implement functionality similar to Currency Conversion Type from BW.