This page (revision-37) was last changed on 26-Nov-2021 10:22 by rforbes

This page was created on 26-Nov-2021 10:22 by jmyers

Only authorized users are allowed to rename pages.

Only authorized users are allowed to delete pages.

Page revision history

Version Date Modified Size Author Changes ... Change note
37 26-Nov-2021 10:22 10 KB rforbes to previous
36 26-Nov-2021 10:22 10 KB rforbes to previous | to last
35 26-Nov-2021 10:22 11 KB rforbes to previous | to last
34 26-Nov-2021 10:22 11 KB rforbes to previous | to last
33 26-Nov-2021 10:22 12 KB rforbes to previous | to last
32 26-Nov-2021 10:22 16 KB jescott to previous | to last
31 26-Nov-2021 10:22 15 KB jmyers to previous | to last
30 26-Nov-2021 10:22 15 KB jmyers to previous | to last
29 26-Nov-2021 10:22 15 KB jmyers to previous | to last
28 26-Nov-2021 10:22 15 KB jmyers to previous | to last
27 26-Nov-2021 10:22 15 KB jmyers to previous | to last
26 26-Nov-2021 10:22 15 KB jmyers to previous | to last
25 26-Nov-2021 10:22 15 KB jmyers to previous | to last
24 26-Nov-2021 10:22 15 KB jmyers to previous | to last
23 26-Nov-2021 10:22 15 KB jmyers to previous | to last
22 26-Nov-2021 10:22 15 KB jmyers to previous | to last
21 26-Nov-2021 10:22 15 KB jmyers to previous | to last

Page References

Incoming links Outgoing links
US Load GNIS Locations
US Load GNIS Locations

Version management

Difference between version and

At line 3 changed one line
!!!US LOAD GNIS LOCATIONS
!
At line 5 changed one line
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).
Intent
At line 7 changed one line
!!UDGNIS - Load GNIS Codes
This document 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).
At line 9 removed 3 lines
!UDGNIS - Selection Criteria
[US_Load_UDGNIS Locations_01.JPG]
%%warning 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.%%
At line 13 changed 13 lines
|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|X_YES_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|X_YES_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|X_YES_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|X_YES_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|X_YES_NO] lexicon available\\This field is used to generate a .csv file with the applicable geographic information.
|Print Detail|Optional, [Yes or No|X_YES_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|Obsolete - set to No
|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|X_TRACE_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.
Audience
At line 27 changed one line
!!UDGNIS Processing
All users who are responsible to set up Geographic Information for State, County, City, Municipalities, School Districts and users who are responsible to do the Payroll Tax Calculation in the Pay cycle process.
At line 29 changed 18 lines
*UDGNIS loads in the "US Geographic Names Information System (GNIS)" Location codes from the Symmetry Tax Engine (STE)
*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.
*UDGNIS should be run periodically to load in the latest GNIS locations into the system:
*#Annually as of January of each year
*#*UDGNIS should be scheduled as part of the Legislation cycle at the beginning of the year
*#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.
*#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.
*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.
Prerequisites
At line 16 added one line
All users must have an understanding of running the ePersonality application.
At line 49 removed one line
!UDGNIS - List of Data Base Tables to be updated
At line 51 removed one line
If UDGNIS is run in Non-Trial mode, the following list of screens and database tables will be updated:
At line 53 removed 3 lines
;IDCO:
*Define Countries (P2K_CM_COUNTRIES)
*'ISO Alpha 2' and 'ISO Alpha 3' will be updated if these are not set up
At line 57 changed 3 lines
;IDSP:
*Define States and Provinces (P2K_CM_STATE_PROVINCES)
*FIPS code will be updated if this is not set up
2 UDGNIS – Load GNIS Codes
At line 61 changed 7 lines
;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
2.1 UDGNIS – Selection Criteria
At line 69 removed 7 lines
;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
At line 77 changed 7 lines
;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
At line 85 removed 7 lines
;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
At line 93 changed 7 lines
;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
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 minute to 10 minutes. To load all States, it may take 2 to 3 hours. Users may schedule UDGNIS to be run at night if necessary.
At line 101 changed 6 lines
;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.
Country - Mandatory, user must enter the Country Code to filter the list of States
State: - Optional, user may 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 list of States are not entered, then all States will be processed
Load Symmetry GNIS - Optional, default is No
- if Yes, UDGNIS will load from the Symmetry database for the specified States
- if user has already loaded from the Symmetry database, then user can re-run UDGNIS to load in IDTX Tax Jurisdictions from IDCN, IDCI without re-loading from Symmetry database, therefore user can enter ‘No’ to this prompt
Remove Jurisdictions - Optional, default is No
- 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, default is No
- if Yes, UDGNIS will generate Tax Jurisdictions for the specified States on IDTX according to IDCI Cities
- users have 2 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 toggle to allow user to run in Trial or Update mode
- this allows user to review the Run Log report prior to Update
- in Non-Trial mode, IDSP, IDCN, IDCI, IDSD, IDMN, IDZR, IDTX are updated
Create File - Optional toggle to generate a .csv file with the applicable geographic information
Print Detail - Optional toggle to show detail dump of the geographic information
- if ‘Print Detail’ is Yes, the report size may be quite large, user should run with ‘Print Detail’ = No unless for Tracing
Vertex Conversion - Optional toggle 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 Create File = ‘Y’, user must enter a valid Directory Name for the .csv file
File Name - if Create File = ‘Y’, user must enter a File Name for the .csv file
Exception Level - user should run with Exception Level except for Tracing
- in ‘Utility Trace’, the report may be quite large
User Comment - enter User Comments to be printed on the Parameter page
3 UDGNIS Processing
3.1 UDGNIS – Processing
- UDGNIS loads in the ‘US Geographic Names Information System (GNIS)’ Location codes from Symmetry Tax Engine
- 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, the user does not need to enter the States selection, when UDGNIS is first run, it will automatically load in IDCO and IDSP entries for USA
- UDGNIS is a high volume process, this may take from one minute to 10 minutes to process each State to load 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, the user may check the Size of the Output .csv file from the Output Directory to watch the progress of the run
- if ‘Create File’ = 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, user 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
- if ‘Print Detail’ = Yes, UDGNIS will list the detail entries that are processed for each level: Country, States, Counties, Cities, Schools, Municipalities, Tax Jurisdictions
- if user is converted from Vertex Taxation to Symmetry Taxation for the Payroll Module, user MUST run UDGNIS with ‘Vertex Conversion’ = ‘Y’ 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, this may be due to Software upgrade or Tax Rate changes, user should run UDGNIS to pick up any new GNIS location codes
(3) At User Request
- if user starts to pay in s new State that has not been loaded, please run UDGNIS
- user may run UDGNIS at any time to load in the latest GNIS location codes from Symmetry
- for user who is not converted from Vertex, UDGNIS will load in all necessary Geographic Names Information into the system
- after UDGNIS is run, user should review the .csv file for all the Geographic entries that have been loaded
3.2 UDGNIS – List of Data Base Tables to be updated
- If UDGNIS is run in Non-Trial mode, the following list of Screens and Data Base 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 that are for each County
- this helps the user to 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 that are for each County
- this helps the user to 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 that are for each County
- this helps the user to 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 that are for each County
- the applicable City code within each County are provided for each ZIP code
- this helps the user to 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 parameter Create Jurisdictions = Yes, the system will load in cities into IDTX creating Tax Jurisdictions for all cities for the states selected in the UDGNIS. The user would then have to manually enter Municipality and School District for Pennsylvania and Ohio.
-If UDGNIS was run with the parameter Create Jurisdictions = No, the user will need to enter the IDTX entry using the ‘Tax Jurisdiction Editor Dialog Box’ provided by the system on the applicable screen
At line 108 removed one line
!!UDGNIS Output File
At line 110 changed 3 lines
!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:
4 Vertex Conversion
At line 114 changed 4 lines
;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]
4.1 Vertex Conversion Processing
- if user is converting from Vertex Taxation to Symmetry Taxation in the Payroll Module, user MUST run UDGNIS with ‘Vertex Conversion’ = ‘Y’ for the first time
- after UDGNIS is run with ‘Vertex Conversion’ = ‘Y’, all related Vertex Geographic Data Base Tables are converted, after the conversion, user MUST review UDGNIS exception report and manually fix any IDTX entries that cannot be converted
- IDCN County Code format is changed from Vertex Format ‘XX-XXX-0000’ to Symmetry Format ‘XX-XXX’, the Alternate County Code will store the previous Vertex County Code for reference
- Vertex does not use IDCI City Code, no conversion is needed for City Codes
- Vertex does not use IDMN Municipality Code, no conversion is needed for Municipality Codes
- IDSD School Districts code are checked, if the Vertex School District has not been used in the System, this Vertex School District Code will be removed
- the Vertex School District Name is used to look for a close match from Symmetry and update the existing School District entry with the Symmetry School District Code and Name, along with the applicable County Code of the School District
- for Vertex IDTX Tax Jurisdiction entry that has not been used in the System, if the parameter Remove Jurisdiction was set to Yes, this IDTX entry will be removed. If the parameter was set to No, the tax jurisdiction will remain.
- for Vertex IDTX Tax Jurisdiction entry that has been used in the System, this IDTX entry will try to be converted to Symmetry Format
- the IDTX Jurisdiction name is used to look for a close match from Symmetry
- IDTX Vertex GEO Code format is changed from ‘XX-XXX-XXXX’ to Symmetry Format ‘AA-BBB-CCCCCCC’, the old Vertex GEO Code will be stored in the Vtx GEO Code for reference
- if Symmetry has an exact match of the Jurisdiction Name, then this IDTX entry will be updated with the new GNIS Code in format of ‘AA-BBB-CCCCCCC’, along with the County dcn_id and City dci_id
- if Symmetry has no exact match for the Jurisdiction Name, then an exception message will be displayed, a Suggested County and City Name may be displayed, user MUST manually convert this IDTX entry after UDGNIS is run
- after Vertex GEO entries are cleaned up from IDTX, the user MUST review the UDGNIS Exception Report and manually fix the IDTX entries that cannot be converted
- symmetry may not be able to convert a Vertex Jurisdiction if the name is not a valid town name as considered by the US Postal Service. Symmetry uses Zip Codes within their GNIS filter mechanism to determine the appropriate County, City, Feature id combination, if the town name is not valid then it will not be able to generate the correct combination.
4.2 Manual Conversion - IDTX
There may be cases where the UDGNIS could not convert a tax jurisdiction. This could be a result of a spelling error or the jurisdiction is not classified as a real city by the US Postal Service and therefore the UDGNIS could not find the correct GNIS code to use.
To manually change a Vertex GEO Code to the Symmetry GNIS format follow the steps below:
1) In IDTX, copy the Vertex GEO Code into the Vtx Geo Code field. This is needed so that when UPCALC is run using Vertex, UPCALC knows to use the Vertex GEO Code rather than the GNIS code.
2) Using the fields on the right hand side, select the correct County and City. This will create the appropriate GNIS Geo Code. The name defined in the Jurisdiction field will be replaced with the City name.
5 UDGNIS Output File
5.1 UDGNIS – Sample .csv File
- if ‘Create File’ = 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 State Level, the FIPS code is retrieved, e.g PA FIPS code is 42
- then all counties will be retrieved for this State, e.g. PA has 67 Counties
At line 119 removed 4 lines
;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]
At line 124 changed 3 lines
;CITY LEVEL:
*UDGNIS will loop through each County to retrieve all Cities by County, which will then be sorted and updated to IDCI
[US_Load_UDGNIS Locations_05.JPG]
COUNTY LEVEL
- at County Level, all Counties are retrieved, the County Codes and Names are displayed, not sorted
- after all Counties are read, the County Code is built in Format XX-XXX, then will be sorted and updated to IDCN
At line 128 removed 3 lines
;SCHOOL LEVEL:
*UDGNIS will loop through each County to retrieve all Schools by County, which will then be sorted and updated to IDSD
[US_Load_UDGNIS Locations_06.JPG]
At line 132 removed 3 lines
;MUNICIPALITY LEVEL:
*UDGNIS will loop through each County to retrieve all Municipalities by County, which will then be sorted and updated to IDSD
[US_Load_UDGNIS Locations_07.JPG]
At line 136 changed 3 lines
;ZIP LEVEL:
*UDGNIS will loop through each County to retrieve all ZIPs by County and City, which will then be sorted and updated to [IDZR]
[US_Load_UDGNIS Locations_08.JPG]
CITY LEVEL
- UDGNIS will loop through each County to retrieve all Cities by County, then sorted and updated to IDCI
At line 141 changed 2 lines
![Notes|Edit:Internal.US Taxation Processing - US Load GNIS Locations]
[{InsertPage page='Internal.US Taxation Processing - US Load GNIS Locations' default='Click to create a new notes page'}]
SCHOOL LEVEL
- UDGNIS will loop through each County to retrieve all Schools by County, then sorted and updated to IDSD
MUNICIPALITY LEVEL
- UDGNIS will loop through each County to retrieve all Municipalities by County, then sorted and updated to IDSD
ZIP LEVEL
- UDGNIS will loop through each County to retrieve all ZIPs by County and City, then sorted and updated to IDZR
5.2 UDGNIS – Vertex Conversion Sample File
- During Vertex Conversion, if the IDTX Vertex GEO Code entries cannot find an exact match from Symmetry, a WARNING Message will be displayed
- user MUST manually go into IDTX screen to modify the Vertex GEO Code with Description to the Symmetry Format
- e.g the following is an example of UDGNIS Message Displays after Vertex Conversion:
- user should review the UDGNIS Messages and the Output File for the Vertex IDTX GEO Codes displayed
- if there is an exact match with the Jurisdiction Description, IDTX will be converted
- if there is no match, a partial search will be performed to help the user to manually enter on IDTX screen
- if there is no partial match, the user must manually convert the Vertex GEO Code on IDTX screen
- UDGNIS will remove the unused old Vertex IDTX entries, after UDGNIS is run, user MUST manually convert the left over IDTX Vertex entries because these entries are being used in the system by Payroll
%%warning 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 minute to 10 minutes. To load all States, it may take 2 to 3 hours. Users may schedule UDGNIS to be run at night if necessary.%%
;Country:
*Mandatory, user must enter the Country Code to filter the list of States
;State:
*Optional, user may 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 list of States are not entered, then all States will be processed
;Load Symmetry GNIS:
*Optional, default is No
*if Yes, UDGNIS will load from the Symmetry database for the specified States
*if user has already loaded from the Symmetry database, then user can re-run UDGNIS to load in IDTX Tax Jurisdictions from IDCN, IDCI without re-loading from Symmetry database, therefore user can enter ‘No’ to this prompt
;Remove Jurisdictions:
*Optional, default is No
*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, default is No
*if Yes, UDGNIS will generate Tax Jurisdictions for the specified States on IDTX according to IDCI Cities
*users have 2 options to create IDTX Tax Jurisdictions
*#manually enter on IDTX
*#enter Yes to this prompt to load in all Tax Jurisdictions for IDCI Cities
Trial - Optional toggle to allow user to run in Trial or Update mode
- this allows user to review the Run Log report prior to Update
- in Non-Trial mode, IDSP, IDCN, IDCI, IDSD, IDMN, IDZR, IDTX are updated
Create File - Optional toggle to generate a .csv file with the applicable geographic information
Print Detail - Optional toggle to show detail dump of the geographic information
- if ‘Print Detail’ is Yes, the report size may be quite large, user should run with ‘Print Detail’ = No unless for Tracing
Vertex Conversion - Optional toggle 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 Create File = ‘Y’, user must enter a valid Directory Name for the .csv file
File Name - if Create File = ‘Y’, user must enter a File Name for the .csv file
Exception Level - user should run with Exception Level except for Tracing
- in ‘Utility Trace’, the report may be quite large
User Comment - enter User Comments to be printed on the Parameter page
3 UDGNIS Processing
3.1 UDGNIS – Processing
- UDGNIS loads in the ‘US Geographic Names Information System (GNIS)’ Location codes from Symmetry Tax Engine
- 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, the user does not need to enter the States selection, when UDGNIS is first run, it will automatically load in IDCO and IDSP entries for USA
- UDGNIS is a high volume process, this may take from one minute to 10 minutes to process each State to load 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, the user may check the Size of the Output .csv file from the Output Directory to watch the progress of the run
- if ‘Create File’ = 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, user 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
- if ‘Print Detail’ = Yes, UDGNIS will list the detail entries that are processed for each level: Country, States, Counties, Cities, Schools, Municipalities, Tax Jurisdictions
- if user is converted from Vertex Taxation to Symmetry Taxation for the Payroll Module, user MUST run UDGNIS with ‘Vertex Conversion’ = ‘Y’ 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, this may be due to Software upgrade or Tax Rate changes, user should run UDGNIS to pick up any new GNIS location codes
(3) At User Request
- if user starts to pay in s new State that has not been loaded, please run UDGNIS
- user may run UDGNIS at any time to load in the latest GNIS location codes from Symmetry
- for user who is not converted from Vertex, UDGNIS will load in all necessary Geographic Names Information into the system
- after UDGNIS is run, user should review the .csv file for all the Geographic entries that have been loaded
3.2 UDGNIS – List of Data Base Tables to be updated
- If UDGNIS is run in Non-Trial mode, the following list of Screens and Data Base 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 that are for each County
- this helps the user to 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 that are for each County
- this helps the user to 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 that are for each County
- this helps the user to 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 that are for each County
- the applicable City code within each County are provided for each ZIP code
- this helps the user to 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 parameter Create Jurisdictions = Yes, the system will load in cities into IDTX creating Tax Jurisdictions for all cities for the states selected in the UDGNIS. The user would then have to manually enter Municipality and School District for Pennsylvania and Ohio.
-If UDGNIS was run with the parameter Create Jurisdictions = No, the user will need to enter the IDTX entry using the ‘Tax Jurisdiction Editor Dialog Box’ provided by the system on the applicable screen
4 Vertex Conversion
4.1 Vertex Conversion Processing
- if user is converting from Vertex Taxation to Symmetry Taxation in the Payroll Module, user MUST run UDGNIS with ‘Vertex Conversion’ = ‘Y’ for the first time
- after UDGNIS is run with ‘Vertex Conversion’ = ‘Y’, all related Vertex Geographic Data Base Tables are converted, after the conversion, user MUST review UDGNIS exception report and manually fix any IDTX entries that cannot be converted
- IDCN County Code format is changed from Vertex Format ‘XX-XXX-0000’ to Symmetry Format ‘XX-XXX’, the Alternate County Code will store the previous Vertex County Code for reference
- Vertex does not use IDCI City Code, no conversion is needed for City Codes
- Vertex does not use IDMN Municipality Code, no conversion is needed for Municipality Codes
- IDSD School Districts code are checked, if the Vertex School District has not been used in the System, this Vertex School District Code will be removed
- the Vertex School District Name is used to look for a close match from Symmetry and update the existing School District entry with the Symmetry School District Code and Name, along with the applicable County Code of the School District
- for Vertex IDTX Tax Jurisdiction entry that has not been used in the System, if the parameter Remove Jurisdiction was set to Yes, this IDTX entry will be removed. If the parameter was set to No, the tax jurisdiction will remain.
- for Vertex IDTX Tax Jurisdiction entry that has been used in the System, this IDTX entry will try to be converted to Symmetry Format
- the IDTX Jurisdiction name is used to look for a close match from Symmetry
- IDTX Vertex GEO Code format is changed from ‘XX-XXX-XXXX’ to Symmetry Format ‘AA-BBB-CCCCCCC’, the old Vertex GEO Code will be stored in the Vtx GEO Code for reference
- if Symmetry has an exact match of the Jurisdiction Name, then this IDTX entry will be updated with the new GNIS Code in format of ‘AA-BBB-CCCCCCC’, along with the County dcn_id and City dci_id
- if Symmetry has no exact match for the Jurisdiction Name, then an exception message will be displayed, a Suggested County and City Name may be displayed, user MUST manually convert this IDTX entry after UDGNIS is run
- after Vertex GEO entries are cleaned up from IDTX, the user MUST review the UDGNIS Exception Report and manually fix the IDTX entries that cannot be converted
- symmetry may not be able to convert a Vertex Jurisdiction if the name is not a valid town name as considered by the US Postal Service. Symmetry uses Zip Codes within their GNIS filter mechanism to determine the appropriate County, City, Feature id combination, if the town name is not valid then it will not be able to generate the correct combination.
4.2 Manual Conversion - IDTX
There may be cases where the UDGNIS could not convert a tax jurisdiction. This could be a result of a spelling error or the jurisdiction is not classified as a real city by the US Postal Service and therefore the UDGNIS could not find the correct GNIS code to use.
To manually change a Vertex GEO Code to the Symmetry GNIS format follow the steps below:
1) In IDTX, copy the Vertex GEO Code into the Vtx Geo Code field. This is needed so that when UPCALC is run using Vertex, UPCALC knows to use the Vertex GEO Code rather than the GNIS code.
2) Using the fields on the right hand side, select the correct County and City. This will create the appropriate GNIS Geo Code. The name defined in the Jurisdiction field will be replaced with the City name.
5 UDGNIS Output File
5.1 UDGNIS – Sample .csv File
- if ‘Create File’ = 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 State Level, the FIPS code is retrieved, e.g PA FIPS code is 42
- then all counties will be retrieved for this State, e.g. PA has 67 Counties
COUNTY LEVEL
- at County Level, all Counties are retrieved, the County Codes and Names are displayed, not sorted
- after all Counties are read, the County Code is built in Format XX-XXX, then will be sorted and updated to IDCN
CITY LEVEL
- UDGNIS will loop through each County to retrieve all Cities by County, then sorted and updated to IDCI
SCHOOL LEVEL
- UDGNIS will loop through each County to retrieve all Schools by County, then sorted and updated to IDSD
MUNICIPALITY LEVEL
- UDGNIS will loop through each County to retrieve all Municipalities by County, then sorted and updated to IDSD
ZIP LEVEL
- UDGNIS will loop through each County to retrieve all ZIPs by County and City, then sorted and updated to IDZR
5.2 UDGNIS – Vertex Conversion Sample File
- During Vertex Conversion, if the IDTX Vertex GEO Code entries cannot find an exact match from Symmetry, a WARNING Message will be displayed
- user MUST manually go into IDTX screen to modify the Vertex GEO Code with Description to the Symmetry Format
- e.g the following is an example of UDGNIS Message Displays after Vertex Conversion:
- user should review the UDGNIS Messages and the Output File for the Vertex IDTX GEO Codes displayed
- if there is an exact match with the Jurisdiction Description, IDTX will be converted
- if there is no match, a partial search will be performed to help the user to manually enter on IDTX screen
- if there is no partial match, the user must manually convert the Vertex GEO Code on IDTX screen
- UDGNIS will remove the unused old Vertex IDTX entries, after UDGNIS is run, user MUST manually convert the left over IDTX Vertex entries because these entries are being used in the system by Payroll
----
![Notes|Edit:Internal.US Taxation Processing - US Load GNIS Locations]
[{InsertPage page='Internal.US Taxation Processing - US Load GNIS Locations' default='Click to create a new notes page'}]
This page (revision-37) was last changed on 26-Nov-2021 10:22 by rforbesTop