Search Results pay_us_city_geocodes




Overview
Explore in AI Chat

Overview

The PAY_US_CITY_GEOCODES table is a core reference table within the Oracle E-Business Suite (EBS) Payroll module, specifically for the United States localization. Its primary role is to serve as a master repository for distinct geographic code combinations that define a city's jurisdiction. It acts as a normalized intersection table, uniquely identifying every valid combination of state, county, and city codes used for tax reporting and withholding calculations. This table is fundamental for ensuring data integrity in the complex hierarchy of US tax jurisdictions, enabling accurate payroll processing at the city level across various states and counties.

Key Information Stored

The table's structure is concise, consisting of three key columns that together form its primary key, PAY_US_CITY_GEOCODES_PK. The STATE_CODE column holds the standard abbreviation for a US state or territory. The COUNTY_CODE contains the internal identifier for a county within that state, as defined in the PAY_US_COUNTIES table. The CITY_CODE stores the identifier for a specific city or locality within that county. The table does not store descriptive names; instead, it provides the unique code combination that links to other reference tables for names, tax rules, and ZIP code mappings. This design enforces referential integrity and prevents duplicate geographic definitions.

Common Use Cases and Queries

This table is central to queries that validate or report on employee city tax jurisdictions. A common use case is verifying an employee's assigned tax location against the master list of valid geocodes. For reporting, it is frequently joined to descriptive tables to generate readable lists of active city tax jurisdictions. A typical query pattern involves joining PAY_US_CITY_GEOCODES with PAY_US_CITY_NAMES to get human-readable descriptions, and potentially with PAY_US_EMP_CITY_TAX_RULES_F to analyze active tax rules. Sample SQL to retrieve all valid geocodes with names would be:

  • SELECT gc.state_code, gc.county_code, gc.city_code, cn.city_name FROM hr.pay_us_city_geocodes gc, hr.pay_us_city_names cn WHERE gc.state_code = cn.state_code AND gc.county_code = cn.county_code AND gc.city_code = cn.city_code;

It is also critical for setups where ZIP codes are mapped to tax jurisdictions via the PAY_US_ZIP_CODES table.

Related Objects

As indicated by its foreign key relationships, PAY_US_CITY_GEOCODES is a hub for several important payroll tables. It is referenced by PAY_US_CITY_NAMES for descriptions, PAY_US_CITY_SCHOOL_DSTS for school district information, and PAY_US_EMP_CITY_TAX_RULES_F, which holds the definitive tax rules for employees. Furthermore, it is referenced by PAY_US_ZIP_CODES to map postal codes to tax jurisdictions. Crucially, it has a foreign key dependency on PAY_US_COUNTIES, ensuring that every city geocode is associated with a valid state and county combination. These relationships underscore its position as a foundational validation point within the US payroll tax geography data model.