1 1 1 1 1 Rating 0% (0 Votes)

One of the standard requests that comes with every SAP implementation is automatic download of exchange rates to your system. Today I will show you on an example of Central Bank Of Turkey, how to transform XML from bank site into internal table, which can be then used inside your code to populate values of  BAPI_EXCHANGERATE_CREATE FM.

There are few ways to download the XML to your ABAP code, I prefer to create an RFC destination (lets call it here Z_DESTINATION_TO_TCMB), as on many servers you need to provide credentials for PROXY and it is safer to keep it saved inside RFC destination, than in your ABAP code in plain text.

So lets assume you have an RFC destination that points to https://tcmb.gov.tr/kurlar/today.xml. It will look like this:

 

The XML behind has following structure:

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="/isokur.xsl"?>
<Tarih_Date Tarih="06.04.2020" Date="04/06/2020"  Bulten_No="2020/68" >
    <Currency CrossOrder="0" Kod="USD" CurrencyCode="USD">
            <Unit>1</Unit>
            <Isim>ABD DOLARI</Isim>
            <CurrencyName>US DOLLAR</CurrencyName>
            <ForexBuying>6.7549</ForexBuying>
            <ForexSelling>6.7671</ForexSelling>
            <BanknoteBuying>6.7502</BanknoteBuying>
            <BanknoteSelling>6.7773</BanknoteSelling>
            <CrossRateUSD/>
            <CrossRateOther/>
        
    </Currency>
    <Currency CrossOrder="1" Kod="AUD" CurrencyCode="AUD">
            <Unit>1</Unit>
            <Isim>AVUSTRALYA DOLARI</Isim>
            <CurrencyName>AUSTRALIAN DOLLAR</CurrencyName>
            <ForexBuying>4.0841</ForexBuying>
            <ForexSelling>4.1108</ForexSelling>
            <BanknoteBuying>4.0654</BanknoteBuying>
            <BanknoteSelling>4.1354</BanknoteSelling>
                <CrossRateUSD>1.6501</CrossRateUSD>
                <CrossRateOther/>
        
    </Currency>
    <Currency CrossOrder="2" Kod="DKK" CurrencyCode="DKK">
            <Unit>1</Unit>
            <Isim>DANİMARKA KRONU</Isim>
            <CurrencyName>DANISH KRONE</CurrencyName>
            <ForexBuying>0.97584</ForexBuying>
            <ForexSelling>0.98063</ForexSelling>
            <BanknoteBuying>0.97515</BanknoteBuying>
            <BanknoteSelling>0.98289</BanknoteSelling>
                <CrossRateUSD>6.9115</CrossRateUSD>
                <CrossRateOther/>
        
    </Currency>
    <Currency CrossOrder="9" Kod="EUR" CurrencyCode="EUR">
            <Unit>1</Unit>
            <Isim>EURO</Isim>
            <CurrencyName>EURO</CurrencyName>
            <ForexBuying>7.2990</ForexBuying>
            <ForexSelling>7.3121</ForexSelling>
            <BanknoteBuying>7.2939</BanknoteBuying>
            <BanknoteSelling>7.3231</BanknoteSelling>
                <CrossRateUSD/>
                <CrossRateOther>1.0805</CrossRateOther>
        
    </Currency>
    <Currency CrossOrder="10" Kod="GBP" CurrencyCode="GBP">
            <Unit>1</Unit>
            <Isim>İNGİLİZ STERLİNİ</Isim>
            <CurrencyName>POUND STERLING</CurrencyName>
            <ForexBuying>8.2941</ForexBuying>
            <ForexSelling>8.3373</ForexSelling>
            <BanknoteBuying>8.2883</BanknoteBuying>
            <BanknoteSelling>8.3498</BanknoteSelling>
                <CrossRateUSD/>
                <CrossRateOther>1.2300</CrossRateOther>
        
    </Currency>
    <Currency CrossOrder="3" Kod="CHF" CurrencyCode="CHF">
            <Unit>1</Unit>
            <Isim>İSVİÇRE FRANGI</Isim>
            <CurrencyName>SWISS FRANK</CurrencyName>
            <ForexBuying>6.8935</ForexBuying>
            <ForexSelling>6.9378</ForexSelling>
            <BanknoteBuying>6.8832</BanknoteBuying>
            <BanknoteSelling>6.9482</BanknoteSelling>
                <CrossRateUSD>0.9776</CrossRateUSD>
                <CrossRateOther/>
        
    </Currency>
    <Currency CrossOrder="4" Kod="SEK" CurrencyCode="SEK">
            <Unit>1</Unit>
            <Isim>İSVEÇ KRONU</Isim>
            <CurrencyName>SWEDISH KRONA</CurrencyName>
            <ForexBuying>0.66188</ForexBuying>
            <ForexSelling>0.66873</ForexSelling>
            <BanknoteBuying>0.66141</BanknoteBuying>
            <BanknoteSelling>0.67027</BanknoteSelling>
                <CrossRateUSD>10.16</CrossRateUSD>
                <CrossRateOther/>
        
    </Currency>
    <Currency CrossOrder="6" Kod="CAD" CurrencyCode="CAD">
            <Unit>1</Unit>
            <Isim>KANADA DOLARI</Isim>
            <CurrencyName>CANADIAN DOLLAR</CurrencyName>
            <ForexBuying>4.7765</ForexBuying>
            <ForexSelling>4.7980</ForexSelling>
            <BanknoteBuying>4.7588</BanknoteBuying>
            <BanknoteSelling>4.8162</BanknoteSelling>
                <CrossRateUSD>1.4123</CrossRateUSD>
                <CrossRateOther/>
        
    </Currency>
    <Currency CrossOrder="11" Kod="KWD" CurrencyCode="KWD">
            <Unit>1</Unit>
            <Isim>KUVEYT DİNARI</Isim>
            <CurrencyName>KUWAITI DINAR</CurrencyName>
            <ForexBuying>21.7240</ForexBuying>
            <ForexSelling>22.0083</ForexSelling>
            <BanknoteBuying>21.3982</BanknoteBuying>
            <BanknoteSelling>22.3384</BanknoteSelling>
                <CrossRateUSD/>
                <CrossRateOther>3.2342</CrossRateOther>
        
    </Currency>
    <Currency CrossOrder="7" Kod="NOK" CurrencyCode="NOK">
            <Unit>1</Unit>
            <Isim>NORVEÇ KRONU</Isim>
            <CurrencyName>NORWEGIAN KRONE</CurrencyName>
            <ForexBuying>0.64160</ForexBuying>
            <ForexSelling>0.64591</ForexSelling>
            <BanknoteBuying>0.64115</BanknoteBuying>
            <BanknoteSelling>0.64739</BanknoteSelling>
                <CrossRateUSD>10.50</CrossRateUSD>
                <CrossRateOther/>
        
    </Currency>
    <Currency CrossOrder="8" Kod="SAR" CurrencyCode="SAR">
            <Unit>1</Unit>
            <Isim>SUUDİ ARABİSTAN RİYALİ</Isim>
            <CurrencyName>SAUDI RIYAL</CurrencyName>
            <ForexBuying>1.7958</ForexBuying>
            <ForexSelling>1.7990</ForexSelling>
            <BanknoteBuying>1.7823</BanknoteBuying>
            <BanknoteSelling>1.8125</BanknoteSelling>
                <CrossRateUSD>3.7615</CrossRateUSD>
                <CrossRateOther/>
        
    </Currency>
    <Currency CrossOrder="5" Kod="JPY" CurrencyCode="JPY">
            <Unit>100</Unit>
            <Isim>JAPON YENİ</Isim>
            <CurrencyName>JAPENESE YEN</CurrencyName>
            <ForexBuying>6.1745</ForexBuying>
            <ForexSelling>6.2154</ForexSelling>
            <BanknoteBuying>6.1517</BanknoteBuying>
            <BanknoteSelling>6.2390</BanknoteSelling>
                <CrossRateUSD>109.14</CrossRateUSD>
                <CrossRateOther/>
        
    </Currency>
    <Currency CrossOrder="12" Kod="BGN" CurrencyCode="BGN">
            <Unit>1</Unit>
            <Isim>BULGAR LEVASI</Isim>
            <CurrencyName>BULGARIAN LEV</CurrencyName>
            <ForexBuying>3.7110</ForexBuying>
            <ForexSelling>3.7596</ForexSelling>
            <BanknoteBuying></BanknoteBuying>
            <BanknoteSelling></BanknoteSelling>
                <CrossRateUSD>1.8100</CrossRateUSD>
                <CrossRateOther/>
        
    </Currency>
    <Currency CrossOrder="13" Kod="RON" CurrencyCode="RON">
            <Unit>1</Unit>
            <Isim>RUMEN LEYİ</Isim>
            <CurrencyName>NEW LEU</CurrencyName>
            <ForexBuying>1.5024</ForexBuying>
            <ForexSelling>1.5221</ForexSelling>
            <BanknoteBuying></BanknoteBuying>
            <BanknoteSelling></BanknoteSelling>
                <CrossRateUSD>4.4709</CrossRateUSD>
                <CrossRateOther/>
        
    </Currency>
    <Currency CrossOrder="14" Kod="RUB" CurrencyCode="RUB">
            <Unit>1</Unit>
            <Isim>RUS RUBLESİ</Isim>
            <CurrencyName>RUSSIAN ROUBLE</CurrencyName>
            <ForexBuying>0.08786</ForexBuying>
            <ForexSelling>0.08901</ForexSelling>
            <BanknoteBuying></BanknoteBuying>
            <BanknoteSelling></BanknoteSelling>
                <CrossRateUSD>76.46</CrossRateUSD>
                <CrossRateOther/>
        
    </Currency>
    <Currency CrossOrder="15" Kod="IRR" CurrencyCode="IRR">
            <Unit>100</Unit>
            <Isim>İRAN RİYALİ</Isim>
            <CurrencyName>IRANIAN RIAL</CurrencyName>
            <ForexBuying>0.01599</ForexBuying>
            <ForexSelling>0.01620</ForexSelling>
            <BanknoteBuying></BanknoteBuying>
            <BanknoteSelling></BanknoteSelling>
                <CrossRateUSD>42000</CrossRateUSD>
                <CrossRateOther/>
        
    </Currency>
    <Currency CrossOrder="16" Kod="CNY" CurrencyCode="CNY">
            <Unit>1</Unit>
            <Isim>ÇİN YUANI</Isim>
            <CurrencyName>CHINESE RENMINBI</CurrencyName>
            <ForexBuying>0.94703</ForexBuying>
            <ForexSelling>0.95943</ForexSelling>
            <BanknoteBuying></BanknoteBuying>
            <BanknoteSelling></BanknoteSelling>
                <CrossRateUSD>7.0928</CrossRateUSD>
                <CrossRateOther/>
        
    </Currency>
    <Currency CrossOrder="17" Kod="PKR" CurrencyCode="PKR">
            <Unit>1</Unit>
            <Isim>PAKİSTAN RUPİSİ</Isim>
            <CurrencyName>PAKISTANI RUPEE</CurrencyName>
            <ForexBuying>0.04016</ForexBuying>
            <ForexSelling>0.04069</ForexSelling>
            <BanknoteBuying></BanknoteBuying>
            <BanknoteSelling></BanknoteSelling>
                <CrossRateUSD>167.26</CrossRateUSD>
                <CrossRateOther/>
        
    </Currency>
    <Currency CrossOrder="18" Kod="QAR" CurrencyCode="QAR">
            <Unit>1</Unit>
            <Isim>KATAR RİYALİ</Isim>
            <CurrencyName>QATARI RIAL</CurrencyName>
            <ForexBuying>1.8321</ForexBuying>
            <ForexSelling>1.8561</ForexSelling>
            <BanknoteBuying></BanknoteBuying>
            <BanknoteSelling></BanknoteSelling>
                <CrossRateUSD>3.6663</CrossRateUSD>
                <CrossRateOther/>
        
    </Currency>
    <Currency CrossOrder="0" Kod="XDR" CurrencyCode="XDR">
        <Unit>1</Unit>
        <Isim>ÖZEL ÇEKME HAKKI (SDR)                            </Isim>
        <CurrencyName>SPECIAL DRAWING RIGHT (SDR)                       </CurrencyName>
        <ForexBuying>9.1853</ForexBuying>
        <ForexSelling/>
        <BanknoteBuying/>
        <BanknoteSelling/>
        <CrossRateUSD/>
        <CrossRateOther>1.35856</CrossRateOther>
    </Currency>
</Tarih_Date>

 

Now there are two main ways to convert XML, you can parse XML using for example CL_XML_DOCUMENT class, or you can create a transformation that will convert the XML into internal table.

I've choosen the second option in this example, as I've never done XSLT transformation before. I've ended up with transformation Z_TCMB_TRANSFORMATION that you can find bellow. 

<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:sap="http://www.sap.com/sapxsl" version="1.0">
  <xsl:strip-space elements="*"/>
  <xsl:template match="/">
    <asx:abap xmlns:asx="http://www.sap.com/abapxml" version="1.0">
      <asx:values>
        <EXCHANGE_RATES>
          <IMPORT_FILE>
            <xsl:for-each select="/Tarih_Date">
              <TARIH>
                <xsl:value-of select="@Tarih"/>
              </TARIH>
              <DATE>
                <xsl:value-of select="@Date"/>
              </DATE>
              <BULTEN_NO>
                <xsl:value-of select="@Bulten_No"/>
              </BULTEN_NO>
              <CURRENCIES>
                <xsl:for-each select="/Tarih_Date/Currency">
                  <CURRENCY>
                    <CROSS_ORDER>
                      <xsl:value-of select="@CrossOrder"/>
                    </CROSS_ORDER>
                    <KOD>
                      <xsl:value-of select="@Kod"/>
                    </KOD>
                    <CURRENCY_CODE>
                      <xsl:value-of select="@CurrencyCode"/>
                    </CURRENCY_CODE>
                    <UNIT>
                      <xsl:value-of select="Unit"/>
                    </UNIT>
                    <ISIM>
                      <xsl:value-of select="Isim"/>
                    </ISIM>
                    <CURRENCY_NAME>
                      <xsl:value-of select="CurrencyName"/>
                    </CURRENCY_NAME>
                    <FOREX_BUYING>
                      <xsl:value-of select="ForexBuying"/>
                    </FOREX_BUYING>
                    <FOREX_SELLING>
                      <xsl:value-of select="ForexSelling"/>
                    </FOREX_SELLING>
                    <BANKNOTE_BUYING>
                      <xsl:value-of select="BanknoteBuying"/>
                    </BANKNOTE_BUYING>
                    <BANKNOTE_SELLING>
                      <xsl:value-of select="BanknoteSelling"/>
                    </BANKNOTE_SELLING>
                    <CROSS_RATE_USD>
                      <xsl:value-of select="CrossRateUSD"/>
                    </CROSS_RATE_USD>
                    <CROSS_RATE_OTHER>
                      <xsl:value-of select="CrossRateOther"/>
                    </CROSS_RATE_OTHER>
                  </CURRENCY>
                </xsl:for-each>
              </CURRENCIES>
            </xsl:for-each>
          </IMPORT_FILE>
        </EXCHANGE_RATES>
      </asx:values>
    </asx:abap>
  </xsl:template>
</xsl:transform>

 

To download the XML file and to call transformation, you can use following dirty ABAP code. The structure of the XML will be transformed into internal table of a type EXCHANGE_RATES.

 

CLASS lcl_exchange_rates DEFINITION.
  PUBLIC SECTION.
    TYPESBEGIN OF currency,
             cross_order      TYPE string,
             kod              TYPE string,
             currency_code    TYPE string,
             unit             TYPE string,
             isim             TYPE string,
             currency_name    TYPE string,
             forex_buying     TYPE string,
             forex_selling    TYPE string,
             banknote_buying  TYPE string,
             banknote_selling TYPE string,
             cross_rate_usd   TYPE string,
             cross_rate_other TYPE string,
           END OF currency,
           BEGIN OF exchange_rate,
             tarih      TYPE string,
             date       TYPE string,
             bulten_no  TYPE string,
             currencies TYPE STANDARD TABLE OF currency WITH DEFAULT KEY,
           END OF exchange_rate,
           exchange_rates TYPE STANDARD TABLE OF exchange_rate WITH DEFAULT KEY.
    METHODSget_exchange_rates RETURNING VALUE(exchange_ratesTYPE exchange_rates.
  PRIVATE SECTION.
    DATAxml TYPE string.
    METHODSget_xml_from_bank.
    METHODStransform_xml_to_table RETURNING VALUE(exchange_ratesTYPE exchange_rates.

ENDCLASS.

CLASS lcl_exchange_rates IMPLEMENTATION.

  METHOD get_xml_from_bank.
    cl_http_client=>create_by_destination(
        EXPORTING
          destination              'Z_DESTINATION_TO_TCMB'
        IMPORTING
          client                   DATA(client)
        EXCEPTIONS
          argument_not_found       1
          destination_not_found    2
          destination_no_authority 3
          plugin_not_active        4
          internal_error           5
          OTHERS                   6
      ).
    IF sy-subrc EQ 0.
      client->request->set_methodif_http_request=>co_request_method_get ).
      client->sendEXCEPTIONS
                     http_communication_failure 1
                     http_invalid_state         2
                     http_processing_failed     3
                     http_invalid_timeout       4
                     OTHERS                     5
                    ).
      IF sy-subrc EQ 0.

        client->receive(
        EXCEPTIONS
          http_communication_failure 1
          http_invalid_state         2
          http_processing_failed     3
          OTHERS                     ).
        IF sy-subrc EQ 0.
          xml =  client->response->get_cdata).
        ENDIF.
      ENDIF.
    ENDIF.
  ENDMETHOD.

  METHOD transform_xml_to_table.
    CALL TRANSFORMATION z_tcmb_transformation
      SOURCE XML xml
      RESULT exchange_rates exchange_rates.
  ENDMETHOD.

  METHOD get_exchange_rates.
    get_xml_from_bank).
    exchange_rates transform_xml_to_table(  ).
  ENDMETHOD.

ENDCLASS.

 

Once you have your internal table filled, you can prepare the code to use BAPI_EXCHANGERATE_CREATE.