Bank Accounts
Bank Account Reconciliation (Bank Statement Imports)
Business Central provides functionality that allows you to import bank statement files. The feature uses the Data Exchange Definitions, which will enable you to handle different bank statement formats. The Theta Base Pack provides pre-configured bank statement formats for the most common banks used by New Zealand companies. The app also offers minor enhancements to the Data Exchange Definitions to enable more support for the many different bank statement formats available.
This section covers the additional features added by the Theta Base Pack. If you want help with processing a bank reconciliation, try the following links:
Here's a link to the Business Central help on Bank Reconciliation.
Processing Files with more than three record types
The bank statement import functionality requires that only the transaction lines from the statement get imported. The Data Exchange Definitions can exclude header and footer lines so that only the transaction lines get imported. It cannot handle more than three record types.
Example ANZ NZ Format CSV File
In the example below, you can see that this statement format has five different record types. You cannot import this file correctly using the standard Data Exchange variable text import unless you first edit the file.
Example Configuration to handle more than three record types
You can import the file without first editing it with the configuration below.
Please note the following fields:
| Field | Comments |
|---|---|
| File Type | This field gets set to Variable Text because the file format is comma-separated values (CSV). |
| Reading/Writing XMLport | The value 58400 refers to the Id of the Theta Base Pack enhanced reading XML Port. |
| Column Separator | This field is set to Comma, the default type for Variable Text. |
| Footer Tag | Enter a text string separated by commas or semi-colons to indicate the record types you want to ignore. In our example, we want to ignore record types 5, 6, 8 and 9. Note that the records you want to ignore do not have to be at the end of the file for the app to ignore them. |
| Header Tag | The Header Tag works like the Footer Tag. |
Handling Fixed Text Files
The Data Exchange Definitions do not support importing Fixed Text as standard (although it does support exporting Fixed Text).
Example Westpac Fixed Text File
You cannot import this fixed text file with standard functionality because it has more than three record types and it is fixed text.
Example Configuration to handle fixed text and more than three record types
You can import the file without first editing it with the configuration below.
Please note the following fields:
| Field | Comments |
|---|---|
| File Type | This field gets set to Fixed Text because the format of the file is fixed text. |
| Reading/Writing XMLport | The value 58400 refers to the Id of the Theta Base Pack enhanced reading XML Port. |
| Column Separator | This field is set to Comma, the default type for Variable Text. |
| Header Lines | We specified 1 here so that the app would skip the first line. Setting the header tag in this scenario is unnecessary because Business Central will ignore the number of rows you specify here. |
| Footer Tag | Enter a text string separated by commas or semi-colons to indicate the record types you want to ignore. In our example, we want to ignore record types 5, 6, 8 and 9. Note that the records you want to ignore do not have to be at the end of the file for the app to ignore them. |
Additional EFT Fields
Bank Account (EFT Fields)
The app adds fields to the bank accounts that need to get updated on the accounts you want to configure for EFT Transfers.
| Field | Purpose |
|---|---|
| General | |
| Disable Bank Check | Disables the New Zealand Bank Account validation for this bank account. This only has an effect if this account is a New Zealand Bank account. |
| EFT | |
| Use Bank For EFT Payment | Specifies that you will use this bank account for EFT Transfers. |
| Enable Cross-Border Payments | Specifies that cross-border payments are enabled for this bank account. Cross-border payments are payments in a currency different from the bank's currency or a payment into a bank account domiciled in a different country. |
| Cross Border Identification Method | Specifies how to identify a cross-border payment. You can select Currency (payment in a different currency to the bank account) or Currency/Country (payment in a different currency or a payment to a bank domiciled in another country). |
| Bank Code | Specifies the EFT Bank file configuration, which gets used to determine the EFT file format. You can read more about defining the format here. |
| Bank Financial Institution Code | Specifies the Bank Financial Institution Code, which identifies the bank. This field gets used when validating EFT Approval Limits when you have selected to validate per Financial Institution. |
| EFT Filename | Specifies the name EFT File created when exporting an EFT Transfer. You can also include %1, %2, %3 = day, month, year from the date exported; %4, %5, %6 = Hour, Minute, Seconds from the time exported, %7 = EFT Bank Code, %8 = EFT Transaction No., %9 = Company Information Name. |
| EFT Bank No. | Specifies the EFT Bank No. For New Zealand and Australian bank accounts, this will get defaulted from the Branch or BSB. |
| EFT Bank Branch No. | Specifies the EFT Branch No. For New Zealand and Australian bank accounts, this will get defaulted from the Branch or BSB. |
| EFT BSB No. | Specifies the value of the bank state branch for Australian bank accounts. You can specify this with or without a hyphen. |
| EFT Bank Account No. | Specifies the EFT Bank Account No. This gets defaulted from the bank account number. |
| EFT Bank Suffix No. | Specifies the EFT Bank Suffix No. For New Zealand and Australian bank accounts, this will get defaulted from the bank account. |
| File Transmission Method | |
| Download to Client | Specifies that you want to download the EFT File to the client. |
| Bank Integration | Specifies an integration between Business Central and the Bank exists, and you want to enable this for this bank account. |
| Upload to External Storage | Specifies that the file will get uploaded to external storage (e.g. Azure Blob Storage). You must also specify the External Storage Endpoint and the External Storage Container for this to work. |
| External Storage Endpoint | Specifies the External Storage Endpoint that you want to use. |
| External Storage Container Name | Specifies the name of the container that you want to use. |
| Sequence No. | Used when generating EFT files for some banks. |
| Subscriber ID | Used when generating EFT files for some banks. |
| Subscriber Name | Used when generating EFT files for some banks. |
| Subscriber Code | Used when generating EFT files for some banks. |
| Subscriber Reference | Used when generating EFT files for some banks. |
| Clearing System ID | Used when generating EFT files for some banks. |
| Bank Integration | |
| Update Journal with Bank Exchange Rate | Specifies if the bank integration should update the journal lines exchange rate based on the payment feedback file. |
| Auto Post Journal | Specifies that the payment/cash receipt journal gets automatically posted when confirmation is received from the bank. |
| Post Domestic Payment Journal On | Specifies the point at which the journal gets posted regarding the files received from the bank. |
| Post Cross Border Payment Journal On | Specifies the point at which the journal gets posted regarding the files received from the bank. |
| Folder Reference | Specifies the value of the Folder Reference, which is used to identify the folder in the related bank sFTP. |
| EFT File Uploading to Bank Status | Specifies the status to represent when the file is busy being transmitted to the bank but has not been confirmed as received yet. |
| EFT File In Progress Status | Specifies the status to represent when the bank has received the file and is busy getting processed by the bank. |
| EFT File Accepted Status | Specifies the status to represent when the file is accepted by the bank and processed. |
| EFT File Rejected Status | Specifies the status to represent when the bank rejects the file and is not processed. |
| Bank Statement Integration | |
| Custom Statement Date | Specifies that you want to use the statement date from the statement file. |
| Statement Line Type | Specifies the entry type that contains the bank statement date. |
| Statement Filter Field No. | Specifies the field No. to filter the lines to get the statement date. |
| Statement Filter | Specifies the filter value to filter the records to get the statement date. |
| Statement Date Field No. | Specifies the field No. which contains the bank statement date. |
| Bank Reconciliation No. From | Specifies the value of the Bank Reconciliation No. From field. |
The status codes used for the bank integration can be defined per bank account to support different status codes from different banks.
Vendor and Customer Bank Accounts
The app adds fields to the customer and vendor accounts that need updating on bank accounts you want to configure for EFT. The app supports your vendors having multiple bank accounts, and you can specify a default per currency.
| Field | Purpose |
|---|---|
| General | |
| Disable Bank Check | Disables the New Zealand Bank Account validation for this bank account. This only has an effect if this account is a New Zealand Bank account. |
| EFT | |
| Use Bank For EFT Payment | Specifies that you will use this bank account for EFT Transfers. |
| Default for EFT | Specifies that the account will be used for EFT transfers. You can setup different accounts for direct debit and direct credit or use the same accounts. |
| Payee Name | Specifies the payee name. If you do not specify a value here then the Vendor's name will be used as the payee name. |
| EFT Filename | Specifies the name EFT File created when exporting an EFT Transfer. You can also include %1, %2, %3 = day, month, year from the date exported; %4, %5, %6 = Hour, Minute, Seconds from the time exported, %7 = EFT Bank Code, %8 = EFT Transaction No., %9 = Company Information Name. |
| EFT Bank No. | Specifies the EFT Bank No. For New Zealand and Australian bank accounts, this will get defaulted from the Branch or BSB. |
| EFT Bank Branch No. | Specifies the EFT Branch No. For New Zealand and Australian bank accounts, this will get defaulted from the Branch or BSB. |
| EFT BSB No. | Specifies the value of the bank state branch for Australian bank accounts. You can specify this with or without a hyphen. |
| EFT Bank Account No. | Specifies the EFT Bank Account No. This gets defaulted from the bank account number. |
| EFT Bank Suffix No. | Specifies the EFT Bank Suffix No. For New Zealand and Australian bank accounts, this will get defaulted from the bank account. |
| EFT Particulars | Specifies the EFT Particulars that will be copied to EFT Transactions when using this bank account. |
| EFT Code | Specifies the EFT Code that will be copied to EFT Transactions when using this bank account. |
| EFT Reference | Specifies the EFT Reference that will be copied to EFT Transactions when using this bank account. |
You can specify default values for EFT Particulars, Code and Reference on the EFT Setup.
Employee Card
The app adds similar fields to those added to the customer and vendor bank accounts.
EFT Bank Validation
The app allows you to configure rules per bank country:
- You can specify if the following fields are Optional, Compulsory or Invalid:
- Bank Branch No.
- SWIFT Code
- Bank Clearing Code
- IBAN
- You specify if the Bank Account No. can contain letters and not only numbers.
- The app will check that the IBAN or the Bank Account No. is specified for all payments, irrespective of currency.
- When the app gets installed or upgrade, defaults are set for AU, NZ, US, CA.
While configuring bank account information, the app will raise non-intrusive notifications to warn you of any potential issues. You get an error if the field Use for EFT is enabled. The bank account details also get validated while generating the EFT File. The notifications raised include a link to open a page where you can change country specific settings. You can also navigate to the Countries/Regions page and choose the EFT Bank Account Validation action.
You will notice that the validation rules can be different based on the currency too. For example, New Zealand domiciled USD bank accounts will contain
The EFT Validation fields are also available in the countries list but they are hidden by default.
Change to validation logic
Before 22.1.0.0, the app applied different logic to the above, and the app only applied the rules when generating the EFT Transaction. The rules were:
- For payments in the same currency as the bank:
- The Bank Account No. was compulsory
- You had to specify either the Branch No. or the SWIFT Code.
- For payments in a different currency from the bank (cross border):
- The SWIFT Code was compulsory. It was possible to turn off this validation per currency.
The feature to turn off the SWIFT Code validation by currency is no longer available. You must specify the country code on the affected bank accounts and ensure the country's validation is correct.
New Zealand Bank Account Validation
Business Value
When capturing New Zealand bank account details for vendors, customers, and employees, you must ensure the details are valid. When you enter the account details, Business Central will check the branch and account number combination to ensure that the account number is a proper branch and account number.
Feature Details
The New Zealand Bank Account validation is enabled when you install the app and create a new company. If you enter invalid account details, Business Central will display a notification. You will not be able to save the changes unless you resolve the issue. You can also disable the validation on a specific bank account if you are sure your account details are valid.
You can read more about New Zealand bank account numbers here.
The validation can only check if the branch and account number are within the bank's correct range. The feature cannot check that the bank account exists and is still active.
Configuration
You can enable or disable the New Zealand Bank validation for a Business Central company on the EFT Setup.