Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
jmalla
Contributor
9,292
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

 

 

 
4 Comments
Labels in this area