Search Results pay_wc_state_surcharges




Overview
Explore in AI Chat

Overview

The PAY_WC_STATE_SURCHARGES table, owned by the HR schema and operating within the Oracle Payroll (PAY) module, stores Workers Compensation surcharge definitions applied at the state level. Workers compensation surcharges represent additional assessments levied by state jurisdictions on top of standard workers compensation insurance rates, and this table serves as the configuration repository that payroll processing and costing routines reference when calculating these statutory obligations. Each row defines a surcharge rule keyed to a specific state, governing the rate, additive basis, and display sequencing used during calculation.

The documented ETRM metadata assigns this object a heuristic Data Vault classification of satellite-leaning. This classification is a modeling suggestion rather than a delivered constraint: the table functions primarily as a descriptive attribute container attached to a state-level business key rather than as an independent hub of business entities. The foreign key from STATE_CODE to PAY_STATE_RULES positions the surcharge record within a broader state rules construct, reinforcing its role as a dependent, attribute-bearing satellite of the state rules hub. Analysts building dimensional or Data Vault models from EBS Payroll data should treat SURCHARGE_ID as a surrogate artifact and STATE_CODE as the meaningful link to the business conformance layer.

Key Information Stored

The table exposes eleven documented columns in the 12.2.2 physical schema. The most operationally significant are:

  • SURCHARGE_ID — The surrogate primary key, enforced by the unique index PAY_WC_STATE_SURCHARGES_PK. It uniquely identifies each surcharge definition but carries no business meaning of its own.
  • STATE_CODE — The state to which the surcharge applies. This is the only documented business-key candidate and the sole foreign key column, referencing PAY_STATE_RULES.STATE_CODE. It is the principal join path to state-level legislative configuration.
  • NAME — The descriptive label for the surcharge, used in forms, reports, and payroll output.
  • RATE — The surcharge rate applied during calculation, typically expressed as a percentage or multiplier depending on state statute.
  • ADD_TO_RT — A flag controlling whether the surcharge is added to the base rate (rather than applied independently), directly affecting the arithmetic performed by the calculation engine.
  • POSITION — A sequencing value governing display or evaluation order when multiple surcharges exist for a state.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard EBS audit columns supporting change tracking, user attribution, and concurrent-manager session tracing.

Because SURCHARGE_ID is a generated identifier, natural-key resolution for ETL and reconciliation should be performed on STATE_CODE combined with NAME, since the unique index does not enforce uniqueness on that pair; validation against PAY_STATE_RULES is required to guarantee referential integrity.

Common Use Cases and Queries

Typical usage centers on extracting state surcharge configuration for payroll validation, rate auditing, and integration to downstream costing or data warehouse systems. A common pattern retrieves all surcharges for a jurisdiction and joins to the state rules definition:

  • Configuration audit: SELECT s.STATE_CODE, s.NAME, s.RATE, s.ADD_TO_RT, s.POSITION FROM HR.PAY_WC_STATE_SURCHARGES s ORDER BY s.STATE_CODE, s.POSITION;
  • State rules reconciliation: SELECT wc.SURCHARGE_ID, wc.NAME, wc.RATE, sr.STATE_CODE FROM HR.PAY_WC_STATE_SURCHARGES wc, HR.PAY_STATE_RULES sr WHERE wc.STATE_CODE = sr.STATE_CODE;
  • Change tracking: filter on LAST_UPDATE_DATE to identify surcharge definitions modified after a legislative effective date, supporting compliance review.

Reporting use cases include effective-rate summaries by state for budgeting, surcharge exposure analysis for workers compensation accruals, and delta reports comparing configured rates against statutory published schedules.

Related Objects

  • PAY_STATE_RULES — Parent configuration table referenced via PAY_WC_STATE_SURCHARGES.STATE_CODE. Defines state-level payroll legislative parameters and is the primary join target.
  • HR_S_WC_STATE_SURCHARGES — A secured or shadow object referencing SURCHARGE_ID back to this table, commonly used for restricted data access or audit trail replication.
  • PAY_WC_STATE_SURCHARGES_PK — The unique index enforcing the SURCHARGE_ID primary key; relevant to DBA performance and integrity checks.

Additional dependencies should be confirmed through the application's data model and AOL registration, as the documented ETRM metadata identifies only the foreign key to PAY_STATE_RULES and the inbound reference from HR_S_WC_STATE_SURCHARGES.