Results for “pay_us_city_geocodes”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The HR.PAY_US_CITY_GEOCODES table is a reference data object within the Oracle E-Business Suite Payroll (PAY) module. It holds all distinct combinations of state code, county code and city code that are used by U.S. payroll and tax processing functionality. In EBS 12.1.1 and 12.2.2, this table acts as the geographic validation anchor that ties city-level tax jurisdictions and school district assignments to a single canonical city key. Downstream city-level objects — city names, city school districts, employee city tax rules and ZIP codes — resolve their geographic context through this table.

From a Data Vault modeling perspective, the heuristic classification of this object is hub-leaning. The composite business key (STATE_CODE, COUNTY_CODE, CITY_CODE) plus the ZD_EDITION_NAME column define the natural identity of a city geocode. The absence of descriptive, change-tracked attributes beyond these keys supports treating the object as a hub rather than a satellite; change history, where required, would typically be captured in adjacent satellites.

Key Information Stored

The documented physical schema contains four columns, with the ZD_EDITION_NAME attribute introduced for edition-based / data-loss-prevention versioning:

• STATE_CODE — Two-character U.S. state code portion of the composite business key.
• COUNTY_CODE — County identifier within the state; together with STATE_CODE and CITY_CODE forms the natural city geocode.
• CITY_CODE — City identifier within the state/county combination.
• ZD_EDITION_NAME — Edition identifier that extends the unique index, allowing multiple edition versions to coexist.

The unique indexes (business-key candidates) are PAY_US_CITY_GEOCODES_PK, defined over (STATE_CODE, COUNTY_CODE, CITY_CODE, ZD_EDITION_NAME). The surrogate primary key PAY_US_CITY_GEOCODES_PK is the same constraint used as the business-key candidate, i.e. EBS does not carry a separate numeric surrogate column in the documented metadata. Any joins must therefore use the full four-column key family.

Common Use Cases and Queries

The table is primarily queried as a lookup/validation source when payroll calculations need to resolve a city to a valid combination, and when upstream reference data (counties, ZIP codes) must be reconciled. Typical patterns include:

• Validating a de-normalized inbound file of state/county/city combinations before loading PAY_US_CITY_NAMES or PAY_US_EMP_CITY_TAX_RULES_F.
• Reporting the number of active city geocodes by state, to support U.S. payroll jurisdiction coverage analysis.
• Finding orphan ZIP codes — ZIP records whose (STATE_CODE, COUNTY_CODE, CITY_CODE) triple is absent from the city geocode registry.

Representative SQL:

SELECT state_code, COUNT(*) city_cnt
FROM   hr.pay_us_city_geocodes
GROUP  BY state_code
ORDER  BY state_code;

SELECT z.state_code, z.zip_code
FROM   hr.pay_us_zip_codes z
LEFT   JOIN hr.pay_us_city_geocodes g
       ON g.state_code  = z.state_code
      AND g.county_code = z.county_code
      AND g.city_code   = z.city_code
WHERE  g.state_code IS NULL;

Related Objects

Relationship data from the ETRM shows the following dependencies. This table references PAY_US_COUNTIES via (STATE_CODE, COUNTY_CODE), and is referenced by several downstream city-level objects:

• PAY_US_COUNTIES — Parent county registry (this table’s only outbound FK).
• PAY_US_CITY_NAMES — Reverses the FK; supplies localized city names keyed by STATE_CODE, COUNTY_CODE, CITY_CODE.
• PAY_US_CITY_SCHOOL_DSTS — School district assignments for the same city key.
• PAY_US_EMP_CITY_TAX_RULES_F — Effective-dated employee city tax rules that require a valid city geocode.
• PAY_US_ZIP_CODES — ZIP code registry anchored to the same state/county/city triple.

Because every dependent object uses the same four-column key, the primary operational risk is key drift: if a state/county/city combination is retired or re-keyed, all four dependent tables must be reconciled in the same data-maintenance window.