1. Localization Information Guide
  2. Geography Area Codes for the US

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

Related Topics