Search Results pay_job_wc_code_usages




Overview
Explore in AI Chat

Overview

The PAY_JOB_WC_CODE_USAGES table is a Payroll (PAY) module object owned by the HR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores Workers Compensation (WC) codes that apply to specific combinations of a job and a state. In Oracle Payroll, workers compensation reporting requires that each employee be assigned a WC classification derived from the job they hold and the state in which they work. This table provides the maintenance point where those job-to-state-to-WC-code mappings are defined and subsequently consumed by payroll processing and statutory reporting.

From a Data Vault modeling perspective, the metadata's heuristic classification for this table is link. This designation reflects the table's structure as an associative entity: its business identity is composed entirely of references to PER_JOBS (JOB_ID) and PAY_STATE_RULES (STATE_CODE), making it a natural junction between those two dimensions. The WC_CODE attribute functions as a descriptive payload carried on that relationship rather than as an independent business key.

Key Information Stored

The documented physical schema for 12.2.2 contains nine columns. The most significant are:

  • JOB_ID — Identifies the job from PER_JOBS to which the workers compensation code applies. Part of the composite primary key.
  • STATE_CODE — Identifies the state from PAY_STATE_RULES. Part of the composite primary key.
  • BUSINESS_GROUP_ID — The business group (organization) that owns the record; foreign key to HR_ALL_ORGANIZATION_UNITS. This provides the multi-tenant partitioning used throughout HR and PAY tables.
  • WC_CODE — The workers compensation code assigned to the job/state combination. This is the primary business payload of the row.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard Oracle EBS audit columns (the WHO columns) recording row creation and last modification context.

The surrogate primary key is documented as PAY_JOB_WC_CODE_USAGES_PK, defined over (JOB_ID, STATE_CODE). Because that same index also enforces the business uniqueness of a job/state pairing, it serves simultaneously as the business-key candidate. The combination guarantees that at most one WC code is recorded per job and state within the owning business group.

Common Use Cases and Queries

The principal use case is resolving the workers compensation code for an employee during payroll runs and WC reporting. Because the employee's job is derivable through assignment records and the work location determines the state, applications join through PER_JOBS and the state rules to retrieve WC_CODE. A typical lookup pattern:

  • Query the WC code for a given job and state: SELECT wc_code FROM pay_job_wc_code_usages WHERE job_id = :job_id AND state_code = :state_code AND business_group_id = :bg_id;
  • List all WC code assignments for a business group: SELECT job_id, state_code, wc_code FROM pay_job_wc_code_usages WHERE business_group_id = :bg_id ORDER BY job_id, state_code;
  • Join to PER_JOBS to display job names alongside codes: SELECT pj.name, u.state_code, u.wc_code FROM pay_job_wc_code_usages u, per_jobs pj WHERE u.job_id = pj.job_id AND u.business_group_id = pj.business_group_id;
  • Reporting: extract job/state/WC-code matrices for state fund audits, rate determination, and reconciliation of workers compensation premiums against employee populations.

Related Objects

The foreign key relationships documented in the ETRM metadata identify the primary related objects:

  • PER_JOBS — joined on JOB_ID; the job definition referenced by each row. The dominant inbound business-key relation.
  • PAY_STATE_RULES — joined on STATE_CODE; supplies the state-level statutory rules that WC reporting depends upon.
  • HR_ALL_ORGANIZATION_UNITS — joined on BUSINESS_GROUP_ID; the organization/business group owning the record.
  • PER_ALL_ASSIGNMENTS_F / PER_ALL_PEOPLE_F — not directly referenced by FK, but the natural downstream consumers, since employee job and location resolve to a WC code through this table.
  • PAY_JOB_WC_CODE_USAGES_PK — the unique index that enforces business-key uniqueness on (JOB_ID, STATE_CODE).

Together these relationships position PAY_JOB_WC_CODE_USAGES as a focused link between job definitions, state rules, and the workers compensation codes required for compliant payroll reporting.