Results for “pay_us_states”

50+ results




Overview
Explore in AI Chat

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

Overview

PAY_US_STATES is a reference (lookup) table owned by the HR schema within the Oracle E-Business Suite Payroll (PAY) product. Its documented purpose is to store United States states together with their VERTEX geocodes, which are used by the application to validate US addresses during payroll and HR processing. Because address validation and jurisdiction determination are foundational to US payroll taxation, this table functions as a low-volume, highly stable reference set that is queried continuously by tax engines, address validation routines, and employee data entry forms.

In heuristic Data Vault terms, the mined foreign-key structure classifies PAY_US_STATES as a hub. This classification is a modeling suggestion rather than a prescribed design: the table carries a single natural business key (the state) and serves as the anchor from which multiple dependent objects derive their state references. Its near-static content and narrow column footprint are consistent with hub behavior, where descriptive attributes are minimal and referential stability is the primary concern.

Key Information Stored

The documented physical schema for release 12.2.2 comprises four columns in the HR schema. The most significant are:

  • STATE_CODE — the primary key column of PAY_US_STATES_PK. It is the surrogate/system key by which dependent tax and geographic tables reference a state.
  • STATE_ABBREV — the two-character state abbreviation and the business-key candidate exposed through the unique index PAY_US_STATES_UK2. This is the human-recognizable identifier used on addresses and tax forms.
  • STATE_NAME — the full descriptive name of the state, used for display, reporting, and label generation.
  • ZD_EDITION_NAME — the editioning column included in both unique indexes (PAY_US_STATES_PK and PAY_US_STATES_UK2), supporting Oracle EBS online patching and edition-based redefinition in 12.2.x.

The distinction between the two unique indexes is important: PAY_US_STATES_PK enforces uniqueness on STATE_CODE (with ZD_EDITION_NAME), while PAY_US_STATES_UK2 enforces uniqueness on STATE_ABBREV (with ZD_EDITION_NAME). The user search term "pay_us_states_uk2" therefore refers directly to the alternate unique key that guarantees no two rows within an edition share the same state abbreviation.

Common Use Cases and Queries

The table is typically joined to resolve a state code into a readable name or abbreviation for reports, or to validate that a supplied abbreviation is legitimate before an address is accepted. Typical patterns include joining state tax rules to retrieve the state name for a payroll register, or restricting a query to a single jurisdiction for a quarterly tax extract.

  • Resolving a tax rule to its state: SELECT s.STATE_ABBREV, s.STATE_NAME FROM PAY_US_STATES s, PAY_US_EMP_STATE_TAX_RULES_F r WHERE r.STATE_CODE = s.STATE_CODE;
  • Validating an entered abbreviation: SELECT STATE_CODE FROM PAY_US_STATES WHERE STATE_ABBREV = :p_abbrev;
  • Reporting all states with counties for geographic drill-downs by joining PAY_US_COUNTIES on STATE_CODE.
  • Driving address validation logic where the VERTEX geocode associated with each state underpins US address verification.

Related Objects

Because PAY_US_STATES is a hub, several tax and geographic tables carry foreign keys referencing its STATE_CODE. The most significant documented dependencies are:

  • PAY_US_COUNTIES — joins on PAY_US_COUNTIES.STATE_CODE to PAY_US_STATES.STATE_CODE, giving the county hierarchy beneath each state.
  • PAY_US_EMP_STATE_TAX_RULES_F — joins on STATE_CODE; holds employee-level state tax rule definitions.
  • PAY_US_EMP_COUNTY_TAX_RULES_F — joins on STATE_CODE; holds employee-level county tax rules.
  • PAY_US_EMP_CITY_TAX_RULES_F — joins on STATE_CODE; holds employee-level city tax rules.
  • PAY_US_EMP_FED_TAX_RULES_F — joins on SUI_STATE_CODE, linking federal tax rules to the state unemployment insurance jurisdiction.

These relationships confirm that PAY_US_STATES acts as the central state reference for US payroll tax rule maintenance, county geography, and address validation across the Payroll module.