Skip to Content
Author's profile photo SAURABH JAIN

Advanced use of search strings in Electronic Bank Statement

This document explains capabilities of search strings in electronic bank statement processing

Electronic Bank Statement is one of the most widely used functionality to automate bank reconciliation and related posting. It can be further optimized by use of ‘search strings’. Search strings by reading strings in “Note to Payee” can help EBS further automate functions like changing posting rule, populating values from EBS file to assignment field in accounting document, skip processing of bank code for specific house bank etc. We will see all these functionality in detail.

Passing values from EBS file to assignment field in accounting document

Once electronic bank statement is posted it becomes difficult to match accounting document with physical bank statements based on just amounts and date. Using search string we can pass specific string (e.g. reference number) from electronic file to assignment field in line item of “Wires/Check in clearing” account. This value in assignment field in line item of “Wires/Check in clearing” account can be useful in setting up automatic clearing of “Wires/Check in clearing” account. For example where receivables (Non-Lockbox) are post processed after posting of EBS (e.g. inter-company receivables), line items in clearing account can be cleared by background job for T-Code F.13.


1. Incoming Electronic Bank File (BAI format):

Electronic Bank file can be in many formats like MT940, MULTICASH, BAI etc. Shown below is one sample file in BAI format where reference number generated bank is in specific format. We want to pass reference number for each bank code in Assignment field in line item of clearing account –

Incoming Electronic Bank File (BAI format).jpgg

2. Configuration of Search String –

To pass value from ‘Note to Payee’ to accounting document we need 2 search strings.

a) First one to identify the target field in accounting document for search string  and,

b) Second one to pass bank reference value in assignment field.

Search String definition-

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

Display Search Strings.jpg

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

Config - first search string.jpg

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:


  • First 2 characters I and A as constant.
  • Next 7 characters as numbers. Numbers in search strings are identified by sign ‘#’.
  • 8th character as (#|O), which means 8th character can be an alphabet ‘O’ or a number. System will identify string in both the cases.
  • 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 –

Config - second search string.jpg

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 field in accounting document we will use BDC Fields.

Search String Use.jpg

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 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 in the event that 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 in 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 particular 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 cause 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 Co Cd/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”:

BDC Field Name 1                 – BSEG-ZUONR (Target field)

BDC Account Type 1             – 2 (Target line)

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########O#### and pass that value to assignment field of clearing account. Of course you will have to configure your search string according to your requirements.


3.  Changing the posting rule for same bank code:

It may be possible that a bank code is posting to specific set of GL accounts via a posting rule; however we want to 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 –

ACH Out.jpg

BAI file comes as shown below for ACH return –

ACH return.jpg

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

            WF08.jpg

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 –

Search Strings - ACH Ret.jpg

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

Search String Use ACH ret.jpg

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 as long as, 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 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.

Assigned Tags

      19 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi Saurabh,

       

      Good Informative document. Thanks for sharing.

       

      BR, Jaymin

      Author's profile photo ECS SAP Team
      ECS SAP Team

      Nice work !!

      Author's profile photo Bohdan Petrushchak
      Bohdan Petrushchak

      Hello Saurabh,

       

      Thank you for a very good document on search string use.

      Author's profile photo Sridhar Bolisetti
      Sridhar Bolisetti

      Good one Saurabh Jain.

       

      Sridhar

      Author's profile photo Former Member
      Former Member

      Hi Saurabh,

       

      This is very useful document.

       

      Thanks

      Andy

      Author's profile photo Cecil D'Souza
      Cecil D'Souza

      Hello Saurabh

       

      Thank you for this article and information. Can you increase the size of the font for better readability. Seems like it is a strain on the eyes.

       

      Thanks

      Author's profile photo SAURABH JAIN
      SAURABH JAIN
      Blog Post Author

      Here you go..font size 12..

      Author's profile photo Cecil D'Souza
      Cecil D'Souza

      Thank you Saurabh for your help. It is appreciated.

      Author's profile photo Former Member
      Former Member

      Very useful document Saurabh.

       

      Can you please let me know how to post an external code having two search strings?

       

      Regards,

      K.S.K

      Author's profile photo SAURABH JAIN
      SAURABH JAIN
      Blog Post Author

      Saravana

       

      Can you please elaborate what are you trying to achieve with 2 search strings. Multiple search strings work on same external code but you will have to map it in search string use config using 2 different search strings.

      Author's profile photo Former Member
      Former Member

      Hi Saurabh,

       

      Please check the below link for detailed explanation of the issue :

       

      Electronic Bank Statement - Two Search Strings for the same external code

       

      Kindly give me your valuable suggestions.

       

      Regards,

      K.S.K

      Author's profile photo Cristiano Silveira
      Cristiano Silveira

      Amazing document!! Thank you!!

      Author's profile photo Former Member
      Former Member

      Hi Saurabh,

      This is a v good document. much appreciated.

      I have an issue where I need your input.

      we have specific codes for each type of transaction (each group of external transaction)

      eg ABC for incoming payment and DEF for outgoing payment.

      The question is : what are the options available to post the specific code (ABC, DEF) in each line item posted using EBS.

      Should i use search string or is there any other way of doing it?

      Regards,

      Sangarsh

      Author's profile photo SAURABH JAIN
      SAURABH JAIN
      Blog Post Author

      Hi Sangarsh

       

      Search strings are added functionality to support posting of EBS. In cases where there is no straight forward way to achieve a result you can use search strings. E.g. You want to default a cost center whenever there is a bank fee and EBS in such cases always contains string 'Bank Fee', you can configure search string to update cost center in these cases.

       

      In your case to post EBS, you should first go using straight forward configuration of - Creating Account Symbols, assigning the same to Bank GL accounts (dynamic determination), Creation of posting rules and then linking external codes to account symbols using posting rules. If any of your requirement is not getting fulfilled using this configuration then you can think of using search strings.

       

       

      Thanks

       

      Saurabh

      Author's profile photo Former Member
      Former Member

      Such a helpful document, thanks!

      Just a question. What do you do when your search string is shorter than the space required to enter BSEG_ZUONR in the Mapping area? So I have an 8 digit search string and when I want to post the result to BSEG_ZUONR, the space available is not long enough to enter the field name. How can I get around this?

      Thanks,
      Leigh

      Author's profile photo Former Member
      Former Member

      Hi,

      This is a very good document. But I have a problem:

      I need to make "on account" postings to the customer. I don't have any remittance information (i.e. invoice numbers) in the note to payee that I can use to clear specific documents - but I do have the customers name.  Can you help me?

       

      Thanks.

      Andreea

      Author's profile photo Inna Merai
      Inna Merai

      Hi Saurabh,

       

      Thank you for sharing the details on the search string scenarios.

       

      I am still having a challenge to replacing the posting rule with a target one or filling the BDC fields. Would it be okay to share more details on my scenario?

       

      Thank you,

      Inna

       

       

      Author's profile photo Steven Dey
      Steven Dey

      Passing the IA number from the EBS file to the assignment field of the accounting document only works if you are posting only and clearing through a different process like F.13. This does not work if you are posting and clearing at the same time (posting types 4 and 5).

      Author's profile photo Ifty Din
      Ifty Din

      Is it possible to configure the search string so that it picks a an 8 digit or 7 digit customer number? for example there are 2 86 lines on the statement file and we want both to picked up and posted to. Here are same examples:

      :86:/CUS/1000001

      :86:/CUS/10000019

      I have configured the search strings in transaction OTPM to /CUS/########  this works when the customer number is 8 digits but do you know how I can configure the line for both 8 or digits for the customer number?