Optimizing Search String use in Sap Electronic Bank Statement

Optimizing Search String use in Sap Electronic Bank Statement

Saurabh Jain Arvind Napa

Ohio, USA Kansas, USA

Abstract The Electronic Bank Statement (EBS) is one of the most widely used tools for automating bank reconciliation and related postings. Its efficiency can be further enhanced with search strings. By analyzing the "Note to Payee" field, search strings enable EBS to perform additional automated tasks such as modifying posting rules, populating assignment fields in accounting documents directly from the EBS file, or excluding specific bank codes for designated house banks. In this article, we will explore these functionalities in detail.

Keywords SAP, S/4 HANA, ECC, Electronic Bank Statement, Search String

Auto-Populating the Assignment Field in accountind document from EBS

Once an Electronic Bank Statement (EBS) is posted, it can be challenging to match the resulting accounting documents with the physical bank statement using only the amount and date. To address this, search strings can be used to pass specific identifierssuch as a reference numberfrom the electronic file to the assignment field of the line item in the "Wires/Checks in Clearing" account. This assignment field value can then be leveraged to enable automatic clearing of the clearing account. For example, in cases where receivables (excluding Lockbox) are processed after the EBS is postedsuch as intercompany receivablesthe line items in the clearing account can be automatically cleared using a background job via transaction code F.13.

1. Incoming Electronic Bank File

   Electronic bank files can come in various formats such as MT940, MULTICASH, and BAI, among others. Below is an example of a bank file in BAI format, where the reference number generated by the bank follows a specific structure. Our objective is to extract this reference number for each bank transaction and populate it into the Assignment field of the corresponding line item in the clearing account.

2. Configuring search string

   To pass value from Note to Payee to accounting document we need 2 search strings-

   1. First one to identify the target field in accounting document for search string and,

   2. Second one to pass bank reference value in assignment field.

      Configure search string as shown below

      For configuration follow path – SPRO > Financial Accounting > Bank Accounting > Business Transactions > Payment Transactions > Electronic Bank Statement > Define Search String for Electronic Bank Statement.

      Configuration of first search string (BANK REF. IDENTIFIER) to identify the target field in accounting documents:

      Please note that there is no mapping for this search string as it is only used to identify the target field in accounting document. In our example in BAI file bank reference comes in format IA123456789012 or IA1234567O9012 where first 2 characters are constant (IA) followed by 12 numeric

      values where 8th character can be alphabet O or a number. In SAP performance assistant you can see how to configure search strings. I have configured search string as follows:

      1. First 2 characters I and A as constant.

      2. Next 7 characters as numbers. Numbers in search strings are identified by sign #.

      3. 8th character as (#|O), which means 8th character can be an alphabet O or a number. System will identify string in both the cases.

      4. Last 4 characters as numbers.

For different banks format of reference value can be different. However, it should have some unique identification like in our example it starts with IA, so that system can uniquely identify the string.

Configuration of second search string (BANK REF. POSTING) to pass bank reference value to assignment field

Please note that here I have configured search string exactly as the first one, but it has mapping. If search string has mapping it means that value will be passed to accounting document.

Search String Use-

Search strings are assigned to combination of Company Code/House Bank/Acct ID/Bank Code. To pass value to specific fields in accounting documents we will use BDC Fields.

know the technical name of the relevant screen field (if you do not know it, use the F1 help to find it out). For example, if you want to transfer a control indicator if the key word "TELECOM" appears in the note to payee, you can proceed as follows:

Use the search string TELECOM with mapping " " (all blanks) for the three target fields "BDC Field Value 1", "BDC Field Name 1" and "BDC Account Type 1". The constants to be entered into the individual BDC fields are always stored in the "Mapping Basis" field:

BDC Field Value 1 = "V1"

BDC Field Name 1 = "BSEG-MWSKZ"

BDC Account Type 1 = "0"

If you want to transfer the content of only one line, you can use the "BDC Account Type" field to control the process. If you enter the constants 0, 1, 2, or 3 in this field, the transfer only takes place as specified. The constants here mean the following:

0: First line, posting area 1

1: First line, posting area 2

2: Second line, posting area 1

3: Second line, posting area 2

If you do not control the transfer in this way, the system tries to transfer the screen field defined under "Field Name" in all the posting lines in posting areas 1 and 2. This causes an error on the grounds that the field does not exist (for example, because a control indicator for a particular account does not accept input.

In the control indicator example, note that you must set the "Calculate Tax" indicator too, so that the posting can be made automatically. You then need the following settings in addition:

BDC Field Name 2 = "BKPF-XMWST"

BDC Field Value 2 = "X" BDC Account Type 2 = "0"

Continuing to our configuration, proceed as follows-

1. For relevant Company Code/House Bank/Acct ID/Bank Code combination assign first search string (BANK REF. IDENTIFIER) to Target Fields BDC Field Name 1 and BDC Account Type 1.

2. For same combination in step 1 assign second search string (BANK REF. POSTING) with target field BDC Field Value 1.

3. Assign following values to fields Mapping Prefix:

4. BDC Field Name 1- BSEG-ZUONR (Target field)

5. BDC Account Type 1 – 2 (Target line)

6. BDC Field Value 1 – IA000000000000 (Target value) Once above configuration is ready, for that specific Company Code/House Bank/Acct ID/Bank Code system will read any search string with format IA############ or IA########Of#### and pass that value to assignment field of clearing account. Of course, you will have to configure your search string according to your requirements.

BDC fields work as follows (Excerpt from SAP Performance Assistant)

The BDC fields are a means of filling screen fields if there is no suitable target field in the bank data store. You must

CHANGING THE POSTING RULE FOR SAME BANK

CODE

It may be possible that a bank code is posted to specific GL accounts via a posting rule; however, we wantto change GL account posting for the same bank code. It may be possible for example bank uses same code for ACH OUT and ACH RETURN. We will want system ACH OUT to post by debiting ACH OUT account and crediting Main Bank Account. However, when there is ACH return, we want to reverse the posting. In case bank uses same bank code however in note to payee gives information in the form of string ACH RETURN, we can configure a search string to identify such returns and change the posting rule for reversal. Example:

Bank Code for ACH OUT 169

Posting rule for ACH OUT – WF08; debits ACH clearing account, credits bank account

Posting rule for ACH RETURN WF10; debits bank account, credits ACH clearing account

BAI file comes as shown below for ACH out

BAI file comes as shown below for ACH return

For ACH out posting would have been gone through posting rule WF08 which is configured as shown below-

We want to substitute posting rule WF08 with posting rule WF10 for bank code 169 if note to payee has string ACH RETURN. For ACH RETURN search string can be defined as shown below-

Assign search string configured above in node Search String Use as follows:

Assign search string ACH RETURN to relevant Company Code/House Bank/Acct ID/Bank Code combination. Select target field as Posting Rule. Assign Mapping Prefix as WF00. When substituting posting rule WF08, system will take WF from mapping prefix and 10 from search string definition. Mapping prefix works as follows (Excerpt from SAP help)

A string of characters you can define yourself to have the funds center found "filled out" in the event of a successful search if it is too short.

The prefix should be the same length as the document numbers being searched for in the system. The later part is then replaced with the search string found.

If a "hit" is longer than, or if, the relevant prefix, the hit overwrites the whole prefix.

Use

This function is particularly useful for filling in redundant parts (such as leading zeros) when the reference information is incomplete.

Example

Prefix "1800000000" and hit "12345" results in number "1800012345".

Once the above configuration is in place any file with bank code 169 assigned for house bank and account when loaded with string ACH RETURN will replace the posting rule WF08 with WF10.

CONCLUSION

-Use of search strings in S4 Finance can greatly enhance system capabilities to automate postings from electronic bank statements, thus saving time and efforts in manual analysis and corrections. It also helps streamline downstream process.

REFERENCE

https://help.sap.com