Loading SAP S/4 HANA Exchange Rates from OANDA For...

jmalla · ‎10-29-2021

We recently had a requirement to load SAP S/4 HANA Exchange Rate tables from OANDA, one of the leaders in foreign currency exchange arena - https://www.oanda.com/

In SAP, exchange rates are stored in the TCURR table and you can use transaction code OB08 to show the maintenance view to the table:

OB08 Foreign exchange rate transaction code

TCURR table maintenance view

Canadian dollar to US dollar exchange rates

So now, our requirement is to update this table from the OANDA interface API call.

The approach we took here was to implement the ALE distribution model to allow for outbound and inbound integration for exchange rates. The outbound integration can be used to send the exchange rates from SAP to other SAP systems or other downstream systems. It also is a way to generate the IDoc to view how it needs to appear for the equivalent inbound process instead of coming up with all of the fields that need to be mapped from scratch.

Outbound Exchange Rate model

Exchange Rate Outbound and Inbound ALE model

Generate Partner profile

Outbound Exchange Rate partner profile

Outbound Exchange Rate partner profile details

So let's generate an EXCHANGE_RATE IDoc so we can take a look at the IDoc segments and fields:

SE38 program RFALEX00

RFALEX00 screen

RFALEX00 screen 2

RFALEX00 output

EXCHANGE_RATE Outbound IDoc

Here is the call to OANDA:

https://www1.oanda.com/rates/api/v2/rates/candle.xml?api_key=your_api_key_here&date_time=2021-08-15&...

Postman OANDA xml call

Here is the response:

<?xml version="1.0" encoding="utf-8"?>

<response>

    <meta>

        <effective_params>

            <data_set>OANDA</data_set>

            <base_currencies>

                <currency>USD</currency>

            </base_currencies>

            <quote_currencies>

                <currency>CAD</currency>

            </quote_currencies>

            <date_time>2021-08-15T00:00:00+00:00</date_time>

            <fields>

                <field>averages</field>

            </fields>

        </effective_params>

        <endpoint>candle</endpoint>

        <request_time>2021-10-29T04:17:36+00:00</request_time>

        <skipped_currency_pairs></skipped_currency_pairs>

    </meta>

    <quotes>

        <quote>

            <base_currency>USD</base_currency>

            <quote_currency>CAD</quote_currency>

            <start_time>2021-08-15T00:00:00+00:00</start_time>

            <open_time>2021-08-15T00:00:00+00:00</open_time>

            <close_time>2021-08-15T23:59:59+00:00</close_time>

            <average_bid>1.25114</average_bid>

            <average_ask>1.25180</average_ask>

            <average_midpoint>1.25147</average_midpoint>

        </quote>

    </quotes>

</response>

Here is the IFlow:

OANDA_ExchangeRate_To_SAP IFlow

For now, we just have a timer that executes when we deploy the flow. This can be scheduled at regular intervals as needed.

Here is the Set Dates Groovy script to set the date interval of the OANDA API call:

import com.sap.gateway.ip.core.customdev.util.Message;

import java.util.HashMap;

import java.util.Calendar;

import java.util.Date;



def Message processData(Message message) {



//    def yesterday = new Date() - 1

//    message.setProperty("yesterday", yesterday.format("yyyy-MM-dd"))

//    return message





//  Get instance of the calender

    Calendar calendar = Calendar.getInstance();



//  Let's go to last month

    calendar.add(Calendar.MONTH, -1);



//  Get the last date of that month

    int max = calendar.getActualMaximum(Calendar.DAY_OF_MONTH);

    

//  Set the calendar to the end of that month    

    calendar.set(Calendar.DAY_OF_MONTH, max);



//  Get the date of the this last day of the month  

    def endOfLastMonth = calendar.getTime();



//  Set this property

    message.setProperty("endOfLastMonth", endOfLastMonth.format("yyyy-MM-dd"))





//  Let's go to month before last month

    calendar.add(Calendar.MONTH, -1);



//  Get the last date of that month

    max = calendar.getActualMaximum(Calendar.DAY_OF_MONTH);

    

//  Set the calendar to the end of that month    

    calendar.set(Calendar.DAY_OF_MONTH, max);



//  Get the date of the this last day of the month  

    def endOfLastLastMonth = calendar.getTime();



//  Set this property

    message.setProperty("endOfLastLastMonth", endOfLastMonth.format("yyyy-MM-dd"))





//   Read in the externalized parameter

    def map = message.getProperties();

    def mode = map.get("mode");

    



    def strDateInput;

     

     if (mode.equals("candle")) {

        strInput = "date_time=" + endOfLastMonth.format("yyyy-MM-dd");

        message.setProperty("dateInput", strInput);

     } else if (mode.equals("aggregated")) {

        strInput = "start_time=" + endOfLastLastMonth.format("yyyy-MM-dd") + "&end_time=" + endOfLastMonth.format("yyyy-MM-dd");

        message.setProperty("dateInput",strInput);

         

     } else {

        strInput = "Logic=NotWorking";

        message.setProperty("dateInput", strInput);

     }

    

    return message;

}

Here is the HTTP Connection to OANDA:

HTTP Connection for OANDA

Here is the Groovy script to log the OANDA response:

import com.sap.gateway.ip.core.customdev.util.Message;

import java.util.HashMap;



def Message processData(Message message) {



   def body = message.getBody(java.lang.String) as String;

   def messageLog = messageLogFactory.getMessageLog(message);



    //Properties

    def properties = message.getProperties();

    

   String sBody = "OANDA Response XML";





   if(messageLog != null) {

       messageLog.setStringProperty(sBody, body);

       messageLog.addAttachmentAsString(sBody, body, 'text/xml');

    }



   return message;

}

Here is the simple graphical mapping of the OANDA response XML to the Exchange Rate IDoc:

OANDA Response to ExchangeRate IDoc Mapping

OANDA Response to ExchangeRate IDoc Mapping continued

Here is the Groovy script to log the mapped Exchange rate IDoc:

import com.sap.gateway.ip.core.customdev.util.Message;

import java.util.HashMap;



def Message processData(Message message) {



   def body = message.getBody(java.lang.String) as String;

   def messageLog = messageLogFactory.getMessageLog(message);



    //Properties

    def properties = message.getProperties();

    

   String sBody = "ExchangeRate IDoc for S4 HANA";





   if(messageLog != null) {

       messageLog.setStringProperty(sBody, body);

       messageLog.addAttachmentAsString(sBody, body, 'text/xml');

    }



   return message;

}

IDOC Receiver General tab

IDOC Receiver Connection tab

So here is the execution of the IFlow with the OANDA response and mapped IDoc captured as attachments:

Message Monitoring

Here is the OANDA response XML:

<?xml version="1.0" encoding="utf-8"?>

<response>

  <meta>

    <effective_params>

      <data_set>OANDA</data_set>

      <base_currencies>

        <currency>CAD</currency>

        <currency>EUR</currency>

        <currency>USD</currency>

        <currency>USD</currency>

      </base_currencies>

      <quote_currencies>

        <currency>CAD</currency>

        <currency>EUR</currency>

        <currency>GBP</currency>

        <currency>USD</currency>

      </quote_currencies>

      <date_time>2021-09-30T00:00:00+00:00</date_time>

      <fields>

        <field>averages</field>

      </fields>

    </effective_params>

    <endpoint>candle</endpoint>

    <request_time>2021-10-15T17:44:43+00:00</request_time>

    <skipped_currency_pairs>

    </skipped_currency_pairs>

  </meta>

  <quotes>

    <quote>

      <base_currency>CAD</base_currency>

      <quote_currency>CAD</quote_currency>

      <start_time>2021-09-30T00:00:00+00:00</start_time>

      <open_time>2021-09-30T00:00:00+00:00</open_time>

      <close_time>2021-09-30T23:59:59+00:00</close_time>

      <average_bid>1.00000</average_bid>

      <average_ask>1.00000</average_ask>

      <average_midpoint>1.00000</average_midpoint>

    </quote>

    <quote>

      <base_currency>CAD</base_currency>

      <quote_currency>EUR</quote_currency>

      <start_time>2021-09-30T00:00:00+00:00</start_time>

      <open_time>2021-09-30T00:00:00+00:00</open_time>

      <close_time>2021-09-30T23:59:59+00:00</close_time>

      <average_bid>0.678736</average_bid>

      <average_ask>0.678941</average_ask>

      <average_midpoint>0.678838</average_midpoint>

    </quote>

    <quote>

      <base_currency>CAD</base_currency>

      <quote_currency>GBP</quote_currency>

      <start_time>2021-09-30T00:00:00+00:00</start_time>

      <open_time>2021-09-30T00:00:00+00:00</open_time>

      <close_time>2021-09-30T23:59:59+00:00</close_time>

      <average_bid>0.584443</average_bid>

      <average_ask>0.584625</average_ask>

      <average_midpoint>0.584534</average_midpoint>

    </quote>

    <quote>

      <base_currency>CAD</base_currency>

      <quote_currency>USD</quote_currency>

      <start_time>2021-09-30T00:00:00+00:00</start_time>

      <open_time>2021-09-30T00:00:00+00:00</open_time>

      <close_time>2021-09-30T23:59:59+00:00</close_time>

      <average_bid>0.786678</average_bid>

      <average_ask>0.786811</average_ask>

      <average_midpoint>0.786744</average_midpoint>

    </quote>

    <quote>

      <base_currency>EUR</base_currency>

      <quote_currency>CAD</quote_currency>

      <start_time>2021-09-30T00:00:00+00:00</start_time>

      <open_time>2021-09-30T00:00:00+00:00</open_time>

      <close_time>2021-09-30T23:59:59+00:00</close_time>

      <average_bid>1.47288</average_bid>

      <average_ask>1.47333</average_ask>

      <average_midpoint>1.47310</average_midpoint>

    </quote>

    <quote>

      <base_currency>EUR</base_currency>

      <quote_currency>EUR</quote_currency>

      <start_time>2021-09-30T00:00:00+00:00</start_time>

      <open_time>2021-09-30T00:00:00+00:00</open_time>

      <close_time>2021-09-30T23:59:59+00:00</close_time>

      <average_bid>1.00000</average_bid>

      <average_ask>1.00000</average_ask>

      <average_midpoint>1.00000</average_midpoint>

    </quote>

    <quote>

      <base_currency>EUR</base_currency>

      <quote_currency>GBP</quote_currency>

      <start_time>2021-09-30T00:00:00+00:00</start_time>

      <open_time>2021-09-30T00:00:00+00:00</open_time>

      <close_time>2021-09-30T23:59:59+00:00</close_time>

      <average_bid>0.860986</average_bid>

      <average_ask>0.861170</average_ask>

      <average_midpoint>0.861078</average_midpoint>

    </quote>

    <quote>

      <base_currency>EUR</base_currency>

      <quote_currency>USD</quote_currency>

      <start_time>2021-09-30T00:00:00+00:00</start_time>

      <open_time>2021-09-30T00:00:00+00:00</open_time>

      <close_time>2021-09-30T23:59:59+00:00</close_time>

      <average_bid>1.15888</average_bid>

      <average_ask>1.15903</average_ask>

      <average_midpoint>1.15896</average_midpoint>

    </quote>

    <quote>

      <base_currency>USD</base_currency>

      <quote_currency>CAD</quote_currency>

      <start_time>2021-09-30T00:00:00+00:00</start_time>

      <open_time>2021-09-30T00:00:00+00:00</open_time>

      <close_time>2021-09-30T23:59:59+00:00</close_time>

      <average_bid>1.27095</average_bid>

      <average_ask>1.27117</average_ask>

      <average_midpoint>1.27106</average_midpoint>

    </quote>

    <quote>

      <base_currency>USD</base_currency>

      <quote_currency>CAD</quote_currency>

      <start_time>2021-09-30T00:00:00+00:00</start_time>

      <open_time>2021-09-30T00:00:00+00:00</open_time>

      <close_time>2021-09-30T23:59:59+00:00</close_time>

      <average_bid>1.27095</average_bid>

      <average_ask>1.27117</average_ask>

      <average_midpoint>1.27106</average_midpoint>

    </quote>

    <quote>

      <base_currency>USD</base_currency>

      <quote_currency>EUR</quote_currency>

      <start_time>2021-09-30T00:00:00+00:00</start_time>

     <open_time>2021-09-30T00:00:00+00:00</open_time>

      <close_time>2021-09-30T23:59:59+00:00</close_time>

      <average_bid>0.862788</average_bid>

      <average_ask>0.862903</average_ask>

      <average_midpoint>0.862846</average_midpoint>

    </quote>

    <quote>

      <base_currency>USD</base_currency>

      <quote_currency>EUR</quote_currency>

      <start_time>2021-09-30T00:00:00+00:00</start_time>

      <open_time>2021-09-30T00:00:00+00:00</open_time>

      <close_time>2021-09-30T23:59:59+00:00</close_time>

      <average_bid>0.862788</average_bid>

      <average_ask>0.862903</average_ask>

      <average_midpoint>0.862846</average_midpoint>

    </quote>

    <quote>

      <base_currency>USD</base_currency>

      <quote_currency>GBP</quote_currency>

      <start_time>2021-09-30T00:00:00+00:00</start_time>

      <open_time>2021-09-30T00:00:00+00:00</open_time>

      <close_time>2021-09-30T23:59:59+00:00</close_time>

      <average_bid>0.742926</average_bid>

      <average_ask>0.743031</average_ask>

      <average_midpoint>0.742978</average_midpoint>

    </quote>

    <quote>

      <base_currency>USD</base_currency>

      <quote_currency>GBP</quote_currency>

      <start_time>2021-09-30T00:00:00+00:00</start_time>

      <open_time>2021-09-30T00:00:00+00:00</open_time>

      <close_time>2021-09-30T23:59:59+00:00</close_time>

      <average_bid>0.742926</average_bid>

      <average_ask>0.743031</average_ask>

      <average_midpoint>0.742978</average_midpoint>

    </quote>

    <quote>

      <base_currency>USD</base_currency>

      <quote_currency>USD</quote_currency>

      <start_time>2021-09-30T00:00:00+00:00</start_time>

      <open_time>2021-09-30T00:00:00+00:00</open_time>

      <close_time>2021-09-30T23:59:59+00:00</close_time>

      <average_bid>1.00000</average_bid>

      <average_ask>1.00000</average_ask>

      <average_midpoint>1.00000</average_midpoint>

    </quote>

    <quote>

      <base_currency>USD</base_currency>

      <quote_currency>USD</quote_currency>

      <start_time>2021-09-30T00:00:00+00:00</start_time>

      <open_time>2021-09-30T00:00:00+00:00</open_time>

      <close_time>2021-09-30T23:59:59+00:00</close_time>

      <average_bid>1.00000</average_bid>

      <average_ask>1.00000</average_ask>

      <average_midpoint>1.00000</average_midpoint>

    </quote>

  </quotes>

</response>

Here is the EXCHANGE_RATE IDoc:

<?xml version="1.0" encoding="UTF-8"?>

<EXCHANGE_RATE01>

  <IDOC BEGIN="1">

    <EDI_DC40 SEGMENT="1">

      <TABNAM>EDI_DC40</TABNAM>

      <DIRECT>1</DIRECT>

      <IDOCTYP>EXCHANGE_RATE01</IDOCTYP>

      <MESTYP>EXCHANGE_RATE</MESTYP>

      <SNDPOR>CPIDEV</SNDPOR>

      <SNDPRT>LS</SNDPRT>

      <SNDPRN>CPIDEV</SNDPRN>

      <RCVPOR>SAPSS4</RCVPOR>

      <RCVPRT>LS</RCVPRT>

      <RCVPRN>SS4CLNT055</RCVPRN>

    </EDI_DC40>

    <E1EXCHANGE_RATE SEGMENT="1">

      <LOG_SYSTEM>CPIDEV</LOG_SYSTEM>

      <UPD_ALLOW>X</UPD_ALLOW>

      <DEV_ALLOW>000</DEV_ALLOW>

      <E1BP1093_0 SEGMENT="1">

        <RATE_TYPE>M</RATE_TYPE>

        <FROM_CURR>CAD</FROM_CURR>

        <TO_CURRNCY>CAD</TO_CURRNCY>

        <VALID_FROM>20210816</VALID_FROM>

        <EXCH_RATE>1.00000</EXCH_RATE>

        <FROM_FACTOR>1</FROM_FACTOR>

        <TO_FACTOR>1</TO_FACTOR>

      </E1BP1093_0>

      <E1BP1093_0 SEGMENT="1">

        <RATE_TYPE>M</RATE_TYPE>

        <FROM_CURR>CAD</FROM_CURR>

        <TO_CURRNCY>EUR</TO_CURRNCY>

        <VALID_FROM>20210816</VALID_FROM>

        <EXCH_RATE>0.678838</EXCH_RATE>

        <FROM_FACTOR>1</FROM_FACTOR>

        <TO_FACTOR>1</TO_FACTOR>

      </E1BP1093_0>

      <E1BP1093_0 SEGMENT="1">

        <RATE_TYPE>M</RATE_TYPE>

        <FROM_CURR>CAD</FROM_CURR>

        <TO_CURRNCY>GBP</TO_CURRNCY>

        <VALID_FROM>20210816</VALID_FROM>

        <EXCH_RATE>0.584534</EXCH_RATE>

        <FROM_FACTOR>1</FROM_FACTOR>

        <TO_FACTOR>1</TO_FACTOR>

      </E1BP1093_0>

      <E1BP1093_0 SEGMENT="1">

        <RATE_TYPE>M</RATE_TYPE>

        <FROM_CURR>CAD</FROM_CURR>

        <TO_CURRNCY>USD</TO_CURRNCY>

        <VALID_FROM>20210816</VALID_FROM>

        <EXCH_RATE>0.786744</EXCH_RATE>

        <FROM_FACTOR>1</FROM_FACTOR>

        <TO_FACTOR>1</TO_FACTOR>

      </E1BP1093_0>

      <E1BP1093_0 SEGMENT="1">

        <RATE_TYPE>M</RATE_TYPE>

        <FROM_CURR>EUR</FROM_CURR>

        <TO_CURRNCY>CAD</TO_CURRNCY>

        <VALID_FROM>20210816</VALID_FROM>

        <EXCH_RATE>1.47310</EXCH_RATE>

        <FROM_FACTOR>1</FROM_FACTOR>

        <TO_FACTOR>1</TO_FACTOR>

      </E1BP1093_0>

      <E1BP1093_0 SEGMENT="1">

        <RATE_TYPE>M</RATE_TYPE>

        <FROM_CURR>EUR</FROM_CURR>

        <TO_CURRNCY>EUR</TO_CURRNCY>

        <VALID_FROM>20210816</VALID_FROM>

        <EXCH_RATE>1.00000</EXCH_RATE>

        <FROM_FACTOR>1</FROM_FACTOR>

        <TO_FACTOR>1</TO_FACTOR>

      </E1BP1093_0>

      <E1BP1093_0 SEGMENT="1">

        <RATE_TYPE>M</RATE_TYPE>

        <FROM_CURR>EUR</FROM_CURR>

        <TO_CURRNCY>GBP</TO_CURRNCY>

        <VALID_FROM>20210816</VALID_FROM>

        <EXCH_RATE>0.861078</EXCH_RATE>

        <FROM_FACTOR>1</FROM_FACTOR>

        <TO_FACTOR>1</TO_FACTOR>

      </E1BP1093_0>

      <E1BP1093_0 SEGMENT="1">

        <RATE_TYPE>M</RATE_TYPE>

        <FROM_CURR>EUR</FROM_CURR>

        <TO_CURRNCY>USD</TO_CURRNCY>

        <VALID_FROM>20210816</VALID_FROM>

        <EXCH_RATE>1.15896</EXCH_RATE>

        <FROM_FACTOR>1</FROM_FACTOR>

        <TO_FACTOR>1</TO_FACTOR>

      </E1BP1093_0>

      <E1BP1093_0 SEGMENT="1">

        <RATE_TYPE>M</RATE_TYPE>

        <FROM_CURR>USD</FROM_CURR>

        <TO_CURRNCY>CAD</TO_CURRNCY>

        <VALID_FROM>20210816</VALID_FROM>

        <EXCH_RATE>1.27106</EXCH_RATE>

        <FROM_FACTOR>1</FROM_FACTOR>

        <TO_FACTOR>1</TO_FACTOR>

      </E1BP1093_0>

      <E1BP1093_0 SEGMENT="1">

        <RATE_TYPE>M</RATE_TYPE>

        <FROM_CURR>USD</FROM_CURR>

        <TO_CURRNCY>CAD</TO_CURRNCY>

        <VALID_FROM>20210816</VALID_FROM>

        <EXCH_RATE>1.27106</EXCH_RATE>

        <FROM_FACTOR>1</FROM_FACTOR>

        <TO_FACTOR>1</TO_FACTOR>

      </E1BP1093_0>

      <E1BP1093_0 SEGMENT="1">

        <RATE_TYPE>M</RATE_TYPE>

        <FROM_CURR>USD</FROM_CURR>

        <TO_CURRNCY>EUR</TO_CURRNCY>

        <VALID_FROM>20210816</VALID_FROM>

        <EXCH_RATE>0.862846</EXCH_RATE>

        <FROM_FACTOR>1</FROM_FACTOR>

        <TO_FACTOR>1</TO_FACTOR>

      </E1BP1093_0>

      <E1BP1093_0 SEGMENT="1">

        <RATE_TYPE>M</RATE_TYPE>

        <FROM_CURR>USD</FROM_CURR>

        <TO_CURRNCY>EUR</TO_CURRNCY>

        <VALID_FROM>20210816</VALID_FROM>

        <EXCH_RATE>0.862846</EXCH_RATE>

        <FROM_FACTOR>1</FROM_FACTOR>

        <TO_FACTOR>1</TO_FACTOR>

     </E1BP1093_0>

      <E1BP1093_0 SEGMENT="1">

        <RATE_TYPE>M</RATE_TYPE>

        <FROM_CURR>USD</FROM_CURR>

        <TO_CURRNCY>GBP</TO_CURRNCY>

        <VALID_FROM>20210816</VALID_FROM>

        <EXCH_RATE>0.742978</EXCH_RATE>

        <FROM_FACTOR>1</FROM_FACTOR>

        <TO_FACTOR>1</TO_FACTOR>

      </E1BP1093_0>

      <E1BP1093_0 SEGMENT="1">

        <RATE_TYPE>M</RATE_TYPE>

        <FROM_CURR>USD</FROM_CURR>

        <TO_CURRNCY>GBP</TO_CURRNCY>

        <VALID_FROM>20210816</VALID_FROM>

        <EXCH_RATE>0.742978</EXCH_RATE>

        <FROM_FACTOR>1</FROM_FACTOR>

        <TO_FACTOR>1</TO_FACTOR>

      </E1BP1093_0>

      <E1BP1093_0 SEGMENT="1">

        <RATE_TYPE>M</RATE_TYPE>

        <FROM_CURR>USD</FROM_CURR>

        <TO_CURRNCY>USD</TO_CURRNCY>

        <VALID_FROM>20210816</VALID_FROM>

        <EXCH_RATE>1.00000</EXCH_RATE>

        <FROM_FACTOR>1</FROM_FACTOR>

        <TO_FACTOR>1</TO_FACTOR>

      </E1BP1093_0>

      <E1BP1093_0 SEGMENT="1">

        <RATE_TYPE>M</RATE_TYPE>

        <FROM_CURR>USD</FROM_CURR>

        <TO_CURRNCY>USD</TO_CURRNCY>

        <VALID_FROM>20210816</VALID_FROM>

        <EXCH_RATE>1.00000</EXCH_RATE>

        <FROM_FACTOR>1</FROM_FACTOR>

        <TO_FACTOR>1</TO_FACTOR>

      </E1BP1093_0>

    </E1EXCHANGE_RATE>

  </IDOC>

</EXCHANGE_RATE01>

So to summarize, in this example CPI triggers the OANDA exchange rate API call and maps the response data to the Exchange rate IDoc and send this to SAP to update the TCURR table.

I hope that comes in handy to you if you are trying to implement a CPI interface that calls an external foreign exchange rate API but not necessarily OANDA, and map this back to SAP format and send this to SAP S/4 HANA system. We also investigated the SOAP approach but we only see the outbound SOAP async ExchangeRateMasterDataReplicationBundleRequest_Out and not an equivalent inbound SOAP async message.

Here you can see the outbound exchange rate

SAP API Hub Exchange Rate Outbound

However, there does not appear to be an inbound asynchronous service exposed through the API Hub at this point but hopefully that will be added soon. But the IDoc approach works really well.

See you at the next blog...

Thanks,

Jay

You can connect with me at - Jay Malla on LinkedIn

Loading SAP S/4 HANA Exchange Rates from OANDA Foreign Exchange Rate API through CPI integration

SAP PI for Beginners

ABAP 7.40 Quick Reference

Fiori: technical installation and configuration of one app from A - Z