How about updating a BDC field with Dynamic value using a Search String?
This is the first time I am writing any blog in my life, so please bear in case its not in a structured manner. 😉
Pre requisite: The reader should be aware of the use of Search strings in the Electronic bank statement (preferable MT940 file format).
A couple of years back when I started learning the configurations of Electronic bank statement, I bumped into this sap node in the SPRO path called, ” Define Search String for Electronic bank Statement”. I was amazed with the use of this functionality and the power it provides to the functional consultant to meet the basic requirements with no involvement of ABAP developer.
While going through the SAP help document for this node I found various usage of this search string and how we can populate the required value in the target fields of any accounting document. There are some basic frequently used fields like cost center, profit center which are provided as default target fields in configuration step of ‘Search string use’. In case the required field is not available then you can use the option of BDC fields as the target field.
While reading the details on the use of BDC fields in the help document (see below snapshot) I realize that it only talks about identifying a string of data from the Bank statement and then passing a fixed value to the required BDC field.
But, then I asked myself, ” What if my data in the file is not the same always and I need to get this dynamically varying data from the bank statement to fill the same BDC field?”.
Do I need to create ‘n’ number of search strings based on each value that appears in the bank statement? or Is there any other option?
Eureka!! 🙂
After a few trial and error methods, I found the below solution or work around or whatever you would like to call it out.. 😉
Lets take a scenario that based on some identifiers appearing in your bank statement, you need to populate the Reference key 3 (XREF3) field of the accounting document with some data from the bank statement itself.
Lets assume we are using MT940 file format for the Electronic bank statement and below is a sample data of tag 61 and tag 86.
Sample1:
:61:1110061006DD30000,00NFEX11XX79024//11XX79024
/CTC/081/FX CONTRACT MATURITY
:86:/PT/FX/AD/EUR21616,95/ER/1,3878/BR/ON MATURITY WE WILL DEBIT
YOUR ACCOUNT /SR/CONTRACT123456
Sample2:
:61:1110061006DD60000,00NFEX11XX84524//11XX84524
/CTC/081/FX CONTRACT MATURITY
:86:/PT/FX/AD/EUR21616,95/ER/1,3878/BR/ON MATURITY WE WILL DEBIT
YOUR ACCOUNT /SR/CONTRACT567890
Now, in the above example we are doing some FOREX transactions (like hedging) with the bank for which the reference contract number is as highlighted above. If we want to populate this contract number in the XREF3 field or another field of the SAP accounting document then we can do it with the below trick 😉 ,
Assumption: the contract number (length 6 digits) always follows after /SR/CONTRACTXXXXXX in the tag 86 of the file. This acts as an identifier for us. In case you are not able to identify a fixed value then you can consult with your respective bank or ask them to add a fixed identifier before the required data.
Step 1: Define first search string say ‘String1’
Srch strg name: String1
Description: Identify data sequence from the file.
Search string: /SR/CONTRACT######
Mapping: empty, empty…. empty (i.e. you need to keep the mapping value as empty).
Step 2: Define another search string say ‘String2’ whose search string definition is same as the first one but with slight change in the mapping value.
Srch strg name: String2
Description: Getting the contract number from the file.
Search string: /SR/CONTRACT######
Mapping: empty, empty…. empty,#,#,#,#,#,# (i.e. you need to only map the # digits as it is and rest keep it empty).
Step 3: Assign the Search String ‘String1’ defined in step 1 to the correct target fields.
In this step add your required details like company code, house bank, account ID, etc. with Search string as ‘String1’, corresponding target field as ‘BDC field name 1’ and in the Mapping Prefix enter the field name i.e. BSEG-XREF3 in our case.
Step 4: Assign the Search String ‘String2’ defined in step 2 to the correct target fields.
In this step add your required details like company code, house bank, account ID, etc. with Search string as ‘String2’, corresponding target field as ‘BDC field value 1’. The Mapping Prefix field will be blank in this case as the value would be determined from the file itself based on the search string definition.
That’s it you are ready to dynamically get the value from the file. 🙂
Didn’t you realize the trick.. 😉
No… 🙂 well below is the explanation,
In case you read the SAP help document (see the first snapshot) then it asks to maintain the target field as ‘BDC field name 1’ and ‘BDC field value 1’ for the same search string and thus we are only able to pass a fixed value via mapping prefix to the BDC field of the accounting document.
In my case, we define two search string which is having the same definition but different mapping rule. Here, the first search string is used to identify the sequence in the tag 86 of the bank statement and if its successful then it will fill the target field ‘BDC field name 1’ with BSEG-XREF3 (as per our example). While the second search string (having the same definition) will be used to identify the dynamic value from the file and then to fill the target field ‘BDC field value 1’ as ‘XXXXXX’ (from the file) i.e. value for the BDC field BSEG-XREF3.
As the definition of both the search string is same, then either both will be successful or both will be failed. There won’t be any case were either of them is successful and this is the Trick 😉 which I have used to identify the BDC field name with first search string and then the BDC field value with the second search string.
This really works. Try it out and let me know your results 🙂
Please share your suggestions, corrections or any other pointers in the comments section.
Thanks again for reading this blog 🙂
Disclaimer: The above work around mentioned is based on my personal experience and is not copied or referred from anywhere else.
Hi Nikhil, Nice blog. Thanks for sharing 🙂 .
Thanks Gaurav.. 🙂
Nice first steps Nikhil. Way to go 🙂
It feels good to hear from a SCN veteran. Thanks Vinod. 🙂
Hi Nikhil,
Nice Blog! Good Start..Keep them coming
thanks Abhishek.. 🙂
Excellent work Nikhil. Works just fine!
Thanks for your contribution!
Hi mgitur,
I am glad that it worked for you.
Thanks
Nikhil.
Nice one nikhil...........
Thanks Svamsi!!
Hi Nikhil..
Thanks for sharing your knowledge with respect to updating of dynamic values by using search string in EBS.
My customer had similar requirement to update some text in SGTXT field and your document was very useful.
Initially my search string was not working because I was filling Transaction type field as UNALLOCATED (since the same is being used for posting rules) but when I kept it as blank..it worked!!!
Thanks again..
Regards,
Seema Saboo
thanks Seema...
Hi Seema,
I faced the same request from my customer.
I did the setup the same as this message, but I still cannot get the text updated as what I want.
step 1:
step 2
step 3 and 4
EBS file example: customer name follows "B/O CUSTOMER=", user wants to show customer name in the post document line item.
but with above setup, the customer name is not showing in the document line text still.
can you share me how you did? Appreciated.
Thanks and Regards,
Grace
Hi Nikhil,
Very good work.. thanks for sharing the knowledge..
do you have any idea, if a search string can be created based on text in tag 61?
regards,
Nitin
Hi Nitin,
The search string only works on the 'note to payee' field information which gets updated in the table FEBRE. There is any OSS note ( can't remember the number now) which can bring some part of the text from tag 61 to the note to payee field section so that search string can work on this. Please check SCN or SAP Support portal to find that OSS note number.
Thanks & Regards
Nikhil Kothari
Hi Nikhil,
thanks for this piece of very useful information! I had a similar issue where I would have to define like 20 different search strings for every value I want to populate.
I will add my piece of experience - it works also with Z fields (table COBL) and Nitin Tulsyan search strings work with any type of bank statement (I am using them for XML). You only need to get the information to FEBRE.
Regards,
Ondrej
Hi Ondrej,
Glad to hear that this was useful for you 🙂
Thanks & Regards
Nikhil Kothari
Hi Nihhil and community:
Your post was very helpful for my requirement, basically I have to extract a 10digits deposit# from segment61 of a MT940 and put in the assignment field of the 2 line items of the document. Following your guide I could do it for only 1 of the 2 line items (the first one). Could you please give me additional advice how to put it in the 2 line item? Thanks in advance for your prompt help.
Scenario Information:
MT940: 10 digits deposit is highlighted in blue.
OT51: I am using external transaction 681 + (Deposit)
Search String: I defined two 1) BS_DO_COL_DEP#_STA1 defining only the search string, but no mapping and 2) BS_DO_COL_DEP#_STA1A repeating the search string, but including desired mapping.
Search String Use: I created 2 lines
LINE1) Scenario combination using the string name = BS_DO_COL_DEP#_STA1 (Search string only), target field = BDC Field Name1 = BSEG-ZUONR.
LINE2) Scenario combination using the string name = BS_DO_COL_DEP#_STA1 (Search string plus desired mapping) target field = BDC field Value1 = empty.
Result after FF_5 (Upload MT940): Doc FI was created, but the 10digits deposit# is in the assignment field of the 1st line item, not in the 2nd line item. (it is the missing part, needing your help!!!)
FEBRE Content:
Thanks again.
Julio Lo
Hi Julio,
Thanks for this kind reply. As of now I don't have system access so I cannot replicate your scenario. But I see below possibilities,
1) There is another field for BDC line number to which search string can be assigned along with the BDC field name and value field which you have already done. You check the SAP SPRO documentation note for search string. So, you would need to aad two more assignments giving the line numbers where the mapping value needs to be updated.
2) The might be an existing custom logic to update the text field trying getting this scenario debug by an ABAPer.
Thanks & Regards
Nikhil Kothari
Nikhil and Community,
I found the root cause, there was a FI substitution replacing the assignment value defined by search string customizing. I deactivated the FI substitution and works fine.
I also recommend to check other customized code overwriting your search string result. for example exits, badis, btes or enhancements. In my case, I had the bad luck that the enhancement "*TRE_I300024_GL_UPDATE_POST" was also implemented, we realized it after hours of FF_5 debbuging.
I hope it helps.
Julio
Hi Julio,
As I suspected your issue was related to some other custom logic.
Good to know you found the root cause and this blog helped you for your solution.
Regards
Nikhil
Hi Nikhil,
Great document. Thanks for sharing.
Regards
Aman
Thanks for appreciating it, Aman
Hi Nikhil,
Excellent work . Thanks for sharing it.
Regards,
Atul
Hi Nikhil,
I configured as you guided but I have one query how to hit reference field for credit line item.
Regards,
Atul
Hi Atul,
I believe you are referring to reference key 3 field for credit line item. Check my blog the example I shared was for reference key 3 field. For hitting the specific line item check you accounting entry and at which line item the credit item appears i.e. line item 1 or 2. According pass the value for 'BDC account type' as 0,1,2,3 check the screen shot in the blog or help document in the search string node for more details.
0 - posting area 1 line item 1.
1 - posting area 2 line item 1.
2 - posting area 1 line item 2.
3 - posting area 2 line item 2.
In case still stuck then share an example of your data and current configuration so that I can provide specific inputs.
Regards
Nikhil Kothari
Hi Nikhil,
I have done Below Configuration.
Hi Nikhil,
Below is configuration of Strings.
Hi Nikhil,
I like to update the reference Key 3 on Debit and credit line item... as of now only able to update reference key 3 for debit line.But same I have to update it in Credit Line item .for Clearing purpose .
Appreciate your help.
Best Regards
Atul Khosla.
Hi Atul,
I see that you have not used the option of "BDC account type" in your configuration. So, this means that ideally the reference key should be updated for both the line items (debit and credit).
Is the document getting posted based on current settings? check the FSG for the credit GL account to see if XREF3 is available for input or not.
Thanks & Regards
Nikhil Kothari
Hi Nikhil,
Thanks for your help. Since it was issue of Field status. Now it is updating in both Debit as well as credit line item.
Best Regards,
Atul Khosla.
Great to hear that this solution is working for you as well 🙂
Dear Nikhi,
Now the issue for Clearing document both the XREF3 are getting updated with check number. along with this I have set a clearing rule in OB74 for XREF3 But when I excute F.13 Automatic clearing I am not able to find any item in it with it.
Best Regards,
Atul Khosla
Hi Atul,
If you can share with examples of EBS posting and then expected results from clearing program F.13 then I would be help you better.
Thanks & Regards
Nikhil Kothari
Dear Nikhil,
We have done EBS IN MT940 format. From format it updates Ref key3 which is given in MT940 Format.
Lets take a simple Example here. When Payment is made to vendor the entry passed is Vendor a/c DR.
To Outgoing Bank...
And when Bank Reco is Done the Entry generated is:
Outgoing bank A/c Dr.
To Main Bank.
So my credit and debit line of outgoing bank have same reference key 3 info i.e check number it should be clear automatically clear on the basis of rule set in OB74 as below
We have set Rule in OB74. With Criteria 1 XREF3 ie Reference field 3.
and when execute F.13 for outgoing bank account clearing does not happen please refer below screen shots.
Hi Atul,
As per the OB74 settings I see the GL account range given is from A to Z but your selection screen of transaction F.13 shows GL account number as 100027. So, I dont think your OB74 settings are correct. You check this by following approach.
1) You can do a manual test i.e. post F110 document and then manually post the bank reco posting with correct expected check number in XREF3 field. After this execute F.13, if the documents are cleared then check your OB74 configuration.
2) If F.13 works above then check the XREF3 field in the document posted through EBS whether or not then field contains any white spaces. If it does then this might be the cause of the issue.
Thanks & Regards
Nikhil Kothari
Hi Friends,
Need some help to setting up a search string with SGTXT. I have a requirement to update to setup a search string to automate a specific postings & copy some text from note to payee to BSEG-SGTXT.
Result - Same, unable to populate SGTXT from Note to payee.
Regards,
Santosh
Nice blog, it helped me in my project for getting dynamic value
WOW - amazing explanation, it really helped me out and all worked wonderful. Thank you very much for this great work!
I only have one issue, and I hope you have an idea how to solve it,
I have an issue with a search string definition.
I am not able to make the system have a space (blank) in the mapping.
My search string is setup and is writing dynamically the licence plate in BSEG-ZUORN, but unfortunately not (example):
F-X 1000, it is returning with F-X1000. So after the X, I would need a SPACE.
I really do not have an idea how to do this 🙁 Anyone here to help me with this? It is driving me nuts...
Giving more details, this is how I have setup the mapping:
1 F -> F
2 -> -
3 ? -> ?
4 ->
5 # -> #
6 # -> #
7 # -> #
8 # -> #