Search Results state_code

Overview

The HR.PAY_US_COUNTIES table is a core reference data object within Oracle E-Business Suite 12.1.1 and 12.2.2, specifically for the US Payroll module. It serves as the master repository for United States county information, storing the official geocodes required by the VERTEX tax engine to identify precise tax jurisdictions. Its primary functional role is to validate US employee addresses and accurately calculate county-level taxation. As indicated in the documentation, this table is only relevant and utilized if the US Payroll product is installed within the EBS instance.

Key Information Stored

The table is structured around a composite primary key and contains descriptive county data. The key columns are STATE_CODE and COUNTY_CODE, which together form the unique identifier (PAY_US_COUNTIES_PK) for each county record. These codes are the standardized geocodes used for tax processing. The table also stores the COUNTY_NAME and an optional COUNTY_ABBREV for the county abbreviation. A critical data integrity constraint is enforced by the PAY_US_COUNTIES_UK2 unique key, which ensures that a COUNTY_NAME is unique within a given STATE_CODE, preventing duplicates. The STATE_CODE column is also a foreign key referencing the PAY_US_STATES table, maintaining referential integrity with the parent state definitions.

Common Use Cases and Queries

The most frequent use case involves address validation and tax setup during employee data entry or maintenance. When a user enters a work or residence location, the system cross-references the state and county against this table to ensure it is a valid jurisdiction for tax withholding. For reporting and data extraction, a basic query to retrieve all counties for a specific state is essential. The following SQL pattern, as suggested by the ETRM, is foundational:

SELECT STATE_CODE,
       COUNTY_CODE,
       COUNTY_ABBREV,
       COUNTY_NAME
FROM   HR.PAY_US_COUNTIES
WHERE  STATE_CODE = 'CA' -- Example for California
ORDER BY COUNTY_NAME;

Developers and analysts also frequently join this table with related tax rule tables to generate reports on county-level tax configurations or to debug tax calculation issues by verifying the underlying county geocode mappings.

Related Objects

HR.PAY_US_COUNTIES sits at the center of a hierarchy of US tax-related reference tables. As documented in the foreign key relationships:

• Referenced Parent Table: The table references HR.PAY_US_STATES via its STATE_CODE column, ensuring every county is associated with a valid state.
• Referenced Child Tables: Several key payroll tables reference PAY_US_COUNTIES, using the composite key of (STATE_CODE, COUNTY_CODE):
  • PAY_US_CITY_GEOCODES: Links city geocodes to their parent county.
  • PAY_US_COUNTY_SCHOOL_DSTS: Stores school district information within counties.
  • PAY_US_EMP_COUNTY_TAX_RULES_F: The critical table holding employee-specific county tax rules, directly dependent on valid county definitions for proper tax withholding calculations.

These relationships underscore the table's integral role in the US payroll tax data model, where it acts as a key lookup for downstream tax jurisdiction and rule definitions.