Technical Articles
EBS: configuration of search string, part 1
Hello, SAPers!
Search string represents very useful functionality that is used to enhance the efficiency of standard EBS interpretation mechanisms. There are different instances when this functionality might be applicable e.g. for clearing purposes, for filling different fields with input values etc. I think that this functionality is especially useful for bank statement in MT940 format with unstructured field 86. However, content (both official documentation and user documents on SCN and similar resources) related to the configuration of this functionality is limited. Among those limited sources, I would like to recommed a great document on search string for EBS by Nitesh Patel: Search String for EBS (Electronics bank statement). At the same time, I would like to share my own experience of search string configuration, which might be useful for some of you.
1) Change of posting rule depending on the fixed text in the line items
Standard functionality of EBS implies that you have performed mapping and assigned external Business Transaction Codes (hereinafter referred to as BTC codes) to internally defined posting rules. However, some banks cannot provide you with the list of BTC codes or use just one BTC code for all transactions. For instance, one of the Ukrainian banks uses BTC code 110 for all operations (however, they do not call it BTC code, they just say that this is some kind of constant without any business logic behind it:). Nonetheless, this constant can be used as BTC code. We can assign this BTC code to two internally defined posting rules with different signs for example:
As you can see, BTC code has been assigned to two posting rules associated with incoming payments from customers and outgoing payments to vendors. Standard interpretation mechanisms can distinguish between different transactions depending on the transaction sign from the bank statement and deploy either one posting rule or another. However, you are limited only to two posting rules and consequently you’ll have to perform a lot of post processing to post all transactions. Therefore, you’ll have to analyze separate lines of bank statement and define some patterns that will enable you to apply search string functionality. You can find an example of line item with commission payments below.
As you can see, note to payee of this line item contains the word “COMMISSION053”. Consequently, we can use this word for configuration of search string. In order to configure search string use the transaction OTPM or following menu path:
SPRO → Financial Accounting (new) → Bank Accounting → Business Transactions → Payment Transactions → Electronic Bank Statement → Define Search String for Electronic Bank Statement.
In this transaction, specify the name of search string e.g. BANK_COM, short description and the content of search string. Afterwards, go to mapping part of the menu, clear all entries in the column “Target” and write instead of these entries UA-B, which represents a posting rule for bank charges (please, refer to the figure below):
In order to test your search string, enter your sampe text (note to payee from bank statement) in the Entry text box and press the button Test. If you’ve configured search string correctly, it will return the UA-B or another configured custom value. Afterwards, go to tab Search string use and add the following entry:
Please, specify on this tab for which combination of company code, house bank and account ID this posting rule will be applicable. Afterwards, specify 110 (or another BTC code) as external transaction code, sign of the transaction (positive or negative), as well as interpretation algorithm assigned to target posting rule (e.g. to posting rule UA-B). In my case, we do not use any algorightms, therefore this field was left blank. Then specify search string name and “Posting rule” in Target field column. Do not forget to activate check box active. Leave the columns Mapping prefix and Partner ID blank.
Thus, the logic behind this search string functionality is as follows: when search string finds the word “COMMISSION053” in the text of note to payee it identifies this transaction as payment of bank commission and triggers posting rule UA-B that posts bank commission.
2) Identification of business parterns via tax number 2
Short intro: sometimes companies decide to use tax numbers as customers/vendors key. This approach has its advantage, because normally there should not be another company with the same tax number 1 or 2. Another advantage is as follows: sometime it is legally required to write your business partern tax number 1 or 2 as requisite of your payment order. For example, in Ukraine there is a legal requirement that payment order should containt tax number 2 as mandatory requisite.
If house bank provides you with a well standardized bank statement (e.g. in MT940 format with all BTC codes), standard interpretation algorithms work perfectly and SAP is able to post all mapped transactions without problem. However, sometime banks apply their own rules (or dialects) that do not comply with SWIFT MT940 format. For instance, if you analyze structured field 86 in MT940 statement (note to payee) you should be able to find customer’s/vendor’s bank key and bank account in the subfields 30 and 31 respectively (at least in Ukraine and/or Russia):
However, sometimes banks apply their own rules and their statement look somewhat different e.g. below you can find sample of MT940 statement from Ukrainian banks. As you can see, customer’s bank account can be found in the subfield 33 whereas subfield 23 contains bank key and other information (MFO – local abbreviation for bank key, EDRPOU/OKPO – local reference to tax number 2 etc).
Therefore, if you try to upload bank statement without any changes you will face the similar error:
However, the company uses tax number 2 of customers and vendors as their key. Therefore, in order to recognize this customer key the following search string has been configured:
Afterwards, the following search string use has been configured on the tab Use of search string:
The combination of company code, house bank and account ID for which this search string will be applicable was specified. Afterwards 110 was specified as external transaction code and + as transaction sign. There was no interpretation algorithm assigned to posting rule associated with incoming payments, therefore the respective column was left blank. Afterwards search string name was specified in the respective column and “Account number” was chosen in the Target field column. Check box “Active” was activated. The columns Mapping prefix and Partner ID were left blank.
Thus, the logic behind this search string functionality is as follows: when search string finds the combination of words “ОКПО ########” in the text of note to payee it identifies ######## as customer key and triggers posting rule associated with incoming payments that posts this payment directly to customer account.
I hope this information will be useful for some of you. I also hope that I’ll be able to post another document of search string definition in the near future. All suggestions are welcome!
Best regards,
The Wirtschaftsmann
P.S. You can find two more documents on this topic under the following links:
Hi Wirtschaftsmann,
Wonderful piece of document that I have read in recent times. The intricate settings relating to search string functionality are well explained. I believe there is no other better way of explaining the stuff than this. Hats off to you.
I am eagerly waiting for your next document.
Thanks
Regards,
Sridhar
Thank you, Sridhar!
I appreciate your comments and support very much!
Best regards,
The Wirtschaftmann
Hi Wirtschaftmann,
Wonderful document. I have configured MT940 recently and it works fine in Develop client and Quality client. How ever it is not working fine in Production server.
After some research, I found below points.
Eg: Transaction 826 EFT Outgoing DR25000
DR Payment account 25000
CR main account 25000
How ever in Production entry is posting as
DR suspense account 25000
CR main account 25000
2. After some research we found that in Development and Quality transaction code picking Z826. But in Production transaction is picking as NSTO, NTRF etc..
Can any one help how fix this in production? We want system to pick Z826 instead of NSTO, NTRF etc. we have moved all the TRs properly from Quality to Production.
The above issue is with CITI bank but for HDFC it is working fine.
Thanks in Advance.
Hi Karthik Vaddi,
It might be caused by different upload format, which is selected by the user in FF_5 during upload of bank statement:
If you select, the format "... with field 86 structured", the program will fetch the BTC code from line :86: - in my case it would be 031. If you select the format "... (field 86 not structured), the system will retrieve the BTC-code from the line :61: - i.e. NTRF.
Regards,
Bohdan
Hi Bohdan,
Thank you so much for your advise. It worked.
The Wirtschaftmann - Thanks for your well-explained document. If I understand this functionality, It is because of your information you have given here.
Thank you!
Hi Wirtschaftsmann,
Though this place is not for queries, this is related to this document only. So, I felt it could be useful for the readers as well.
In case MT940 provides only invoice reference number as well as customer name without anything else, then how the system will be able to identify the relevant open items for automatic clearing ? Because customer name mentioned by the customer in Note to payee may not match with the customer name that we have in SAP. I think if you could throw some light in this area as well, then it would be great. Thanks.
Regards,
Sridhar
Hi Sridhar
As you are talking about customer line items, the invoice reference will be unique in this case and thus you can use interpretation algorithm 001 or 026 for identifying the correct open item.
I would suggest to raise a separate discussion or search the forum for more details.
Thanks & Regards
Nikhil kothari
Good one
Thank you!
Hi Wirtschaftsmann,
I need to maintain this info as Search String (BANK OF AMERICA DEPOSITS)|(BANK OF AMERICA NAB DEP). In the mapping area, I should maintain ZO05.
Pls let me know how it should be maintained in target field in Definition of Search String.
Thanks
Hello!
Yes... I think that you have to maintain ZO05 in the target column of search string definition. Fixed text (BANK OF AMERICA DEPOSITS or BANK OF AMERICA NAB DEP) should be maintained in the search string definition field.
Hello Wirtschaftsmann - I defined Search String as below.
Search String name BOA_169
Search String (BANK OF AMERICA DEPOSITS)|(BANK OF AMERICA NAB DEP)
Source (BANK OF AMERICA DEPOSITS)|(BANK OF AMERICA NAB DEP)
Mapping ZP05
When I am doing simulation putting in Entry text - BANK OF AMERICA DEPOSITS, I can see Posting rule ZP05 in the Hit list.
and when I am putting BANK OF AMERICA NAB DEP, Posting rule is not shown in Hit list.
I am not able to understand why it is not working for BANK OF AMERICA NAB DEP.
Can you pls help me.
Thaks
Hello,
You have to specify the posting rule twice in the target column like this:
ZP05 ZP05
(BANK OF AMERICA DEPOSITS) | (BANK OF AMERICA NAB DEP)
Or you have to maintain two different search strings with the same use settings (one with BANK OF AMERICA DEPOSITS, another one with BANK OF AMERICA NAB DEP). Anyway, it's up to you.
Hi Wirtschaftsmann,,
Very well explained document. Thanks alot for sharing your knowledge. Waiting for the next part 🙂
Please avoid typo errors 🙂
Regards,
Lakshmi S
Hi, Lakshmi!
Thanks for feedback! I'll review the document and try to fix the errors!
Hi Wirtschaftsmann,
Thanks a lot for sharing your knowledge with us.
Cheers
Vivek
Thank you, Vivek!
Hi Wirtschaftsmann,
In our case, our customers provide ONLY the customer name. They don't provide invoice reference number (XBLNR) or accounting document number (BELNR) while making the payment. Now the objective is that SAP system should identify the related open items of the customer from the name given in Notes to Payee field for the purpose of automatic clearing.
I believe I can use the BAdi FIEB_CHANGE_BS_DATA to fetch the customer number based on customer name given in Notes to Payee. And now to fetch the related open items for automatic clearing, do I need to use enhancement FEB00001 ? Or do I need to create custom interpretation algorithm ? Or do you suggest anything else. Could you kindly provide your expert help in this case.
Thanks
Regards,
Sridhar
Hello, Sridhar!
Unfortunately, I have not tried to implement any BAdi related to EBS configuration, therefore I'm not able to help you with your question. But I will be very grateful if you could share with us the results of implementation of this BAdi.
Best regards,
The Wirtschaftsmann
Hi Wirtschaftsmann,
Thanks for your reply.
Yep, sure I will share with you on this page on the solution. Currently we are still in the process of prototyping the solution for testing.
Thanks
Regards,
Sridhar
I got this resolved by using the enhancement FEB00001. If the field "Distribute by age" is activated through this enhancement, then system will be able to match the paid amount and the combination of open items for automatic clearing. If there is no exact match, then automatic clearing through FF.5 is not possible as indicated in SAP Note 124655.
There is no more necessity to use BAdI FIEB_CHANGE_BS_DATA. Complete relevant logic can be maintained directly in the enhancement itself.
Regards,
Sridhar
Hi Wirtschaftsmann,
This is a very very informative document and brings together the exact steps and concepts needed for the search string configuration.I kept on searching in SDN , but was not able to get the same earlier.Thanks again for sharing this very valuable information.
Regards
SR
Hello, Swastik!
Thank you for valuable feedback!
I'm in process of preparing the second part for this document. Hope it would also be interesting.
Hi Wirtschaftsmann,
Nice explanation with examples. I never worked on this area and will try this in sandbox system.
Thanks,
V V
Thank you for feedback, Vinod!
Thanks for the document on Search String. This is the area many of the consultants are not aware of.
Best Regards,
Ravi
Hello, Ravi!
Thank you for valuable feed-back!
Best Regards,
The Wirtschaftsmann
Hi,Thank you for this documentation. Helped me a lot. I just have a question. Can search string work on Line 61 of MT940 or the 2nd Record updated in FEBRE Payment Note? Or is search string limited to line 86?
Hi Dino,
FEBRE table contains all the information from tag 86 and thus search string works on all the records
If a OSS note (can't remember the number) is implemented then part of tag 61 also gets added in the FEBRE table.
Regards,
Nikhil
Hi Nikhil,
You are right. I just implemented Note 396776. However Now I noticed not all of the line 86 are copied in FEBRE.
There were 3 lines created in FEBRE for the transaction but it seems to concatenate at the 60th character from line 86. It this a limitation?
Thanks for sharing this valuable document !!
Kind Regards,
Prakash K.
Hello Prakash!
Thank you for feed-back!
Best regards,
The Wirtschaftsmann
Hi Wirtschaftsmann,
How would it be when the Tax ID delivered in the note to payee is NOT used as customer key (KUNNR)?
Is it then necessary to do it via EXIT_RFEBBU10_001 adding a 9XX interpretation algorithm? which should make a select to the table KNA1 to get the customer key KNA1-KUNNR, and after update this value in the structure E-FEBEP (and E_FEBCL*)?
(*) in case there are clearing posting
looking to hearing from u
Best
J
Hello Juan!
I this blog I presented several examples of how search string functionality can be used to enhance EBS interpretation mechanisms. However, that doesn't mean that tax id necessarily has to be configured as customer key, it's just an example of how a particular feature of EBS search string can be used under given circumstances.
In case you need more sophisticated solutions, I guess you're right that we need to develop user exist and use alternative interpretation mechnisms. However, I didn't cover this topic in blog. Sometime I will try to prepare a document like that, but I'm not sure when it will be. In the meantime, I would be interested how you solved your problem. Perhaps you can prepare a document of your own and post it on SCN? SAP community will appreciate your contribution.
Best regards,
The Wirtschaftsmann
Hi Wirtschaftmann,
In fact it is enough to update the field FEBCL-AGKON = KNA1-KUNNR using the EXIT_RFEBBU10_001. The second posting area (subledger) will consider this value irrespectible it is carried out using FB05 (w/clearing) or using FB01(only credit on customer account).
Best
JC
Hi
Thanks a lot for adding such useful information in the community.
Regards
Mahmoud El nady
Hello Mahmoud,
Thank you for your feedback!
Regards,
The Wirtschaftsmann
very nice document
Hello Preeti,
Thank you for feedback!
Regards,
The Wirtschaftsmann
Hi,
This is very insightful content. Very nicely explained
Thanks
Hi, Prabhakar!
Thank you for feedback!
Regards,
Bohdan
Very Nice Document 🙂
Thanks.
Hi Poonam,
Thank you for your feedback!
Regards,
Bohdan
Hi,
Beautiful document. A layman also could understand easily. Thank you very much for this document.
Regards
Karthi
Hi Karteek,
Thank you for your feedback!
Regards,
Bohdan
Hi Wirtschaftsmann,
Thank you very much for nice and useful document.
I have a very basic question for EBS configuration, please give your valuable input on this:
Q-1. How to handle transaction of cash deposit in bank account by company itself. Example, my company code has City Bank housebank, and I have deposit the 1000 INR in bank account. Now when i get the bank statement, i see one line item of 1000INR as cash deposit.
What would be accounting entry at the time of cash deposit i.e what all GLs would be posted and what would be the accounting entry when EBS posted.
Thanks,
Ram
Hi Ram,
Let's start with a basic question: where did the money came from and how it is accounted for? If the money came from some client and / or shareholder and it is not deposited to bank account then it is paid in cash. If that's the case, then obviously you need to configure Cash Ledger (that's part of another discussion, which I'll not cover here). These money receipts should be accounted for on account that is typically called "Petty cash".
When you have a deposit of company's own money to bank account, then you have the following typical posting:
1st step (posting via cash ledger):
Dr. Money in transit
Cr. Petty cash
2nd step (posting in bank statement):
Dr. Bank account
Cr. Money in transit
Posting on bank statement side can be configured via simple posting rule with postings to one area (two areas are no necessary).
Hope this would help you! Please not, that such questions once not related to the subject of the article should be raised on Forum.
Regards,
Bohdan aka The Wirtschaftsmann
Hi Wirtschaftmann,
I have a problem with the search string. I need take the pay reference from tag 86. I made the configuration how you say, but in the “Target field column” I chose “BDC Field Name 1”, because I need put the number in the Field BSEG-ZUONR. I don’t know if I made a mistake or if I need configure something else.
Could you help me with this problem?
Thank you very much
Nice Article , Well explained .
Hi Bohdan Petrushchak
Very educative write-up.
Quick question to all: Does anyone know search string/algorithm that could be used to read "Billing document" number from Note to Payee, without programming?
When customer provides Accounting doc No, or Reference No, or Payment Reference No, standard confguration is working fine, and open items are cleared automatically. But when it is only the Billing Doc No (VBELN) that is provided in the Note to payee, this is not being matched.
I know I can put some codes in, but want to pick on expert's brain if there is any standard configuration that can read VBELN (billing doc No) from Note to payee.
Thanks in advance.
Hi Bohdan,
Thanks for the great documents
Is it possible to apply the Search String on field :61?
:61:2008010701D200246,NSTONONREF//552276599868289
:86:AUTH'ZED DEBIT
I see this string in the Note to Payee.
I would like to add text into Note to Payee. If String = "5522765998" the text="xxxxxxx"
Thanks in advance,
Orna
Hi Orna Cohen02,
Usually it is not possible. Search string works with the values in table FEBRE, which are built using the values from field :86:. So if the value is available in the table - you can use it in search string definition, if not - then it's not possible.
Greetings from Ukraine:)
Thanks Bohdan for the prompt answer
yes, I do see this value in FEBRE in 2 rows:
1st row with the value from field :86 "AUTH'ZED DEBIT"
2nd line with the value from field :61 "552276599868289"
In the Search String I have "AUTH'ZED DEBIT?5522765998?" but it doesn't bring the result as I expected.
Any clue?
Greeting from Israel. Wish you can come again 🙂
Orna Cohen02 , sometimes search string do not work as expected for a variety of reasons. Rule of thumb is - the definition should be as short as possible. As soon as you include several components into definition, you can expect issues. I'd start by simple example e.g. definition something like "AUTH?ZED DEBIT" and check if it triggers anything or not. If it works, I'd proceed by adding "AUTH?ZED DEBIT?5522765998".
If it still doesn't work - maybe some of the requisites in customizing of search string use are not correct. To verify it - I'd recommend to put breakpoint in FM BSPS_PATTERN_SEARCH - it will show you if search string is reaching the point where it is search for values or is skipping the transaction at all.
Hi Bohdan,
It works. I created 2 Search String Definition:
Now my challenge is to find a Target Field which user will be able to see in FEBA_BANK_STATEMENT.
I succeeded to map EBINFO1 but this field is not available to user.
Do you know if it is possible to add line into Note to Payee?
Thanks, Orna
Orna Cohen02 , if you mapped the search string to EBINFO1, this information is supposed to be appended to note to payee details in FEBAN. Below note to payee there is a button to switch between original note and adjusted note - i.e. the one adjusted by user manually. Please check this option.
If you'll not succeed via search string - I would suggest to check via BADI FIEB_CHANGE_BS_DATA, method CHANGE_DATA. BADI allows more flexible handling of bank statement updates during upload.
Thanks Bohdan,
Additional Information populated with Search String is visible in the Payment Notes (see attached). To see the Payment Notes you need to click on the Memo record number.
I’m still looking on a solution to populate the Note to Payee instead of Payment Notes.
Orna Cohen02 , thanks! That's actually the first time when I see actual use of this mapping option. I'll explore this option in more details and probably write another post on search strings with more details:)
In the meantime - BADI FIEB_CHANGE_BS_DATA, method CHANGE_DATA can do the work really quick. It definitely works.
Hi Bohdan,
I got the search string setup you mentioned. but our requirement is put some MT940's file value into "text" field. for example in MT940, there is customer name, we want to put this customer name into
"text" field in the posting document. Do you have any idea on how to achieve this?
Thanks in advance.
Grace
Hi Grace,
Yes, it is possible, but a bit more complicated and not overly reliable. Please check this link for further details.
Regards,
Bohdan
Thanks for your quick reply.
Let me study the link you provided.
real appreciated!
Regards,
Grace
Dear Bohdan,
Thank you for useful document.
I would be thankful if explain below points.
How can I update exact WBS element field for document. Let's assume, If the system finds "CHARGE" text on statement, then it should populate exact WBS code for WBS element field for document. Please take into account that WBS does not appear on statement.
Kind Regards,
Nurlan Karimli
Dear Bohdan,
In the first scenario that you mention in this article, there is one unique external reference (110) and you use text commission to derive a different posting rule. Imagine that in this case 'commission' could be positive and negative (each with different posting rule), is it possible to differentiate per sign and so, how would it be done?
Thanks
Hi Iago,
Yes, it is possible. I'd suggest to define two separate search strings with different settings under "Search string use". In this menu you specifically activate search string for a combination of external transaction and its sign.
Regards,
Bohdan
Hi Bohdan,
I do not see how to handle it, this is my EBS. My bank is providing always a fixed value in external operational (NMSC), and they provide their internal code in register 86 (7700)
:61:2110201019C1742,46NMSCNONREF
:86:7700-EST-0018TPA-00782809PER-050
I thought using a string simple like this:
But I cannot differentiate depending the sign of the amount, and I believe I cannot make a different string for this. So when I go to the 'search string use' I get that both signs point to the same search string mapped
How could I manage this?
Thank you for your time and help
i found a solution, an unconventional solution but apparently works. I could manage to do different search strings. I do not like what I did but it works.
I do not understand why there are banks that do not use the external operations defined by mt940.
regards
Hi Iago,
why use two different search strings in the first place?
My approach would be to use one search string, leave the target string blank, and assign different posting rules through the Mapping Prefix instead.
Cheers,
Lars