Results for “pay_us_counties_uk2”
14 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
HR.PAY_US_COUNTIES is a seed data table in the Oracle E-Business Suite HR schema that stores the authoritative list of United States counties together with the geocodes used by the VERTEX tax module to identify tax jurisdictions. The table's primary function is the validation of US addresses and tax information, and it is only populated and actively referenced when US Payroll is installed. Consequently, it acts as a foundational reference object for county-level tax determination, address validation, and jurisdiction mapping within Oracle Payroll and related HR processes.
Physically, the table resides in the APPS_TS_SEED tablespace, consistent with its role as seeded reference data rather than transactional content. The documented schema in Oracle EBS 12.2.2 lists five columns: STATE_CODE, COUNTY_CODE, COUNTY_ABBREV, COUNTY_NAME, and ZD_EDITION_NAME. The presence of the ZD_EDITION_NAME column reflects the editioning architecture introduced in 12.2, which allows multiple editions of seed/quasi-seed data to coexist during online patching cycles.
From a heuristic Data Vault modeling perspective (derived from the documented foreign key structure), PAY_US_COUNTIES is classified as hub-leaning. Its composite primary key of STATE_CODE and COUNTY_CODE forms a natural business key that uniquely identifies each county, making it suitable as a hub candidate in a Data Vault design. The dependent attributes—COUNTY_ABBREV and COUNTY_NAME—would typically be modeled as a satellite attached to that hub, while the surrounding tax-rule and geocode tables represent link candidates.
Key Information Stored
The most significant columns in PAY_US_COUNTIES are:
- STATE_CODE — The state geocode. This is a mandatory component of the primary key and serves as the foreign key to PAY_US_STATES. It is also the column most commonly used as a filter in queries, including searches explicitly for "state_code."
- COUNTY_CODE — The county geocode. Together with STATE_CODE it constitutes the composite primary key PAY_US_COUNTIES_PK and uniquely identifies each county record.
- COUNTY_NAME — The full county name, with a documented length of 30 characters. Along with STATE_CODE it forms the unique business key PAY_US_COUNTIES_UK2, which enforces uniqueness of county names within a state.
- COUNTY_ABBREV — A short abbreviation of the county name, used primarily for compact display and reporting.
- ZD_EDITION_NAME — The editioning column present in 12.2, which partitions the unique index definitions (PAY_US_COUNTIES_PK and PAY_US_COUNTIES_UK2) across editions.
Regarding key structure, the surrogate-level identifier in this object is the composite primary key PAY_US_COUNTIES_PK (STATE_CODE, COUNTY_CODE, ZD_EDITION_NAME), while the primary business-key candidates are represented by the two unique indexes: PAY_US_COUNTIES_PK and PAY_US_COUNTIES_UK2 (COUNTY_NAME, STATE_CODE, ZD_EDITION_NAME). The non-unique index PAY_US_COUNTIES_FK1 on STATE_CODE supports foreign-key navigation.
Common Use Cases and Queries
The dominant use case is validation of county-level tax jurisdictions during US Payroll processing. When an employee's address or tax information is entered, the VERTEX tax module consults PAY_US_COUNTIES to resolve the correct county geocode and identify the applicable tax authority. A second common use case is public-sector and reporting analysis requiring a definitive list of counties per state.
A typical query pattern filtering on the searched attribute is:
SELECT STATE_CODE, COUNTY_CODE, COUNTY_ABBREV, COUNTY_NAME FROM HR.PAY_US_COUNTIES WHERE STATE_CODE = :state_code;- Reporting joins to PAY_US_COUNTY_SCHOOL_DSTS to derive school district tax associations per county.
- Geocode lookups joining STATE_CODE and COUNTY_CODE to PAY_US_CITY_GEOCODES to expand a city into its constituent counties.
Because the table is seeded and editioned, queries should generally include or be aware of ZD_EDITION_NAME when running in a 12.2 environment to avoid returning rows from obsolete editions.
Related Objects
The following objects have documented relationships with PAY_US_COUNTIES:
- PAY_US_STATES — Referenced by PAY_US_COUNTIES.STATE_CODE; provides the parent state geocode definitions.
- PAY_US_CITY_GEOCODES — References PAY_US_COUNTIES on STATE_CODE and COUNTY_CODE; maps cities to counties.
- PAY_US_COUNTY_SCHOOL_DSTS — References PAY_US_COUNTIES on STATE_CODE and COUNTY_CODE; defines county school districts for tax purposes.
- PAY_US_EMP_COUNTY_TAX_RULES_F — References PAY_US_COUNTIES on STATE_CODE and COUNTY_CODE; holds employee-level county tax rules.
Together these objects form the county-level jurisdiction layer that underpins VERTEX-driven US Payroll tax calculation.
-
TABLE: HR.PAY_US_COUNTIES 12.1.1
-
US counties and their VERTEX geocodes, used to validate US addresses.
-
US counties and their VERTEX geocodes, used to validate US addresses.
-
TABLE: HR.PAY_US_COUNTIES 12.2.2
-
12.1.1 DBA Data 12.1.1
-
12.2.2 DBA Data 12.2.2
-
12.1.1 DBA Data 12.1.1
-
12.2.2 DBA Data 12.2.2
-
eTRM - PAY Tables and Views 12.2.2
Temporary table used to hold invalid location addresses.
-
eTRM - PAY Tables and Views 12.1.1
Temporary table used to hold invalid location addresses.
-
eTRM - PAY Tables and Views 12.1.1
Temporary table used to hold invalid location addresses.
-
eTRM - PAY Tables and Views 12.2.2
Temporary table used to hold invalid location addresses.