Transaction Cat / GL Group Import Guide - Revision history

Freddy at 17:12, 29 May 2019

2019-05-29T17:12:15Z

Freddy at 16:48, 29 May 2019

2019-05-29T16:48:22Z

Freddy at 16:44, 29 May 2019

2019-05-29T16:44:37Z

Freddy at 16:40, 29 May 2019

2019-05-29T16:40:25Z

Freddy at 16:38, 29 May 2019

2019-05-29T16:38:48Z

Freddy: Created page with " ==General Data Import Notes== Refer to the Data Import General Information page for information and guidance on general import timeline, formatting import templates, as..."

2019-05-29T16:29:22Z

Created page with " ==General Data Import Notes== Refer to the Data Import General Information page for information and guidance on general import timeline, formatting import templates, as..."

New page

==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.

==Trans Cat / GL Group Import Notes==

The Trans Cat / GL Group Import does not have an Export Template option. You can find a copy of the Transcat Import Template in the ABIS FTP site in \Implementation Files\Import Templates.

The Trans Cat / GL Group 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 Trans Cat / GL Group Import can be used any time after the GL Chart of Accounts has been established. Transaction Categories and GL Groups can be associated with various records in Adjutant, such as customers, warehouses, and certain rules.

===General Notes===

The Trans Cat / GL Group Import can be used to add new Transaction Categories and/or GL Groups. It can also be used to update the GL Accounts associated with existing Transaction Categories and/or GL Groups if an exact match is found.

==Trans Cat / GL Group Import File Data Scrubbing==

Confirm that all GL accounts are formatted with the proper GL mask.

==Chart of Accounts Import Screen (IMPORTGL)==

Menu Location: Transaction>>Importers/Exporters>>Accounting>>Chart of Accounts Import

[[File:IMPORTGL.png]]

'''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.

[[Category: General Ledger]]
[[Category: Import Guides]]

@@ Line 51: / Line 51: @@
 Required fields are indicated with *
-'''CustCat''' - Customer Transaction Code from the CUSTCAT rul
+'''CustCat''' - Customer Transaction Code from the CUSTCAT rule (3 characters max).  The combination of CustCat and ItemCat is used to add or update Transaction Category records.
-'''ItemCat''' -
+'''ItemCat''' - Item Transaction Code from the ITEMCAT rule (3 characters max). The combination of CustCat and ItemCat is used to add or update Transaction Category records.
-'''GLGroup''' -
+'''GLGroup''' - GL Group name (20 characters max)
-'''Descrip''' -
+'''Descrip''' - GL Group description (40 characters max)
-'''DL1''' -
+'''DL1''' - Direct Labor revenue GL account used for items with a Product Group of Labor.
-'''DM1''' -
+'''DM1''' - Direct Material revenue GL account used for items with a Product Group of Material.
-'''DS1''' -
+'''DS1''' - Direct Subcontract revenue GL account used for items with a Product Group of Subcontract.
-'''DE1''' -
+'''DE1''' - Direct Equipment revenue GL account used for items with a Product Group of Equipment.
-'''DL2''' -
+'''DL2''' - Direct Labor cost of goods sold (COGS) GL account used for items with a Product Group of Labor.
-'''DM2''' -
+'''DM2''' - Direct Material cost of goods sold (COGS) GL account used for items with a Product Group of Material.
-'''DS2''' -
+'''DS2''' - Direct Subcontract cost of goods sold (COGS) GL account used for items with a Product Group of Subcontract.
-'''DE2''' -
+'''DE2''' - Direct Equipment cost of goods sold (COGS) GL account used for items with a Product Group of Equipment.
-'''FD1''' -
+'''FD1''' - Frozen Digits associated with the revenue accounts. Frozen Digits are only valid for Transaction Category records.
-'''FD2''' -
+'''FD2''' - Frozen Digits associated with the cost of goods sold (COGS) accounts. Frozen Digits are only valid for Transaction Category records.
+==Trans Cat / GL Group 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.
+Carefully review any wildcard entries to ensure that they are properly aligned with the GL mask.
+==Trans Cat / GL Group 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.
+'''GLGROUP''' - Each imported Transaction Category and/or GL Groups is assigned a unique KEYNO value tied to the either the GL Group NAME value, or the combination of CUSTCAT and ITEMCAT that make up the Transaction Category.

@@ Line 51: / Line 51: @@
 Required fields are indicated with *
-'''CustCat''' -
+'''CustCat''' - Customer Transaction Code from the CUSTCAT rul
 '''ItemCat''' -

← Older revision		Revision as of 16:40, 29 May 2019
Line 38:		Line 38:
	Menu Location: Transaction>>Importers/Exporters>>Accounting>>Trans Cat/GL Group Import		Menu Location: Transaction>>Importers/Exporters>>Accounting>>Trans Cat/GL Group Import

−	[[File:~~IMPORTGLGRP~~.png]]	+	[[File:IMPORTGLGRP1.png]]

@@ Line 41: / Line 41: @@
-'''File Name/Browse:''' Use the Browse button to locate and select the completed Chart of Accounts import template file (in XLS format)
+'''File Name/Browse:''' Use the Browse button to locate and select the completed Trans Cat / GL Group 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.
@@ Line 47: / Line 47: @@
-==Chart of Accounts Import File Definitions==
+==Trans Cat / GL Group 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.
+'''CustCat''' -
-'''*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.
+'''ItemCat''' -
-'''GLDesc''' - GL description. Maximum 110 characters.
+'''GLGroup''' -
-'''GLSDesc''' - GL short description. Maximum 25 characters. The short description is available for use on custom reports.
+'''Descrip''' -

@@ Line 30: / Line 30: @@
 Confirm that all GL accounts are formatted with the proper GL mask.
-==Chart of Accounts Import Screen (IMPORTGL)==
+==Trans Cat / GL Group Import Screen (IMPORTGLGRP)==
-Menu Location: Transaction>>Importers/Exporters>>Accounting>>Chart of Accounts Import
+Menu Location: Transaction>>Importers/Exporters>>Accounting>>Trans Cat/GL Group Import
-[[File:IMPORTGL.png]]
+[[File:IMPORTGLGRP.png]]