There is a new feature called bank statement processing (BSP) in SAP Business One 2007 which greatly enhances the matching and reconciliation functionalities against previous solutions. Together with manual bank statement key-in, SAP also provides an automatic solution for customers to import their bank statements into Business One. In this solution, a brand new Add-on named Format Definition is introduced. It helps you to describe the structure of statement files and define its mapping to Business One. In this series of blogs, I will guide you through all the features of this new tool.
Recall that in the first post we reviewed the history of bank statement processing solutions in different Business One versions and the advantage of introducing a mapping tool to cover the flexibility and diversity of variant bank formats in the world. In this and next post, we will take a closer look at the file importing solution by a case study of an imaginary bank statement format, which is invented purely for the case study and contains all the necessary fields in real plain-text bank statements but simple enough for demonstration. Especially in this post, we focus on describing the format structure in the tool. In the next post, we go on to demonstrate how the mapping from bank statement to Business One object is done.
Keywords: bank statement processing, BSP, Format Definition, Payment Engine, BTHF, file format
Along with Business One 2007A, 20 commonly-used bank statement formats are released. However, there must be some other formats not covered in the official release, which are also necessary for some Business One customers. Especially for all 2007B countries (India, Brazil, China, Japan, Korea, Singapore), there are no usable predefined formats in the package. Customers in those countries might seek help from their partners. Fortunately we can define a usable new format in a short time by virtual of Format Definition tool without any programming knowledge as it used to be.
In the following sections, let’s see how the tool deal with a new bank format definition in a user-convenient way step by step.
Assume that Bank Universe (an imaginary bank) has issued a new bank statement format on their website which will replace their old one as of Jan 1st 2010. As a Business One partner, there are a lot of joint customers of the bank and your company who ask you to implement such a file format in Business One 2007 before the end of the year so that it wouldn’t block their regular bank statement importing process.
The new file format is a plain text document which includes a header line and a trailer line as well as many transaction records therein. Every transaction record contains two parts, a main record to tell the routine information about the transaction followed by some optional supplementary lines to further describe the payment list in the situation of a consolidated payment. A consolidated payment happens when the bank recognizes there are more than one payments are relating to the same other party (receiver) and it automatically consolidate those payments into a single one to save the bank fee for the sender. To be simple, each field in all those records will occupy a fixed length. We also only consider the fields mandatory for the bank reconciliation process later in Business One. A formal format specification is downloadable on the top of the post.
Besides the specification of the new file format, in most cases we could also get such a bank statement file conformant to the specification, either from bank or customer. To that end, I also invent one with only three transactions. You can also download such one on the top of the post.
It’s quite easy to understand the structure of this bank format. Usually this is the typical case you would ever meet. We can quickly draw a hierarchy on the paper to describe it.
Now let’s see what to do in the Format Definition tool. Assume that you have connected it from add-on manager, click the menu with the same name under Tools in the top menu to open the main window. Choose “New Format” to start it from scratch. You will see the initial format tree with the only root node. As a good habit, give the format a meaningful name and description. Choose a suitable character encoding other than the default “ISO-8859-1 Latin 1” if the bank file is not containing only ASCII characters. The encoding is often noted in the specification from bank. For example, for Asian countries, you have to select an appropriate one such as shift-JIS, UTF-8 and so on. You can open the bank statement examples on your hand to verify the encoding you selected by checking if the file is displayed correctly. Just click the browse button in the bottom-right file view zone to do that. In our case, open the sample statement from the download in the file view zone.
Figure 1. Supported encoding list
Another preparation work is to set the line break, fixed line length, or field separator if it’s a CSV file. In our example, we only need to set the line break to be “
” which means carriage return and line feed, the line break in Windows operating system.
Figure 2. File setting
Format tree: rough structure
Next we can go on with the rough structure of the format. By right clicking on the Root node, we can insert a child segment node underneath. There are two kinds of segment nodes in the tool, the standard segment and the segment group. The difference between them is quite straightforward. The former can take concrete fields as child-nodes but the latter can’t. Actually the latter is acting as a container to group other segment nodes. You can simply regard the relationship between them as that of files and directories in the file system. Ok, the next question is when I shall use one and when another. The answer is if you are describing a record, normally a line in the bank statements, you use the standard segment. Otherwise, if you are describing a compound structure with more than one record, please use the segment group. We will see an example of the latter soon. For now, you might guess out we should opt for the standard segment to describe the header record.
In the general tab, input the relevant fields for your format tree nodes. Let me explain them a little bit. First of all, Node ID is a globally unique ID for each node assigned automatically by the system, which might be useful when you have to investigate into the BFP file dumped by the tool. Fortunately you have very rare chances to do that. Secondly, Reference number is used in the mapping phase, which we will visit later. Last, the Mandatory and Repetitive radio buttons at the bottom of the tab are meant to describe the occurrence times of current node in the context of its parent. Check the matrix below to know how to express different occurrence times range.
For instance, each bank statement in our case can only have exactly one header, so we will check the mandatory option but leave repetitive option alone.
Figure 3. General information of header record node
In the attribute tab, there lists some locate by options to let you define the positional characteristics of the current node in the context of its parent. We know that header record is always starting with record identification “10” and will totally occupy 38 characters until a line break. Notice that the following line break is not counted into the length, i.e. 38, so we put it inside following keyword. The prior keyword is of the same case, but in another direction, i.e. at the beginning of the node. On the other hand, starting keyword and ending keyword will always be counted in the length. By leaving and re-focusing on the current node, the changes will take effect. You will observe that the header line is highlighted in the file view and the status bar tells one match was found.
Figure 4. Location characteristics of header record node
Note: For those ex-C/C++ programmers, the length here is the character size rather than byte size, so even you are handling a Unicode bank statement, the length is still the count of all characters, no double!
Let’s continue with other records before delving into fields. The next two records are transaction record 20 and supplementary record 21. Of course you can insert both in the similar way as header record. But if you do that, you break the containing relationship between the transaction and its optional supplementary lines at the mapping phase. The appropriate way is to treat the block including both as a compound group, like the following structure, thus the containing relationship is well kept.
By following this approach, we need to insert a segment group under Root to represent the logical compound group. This time we mark repetitive option only in order to tolerate bank statements without any transactions in the specified period.
Figure 5. General information about the transaction group node
In the attribute tab, we select to locate the group node by its sub-nodes. The options for sub-nodes could be sequential or alternative. But we know the sub-nodes record 20 and record 21 will be presented exactly in that order rather than alternatively, so we keep the default sequential option.
Figure 6. Location characteristics of transaction group node
Then we can quickly add two sub-nodes under the group by simulating what we did for header. For each logical group of transaction, there is exactly one record 20 and possibly any number of supplementary records 21. So the transaction node should only check mandatory option while the supplementary node should check only the repetitive option. You can then switch back to transaction group node to see the preview, and 3 matches were found, highlighted in blue and red color, alternatively. You can switch between the mandatory and repetitive options for those two sub-nodes and see how it affects the preview.If you don’t recognize the necessity of a segment group until you have completed the full format tree, don’t worry! To fix it, you can insert it anytime you’d like to and then cut and paste the sub-structure into it.
Figure 7. Sample bank statement preview of selected node
Follow the same procedure as header to inset the trailer record, except that you have to remove the following keyword otherwise it won’t get matched when the trailer record doesn’t end with a line break in the bank statement. Even by doing this, you will still find that the tool found 2 matches. One unexpected match is in the middle of the header line. You can fill “
” to prior keyword to exclude it.
Figure 8. Unexpected match of trailer node
So far we’ve finished the coarse structure.
Format tree: detailed structure
Next we will complete the format tree with detailed fields under each record. It’s a very typical and routine job, so let’s see how to add a field for an example. By right clicking on header, choose to insert a standard field and name it “Statement Date”. In our case, statement date is ranging from character 3 to character 8. Go to attribute tab, choose to locate this node by position, which is actually the default option for non-CSV fields. Fill starting position in position field, i.e. 3; fill total length in the length field, i.e. 6 (equals to 8 – 3 + 1).
Figure 9. Adding a field
There are several points necessary to be explained. Firstly, ignore the condition field and result field for now, which is meaningful in mapping phase but not in conversion phase. You will see in mapping phase we will change some of the standard fields into a combination of conditional and result fields to define a conditional mapping. For the very basic new user, we won’t touch these concepts until mapping phase. Secondly, data format pane is also mapping relevant, which is handy to convert text/number/date in a different format into the standard one of the mapping target, i.e. Business One SDK object BankStatement equivalent XML. Last but not least, you only have to add those fields relevant to you in any order, as the tool doesn’t depend on the completeness and positional order of child-nodes to locate its parent, except when the parent is defined as located by sub-nodes.
Keep adding all the relevant fields into the format tree and finally you will get the complete structure as below.
Figure 10. Full structure of format tree
A BFP file with the full structure is also downloadable from the top of this post. You can open it from Format Definition on your local to have a reference.
Due to the big size of this post, I will leave the mapping related topics to next post.
Conclusion of this post
In this post, we explained how to describe a plain-text bank format in the tool. We introduced 3 out of 5 built-in locating by options: keyword, sub-nodes and position. For the other 2 options, i.e. separator and regular expression, we will postpone the explanation to later posts.