- SAP Managed Tags:
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.
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.
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:
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:
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
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
23 Comments
| User | Count |
|---|---|
| 8 | |
| 3 | |
| 3 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 2 |
