Product documentation
Standard Microsoft Dynamics 365 for Finance and Operations has functionality for Bank Statement and even something called Bank Reconciliation. Even though it has good functionality, if a customer does not use lockboxes and/or checks, they might not truly require it.
We focused on developing our own solution within the General Journals because many customers do not use lockboxes and/or checks.
But it is nice to have a dedicated Workspace, and use GER and other functionality in the area of Bank Statements. And that is why we have switched our primary focus to the support of importing files into the standard Bank Statements. We still support the importing directly into the General Journals, but we won't optimize that further.
The importing into the Bank Statements is supported by the Generic Electronic Reporting framework (GER), and takes care of the initial import and validation of the bank file. After a successful import, the user processes it to a General Journal, which still takes care of the reconciliation/settlements.
| Bank Management workspace | 3 tiles have been aded to the Bank Management workspace |
| Imported bank statements | Shows the number of bank statements with status 'Imported'. |
| Journalized bank statements | Shows the number of bank statements with status 'Journalized'. |
| Open banking journals | Shows the number of banking journals that haven’t been posted yet. |
A standard import format for CAMT053 is available in the Banking solution. All European banks use the same format, so no bank-specific formats are needed. It is an XML file, which allows for specific tags for specific information.
If you need more information on CAMT053, this may be a starting point: https://www.sepaforcorporates.com/swift-for-corporates/a-practical-guide-to-the-bank-statement-camt-053-format/
The mapping of the formats (both MT940 and CAMT053) is done in Bank statement import methods for general journal.
If a CAMT053 holds batch transactions, the number of transactions found, is stored on the Bank Statement tab, in addition to the Payment Information (using tag PmtInfId). The Payment Information, amongst others, holds the journalnumber of the outgoing payment journal. The bridging ledger account associated with this journal is used as ledger account in the bank journal line.
Note: the system validates the number of transactions and the total value. If either differs between the imported statement and the found payment journal, the user will have to manually set the ledger account and fill out the payment journal through list / functions / update batch payment journal.
On posting the bank journal, the standard functionality of processing bridging transactions is called for every voucher in the found payment journal.
Four MT940 and four MT940S formats are available in the Banking solution. Those are the formats used by the major Dutch banks. The MT940 is the older variant, while the MT940S (=Structured) holds IBAN information, and ‘structures’ information like invoice-data, bank account, which makes it better to find.
Supported banks:
The banks are replacing the MT940(S) format with the CAMT053 format, but in some cases CAMT053 is not yet available, and sometimes the banks charge a premium.
Though the MT940 format is a standardized format, banks tend to interpret this standard in slightly different ways. Therefore, it can be necessary to adapt the standard import routine in the system, in order to get the data in the right field.
As more and more customers start using MT940S the variety in content increases. Most electronic banking software contains parameters, of which our customers have no understanding. Per situation an appropriate solution will need to be found.
In the RABO-format the tag 'EREF' is used to find the voucher in the case of a return payment.
The BAI2 format is primarily used in the United Kingdom and countries of the Commonwealth. It is a rather basic file, which does not have as much information as the CAMT053 or the MT940. As a result, not all reconciliation scenarios can be supported.
The Electronic Reporting framework is standard Microsoft Dynamics 365 for Finance and Operations functionality, and allows users to configure formats for both incoming and outgoing electronic documents. Amongst many others, Bank Statements are an example of these documents.
The standard Microsoft documentation on ER can be found here.
The other formats available are BAI2 and MT940. In this document only CAMT053 will be explained. The setup for are BAI2 and MT940 however are similar. Keep in mind MT940 is planned to be deprecated in the near future.
Prerequisite:
Before importing the HSO GER layouts you should first import the Standard Microsoft GER Banking Statements layouts from MS Microsoft, once this is done you can import the HSO layouts which we ship as part of our solution.
Import can be done in two ways.
1. Import our Banking Solution in the solution tab of Lifecycle Services (LCS), this will put the GER files into the GER Configuration tab. Download them and import them manually.
2. Import via the HSO Electronic reporting repository.
The HSO Banking GER files are available in the HSO Electronic reporting repository but can also be downloaded from the GER Confiugration tab in the Asset Library in Lifecycle Services (LCS).
The Bank statement format needs to be linked to the Import format configuration.
In Cash and bank management / setup / Advanced bank reconciliation setup / Bank statement format a link is specified between the import format configuration and the Statement name. The latter is what the user will select when importing the statements.
Start in the workspace of Bank Management. In the left-hand tiles, use the large tile that reads 'Import Bank Statements'. It opens a dialog, into which the Bank Account needs to be filled out, the import format to be used, and the file to be imported.
After importing, the result can be found under tile 'All Bank Statements'. It shows a list of Bank Statements, and clicking on one, opens the details.
We won't use any of the standard reconciliation functionality, because it is rather limited. Instead, the import is validated and if all is correct, they are transfered to a General Journal. This transfering happens by clicking 'Create bank journal' in the upper menu bar. The system will use the new Journal Name on the Bank Account to create the journal. The new journal can be opened, using menu button "Bank journal(s)". Then the reconciliation functionality of Banking can be used.
Microsoft Dynamics 365 for Finance and Operations checks for gaps and overlaps between Bank Statements. It also compares the calculated ending balance with the imported ending balance, and the actual number of lines to the number specified in the header.
Sometimes you may want to have the possibility to import more than one bank statement at one time and at a pre-defined point in time.
When you setup a default import folder and archive folder for Banking statements via Microsoft Azure File Storage, it will allow you to then to importing the statement periodically via a batch job.
After the Microsoft Azure File Storage has been created you then need to define the default Azure File Storage locations in General Ledger > Ledger Setup >Bank Reconciliation Setup > Import setup.
|
Validate button |
Validate the connection with Azure File Storage |
| Storage account |
Azure storage account name |
| Storage key | Azure storage key |
| File share | File share name |
| Import folder | Import folder name of the location where the Banking statement will be imported from |
| Archive folder | Archive folder name of the location where the imported file(s) will be moved to after being processed |
Note Folder/file location locations very per individual customer setup in Azure. Azure setup is the reponsiblity of the customer/partner. If needed a permier service can be offered for assistance in the setup. Contact HSO for further details.
Once the setup has been completed it is now possible to upload Bank statement to the Azure file location.
When all the bank statements are present in the Azure Import file location go to the workspace of Bank Management. In the left-hand tiles, use the tile that reads 'All Bank Statements'. It opens the Bank Statement form, on the top of the form click on the “Import Folder” button.
This will open the Import Bank Statement dialog. Here you can see the Import and Archive folders that have been predefined in the Import parameters. You also have the possibility to set up a recurring batch job to import the bank statements.
| Standard Bank Statements | Journal Status: a second status field has been introduced to show the user if there are already journals for a statement, and what the status of those journal are. |
| Imported | The Bank Statement has been imported, and no journals have been created yet. |
| Journalized | One or more journals have been created, but they have not ben posted yet. |
| Posted | The created journals have been posted. |
| Bank Management workspace | 3 tiles have been aded to the Bank Management workspace |
| Imported bank statements | Shows the number of bank statements with status 'Imported'. |
| Journalized bank statements | Shows the number of bank statements with status 'Journalized'. |
| Open banking journals | Shows the number of banking journals that haven’t been posted yet. |
Using the 'Create bank journal' button in the Bank statement form copies the lines to a General Journal. Depending on the value in 'Split bank statement at # of lines' a single Bank statement can result in multiple Bank journals. In the dialog the user is able to indicate if Microsoft Dynamics 365 for Finance and Operations should search for Companies, Accounts (Customer, Vendor or Ledger) and immediately start the Reconciliation process.
| Create Bank Journal | Periodic process: A new menu item that allows a user to Create bank journals across Bank Statements. By default, this is filtering on Bank Statement status 'Imported'. It can be set up in a batch job and process in the background. Functionality is similar to the menu item 'Create bank journals' in the Bank Statement form. |
| Delete bank journals | When Banking Journals have been created via Banking statements is only possible to delete the banking journal via the button Delete Banking journal. If a banking journal has been created directly in Dynamics Banking journal then it is only possible to delete the journal in Dynamics banking journal. |