Search Results pay_state_rules




Overview
Explore in AI Chat

Overview

PAY_STATE_RULES is a reference table residing in the HR schema and owned by the Oracle Payroll (PAY) product module. It stores United States state tax information used by Oracle EBS Payroll and Oracle Time and Labor to drive statutory withholding calculations, workers' compensation surcharges, and jurisdictional reporting. In Oracle EBS 12.1.1 and 12.2.2 the table is a foundational seed-data object: each row corresponds to a single U.S. state, territory, or taxing jurisdiction, and downstream payroll processes reference those rows by the STATE_CODE value.

The documented primary key is the single-column key PAY_STATE_RULES_PK over STATE_CODE. Because the table contains stable, descriptive attributes about a jurisdiction (name, FIPS code, jurisdiction code, tax-period and workers' compensation parameters) and is referenced by multiple foreign keys from dependent transactional tables, the mined Data Vault classification is hub-leaning. In Data Vault modeling terms, STATE_CODE functions as a business key that would anchor a hub, with the remaining descriptive attributes treated as satellite content. This classification is a modeling suggestion derived from the foreign-key topology, not a physical EBS construct.

Key Information Stored

The documented physical schema exposes 13 columns in HR.PAY_STATE_RULES. The most consequential are:

  • STATE_CODE — the primary key and the business identifier by which all dependent tables join to this row; also the business-key candidate confirmed by the unique index PAY_STATE_RULES_PK.
  • NAME — the descriptive name of the state or jurisdiction, used in tax reporting and user-facing lists of values.
  • FIPS_CODE — the Federal Information Processing Standards numeric code assigned to the jurisdiction, used for federal and state reporting interfaces.
  • JURISDICTION_CODE — the internal jurisdiction identifier used to associate the state with tax rules and calculation engines.
  • HEAD_TAX_PERIOD — the period basis used for head-of-household or dependent tax-period derivation.
  • WC_OVERTIME_HOURS — the workers' compensation overtime threshold, controlling how overtime hours are split for WC premium computation.
  • WC_EXECUTIVE_WEEKLY_MAX — the maximum weekly wage base applied to executive officers under the state's workers' compensation rules.
  • FS_LOOKUP_TYPE — the FastFormula lookup type associated with the state, enabling extensible rules.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard WHO audit columns used for data lineage and troubleshooting.

No separate surrogate key is documented; STATE_CODE serves as both the primary key and the business-key candidate. Rows originate from Oracle seed data rather than user entry, so CREATED_BY is typically a seeded system identifier.

Common Use Cases and Queries

Typical uses include resolving a state description for payroll reports, validating workers' compensation parameters before running a payroll process, and joining state tax results to jurisdictional attributes. A simple lookup pattern:

  • SELECT state_code, name, fips_code FROM hr.pay_state_rules ORDER BY state_code;
  • SELECT s.state_code, s.name, w.surcharge_rate FROM hr.pay_state_rules s, hr.pay_wc_state_surcharges w WHERE s.state_code = w.state_code;

Reporting use cases include generating state tax withholding summaries, auditing the WC_OVERTIME_HOURS and WC_EXECUTIVE_WEEKLY_MAX values against statutory updates, and driving state-specific FastFormula lookups through FS_LOOKUP_TYPE. Because the table is small and slowly changing, it is frequently cached in payroll batch processes.

Related Objects

The documented foreign keys identify the principal dependent objects, all of which reference PAY_STATE_RULES.STATE_CODE:

  • PAY_JOB_WC_CODE_USAGES — joins on PAY_JOB_WC_CODE_USAGES.STATE_CODE; associates workers' compensation codes with jobs by state.
  • PAY_WC_FUNDS — joins on PAY_WC_FUNDS.STATE_CODE; holds workers' compensation fund definitions per state.
  • PAY_WC_STATE_SURCHARGES — joins on PAY_WC_STATE_SURCHARGES.STATE_CODE; stores state surcharge rates and effective dates.

These three tables form the core dependency fan-out documented in the ETRM metadata. Additional payroll objects reference state codes indirectly through them, but the relationships above are the authoritative, documented joins for query construction and impact analysis in Oracle EBS 12.1.1 and 12.2.2.