Skip to Content
Technical Articles

Bank statement in XML format

Hello SAPers!

Here is another post, where I’d like to share a couple of details about SAP’s functionality. This post as many others that I share deals with functionalities around bank statement processing. Setup of bank statement reconciliation in SAP as a rule is quite simple and intuitive, but sometimes you have to deal with more difficult topics, which are not covered by standard customizing. This post explores the mechanism behind uploading of bank statement in XML-format. Please also check out my other post on import of bank statements in CSV format.  I hope you’ll find something interesting for you.

1.   Introduction

Best practices for bank reconciliation in SAP imply upload of electronic bank statements and their postprocessing. Most banks provide bank statements in format MT940, BAI or Multicash which are well supported by SAP. These are the most popular formats, though there are other formats which SAP supports as well. However, once upon a time you’ll have to deal with some bank that doesn’t support these formats and provides statements in its own format. This post explores the options behind configuration of bank-specific statements in XML-format.

Example of bank statement in XML-format can be found below. This is dummy bank statement devised by me specifically for this example and any similarities with real life bank statements are coincidental.

<?xml version="1.0" encoding="windows-1251"?>
<statement>
  <head>
	<account_no>26209220256927</account_no>
	<bank_key>320854</bank_key>
	<currency>UAH</currency>
	<stmt_no>001</stmt_no>
	<stmt_date>10012018</stmt_date>
	<open_balance>0.00</open_balance>
	<end_balance>1000.00</end_balance>
  </head>
  <linedata>
    <line rownum="1">
	  <amount>1200.00</amount>
	  <dr_cr>C</dr_cr>
	  <bank_ref>5848</bank_ref>
	  <btc>NTRF</btc>
	  <note>Payment for consulting services, invoice #1800000018 from 01.01.2018, contract #10 from 01.01.2018</note>
	  <bp_bank_key>300023</bp_bank_key>
	  <bp_bank_acc>26004123418202</bp_bank_acc>
	  <bp_tax_id>34878963</bp_tax_id>
	  <bp_name>Sport Country LLC</bp_name>
    </line>
	<line rownum="2">
	  <amount>200.00</amount>
	  <dr_cr>D</dr_cr>
	  <bank_ref>5849</bank_ref>
	  <btc>NTRF</btc>
	  <note>Payment for utility services, invoice #431 from 01.12.2017</note>
	  <bp_bank_key>300216</bp_bank_key>
	  <bp_bank_acc>26004139529147</bp_bank_acc>
	  <bp_tax_id>34883012</bp_tax_id>
	  <bp_name>Utility Investments LLD</bp_name>
    </line>
  </linedata>
</statement>

2.   Overview of development objects and configuration activities

First step is to develop XSLT-transformation that will parse bank statement and transform it into internal table with type TTY_FEB_STATEMENT. This internal table contains two item components:

  • FEBKO – which stores requisites of bank statement header;
  • ITEMS_FEB, which in turn consists of internal tables storing line item data (i.e. FEBEP), note to payee (i.e. VWEZW) and clearing data (i.e. FEBCL).

Representation of a variable with this data type in a debug mode can be found below:

Main responsibility of a consultant with regards to this task is to prepare a mapping between tags of XML-files and fields of target structure. Check out the contents of standard tables FEBKO, FEBEP and FEBCL in SAP to understand which fields should be filled and what are the rules for data formatting.

Transformation should be created by a developer (in t-code STRANS). But if you want to do it yourself, you can check out an example below:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:sap="http://www.sap.com/sapxsl" version="1.0">
  <xsl:template match="/">
    <asx:abap xmlns:asx="http://www.sap.com/abapxml" version="1.0">
      <asx:values>
        <FEB_NTIMES>
          <FEB>
            <!-- Bank statement header data -->
            <xsl:for-each select="/statement/head">
              <xsl:variable name="stmtCurrency">
                <xsl:value-of select="currency"/>
              </xsl:variable>
              <xsl:variable name="stmtDate">
                <xsl:value-of select="concat(substring(stmt_date,5,4), substring(stmt_date,3,2), substring(stmt_date,1,2))"/>
              </xsl:variable>
              <FEBKO>
                <AZNUM>
                  <xsl:value-of select="stmt_no"/>
                </AZNUM>
                <AZDAT>
                  <xsl:value-of select="$stmtDate"/>
                </AZDAT>
                <WAERS>
                  <xsl:value-of select="stmtCurrency"/>
                </WAERS>
                <ANZES>
                  <xsl:value-of select="'1'"/>
                </ANZES>
                <BANKKEY>
                  <xsl:value-of select="bank_key"/>
                </BANKKEY>
                <BANKACC>
                  <xsl:value-of select="account_no"/>
                </BANKACC>
                <SSBTR>
                  <xsl:value-of select="open_balance"/>
                </SSBTR>
                <ESBTR>
                  <xsl:value-of select="end_balance"/>
                </ESBTR>
              </FEBKO>
            </xsl:for-each>

            <ITEMS_FEB>
              <!-- Bank statement line data -->
              <xsl:for-each select="/statement/linedata/line">
                <xsl:variable name="sign">
                  <xsl:value-of select="dr_cr"/>
                </xsl:variable>
                <ITEM_FEB>
                  <FEBEP>
                    <BVDAT>
                      <xsl:value-of select="$stmtDate"/>
                    </BVDAT>
                    <BUDAT>
                      <xsl:value-of select="$stmtDate"/>
                    </BUDAT>
                    <VALUT>
                      <xsl:value-of select="$stmtDate"/>
                    </VALUT>
                    <KWAER>
                      <xsl:value-of select="$stmtCurrency"/>
                    </KWAER>
                    <KWBTR>
                      <xsl:value-of select="amount"/>
                    </KWBTR>
                      <!-- Transaction code -->
                    <VGEXT>
                      <xsl:value-of select="btc"/>
                    </VGEXT>
                      <!-- Business partner details -->
                    <PAKTO>
                      <xsl:value-of select="bp_bank_acc"/>
                    </PAKTO>
                    <PARTN>
                      <xsl:value-of select="bp_name"/>
                    </PARTN>
                    <PABLZ>
                      <xsl:value-of select="bp_bank_key"/>
                    </PABLZ>
                    <EPVOZ>
                      <xsl:choose>
                        <xsl:when test="$sign='C'">
                          <xsl:value-of select="'H'"/>
                        </xsl:when>
                        <xsl:otherwise>
                          <xsl:value-of select="'S'"/>
                        </xsl:otherwise>
                      </xsl:choose>
                    </EPVOZ>
                    <TEXTS>
                      <xsl:value-of select="note"/>
                    </TEXTS>
                  </FEBEP>
                  <!-- Note to Payee -->
                  <VWEZW>
                    <FEBRE>
                      <VWEZW>
                        <xsl:value-of select="note"/>
                      </VWEZW>
                    </FEBRE>
                  </VWEZW>
                </ITEM_FEB>
              </xsl:for-each>
            </ITEMS_FEB>
          </FEB>
        </FEB_NTIMES>
      </asx:values>
    </asx:abap>
  </xsl:template>
</xsl:stylesheet>

Next step is to define new format for your bank statement in XML-format and link it to XSLT-transformation. Use customizing view VFIEB_MAPP_XCTRL or the following menu path to perform this activity:

SPRO → Financial Accounting (new) → Bank Accounting → Business Transactions → Payment Transactions → Electronic Bank Statement → XML Format and Bank-Specific Formats.

Once you prepared XSLT-transformation and maintained mapping procedure, you can attempt to load bank statement. Go to FF_5 and choose “XML or Bank-Specific Format” option. Once you select it, additional drop-down list will appear, where you can select your custom format. Provide the reference to file path and fill the rest of the screen as usual.

The results of the upload can be found below:

P.S. This post doesn’t describe basic configuration of bank statement, which is also required for bank statement processing e.g. setup of house bank / account IDs, setup of posting rules, transaction types for bank statement etc. Customizing in these areas is standard for any bank statement and well explained.

I hope that you have learn something useful once you reached this sentcen. I’m looking forward to your comments and remarks.

 

Best regards, 

Bohdan Petrushchak

7 Comments
You must be Logged on to comment or reply to a post.
  • Hi,

    Really nice post, congratulations !!!

    I just have one question … When you write the XML code for the XSLT-transformation, is there any way to put some lines of Abap code to read a table and retrieve back some values from that table ?

    For one of the banks that I am dealing with, I will need to read the table used by the BAI pre -processor (T028Q) because they are not following the XML CAMT.053 standard properly and they are not providing the same bank keys (Transit/Routing) that we have stored in the system. And we need the keys the way we have them in the system to be able to do electronic payments.

    In a sense, they need some keys for Electronic Payments and a different set is sent by them in the EBS XML for the same series of Bank Accounts. That is why the BAI  pre processor works really well for us because we can have that transformation. But the pre processor does not kick in for XML.

    I know I could achieve this with a User-Exit, but just thought that maybe could be achieved here in the XSLT. If yes, how can those sentences be defined ? what are the commands to indicate Abap code ?

    Thanks for your help !

    Adrian

    • Hi Adrian,

      Thanks for appreciating:) As far as I know, it’s not possible to perform ABAP queries from XSLT-transformation. But at the end, it’s better to confirm this point with ABAP developer, maybe there is some workaround.

      Anyway, please let us know if you’ll find a way to call ABAP commands, it would be a good update.

      Regards,

      Bohdan

  • Hi Bohdan Petrushchak,

    Thank you so much for sharing such nice blog, we have similar requirement with our existing client

    basically we are getting file from third Party as XML but this file can’t be used directly to SAP for uploading. we need to convert this file in to SAP understandable XML format and post in to SAP

    however we are finding challenges to read SAP XML file format, could you please help/share us the way to identity this file.

     

    Regards,

    Santosh

     

    • Hi Santosh,

       

      Essentially the example of XML-file that I provided in this post cannot be loaded in SAP as is. But the mechanism described in this blog post allows you to take any XML-based bank statement and transform it into internal table that resembles data structure for bank statement header, lines and note to payee information. All you have to understand is how XSLT transformations work and what the the target fields, you’d like to map. Reach out to your development team, they should be able to support you with this task.

       

      Regards,

      Bohdan