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
Hi Shailesh,
Hope you have already implemented the EBS with CAMT53 file format. We are now in process of implementing EBS with CAMT53 file. Can you share a sample CAMT53 file with basic features which are mentioned below. May be you can incorporate some dummy numbers.
Regards
Partha Sarathi Samantaray
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
Hi Bohdan,
Thank you for the great blog! After uploading CAMT53 how to reconcile the collection entries. I mean which algorithm we should use. Any standard available or do I need to build a custom algorithm, if custom, could you please provide a few examples. Because in CAMT53 this statement bank does not provide the customer code/BP.
Regards,
Nrupen
Hi Sharib Ansari,
Yes, you're right - none of the bank statement formats - at least to my knowledge - provide the reference to the customer code in the statement body. Therefore you have to use the the interpretation algorithms provided by SAP or define your custom one. Usually, standard algorithms are enough. In my experience I encountered only one client, who developed their own algorithm for interpretation.
I will not be able to answer your question satisfactorily, because it is too big question and there is too much stuff to explain. But what you should bear in mind is that interpretation mechanisms analyze the content of the note to payee (i.e. table FEBRE) and try to establish the link not even to the customer / vendor, but to an open item. The goal is to automate clearing of open items. So what you need to analyse is what your customers are providing in the bank statement. If they provide SD invoice number - you should interpretation mechanism 021 "Search for reference document number", or you can use standard mechanism 001, which search for both document & reference document numbers. Anyway, you need to understand what is provided in the content of the bank statement, which object in the system does it correspond with and then you should search for the interpretation mechanism.
Regards,
Bohdan
Hi Bohdan,
Appreciate your quick response!
For the payments, we are passing the payment document number in the <EndtoEndId> node using the note to a payee, and the same will be provided in the statement. So no issue while reconciling vendor OI.
But when it comes to collections, that is still not clear what info we will receive for the customers, as you mentioned SD invoice number or only the customer name, but in the initial discussion with the bank, it would be the customer name only. I will try to fit in some standard algorithm as suggested by you.
Thank you Again!
Regards,
Sharib
Hi Sharib Ansari,
I would suggest not to leave anything to the chance. What you should remember is that cash collection is not a standalone operation, but a part of order-to-cash process, where you have the influence over many components. In particular, when you issue the sales invoice you have a choice: will it be issued based on outbound delivery or sales invoice (I encountered both examples) ? What is the primary accounting document that your customer will use for payment: is it a sales invoice or pro-forma invoice ? If it is a proforma invoice, will it be issued based on sales order or a dedicated pro-forma invoice (i.e. billing type F5) ? What are the payment terms: do you expect post payments (i.e. invoice followed by payment) or down-payments (i.e. payment first, then delivery & invoice) ?
You should take into account all integration points and build the process in such a way that allows you to reconcile the incoming payments with proper customers without much unnecessary efforts. Normally speaking, if your customers are paying each invoice separately and provide the reference to the invoice number in the note to payee, it can be handled by standard configurations easily. If you customers pay many invoices at the same time, you should require them to prepare / send the payment advices. If you have payment advices, you can handle them in the system as well to enhance the clearing. There are many questions and aspects to the process, that's why it is always interesting to handle 🙂
Regards,
Bohdan