Overview

The Journal Entry import framework allows users to import new journal entries using an Excel template. The template is provided and can be downloaded from the system as a blank template for data entry.

Step 1: Downloading the Template

From NorthScope, expand the Company module on the navigation menu and select ‘Integration Inbound’. By default, this list view displays all integration inbound transactions with other than Complete and User Bypassed statuses. Clicking the ‘Inquiry Criteria’ toolbar icon opens a dialog window, which allows users to edit the inquiry criteria to e.g. display completed integration transactions.

Click ‘File Import’ toolbar icon to download an item template. This opens a new dialog window. Select ‘Journal Entry’ as the template from the Select Import Template dropdown menu. This will automatically select Journal Entry as the option by displaying ‘All items checked’.  Click ‘Create Template File’ to generate the template.

The template is saved to your computer’s default download location (e.g. Downloads folder). Depending on your browser and browser settings, the file download might be also displayed on the footer or header of the browser window for easy access. The file can be opened from the actual file location or from the browser header / footer.

Step 2: Entering Data

The template contains the following column headers and data can be entered under the column headers (starting from row 2):

• Journal Date – Enter the Journal date, this is the date that the Journal Entry will post to the GL and is required.
• Header Comment – Users can enter a comment for the journal entry.
• Reference – Users can enter a reference for the journal entry.
• Journal Status – Enter the current status of the journal entry. The entry can either be “New”, “Approved”, or “Ready to Post”.  This field is required.
• Currency – Enter the Currency ID of the journal entry. The currency defaults to the company currency saved in the Company Preference ‘Default Currency ID’.
• Exchange Table – This field is the Exchange Table ID that is used to calculate the currency exchange rates. This field is only required when the Currency entered is other than the company currency and the record status is ‘New’.  If the currency entered is the same as the company currency, this field will default to blank on the record view after import.
• Rate Type – This field is only required when the currency entered is other than the company currency, the Exchange Table is Manual, and the record status is New. The Rate Type options are either Multiply or Divide and determines how the Home Debit and Home Credit amounts are calculated in the grid section of the journal record.
• Exchange Rate - This field is only required when the currency entered is other than the company currency, the Exchange Table is Manual, and the record status is New. This field is used to calculate the Home Debit and Home Credit amounts in the grid section. If no value is entered on the file, the field defaults to the Exchange Rate Type when an existing Exchange Table is selected.
• Account Number - Enter the GL Account for the journal entry transaction. This field is required.
• Debit Amount - Enter the debit amount for the line item. Each row must have a value entered for either the Debit Amount or Credit Amount.
• Credit Amount - Enter the credit amount for the line item. Each row must have a value entered for either the Debit Amount or Credit Amount.
• Project - For companies using Project Accounting, this field allows users to associate a Journal Line Item to a Project. Enter an active project that is within the open date range.
• Line Comment – Allows users to enter additional comments to line items.

Note: The header details for each GL journal entry on the excel template must match in order to import multiple lines for one GL entry.

The fields that are optional can be provided in the import file or updated from the journal entry record once the items have been imported into the system.

Step 3: Saving the Template File

The file must be saved in a CSV format. By default, Excel creates the template in a TXT format and users must manually change the file format. This is very important, because the upload fails if the file type is not correct. To do this click ‘Save As’ in Excel and select 'CSV (Comma delimited) (*.csv)' as the file type before clicking Save.

Step 4: Uploading the Template File

If you have closed the file import window, re-open it and re-select ‘Journal Entry’ as the template. Click on ‘Select Files to Import’ and locate the file you saved on your computer. Select the file and click Open. Verify that a green dot is displayed in front of the file name. If the file is not in the correct format, a red dot is displayed indicating that the file cannot be uploaded to the system.

After the file has been selected and the file type validation is green, click on ‘Upload to NorthScope’. A pre-validation is performed before the file is added to the integration queue. This validation verifies that correct lookup values have been provided (e.g. the currency provided exists in the system) and that all required data is provided (e.g. Account Number). If the pre-validation does not pass, an error message is displayed for the user with the error details.

Successfully uploaded files are added to the Integration Inbound list view with the status New. When the integration has been processed successfully, the status is changed to completed and the journal entries are added to the system. If the file cannot be integrated the status is updated to Error and the Error Text field displays the reason why the integration failed. When a file fails to integrate, none of the items from the file are imported to the system and the file fails as a whole. Users must fix the errors in the file and re-import the file.

Additional Documentation

See also the following documentation:

/wiki/spaces/HELP/pages/65276574

Template: Journal Entry Transaction