Results for “proration_style”

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

Overview

PAY_DATETRACKED_EVENTS is an Oracle HRMS (Payroll) configuration table owned by the HR schema. It stores the definitions of the datetracked events that the payroll application monitors on HRMS datetrack-enabled tables. Datetracking is the mechanism by which Oracle HRMS preserves the historical context of a row — every insert, update, or delete on a datetracked table is stamped with effective dates so that past, present, and future states of the record remain queryable. Rather than applying that tracking indiscriminately, Oracle Payroll uses PAY_DATETRACKED_EVENTS as a registry of precisely which columns on which tables constitute a trackable event, and how each change should be classified.

Functionally, the table acts as the bridge between three concepts: the event group (a logical bundle of related changes, defined in PAY_EVENT_GROUPS), the physical datetrack table being monitored (defined in PAY_DATED_TABLES), and the individual column on that table whose change is meaningful (identified by COLUMN_NAME and UPDATE_TYPE). Each row therefore answers the question "when this specific column on this specific table changes in this specific way, which event group should fire?"

Under a heuristic Data Vault classification mined from the foreign-key structure, this object models as a link. It carries no descriptive attributes of its own beyond classification flags; its purpose is to resolve a many-to-many relationship between event groups and dated tables at column granularity. That classification is a modeling suggestion derived from the FK topology, not an Oracle-supplied designation.

Key Information Stored

The surrogate primary key is DATETRACKED_EVENT_ID, enforced by the PAY_DATETRACKED_EVENTS_PK constraint. This is the only truly unique row identifier and is the value propagated to dependent tables. The documented physical schema in ETRM 12.2.2 lists two unique indexes; the second, PAY_DATETRACKED_EVENTS_UK1, is the business-key candidate, spanning EVENT_GROUP_ID, DATED_TABLE_ID, COLUMN_NAME, and UPDATE_TYPE. This composite guarantees that a given column-change pattern is registered only once per event group and dated table.

• DATETRACKED_EVENT_ID — surrogate primary key; the identifier referenced by downstream tracking records.
• EVENT_GROUP_ID — foreign key to PAY_EVENT_GROUPS, grouping the event with related changes that share processing semantics.
• DATED_TABLE_ID — foreign key to PAY_DATED_TABLES, identifying the datetrack-enabled HRMS table being monitored.
• COLUMN_NAME — the specific column on the dated table whose modification constitutes the event.
• UPDATE_TYPE — the category of change (for example, insert or update) that qualifies as the event.
• PRORATION_STYLE — controls how the corresponding payroll element should be prorated when the tracked event occurs mid-period.
• LEGISLATION_CODE — restricts the event definition to a specific legislative jurisdiction, enabling localisation-specific behaviour.
• BUSINESS_GROUP_ID — scopes the definition to a business group, supporting multi-tenant configuration within one installation.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the HRMS framework to detect concurrent updates.
• LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard WHO audit columns recording creation and last modification.
• ZD_EDITION_NAME — editioning identifier; in 12.2.2 it participates in both unique indexes, reflecting the edition-based redefinition architecture.

Common Use Cases and Queries

The most frequent operational need is to determine which event groups fire against a given table, typically while diagnosing why a payroll element was not prorated or why a retroactive change did not trigger expected processing.

• Inventory events for a table: join PAY_DATED_TABLES to PAY_DATETRACKED_EVENTS on DATED_TABLE_ID and filter by the table name to list every tracked column and its update type.
• Resolve event groups: join to PAY_EVENT_GROUPS on EVENT_GROUP_ID to identify the processing bundle that a column change belongs to.
• Trace actual triggered changes: join to PAY_EVENT_VALUE_CHANGES_F on DATETRACKED_EVENT_ID to see which recorded value changes were attributed to each configured event.
• Locate proration configuration: filter on PRORATION_STYLE to audit how events drive proration decisions for a legislation or business group.
• Localisation review: group and count by LEGISLATION_CODE to confirm which jurisdictions have custom event definitions versus delivered ones.

A representative pattern joins the three definition tables and exposes the effective configuration:

• SELECT e.DATETRACKED_EVENT_ID, g.EVENT_GROUP_NAME, t.TABLE_NAME, e.COLUMN_NAME, e.UPDATE_TYPE, e.PRORATION_STYLE FROM PAY_DATETRACKED_EVENTS e, PAY_EVENT_GROUPS g, PAY_DATED_TABLES t WHERE e.EVENT_GROUP_ID = g.EVENT_GROUP_ID AND e.DATED_TABLE_ID = t.DATED_TABLE_ID ORDER BY t.TABLE_NAME, e.COLUMN_NAME.

Because this is seeded setup data rather than transactional data, queries are overwhelmingly read-only. Changes are normally made through the delivered HRMS configuration layer rather than direct DML; direct inserts must populate DATETRACKED_EVENT_ID from the sequence and satisfy the UK1 composite or the row will be rejected.

Related Objects

• PAY_EVENT_GROUPS — parent of EVENT_GROUP_ID; defines the logical processing bundles to which events belong.
• PAY_DATED_TABLES — parent of DATED_TABLE_ID; registers the datetrack-enabled HRMS tables available for monitoring.
• PAY_EVENT_VALUE_CHANGES_F — the principal dependent object; its DATETRACKED_EVENT_ID column references this table, recording the value changes actually attributed to each configured event.
• PAY_DATETRACKED_EVENT_CHANGES — companion tracking table associated with the same event framework.
• PAY_ELEMENT_TYPES_F — consumes DATETRACKED_EVENT_ID to link payroll elements to the events that should affect them.
• PAY_EVENT_GROUP_RULES — applies processing rules to the event groups referenced by EVENT_GROUP_ID.
• PAY_EVENT_UPDATES — downstream processing records driven by events defined here.

Together these objects form the event-driven core of Oracle Payroll datetracking: PAY_DATETRACKED_EVENTS supplies the definition, PAY_EVENT_VALUE_CHANGES_F captures the occurrence, and the element and rule tables determine the payroll consequence.