SAP Integration Suite, Cloud Integration

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

webadmin

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/

Publishing Events from S/4 HANA to Event Mesh for Cloud integration for external systems

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.

SAP Ariba Integration Certification Preparation Guide

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&base=USD&quote=CAD&fields=averages
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.