Results for “pay_us_counties”

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

Overview

PAY_US_COUNTIES is a reference (lookup) table owned by the HR schema within the Oracle EBS Payroll (PAY) product. It stores the authoritative list of United States counties together with their VERTEX geocodes, and it exists primarily to validate US addresses and to support county-level tax determination during payroll processing. The table is a foundational element of the US localization for Oracle Payroll, acting as the master list against which city geocodes, county school district assignments, and employee county tax rules are validated.

Within a Data Vault modeling heuristic, the table is classified as hub-leaning. This classification reflects its role as a stable, business-key-driven master list of county identities, referenced by multiple downstream transactional and satellite-style objects rather than itself carrying volatile descriptive history. The heuristic should be treated as a modeling suggestion rather than a hard architectural constraint.

Key Information Stored

The documented physical schema exposes five columns. The most significant are:

• STATE_CODE — Identifies the US state to which the county belongs. It is the leading component of both the primary key and the foreign key to PAY_US_STATES.
• COUNTY_CODE — The VERTEX-assigned county code. Together with STATE_CODE it forms the composite business identifier for a county.
• COUNTY_NAME — The descriptive name of the county, used in validation and reporting. It participates in the alternate unique key.
• COUNTY_ABBREV — A short abbreviated form of the county name, typically used in compact tax and address displays.
• ZD_EDITION_NAME — The Oracle "edition" attribute (as used by Edition-Based Redefinition), which is included in both documented unique indexes.

Two unique indexes define the business keys. The primary key, PAY_US_COUNTIES_PK, is composite over (STATE_CODE, COUNTY_CODE, ZD_EDITION_NAME). The secondary unique key, PAY_US_COUNTIES_UK2, is composite over (COUNTY_NAME, STATE_CODE, ZD_EDITION_NAME). The combination of STATE_CODE and COUNTY_CODE is the operational surrogate/business identifier most commonly used in joins, while COUNTY_NAME plus STATE_CODE provides an alternate natural key ensuring county names are unique within a state.

Common Use Cases and Queries

Typical usage centers on validating and resolving US addresses and county tax jurisdictions. A common pattern joins a city geocode record back to its county:

• County lookup by state and code — resolving a payroll tax rule to a human-readable county name via STATE_CODE and COUNTY_CODE.
• Validation of city geocodes — confirming that a PAY_US_CITY_GEOCODES row references a valid county.
• County tax rule reporting — joining PAY_US_EMP_COUNTY_TAX_RULES_F to report employee county tax assignments by county name.
• School district cross-reference — resolving county school district records to the parent county.

A representative query resolving tax rules to county descriptions:

SELECT c.county_name, c.state_code, r.* FROM hr.pay_us_counties c, hr.pay_us_emp_county_tax_rules_f r WHERE c.state_code = r.state_code AND c.county_code = r.county_code;

Reporting use cases include county-level headcount and tax distribution reports, and validation extracts confirming data integrity between counties, cities, and school districts.

Related Objects

PAY_US_COUNTIES sits at the center of a small reference cluster, related through documented foreign key relationships:

• PAY_US_STATES — Referenced by PAY_US_COUNTIES.STATE_CODE. It is the parent state lookup for the county list.
• PAY_US_CITY_GEOCODES — References this table via STATE_CODE and COUNTY_CODE; assigns cities to their parent county.
• PAY_US_COUNTY_SCHOOL_DSTS — References this table via STATE_CODE and COUNTY_CODE; associates county school districts with counties.
• PAY_US_EMP_COUNTY_TAX_RULES_F — References this table via STATE_CODE and COUNTY_CODE; stores employee county tax rule assignments.

These relationships make PAY_US_COUNTIES the hub from which county-based address validation and payroll tax jurisdiction logic is driven across the US localization.