Technical Articles
Loading SAP S/4 HANA Exchange Rates from OANDA Foreign Exchange Rate API through CPI integration
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&base=USD"e=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.
See you at the next blog…
Thanks,
Jay
You can connect with me at – Jay Malla on LinkedIn
Good Read.. Thanks Jay.