Geography Area Codes for the US
Some balances require geography area codes to identify their jurisdiction contexts.
-
Area1 = State
-
Area2 = County
-
Area3 = City and tax district
-
Area4 = School district
-
Area5 = Resident Political Subdivision (PSD) code
-
Area6 = Work PSD code
These codes identify what jurisdiction the balance is related to.
For example, this table displays the geocode for Columbus, Ohio for the county of Franklin. Multiple postal codes and school districts exist for this combination.
Area |
Represents |
Geocode |
Value |
---|---|---|---|
Area1 |
State |
36 |
Ohio |
Area2 |
County |
49 |
Franklin |
Area3 |
City and tax district |
570 |
Columbus |
Area4 |
School district |
2104 |
Multiple values exist, see Geocodes for Area4 |
This data is held in these tables.
-
HZ_GEOGRAPHIES
-
HZ_GEOGRAPHY_IDENTIFIERS
Geocodes for Area1, Area2, and Area3
You need to determine the correct Geography codes for Area1, Area2 and Area3.
For further info, see Using BI Publisher Reports to Retrieve Data for the US with HCM Data Loader (2558276.1) on My Oracle Support.
Geocodes for Area4
Use the following SQL query.
select ageo.geography_name
,ageo.area4 school_district_code
,decode(ageo.area1, null, null, 1, 'AL', 2, 'AK', 3, 'AZ', 4, 'AR', 5, 'CA', 6, 'CO', 7, 'CT', 8, 'DE', 9, 'DC', 10, 'FL',
11, 'GA', 12, 'HI', 13, 'ID', 14, 'IL', 15, 'IN', 16, 'IA', 17, 'KS', 18, 'KY', 19, 'LA', 20, 'ME',
21, 'MD', 22, 'MA', 23, 'MI', 24, 'MN', 25, 'MS', 26, 'MO', 27, 'MT', 28, 'NE', 29, 'NV', 30, 'NH',
31, 'NJ', 32, 'NM', 33, 'NY', 34, 'NC', 35, 'ND', 36, 'OH', 37, 'OK', 38, 'OR', 39, 'PA', 40, 'RI',
41, 'SC', 42, 'SD', 43, 'TN', 44, 'TX', 45, 'UT', 46, 'VT', 47, 'VA', 48, 'WA', 49, 'WV', 50, 'WI',
51, 'WY', 60, 'American Samoa', 61, 'F.S. of Micronesia', 66, 'Guam', 67, 'Marshall Islands',
68, 'N. Mariana Islands', 69, 'Palau', 72, 'Puerto Rico', 78, 'Virgin Islands') state_abbrev
,decode(ageo.area2, null, null, (select geo_ident_name.identifier_value
from fusion.hz_geographies geo,
fusion.hz_geography_identifiers geo_ident,
fusion.hz_geography_identifiers geo_ident_name
where geo.geography_type = 'COUNTY'
and geo.geography_use = 'MASTER_REF'
and geo.country_code = 'US'
and geo.geography_id = geo_ident.geography_id
and geo_ident.geo_data_provider = 'VERTEX'
and geo_ident.geography_use = 'MASTER_REF'
and geo_ident.identifier_subtype = 'GEO_CODE'
and geo.geography_id = geo_ident_name.geography_id
and geo_ident_name.geography_use = 'MASTER_REF'
and geo_ident_name.identifier_type = 'NAME' and geo_ident_name.primary_flag = 'Y'
and geo_ident.identifier_value = ageo.area1 || '-' || ageo.area2 || '-0')) county_name
,decode(ageo.area3, null, null, (select hzg.geography_name city
From fusion.hz_geographies hzg,
fusion.hz_geography_identifiers hzgi,
fusion.hz_geography_identifiers_vl hzgivp
where hzg.country_code = 'US'
and hzg.geography_type = 'CITY'
and hzg.geography_use = 'MASTER_REF'
and hzg.geography_element1 = 'United States'
and hzg.geography_element1_code = 'US'
and hzg.geography_id = hzgi.geography_id
and hzgi.identifier_type = 'CODE'
and hzgi.identifier_subtype = 'GEO_CODE'
and hzgi.primary_flag = 'N'
and hzgi.geography_use = 'MASTER_REF'
and hzgi.geography_type = 'CITY'
and hzgi.language_code = 'US'
and hzgi.geography_id = hzgivp.geography_id
and hzgi.identifier_value = ageo.area1 || '-' || ageo.area2 || '-' || ageo.area3 fetch first row only)) city_name
,ageo.area1, ageo.area2, ageo.area3, ageo.area4
from fusion.pay_amer_geographies ageo where ageo.geography_type = 'SCHOOL_DISTRICT';