How do I identify jurisdiction contexts using 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 =

  • Pennsylvania Resident Political Subdivision (PSD) code

  • Portland, Oregon Metropolitan Area

• Area6 = Work Pennsylvania subdivision (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 distinct	570	Columbus
Area4	School district	2104	Multiple, see below

These tables hold this data.

• HZ_GEOGRAPHIES

• HZ_GEOGRAPHY_IDENTIFIERS

• PAY_GEOGRAPHIES

Geocodes for Area1, Area2, and Area3

You must 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 this SQL query.

SELECT ageo.display_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 IN ('ORA_HCM', '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_geographies ageo WHERE ageo.geography_type = 'SCHOOL_DISTRICT';