Overview
The Opening Balance Importer will import any historical balances against customers. It is frequently used after a migration of customers has occurred from another system to Emersion. Once the customer records exist in the system, this tool will update the current balance of an account ready for the next bill run to generate new invoices.
The tool also supports storing a history of account balances, for example, the last 6 months worth of invoice data. This is achieved using the ORIGINAL_INVOICE_ID, INVOICE_START and INVOICE_END fields.
If your account has Multiple Pending Invoices enabled, then your system behaviour may be different in regards to pending invoices and cardlines. Please see this article for more information.
Licencing
This import tool is currently not available to users of Cumulus. Service Providers wishing to import opening balances must:
- prepare an import template as they would any other import.
- open a new quote (QR) request in the Emersion Customer Support Portal
- upload the import file into the QR ticket
- accept the quote and pay the invoice.
Emersion will import the data for you and advise you when the data is ready for validating.
Business Rules
- Users cannot create new invoices if the account already has a pending invoice (unless multiple pending invoices is enabled)
- The importer will create an invoice as per the invoice issue, due and cardline amounts above. If the BALANCE_DUE_EXTAX and BALANCE_DUE_TAX values are less than the cardline values, then the system will create a payment to offset it. The payment will be allocated automatically and will have a payment date equal to the supplied invoice due date.
- In the event of a credit invoice, the importer will create the credit invoice. The system will, as per normal process, turn that invoice into a payment.
- While the import is in progress, the affected accounts will have their billing suspended to prevent the system interfering with the process
- If an imported invoice has an outstanding balance due, and the target account has an unallocated payment, then the payment will allocate to the newly imported invoice accordingly.
Preparing The Data for Import
To prepare your data for import, you will need to create a CSV import file or you can download the import file below. You will need to extract the csv file and save it.
A hard limit of 500 rows is supported via this tool, including the header row.
If you need to import more than this, create multiple import files and process them one-by-one.
Data Definition
This section contains a list of the required fields and a definition of what the system considers as valid inputs.
| Data field | Description | Mandatory? |
|---|---|---|
| ACCOUNT_ID | The Account Id against which you are importing the opening balance. | Yes |
| CARDLINE_AMOUNT_EXTAX | The ex-tax value of the invoice. Can be positive or negative. | Yes |
| CARDLINE_AMOUNT_TAX | The tax value of the invoice. Can be positive or negative. | Yes |
| BALANCE_DUE_EXTAX | The outstanding balance (ex tax) value of the invoice. If the customer did not pay this invoice, the the outstanding value would equal the amount in the CARDLINE_AMOUNT_EXTAX column. If the invoice is a credit invoice, then the only acceptable value is 0.
| Yes |
| BALANCE_DUE_TAX | The outstanding balance tax of the invoice. If the customer did not pay this invoice, the the outstanding value would equal the CARDLINE_AMOUNT_TAX. If the invoice is a credit invoice, then the only acceptable value is 0.
| Yes |
| CARDLINE_DESCRIPTION | The text that is to appear in the created cardline. 255 characters max. | No |
| INVOICE_ISSUE_DATE | The invoice issue date. DD/MM/YYYY (or YYYY-MM-DD) | Yes |
| INVOICE_DUE_DATE | The invoice due date. DD/MM/YYYY (or YYYY-MM-DD) This date must be after the INVOICE_ISSUE_DATE | Yes |
| ORIGINAL_INVOICE_ID | The original identifier for the invoice. Use this if you are storing a history of account balances. 50 characters max. | No |
| INVOICE_START | The original invoice start date. Defaults to the imported date if no date is provided. | No |
| INVOICE_END | The original invoice end date. Defaults to the imported date if no date is provided. | No |
| TAX_ID | The ID of the Tax you wish to use for the Opening Balance cardline. This Tax ID must belong to your account's default country. Report > List > Reference > Tax Type List (874) can be used to obtain the relevant Tax ID. | Yes |
This value should not exceed CARDLINE_AMOUNT_EXTAX
Viewing Imported Data
After importing data it is important to validate it was imported correctly. Choosing a random records and performing a visual check in Cumulus should reveal any mistakes relatively quickly that would be harder to see in an import sheet.
Customer notes can be found via Customer > Invoices
If the Xero Integration is turned on invoices from the opening balance import will be pushed across - if setting up Xero for the first time this is a useful way of aligning Cumulus and Xero. If Xero has been active you will need to have Emersion set the invoice sync date after the opening invoice issue date.