Search Results pay_us_states

Overview

The PAY_US_STATES table is a core reference table within the Oracle E-Business Suite (EBS) Payroll module, specifically for US implementations. Its primary function is to maintain a validated list of US states and their associated VERTEX geocodes. This table is essential for ensuring data integrity in address validation processes, particularly for payroll and tax calculations. It serves as a master source for state codes, enabling consistent data entry and linking across various payroll tax rule and reporting entities. The table is owned by the HR schema and is a VALID object in both EBS releases 12.1.1 and 12.2.2.

Key Information Stored

The table's structure is designed to uniquely identify each state through two primary keys. The STATE_CODE column, which is the primary key for PAY_US_STATES_PK, typically holds a numeric or internal identifier. The STATE_ABBREV column, serving as the primary key for PAY_US_STATES_UK2, stores the standard two-letter postal abbreviation (e.g., CA, NY, TX). A critical piece of data is the VERTEX geocode, a numeric code used by the Vertex tax calculation engine to precisely identify the state for complex payroll and sales tax computations. Additional columns, while not explicitly listed in the provided metadata, commonly include the full state name and potentially effective date ranges for historical tracking.

Common Use Cases and Queries

The primary use case is the validation and lookup of state information during data entry for employee addresses, company locations, and tax authority definitions. It is heavily referenced during the setup and application of state-level payroll tax rules. Common SQL patterns include joining this table to employee tax rules or address tables to retrieve descriptive state information for reports. For example, a query to list all active states with their codes and abbreviations would be: SELECT state_code, state_abbrev, state_name FROM pay_us_states WHERE SYSDATE BETWEEN effective_start_date AND effective_end_date;. Another typical use is in validation logic for forms or interfaces, ensuring that any provided STATE_ABBREV exists in this master table before allowing a transaction to proceed.

Related Objects

As indicated by its foreign key relationships, PAY_US_STATES is a parent table to several key payroll tax rule tables. These dependencies underscore its central role in the tax configuration hierarchy:

• PAY_US_COUNTIES: References STATE_CODE to link counties to their correct state.
• PAY_US_EMP_CITY_TAX_RULES_F, PAY_US_EMP_COUNTY_TAX_RULES_F, and PAY_US_EMP_STATE_TAX_RULES_F: All reference STATE_CODE to anchor employee-specific tax rules to a state.
• PAY_US_EMP_FED_TAX_RULES_F: References the state via SUI_STATE_CODE for Federal Unemployment Tax Act (FUTA) and State Unemployment Insurance (SUI) calculations.

These relationships ensure that all subordinate tax entities are based on a valid, defined state from the PAY_US_STATES table.