Search Results county_code




Overview
Explore in AI Chat

Overview

The HR.PAY_US_CITY_GEOCODES table is a core reference data object within the Oracle E-Business Suite Human Resources and Payroll modules, specifically for the U.S. localization. Its primary role is to serve as the master repository for all valid, distinct combinations of state, county, and city geographic codes. This table functions as a critical lookup and validation entity within the tax and address geocoding infrastructure of Oracle EBS 12.1.1 and 12.2.2. By maintaining these unique combinations, it ensures data integrity and provides the foundational geographic hierarchy necessary for accurate payroll tax calculations, reporting, and employee address management.

Key Information Stored

The table is structurally simple but semantically critical, consisting of three mandatory VARCHAR2 columns that together form a unique composite primary key. The STATE_CODE and COUNTY_CODE columns are foreign keys referencing the PAY_US_COUNTIES table, establishing the hierarchical relationship from state to county. The CITY_CODE column holds the geocode representing a specific city within that state and county. As per the primary key constraint (PAY_US_CITY_GEOCODES_PK), each row represents a unique combination of these three codes, preventing duplicate geographic definitions. The table is stored in the APPS_TS_SEED tablespace, indicating its nature as seeded reference data provided by Oracle.

Common Use Cases and Queries

This table is central to queries that resolve or validate employee geographic data for tax purposes. A common use case is determining the valid city codes for a given state and county, often as part of setting up or verifying employee tax records or organizational locations. For a user searching based on "county_code", a typical query would be to list all distinct city geocodes within specific counties. The fundamental query pattern, as indicated in the ETRM, is a direct select from the three key columns:

  • SELECT STATE_CODE, COUNTY_CODE, CITY_CODE FROM HR.PAY_US_CITY_GEOCODES WHERE COUNTY_CODE = '&COUNTY_CODE' ORDER BY CITY_CODE;

This table is also extensively used in joins with related tables like PAY_US_ZIP_CODES to map postal codes to geographic tax jurisdictions or with PAY_US_EMP_CITY_TAX_RULES_F to validate and apply specific city tax rules.

Related Objects

As documented in the foreign key dependencies, HR.PAY_US_CITY_GEOCODES sits at the center of a network of related tax and geographic tables. It is a parent table referenced by several key objects:

  • PAY_US_COUNTIES: Provides the valid STATE_CODE and COUNTY_CODE combinations.
  • PAY_US_CITY_NAMES: Links the geocode to its textual city name.
  • PAY_US_CITY_SCHOOL_DSTS: Associates the geocode with school district information.
  • PAY_US_EMP_CITY_TAX_RULES_F: A critical table that stores employee-specific city tax rules, dependent on this geocode.
  • PAY_US_ZIP_CODES: Maps ZIP or postal code ranges to these state, county, and city geocode combinations.

This structure underscores the table's role as a fundamental geographic key used to link descriptive data, tax rules, and postal information across the U.S. payroll system.