There are two ways to import General Ledger journals:
Import journals from a spreadsheet.
Import from a flat file into a workspace, where the data can be validated.
GL Import Journals
Importing journals lets you use an Excel spreadsheet to enter transactions in the right format and upload them into the relevant browse list.
Create your Excel import file using the template.
Create a GL Journal Batch in the normal way and once saved, select the Transactions Tab.
GL Journal Batch – Transactions Tab
Click Bulk (
) to select the button, and then click Add (
).The Transactions screen changes to an import view:
Transactions – Bulk Import
Locate the Excel workbook you are using to record and enter all the transactions and journal entries. You need to open the spreadsheet listing all the transactions to import.
Highlight the transactions that you wish to import in the Excel spreadsheet. You must always include all columns and the column headers. Select Copy, via the method you are most familiar with, to add these to your clipboard.
Returning to Coins ERP+, locate the Transactions tab on the GL Journal Batch screen to enable you to import these transactions. Select the large input field to position your cursor.
Paste the transactions from your clipboard, using the method you are most familiar with.
Transactions – Bulk Import
Click Save (
).The Transactions tab is displayed showing the lines with the imported transactions.
GL Journal Batch – Transactions Tab
Click Next (
).The Batches screen is displayed from which you can post the batch when you are ready.
GL Import-Export Journal Template
Creating a template in Excel lets you enter transactions into the spreadsheet and then upload them into the system.
The template needs to be an exact replica for the upload to work, including the field names, and field formats.
To generate a template, it is advisable to select a previously posted journal batch enabling you to utilise this as your example.
Select a journal batch number, then click Export (
).GL Journal Batch – Transactions Tab
A File Download dialogue box will be displayed as follows (format may vary between browsers):
Download the file
An Excel spreadsheet will be displayed containing the data of the transactions listed showing the field formats, along with the all the field headers.
Save the Excel spreadsheet as a template to enable you to continually use this for uploads. Enter journals in the template as required.
NOTE: In Excel, Column A defines the Ledger in Coins ERP+:
1 = Contract Status
2 = Asset
3 = General Ledger
4 = GST
5 = Workshop
As you upgrade and change versions, you may need to rebuild this template if new functionality is available.
Importing Journals – Overview
A new approach to loading General Ledger journals has been provided. This involves importing a flat file into a workspace, where the data can be validated, and with options to make amendments, clear the workspace or load the data into Coins ERP+.
These functions are only available with the correct licence; clients need to contact their account manager if they require a licence to use these functions.
Import Journals – Parameters
The SY parameter EQACCESS controls which users have access to the journal imports, in the same way as it controls access to batches. If EQACCESS is set to Y, users in the same group and with the same security level can access each other’s imported journals. If EQACCESS is set to N, only users with a higher security level can access them.
Importing Journals to Workspace – File Formats
The fields can be separated by commas, semicolons or spaces. The file can optionally include a header row. The first field on each line, Sequence, is a sequential number used to identify the transaction within the file. For each section, a single record can be included on the same line as the header section. Additional lines belonging to the same transaction must begin with the same sequence number. If any fields are not included on a line, the position must be left blank.
The format of the input file depends on the configuration of your Coins ERP+ system, You can generate a template file based on the current configuration, and use that to produce the import file.
To generate a template file
Go to Import Journals.
Tick the Generate Template Only box.
Complete other fields as required.
Click Next (
).
The template file is created and is available on the My Files screen.
Import Journals Workspace
Import Journals Workspace allows you to verify journal data which was imported using Import Journals, before loading it into Coins ERP+.
The initial browse shows a single row for each import.
Internal Reference
The automatically-generated internal reference for the loaded data.
A link on this column gives access to the individual journals.
Description
The description of the import (entered when the import was run).
User ID
The user who ran the import.
User Name
The name of the user.
Date
The date that the import was run.
Time
The time that the import was run.
Status
The status of the import:
Imported – all items have been imported.
Posted – all items are processed and loading into Coins ERP+.
Validated – all items have been validated and there are no errors.
Cancelled – the Clear Workspace action has been applied.
Import Journals Workspace – Actions
The following actions are available on the Import Journals Workspace browse.
Validate Workspace allows you to validate records across all imported data to check for any errors; these will be displayed on the workspace. These errors will need to be corrected before the journals can be loaded into Coins ERP+.
Post Workspace allows you to load the journals into Coins ERP+, once there are no errors against any records. This will create and post a single batch in the current company.
Clear Workspace deletes all details linked to a header record and sets the header Status to Cancelled. The header will remain for auditing but no details will be saved.
Import Journals – Imported Item Workspace
Clicking the link in the Internal Reference column on the Import Journals Workspace screen takes you to the Journal Import screen. This consists of three tabs.
Import Tab
The Import tab shows the header details from the Import Journals Workspace screen. An actions menu is available, which allows you to run the same actions for the import as on the Import Journals Workspace browse.
Imported Transactions Tab
The Imported Transactions tab lists the journals in the import.
Journals Import – Imported Transactions Tab
Column sets are available.
The final column, Validation Notes, shows:
Blank if the Validate Workspace action has not been run, or if the invoice data has been changed since Validate Workspace was last run.
Validated if the invoice has been validated and no errors were found.
Validation errors found if the journal has been validated and errors were found. Details of the errors are shown on the detail frame (when you click Detail (
).
Click Open (
) to open a journal and make corrections. All journals must pass validation before they can be posted into Coins ERP+. Concurrent update is also available.
Move Records to Another Workspace
A browse action allows you to move selected records to another workspace (Import Header). This makes it easier to post/validate data from different imports.
Validation Notes Tab
The Validation Notes tab shows details of “Batch” level validation problems (such as incorrect Financial Date, incorrect Transaction Type, or a batch imbalance). These must be corrected before the batch can be posted to Coins ERP+.