Results for “pay_wci_rates”

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

Overview

The PAY_WCI_RATES table resides in the HR schema and belongs to the PAY (Payroll) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores Workers' Compensation Insurance (WCI) rate definitions used to calculate employer premiums assessed against employee earnings. Although the table is currently populated and consumed primarily for Canadian workers' compensation reporting, its design is jurisdiction-neutral, allowing it to be used globally.

From a Data Vault modeling perspective, the mined foreign-key structure suggests this table is satellite-leaning: it carries descriptive and quantitative attributes (the rate value, code, and description) attached to a business entity identified elsewhere, rather than acting as a pure hub of business keys or a link between two hubs. The single significant outbound reference to PAY_WCI_ACCOUNTS supports this interpretation, with the account serving as the anchor entity.

Key Information Stored

The table contains 13 documented columns in the 12.2.2 physical schema. The most operationally significant are:

• RATE_ID — the surrogate primary key, uniquely enforced by the PAY_WCI_RATES_PK index. This is the column referenced by child tables.
• BUSINESS_GROUP_ID — foreign key to HR_ALL_ORGANIZATION_UNITS, scoping each rate row to a specific business group and enforcing Multi-Org (or legislative) partitioning.
• ACCOUNT_ID — foreign key to PAY_WCI_ACCOUNTS, linking the rate to the WCI account under which it is registered.
• CODE — the user-facing short identifier for the rate, typically the classification or rate code recognized by the compensation authority.
• RATE — the numeric premium rate applied to assessable earnings.
• DESCRIPTION and COMMENTS — free-text descriptive fields for reporting and audit context.

The remaining columns are standard Oracle EBS WHO-audit attributes: LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, and the optimistic locking column OBJECT_VERSION_NUMBER. No unique business-key index beyond the surrogate primary key is documented, so RATE_ID is the sole unique identifier; CODE plus BUSINESS_GROUP_ID is a de facto candidate key in practice.

Common Use Cases and Queries

WCI rates are consumed during payroll costing and premium reporting to compute employer liability per employee based on occupation classification. Typical queries retrieve active rates for a business group, join them to occupation assignments, or audit recently changed rates.

• Listing rates per business group:

  SELECT r.rate_id, r.code, r.rate, r.description
  FROM   pay_wci_rates r
  WHERE  r.business_group_id = :p_bg_id
  ORDER BY r.code;

• Joining rates to their governing account:

  SELECT r.code, r.rate, a.account_name
  FROM   pay_wci_rates r, pay_wci_accounts a
  WHERE  r.account_id = a.account_id;

• Audit of recent changes:

  SELECT rate_id, code, rate, last_updated_by, last_update_date
  FROM   pay_wci_rates
  WHERE  last_update_date >= :since_date;

• Identifying rates referenced by occupations:

  SELECT o.occupation_id, r.code, r.rate
  FROM   pay_wci_occupations o, pay_wci_rates r
  WHERE  o.rate_id = r.rate_id;

Related Objects

• PAY_WCI_ACCOUNTS — parent of PAY_WCI_RATES.ACCOUNT_ID; holds the WCI account registration details.
• HR_ALL_ORGANIZATION_UNITS — referenced via BUSINESS_GROUP_ID; defines the organizational business group context.
• PAY_WCI_OCCUPATIONS — child table referencing PAY_WCI_RATES.RATE_ID; maps occupations to the applicable rate.
• PAY_WCI_RATES_PK — the primary key index enforcing uniqueness on RATE_ID.
• Payroll costing and WCI reporting processes that read the RATE column to derive employer premium amounts.