SAP Business One: EFT Bank File Format (Electronic Funds Transfer)
SEE MY YOUTUBE VIDEO ON THIS TOPIC HERE: https://youtu.be/pu7eLHE9if8
Since SAP Business One 9.0 I had been hearing about new EFT capabilities built right into the system and I had a client request this feature so I decided to really dig into the subject. So after a LOT of investigation and testing I will summarize in plain English what I found.
(NOTE: my focus here is on EFT files, NOT bank reconciliation files but SBO can do this as well. I will attempt to note this whenever possible.)
SAP Business One Has a Really Well Thought Out EFT System
It’s hard to find and understand but it is well thought out once you get the hang of it. It can do EFT files for payment, import files for bank reconciliation and generate CSV files in specific format for any other file type you would want to work with. I was using it specifically for an EFT outgoing payment file to a bank for payments.
The Electronic File System is Two Part
It is not documented very well but you need two SAP add-ons to make this system work properly. One to edit the files called Electronic File Manager (EFM Format Definition) and then one to generate the payment file from the Payment Wizard (Payment). Both should come with a standard installation, in this screenshot I’m using SBO 9.0 PL15.
Built into SBO all you need to define is the file format for the payment method (Administration >> Setup >> Banking >> Payment Methods) for it to be activated with your particular payment method. Of course you need to define the payment method as default for the vendors (this can be done with DTW) and then you are good to go with the payment wizard. If you are having issues with the actual upload to the “File Formats – Setup” just right click the row and click “Assign Format Project…” to assign your file.
The System is Based on Crystal Reports
The EFM uses a very nice Crystal Reports data source setup. You can import and export datasources to Crystal to make modifications, re-import them easily (with automatic link updates) and generally craft your output data using CR grouping and formatting. There is a limited set of formatting formula options within the EFM tool though.
The Actual File Export is Hard to Find!
I made my file and installed it without any issues but finding the actual output was damned near impossible. Once I found it though, the system was pretty tight, letting you generate test files and even recompile the information source before exporting. SO, if you spot missing bank information or the bank rejects the file for some reason you can make the changes and regenerate the file before exporting.
Once you find the little button then you are good to go, but I had no idea where to find it! Here is what the screen looks like:
The button is on the very last page of the Payment Wizard form at the bottom and is only visible when the “Payment” add-on is running.
Documentation is Hard to Find
You will find an ooolllddd expert empowerment session which is referenced but it doesn’t really have the information you are looking for (2007A time period). But if you look hard enough you will stumble across a couple of good references. Here are some links:
You can also use the EFM help file which has a lot of information specifically for formatting and editing within the editor but good overall reference once you find it. Activate the add-on called “EFM Format Definition” from SBO, then relaunch the client. Once installed and running go to Tools >> Electronic File Manager: Format Definition to run the editor. Once in the editor click Help >> Online Help or F1.
There is a Great Sample to Start With
In Reference #2 there is a .BPP file which is a great starting point to understand the structure, I don’t think two banks will be exactly the same so you will likely be adapting it from scratch any way you approach it.
There is NO Standard Layout
From what I can see, each bank (I’m located in Canada, maybe USA or other is different) has their own type of file format definitions that differ slightly (or majorly depending on your bank).
Here are the formats that come with the system:
(NOTE: Minus the 80Byte which is my own)
So if any of those formats fit your definition exactly then you are in luck and it will save you a ton of work. If not, then you will have to get the file format definition from your bank. Also as a side note, banks may or may not be willing to really help you out with the formatting and testing, depends on the bank and branch, etc.
With the two add-ons in place and the reference materials you load up your EFM program and use the template as a starting point, from there you can just add sections and subsections based on your bank documentation. Upload the file and associate it with the payment method of choice (must be bank transfer) and then set as default for vendors. As you are using the payment wizard the steps will be the same, once you get to the very end and the run has been executed, just look for the button in the lower left area to generate your file. Upload to your bank et voilà!