Wiki: US Load GNIS Locations

US LOAD GNIS LOCATIONS
Overview
UDGNIS - Load GNIS Codes
UDGNIS - Selection Criteria
UDGNIS Processing
UDGNIS - Processing
UDGNIS - List of Data Base Tables to be updated
UDGNIS Output File
UDGNIS - Sample .csv File
Notes

US LOAD GNIS LOCATIONS#

Overview#

This page describes the process to load the US Geographic Names Information System (GNIS) locations from the Symmetry Tax server which calls the Symmetry Tax Engine (STE).

UDGNIS - Load GNIS Codes#

UDGNIS - Selection Criteria#

US_Load_UDGNIS Locations_01.JPG

IMPORTANT: UDGNIS is a high volume process to load all Counties, Cities, School, Municipalities, ZIP codes into IDCN, IDCI, IDSD, IDMN, IDZR for each state from Symmetry database. Each state may take from 1 to 10 minutes. To load all states, the process may take 2 to 3 hours, therefore, you may schedule UDGNIS to be run at night if necessary.

Country	This mandatory field allows you to enter the Country code used to filter the list of states.
State	You may use this field to enter list of states to be processed to load GNIS codes. When setting up a new data base environment, if there is no State for lookup, UDGNIS will automatically load in all IDSP states when UDGNIS is run in non-Trial mode. If the list of states are not entered, then all states will be processed.
Load Symmetry GNIS	Optional, Yes or No lexicon available This is an optional field, with a default of <<No>>. If <<Yes>>, UDGNIS will load from the Symmetry database for the specified states. If you have already loaded from the Symmetry database, then you can re-run UDGNIS to load in IDTX Tax Jurisdictions from IDCN, IDCI without re-loading from Symmetry database, therefore you can enter <<No>> to this prompt.
Remove Jurisdictions	Optional, Yes or No lexicon available If Yes, UDGNIS will remove all tax jurisdictions for the specified states from IDTX that have not been used in the system.
Create Jurisdictions	Optional, Yes or No lexicon available If <<Yes>>,UDGNIS will generate tax jurisdictions for the specified states on IDTX according to IDCI cities. You have two options to create IDTX Tax Jurisdictions: 1) manually enter on IDTX 2) enter <<Yes>> to this prompt to load in all Tax Jurisdictions for IDCI Cities
Trial	Optional, Yes or No lexicon available You may choose to run the report in a trial mode to see results <Yes> or in update mode <No>. In Non-Trial mode, IDSP, IDCN, IDCI, IDSD, IDMN, IDZR, IDTX are updated.
Create File	Optional, Yes or No lexicon available This field is used to generate a .csv file with the applicable geographic information.
Print Detail	Optional, Yes or No lexicon available This field is used to show detail dump of the geographic information If <<Yes>>, the report size may be quite large, therefore, you should select <<No>>, unless for Tracing.
Vertex Conversion	Optional, Yes or No lexicon available This toggle is used to convert IDTX entries from Vertex format to Symmetry format. The old Vertex IDTX GEO entries that are not being used will be removed. The old Vertex IDTX GEO entries that are being used will be converted to Symmetry GNIS format.
Directory Name	If the Create File field is set to <<Y>>, you must enter a valid Directory Name for the .csv file.
File Name	If the Create File field is set to <<Y>>, you must enter a file name for the .csv file.
Exception Level	Optional, Exception Level lexicon available This field defines the exception level (report messages) required. You should run with Exception Level except for tracing In 'Utility Trace', the report may be quite large.
User Comments	Optional, Text This field holds the user-supplied comment which will appear in the header of all pages of the report. This is useful during testing phases to identify similar reports from each other.

UDGNIS Processing#

UDGNIS - Processing#

UDGNIS loads in the "US Geographic Names Information System (GNIS)" Location codes from the Symmetry Tax Engine (STE)
GNIS code is the Symmetry version of the Vertex GEO code.
When setting up a new data base environment, if there is no States list for lookup, you do not need to enter the States selection; UDGNIS will automatically load in IDCO and IDSP entries for USA when it is first run.
UDGNIS is a high volume process and may take from one minute to ten minutes to process each state, loading in all Counties, Cities, Schools, Municipalities, ZIP codes and Jurisdictions. Please do not cancel the report while it is running. To see the progress of the run, you may check the size of the Output .csv file from the Output directory.
If the 'Create File' toggle is set to <<Yes>>, each state will generate one .csv file to contain the geographic information for the state.
The file name will be suffixed with the State code; you may attach the mex id of the run to the file name.
e.g. File Name: cathy_gnis_mex 'mex' will be replaced with the execution ID, then suffix with the State code.

US_Load_UDGNIS Locations_02.JPG

If the 'Print Detail' toggle is set to <<Yes>>, UDGNIS will list the detail entries that are processed for each level: Country, States, Counties, Cities, Schools, Municipalities, Tax Jurisdictions.
If you have converted from Vertex Taxation to Symmetry Taxation for the Payroll Module, you MUST run UDGNIS with the 'Vertex Conversion' toggle set to <<Yes>> for the first time. Please read Vertex Conversion below.
UDGNIS should be run periodically to load in the latest GNIS locations into the system:
1. Annually as of January of each year
  - UDGNIS should be scheduled as part of the Legislation cycle at the beginning of the year
2. When Symmetry Tax Engine is upgraded
  - Each time when Symmetry Tax Engine is installed, which may be due to software upgrade or tax rate changes, you should run UDGNIS to pick up any new GNIS location codes.
3. At a user's request
  - If you start to pay into a new state that has not been loaded, you must run UDGNIS.
  - You may run UDGNIS at any time to load in the latest GNIS location codes from Symmetry.
If you have not converted from Vertex, UDGNIS will load in all necessary Geographic Names Information into the system.
After UDGNIS is run, you should review the .csv file for all the Geographic entries that have been loaded.
UDGNIS will not create Tax Jurisdictions with School Districts or Municipalities, therefore the user will have to manually associate the School District and Municipalities to the appropriate Tax Jurisdictions.
In order for School District Tax and Municipality Tax to be calculated these must be associated to a Tax Jurisdiction in IDTX. If you have employees residing in Ohio or Pennsylvania please review their Tax Jurisdictions in IDTX and associate the appropriate School and Municipality.

UDGNIS - List of Data Base Tables to be updated#

If UDGNIS is run in Non-Trial mode, the following list of screens and database tables will be updated:

IDCO

Define Countries (P2K_CM_COUNTRIES)
'ISO Alpha 2' and 'ISO Alpha 3' will be updated if these are not set up

IDSP

Define States and Provinces (P2K_CM_STATE_PROVINCES)
FIPS code will be updated if this is not set up

IDCN

Define Counties (P2K_CM_COUNTIES)
New counties will be loaded
Existing County Code and County Name will be compared to Symmetry County Code and County Name
Symmetry County Code Format is AA-BBB
- where AA - State FIPS code
  BBB - County Code
For Vertex conversion, Alternate County Code will be updated with the Vertex County Code for reference

IDCI

Define Cities (P2K_CM_CITIES)
New cities will be loaded
Existing City Code and City Name will be compared to Symmetry City Code and City Name
City Code format is: CCCCCCC - 7 digits City code
For each county from IDCN, UDGNIS retrieves all City Codes for that county
This helps you enter the correct combination of City with the related County for tax calculation

IDSD

Define School Districts (P2K_CM_SCHOOL_DISTRICTS)
New school districts will be loaded
Existing School District Code and Name will be compared to Symmetry School Code and Name
School District Code Format is: SSSSSS - 6 digits School code
For each County from IDCN, UDGNIS retrieves all School District Codes for that county
This helps you enter the correct combination of School with the related County for tax calculation

IDMN

Define Municipalities (P2K_CM_MUNICIPALITIES)
New Municipalities will be loaded
Existing Municipality Code and Name will be compared to Symmetry Municipality Code and Name
Municipality Code Format is:MMMMMMM - 7 digits Municipality code
For each County from IDCN, UDGNIS retrieves all Municipality Codes for that county
This helps you enter the correct combination of Municipality with the related County

IDZR

Maintain ZIP Ranges (P2K_CM_ZIP_RANGES)
New ZIP codes will be loaded
Existing ZIP Code and Description will be compared to Symmetry ZIP Code and City Name
For each County from IDCN, UDGNIS retrieves all ZIP Codes for that county
The applicable City code within each County are provided for each ZIP code
This helps you enter the correct combination of ZIP code with the related County for Tax calculation

IDTX

Define Tax Jurisdictions (P2K_CM_TAX_JURISDICTIONS)
IDTX screen is used to store the combination of State, County, City, School, Municipality for taxation
IDTX may or may not store all permutations of Tax Jurisdictions, depending on the value selected for the Create Jurisdictions parameter
If the UDGNIS was run with the Create Jurisdictions toggle set to <<Yes>>, the system will load cities into IDTX, creating tax jurisdictions for all cities within the states selected in the UDGNIS. You then have to manually enter Municipality and School District for Pennsylvania and Ohio.
If UDGNIS was run with the Create Jurisdictions toggle set to <<No>>, you will need to enter the IDTX entry using the 'Tax Jurisdiction Editor Dialog Box' provided by the system on the applicable screen.

UDGNIS Output File#

UDGNIS - Sample .csv File#

If the 'Create File' toggle is set to <<Yes>>, each state will generate one .csv file to contain the Geographic Information for the state.
The following is a sample.csv file for Pennsylvania:

STATE LEVEL

At the State level, the FIPS code is retrieved, e.g PA FIPS code is 42
At that point, all counties will be retrieved for this State, e.g. PA has 67 Counties

US_Load_UDGNIS Locations_03.JPG

COUNTY LEVEL

At the County level, all Counties are retrieved and the County codes and names are displayed but not sorted.
After all Counties are read, the County code is built in as format XX-XXX, which will then be sorted and updated to IDCN

US_Load_UDGNIS Locations_04.JPG