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
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
Thanks for your quick response !!! appreciated !!
Adrian Di Nanno, your question bothered me today and distracted from work. I've got in touch with my friends who specialize in ABAP development professionally - as it turns out there is a way to perform queries from transformations to SAP database. Please check out below links:
Call of methods from simple transformations
Blog post on SCN on about use of methods
Hope, this information will be useful.
Hi Bohdan,
Thanks a lot for this research !! I owe you one !! I will bookmark this and use it later once we are ready to change BAI for XML CAMT.
With this I will be able to get an Abaper to do a small Class/Method to read Table T028Q to replicate what the BAI pre-processor is doing when passing through that mapping table.
Thanks !!!!
Adrian.
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
Hi Bohdan,
Thanks for the great blog.
Do you know if we have to do XSLT transformation for CAMT.054.001.02?
Cheers
Ratul
Hi Ratul Chakraborty2 ,
Please check in view VFIEB_MAPP_XCTRL. There is a standard transformation for this format.
Thanks Bohdan. This is already set up as per standard. So we do not have to do any XSLT transformation.
Ratul Chakraborty2 , you're right. The purpose of this post was to explain how to set up the upload for "custom" XML-formats - i.e. those formats which are not supported by standard SAP functionality. If your XML-file is supported as part of standard SAP functionality or localization for a specific country, you do not need to develop anything from scratch.
Got it ... Thanks Bohdan
Hi Bohdan,
This is really helpful, thank you. For CAMT053 and 54, XSLT transformation is already provided. I have following queries. Appreciate your response.
Regards,
Shailesh
Hello,
I would need to configure this step for Multicash. I see that for many formats, it is already configured and created, but not for Multicash (it changes quite a bit as it is with two different files, one with header and the another one with the positions)
For multicash automatic load, should a new transformation be created in STRANS? Or one of the availables formats would work for standard multicash?
Many thanks in advance