Difference between revisions of "Chart of Accounts Import Guide"
From Adjutant Wiki
| Line 1: | Line 1: | ||
| − | ==General Import | + | ==General Data Import Notes== |
| − | + | Refer to the [[Data Import General Information]] page for information and guidance on general import timeline, formatting import templates, as well as how to ensure you are using the most current template information for data imports. | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
Revision as of 13:23, 10 January 2019
Contents
- 1 General Data Import Notes
- 2 Chart of Accounts Import Notes
- 3 Chart of Accounts Import File Data Scrubbing
- 4 Chart of Accounts Import Screen (IMPORTGL)
- 5 Chart of Accounts Import File Definitions
- 6 Chart of Accounts Import Reconciliation
- 7 Chart of Account Import Additional Steps
- 8 Chart of Accounts Import Database Tables
General Data Import Notes
Refer to the Data Import General Information page for information and guidance on general import timeline, formatting import templates, as well as how to ensure you are using the most current template information for data imports.
Chart of Accounts Import Notes
The Chart of Accounts Import does not have an Export Template option. You can find a copy of the Chart of Accounts Import Template in the ABIS FTP site in \Implementation Files\Import Templates.
The Chart of Accounts Import does not use the f1-f999 row and has the data field names in row 1. The import routine starts importing records on row 2 of the XLS file.
Timing and Preparation
The Chart of Accounts is one of the first imports that should be completed. You must have GL accounts in order to complete setting up the CID Maintenance screen and before adding a Warehouse record.
General Notes
During the import process, each account is added to the GL accounts table, and then GL balance records (with zero dollars) are created for each fiscal period month in the current and following year.
The Chart of Accounts import will update existing accounts with details from the import file if a matching GL account number already exists.
Chart of Accounts Import File Data Scrubbing
Confirm that all GL accounts are formatted with the proper GL mask.
Make sure that the Active field is complete (either a Y or N) for all GL accounts.
Work with the customer to clean up and standardize as many descriptions as possible. This is an excellent opportunity to consider standardizing the description format for the chart of accounts to make reports look better, and to make account searches easier.
Make note of any GL account number changes made during the chart of accounts import. The same changes will need to be made for any other GL-related imports, such as balances, budgets, and possibly GL account values on other accounting-related imports like AR Invoices, AP Invoices, and so on.
Chart of Accounts Import Screen (IMPORTGL)
Menu Location: Transaction>>Importers/Exporters>>Accounting>>Chart of Accounts Import
File Name/Browse: Use the Browse button to locate and select the completed Chart of Accounts import template file (in XLS format)
Map Fields: Fields must be mapped prior to importing. If no changes have been made to the column headings, the mapping screen should show all green, and you can click OK to continue. If any of the Input Field Name columns on the left are red, single-click on the line on the left column, and then double-click the desired mapped field in the right column to complete the mapping. Repeat for any red lines on the left that should be mapped. If there are additional columns in the source file that should NOT be mapped, they can be left unmapped (displayed in red). Mandatory fields will require that they be mapped before clicking OK.
Chart of Accounts Import File Definitions
Required fields are indicated with *
*Active - Enter Y to mark the GL account active. Enter N to import an inactive account. If this field is left blank, the GL account will not import.
*GLAcct - GL account number. GL Account must match the GL Mask formatting established in CID Maintenance, or it will cause a validation error during import.
GLDesc - GL description. Maximum 110 characters.
GLSDesc - GL short description. Maximum 25 characters. The short description is available for use on custom reports.
GLType - GL account type code, as defined in the GL Types and Ranges screen.
PostJE - Enter Y to set the GL account Allow Manual Posting field. Enter N to disallow use of this account in Manual Journal Entry.
Chart of Accounts Import Reconciliation
Reconciling imported data should begin with spot-checking several records field-by-field for complete data import. Pick records from the source file that have the most data columns filled in. Verify that all source file data fields imported correctly and display as expected.
Perform a SQL query on the GLACCT table and confirm that the number of records matches the expected number of imported accounts.
Verify that the GL Account Maintenance Balances tab shows all of the current and next year fiscal periods in the grid.
Run the Chart of Accounts Report and verify that imported accounts show up correctly.
Chart of Account Import Additional Steps
Set up and review the GL Types and Ranges screen settings to make sure the ranges include every imported GL account. Use the Update GL button after completing the import to update the GL Account Type on all imported accounts.
Chart of Accounts Import Database Tables
Importing GL account records can affect the following databases:
GLACCT - Each imported GL account is assigned a unique KEYNO value tied to the GLACCT value, which should also be unique.
GLBALS - Each imported GL account also creates 24 entries in the GLBALS table - one for each fiscal period in the current year, and the following year. Each fiscal period month is assigned a unique KEYNO and links to the GLACCT table using the GLACNT field value.