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 the second post, we took 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 that post, we focus on describing the format structure in the tool. In this third post, we go on to demonstrate how the mapping from bank statement to Business One fields is done. By the way, I’m planning to devote the last post in this series to CSV bank statement handling and use of regular expression in the tool.
Keywords: bank statement processing, BSP, Format Definition, Payment Engine, BTHF, file format
So far we have defined the accurate structure of the bank statement from Bank of Universe, an imaginary bank for this blog. With the format tree and locate by options set in different nodes, run-time software can easily understand what information the bank statement file contains. The next interesting issue is what kind of bank statement structure Business One can accept.
Understanding Bank Statement Object in DI API
Since SDK is the mostly recommended way to integrate with Business One core, Format Definition mapping tool also chose BankStatement object in standard Business One DI API as the mapping target. This object is added since 2007 version, and it can only be passed to methods in another BankStatementsService object to process bank statement drafts in Business One. Good news is that you don’t have to cope with DI methods directly in a programming language by yourself, because in the mapping tool we provide an easy user interface with all the relevant fields for you to choose from. The tool ONLY picks up properties that are relevant for automatic data entry from DI BankStatement object, so it’s a subset of the complete property list. All the selected properties are organized in a three tier hierarchy: bank statement (or its header), statement row and multiple payments section in a row, which are referenced in the tool as three tables: BankStatement, BankStatementRow and MultiplePayment.
Under BankStatement table (the header), all statement level properties (fields) are included such as StatementNumber, StatementDate, StartingBalanceF, EndingBalanceF, Currency, StartingBalanceL, EndingBalanceL. For each property, the display name is the same one as in DI API and basically self-explanatory. If you need help, just look up it in the DI online help. Only one thing to be noted is there are two group of balance in either local currency (L) or foreign currency (F). If the bank statement also gives the balance amount in two currencies at the same time, you can fill them into both group as well as the foreign currency filled into the Currency property. If you are not sure about the currency used in the house bank, the safeguard is to always fill the foreign ones as well as Currency property. This is also applicable to amounts elsewhere in this object.
Under BankStatementRow table (the row), more properties are provided to describe a transaction. It’s more clear if we organize them into several groups.
Group 1: AccountNumber, AccountName which are relevant for house bank account.
Group 2: BPName, BPBankCode, BPBankAccount, IBANofBPBankAccount describe the business partner in a payment transaction.
Group 3: CreditAmountFC, CreditAmountLC, DebitAmountFC, DebitAmountLC, ExchangeRate, CreditCurrency, FeeOnTheLine and Balance tell us amounts about the payment transaction, e.g. the payment amount, bank fee and the new balance.
Group 4: BankStmtLineDate, BankStmtDueDate and DueDate signify the dates regarding the payment: entry date and due date. Among them, try to fill BankStmtDueDate rather than DueDate. You can configure how your payment posting date/due date definition is like in banks set-up form in Business One.
Group 5: Reference, Details and Details2 are some extra information which we give to the banks and let them carry forward to the statement. Definitely we could use this group for further reconciliation.
Group 6: ExternalCode tells us the category of the payment from the perspective of the bank, which can be mapped with internal bank codes to instruct the posting.
I guess you might already find some of them are relevant for the external reconciliation provided by the new bank statement processing functionality. That’s correct. You can decide which properties can increase your bank statement line matching rate. Even you can intentionally determine which extra messages you would like to fill the reference/details/details2 properties when you are exporting bank payment files within payment wizard by developing your own outbound bank format plugin. And I can ensure you that in a certain future version, Business One will expose the outbound format definition within the mapping tool just in the same way as the inbound part. So far, unfortunately you have to develop a COM plugin by following the development guide from the Payment Engine add-on landing page in partner portal.
Finally, let’s check about MultiplePayment table, only four properties are included and they are DocumentIdentifier, AmountFC, AmountLC, IsDebit. As we talked, this table is intended to store all the documents paid in a transaction. Therefore, it’s not difficult to realize that those four properties should be filled with the document number, amounts in FC and LC, and finally a flag to reflect whether the paid amount is incoming payment (with value tNO) and outgoing payment (with value tYES).
All the acceptable values for each property follow the definition in DI API. If you still have any questions about any of these properties, please refer to the SDK help.
Ok, after we get familiar with the mapping targets, we can easily figure out which target we should map each of the format tree nodes to. Let’s take a simplest one as the first try. Select “header” from the format tree, go to the mapping tab, choose normal mapping and select “BankStatement” from the target table list. We did this because we know each header has a clear one-to-one mapping to its containing bank statement. Then we try on a field. Select “Currency” just under header, switch to mapping tab and choose normal mapping, then you will see target table is inherited from the header, we can keep it or change it. In this case, we are going to keep it and then choose Currency as the target field.
Next let’s see some advanced cases. First let’s select “Statement Date” field from the format tree, you might instantly figure out the target field for it should be “StatementDate” property in the header. Yes, basically you are right. But if you execute the data preview from the File menu, you can find that the date value is not expected as YYYY-MM-DD, as requested by DI API. How could we transform the date format? You can either use function to manipulate the value (e.g. use substring operations), or easily set data format attributes in attribute tab. For example, you can set data type as date and select “YYMMDD” from the list to signify the format string of the field “Statement Date” in the original bank statement. Run data preview again, you will get the correct data format. Usually it makes life much easier if you set the original format strings of any date or numeric fields.
Then let’s think about how to fill the “StartingBalanceF” property. A direct way is to map “Open Balance” field from the format tree to it. That’s correct in most cases. However, what if the open balance is negative? You would still fill a positive value since the balance sign is told by another field rather than the balance field itself. That’s where conditional field and result fields come into place. This combination of these two kinds of fields is really usefully if you want to fill different values in different cases to a target field, or if you want to fill different target fields by different conditions. Let’s see how to achieve it.
Insert a condition field named “Open Balance” under “Header” node in format tree, assign it with the same locate by attributes as the normal field with the same name (i.e. located by position, position/length = 10/10). Don’t forget to set the data format attributes for this numeric field (i.e. data type/format string = numeric / 00123456.XX). Insert a result field named “positive” as a child node of it. We need to reference “open balance sign” field in the conditional mapping, therefore let’s go to the general tab of this referenced field first, assign a reference no. called “OpenBalanceSign” to it. Then go back to the “positive” result field, switch to condition tab and construct the condition expression as Type1/Arg1-2/Operator/Type2/Arg2-2 = Reference No./OpenBalanceSign/Equals to/Constant/+ . OK, finally switch to mapping tab and choose “StartingBalanceF” as the target field. Repeat those steps to insert another result field named negative and fill an opposite condition and same mapping target field. When you choose another field in the format tree to make the change take effect, you will be asked if you want to replace the mapping from “positive” field to target field “StartingBalanceF”, create a duplicate target field or cancel the action. In this case, we know we don’t want to replace the previous mapping and we exactly want both mappings co-exist yet in two different non-overlapping conditions, so we will choose “duplicate the target” option. You might notice the target field name is changed and followed by a number in a pair of parentheses. That represents you have duplicated the target. Since we want the negative amount has a leading minus sign, so put a function “concat(‘-‘,.)” in the mapping method text box. You can either delete the normal field “Open Balance” or keep it, but you’ve known that it’s useless. If you want to test the correctness of balance sign handling, just change the sign field in the sample statement file and run the data preview.
Please practice the knowledge introduced here to complete the rest mapping by yourself. I also attach a complete BFP file for you reference. You can download it from the top of this post.
Test the Format in Business One
If you have knowledge on how to configure a house bank account with a BFP file, you can skip this section.
After obtaining a bank format project file (BFP file), you can assign it to a house bank account, for which the bank will send the bank statement in exact format defined in the BFP file.
Ensure that you have enable BSP in the company details form. To test the format we just created, first create a new house bank account in Business One. Set the account number to be “1234567890” as shown in our sample statement. Go to the file format column (the last one) of the account in house bank account set-up form, click choose from list button and select new to create a new format. Give it a name in the newly opened form, then right click on this new row and select “assign format project” menu. Find the BFP file and click OK. Then finally click update button to finish the format set-up and house bank account set-up. Now you have configured correctly the bank account and its bound format project.
Start BTHF add-on from add-on manager to make the automatic bank statement importing feature available.
Go to bank module in the main menu and choose bank statement processing menu entry. In the popup form, select the house bank account we just configured, click Import from File button to start importing bank statements. Select the sample statement we used in last post, then you will see the importing summary.
Until now, we have demonstrated a complete example on how to define a bank format project in aspects of creating format tree and mapping to Business One bank statement object. We also show how to configure the format and run the import with BTHF add-on in Business One. Now it’s your turn to practice the knowledge on a real bank format requirement.