Search Results pay_us_states_pk




Overview
Explore in AI Chat

Overview

The HR.PAY_US_STATES 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. Its primary function is to maintain the authoritative list of U.S. states, territories, and their associated identifiers. This table is critical for validating U.S. addresses and, more importantly, for tax jurisdiction identification when used in conjunction with the VERTEX tax integration module. Its status as a seed data table, residing in the APPS_TS_SEED tablespace, indicates it is populated and maintained by Oracle as part of the application's foundational data set, and it is only relevant if the US Payroll product is installed.

Key Information Stored

The table is structured with three key columns, each serving a distinct purpose in the payroll and tax ecosystem. The STATE_CODE is the primary key column, storing the geocode used by the VERTEX tax engine to uniquely identify a state for tax calculation purposes. The STATE_ABBREV column holds the standard two-letter postal abbreviation (e.g., 'CA' for California) and is enforced as unique via the PAY_US_STATES_UK2 index, which was the specific object referenced in the user's search. Finally, the STATE_NAME column stores the full textual name of the state or territory. The mandatory nature of these columns underscores their essential role in ensuring data integrity for downstream payroll and tax processes.

Common Use Cases and Queries

The primary use case for PAY_US_STATES is as a validation and lookup source for any functionality requiring a U.S. state. This includes employee address validation, the setup of state-level tax rules, and the processing of payroll calculations that depend on state jurisdictions. A standard query to retrieve the complete state list, as indicated in the ETRM, is foundational for many reports and integrations:

SELECT STATE_CODE, STATE_ABBREV, STATE_NAME FROM HR.PAY_US_STATES;

In practice, this table is frequently joined in queries related to employee taxation. For example, to list employees alongside their state of residence for tax reporting, one would join from a person or assignment table to PAY_US_STATES on the state code or abbreviation. Its unique constraint on STATE_ABBREV (UK2) is particularly important for application logic that uses the postal abbreviation as a key identifier in user interfaces or data imports.

Related Objects

As a seed data table, PAY_US_STATES is referenced by numerous other payroll objects, primarily as a foreign key constraint to ensure referential integrity. Key dependent tables, as documented, include:

This network of foreign key relationships highlights the table's central role in maintaining a consistent and valid hierarchy for all U.S. sub-national tax jurisdictions within the system.