When receiving payments from customers, who pays a lot of invoices, e.g. insurance companies, telecommunication companies, government institutions etc., the invoice details often exceeds the capabilities of the electronic bank file format.
In those cases, the paying customer usually provides a separate detailed document (Excel, PDF, CVS…), containing a complete list of invoices being paid.
This document aims to explain how to get the external invoice data into the Banking solution’s posting journal, to help the automatic matching of invoice data against open customer invoices in the system.
EXAMPLE OF CUSTOMER PAYMENT WITH EXTERNAL ADVICE
In the system, customer DE-014 has three open invoices registered.
Customer DE-014 pays all three invoices, but no match is achieved because of an inadequate payment advice, which can neither be used to identify customer nor the invoices being paid.
Instead Customer DE-014 sends a separate advice via an Excel file by mail. This file contains the details regarding the invoices being paid
There are two options, in which to import the invoice data from the external file into the system. An unstructured import and a structured import.
UNSTRUCTURED IMPORT OF INVOICES
In most cases a quick and efficient way to import invoice data from the external file into the system, is by using a simple copy-paste method. In this case we copy the content from the Excel file to the customer payment Advice field, which is located on the Payment tab. Then result can be seen below.
With the invoice data now available, the auto match can automatically identify and settle the three related open invoices against the customer payment.
The copy-paste approach is simple and efficient way to import external invoice data. Furthermore, this approach is very flexible, as the type of external advice does not really matter. Data can be copied from highly structured Excel files but just as easily from plain text in a PDF document, an email etc.
The disadvantage to this approach concerns mostly the quality of the imported data. E.g. consider a scenario where a user copies hundreds of invoices directly from an Excel sheet, that contains additional information, such as order number and other external references. In such cases the system can unintentionally be fooled by a correlation between data in the system and the non-related imported data, thereby negatively affecting the match functionality.
Structured Import of Invoices
In addition to the simple unstructured approach to import invoice data, the Banking solution also provides a more structured CSV file import. This structured import is very useful, if the external invoice data is provided in a structured manner. The CSV requirement is there, simply to ensure that the data is available in plain text as well as maintaining its structure.
We use the same external Excel file example as we did in the unstructured import. In this case we convert the file to CSV format, which is done in few steps:
1. Open the Excel file
2. Choose File > Save as (F12)
3. Change file type to CSV (MS-DOS) (*.csv)
4. Click OK
The external invoice data is now converted into plain text, but please note that formatting might differ, depending on standards and localizations of your region.
Next step is to import the CSV file. This is done by selecting the record on which to import data, expand the Invoices form part and click the Import invoices button to open the import dialog.
The import dialog allows the user to upload the external file, by using the Browse button. Only thing remaining before importing the file is to choose the formatting of the file.
By looking at the content of the CSV file, we can easily deduct the formatting:
- Comma is used as the Decimal separator
- Semicolon is used as the Field separator
- First (1) column contains the invoice numbers
- Third (3) column contains the invoice amounts
- Yes, the File has headers
Clicking the dialog’s OK button will initiate the import of data according to the specified formatting. The invoices are then added and shown in the invoice form part.
The auto match can now automatically identify and settle the three related open invoices against the customer payment, using the imported and structured invoice data.
The structured import data is very useful, because data quality is of a high level and it is clearly defined, what is invoices number and what is not This makes it very easy for the match functionality to identify and settle the right invoices.
As the import also stores the exact amounts being paid on invoice-level, this import option allows organizations to extend the match functionality with automatic handling of amount differences and deduction.
The disadvantage of this approach is, that it is only useful if data from customer is provided in a structured way. In addition, this approach is a slower than the unstructured copy-paste approach, which might be more efficient in cases where only a low number of invoices are being paid.