Search Results hxt_prem_interact_rules




Overview
Explore in AI Chat

Overview

HXT_PREM_INTERACT_RULES is a Time and Labor (HXT) configuration table within the Oracle E-Business Suite 12.1.1 and 12.2.2 environments. It stores the definitional rules that govern how premium earnings interact with one another during the payroll time calculation process. In Oracle Time and Labor, premiums such as shift differentials, overtime, and holiday pay frequently overlap or stack; the interaction rules held in this table dictate whether a given earned premium may be applied on top of, instead of, or in substitution of a prior premium. The table therefore acts as the business-rule backbone for premium calculation and is consumed by the Time and Labor Calculation Engine (Fast Formula and premium processing) whenever employee time is evaluated.

Under the Data Vault classification heuristic derived from its foreign key structure, the table leans toward a satellite pattern. It carries descriptive rule attributes keyed to a parent policy, with a composite primary key that includes effectivity dates. This suggests a modeling approach in which HXT_PREM_INTERACT_RULES is treated as an effectivity-dated descriptive satellite attached to the premium interaction policy hub represented by HXT_PREM_INTERACT_POLICIES.

Key Information Stored

The table contains eleven documented columns. The most significant are listed below. Note that the surrogate primary key and the business-key candidate are the same composite index, so there is no separate single-column surrogate identifier.

  • PIP_ID — Identifier of the parent premium interaction policy (the grouping to which the rule belongs). This is both a primary key component and the foreign key to HXT_PREM_INTERACT_POLICIES.
  • ELT_EARNED_PREM_ID — The earned (later-applied) premium involved in the interaction, typically the higher-tier premium such as overtime.
  • ELT_PRIOR_PREM_ID — The prior (earlier) premium that the earned premium interacts with, for example a shift differential already applied to the time.
  • APPLY_PRIOR_PREM_YN — The controlling rule flag indicating whether the prior premium remains applicable when the earned premium is applied. This is the core business decision of each row.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Date-range columns defining the validity period of the rule. These are date-tracked, meaning multiple rule versions for the same premium pairing can coexist over time.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns capturing creation and last-modification context.

The unique index HXT_PREM_INTERACT_RULES_PK spans (PIP_ID, ELT_EARNED_PREM_ID, ELT_PRIOR_PREM_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE), enforcing one active rule per policy/premium pairing per effective window—this composite is the business-key candidate.

Common Use Cases and Queries

Typical scenarios include auditing premium stacking rules, migrating premium policies between environments, and diagnosing calculation discrepancies where the wrong premium was applied to an employee's timecard.

  • Retrieve active rules for a policy: SELECT pip_id, elt_earned_prem_id, elt_prior_prem_id, apply_prior_prem_yn FROM hxt_prem_interact_rules WHERE pip_id = :p_pip_id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
  • Identify rules where the prior premium is suppressed: filter on apply_prior_prem_yn = 'N' to list pairings that replace rather than stack premiums.
  • Date-track a specific premium pairing to see historical rule changes: select all rows for a given ELT_EARNED_PREM_ID and ELT_PRIOR_PREM_ID ordered by EFFECTIVE_START_DATE.
  • Reporting: join to premium lookup tables to resolve the earned and prior premium IDs into user-facing names and produce a policy-to-rule matrix for HR or payroll operations review.

Related Objects

  • HXT_PREM_INTERACT_POLICIES — Parent table; joined via HXT_PREM_INTERACT_RULES.PIP_ID = HXT_PREM_INTERACT_POLICIES.PIP_ID. Defines the policy header each rule belongs to.
  • HXT_PREM_INTERACT_RULES_PK — The unique index/constraint that enforces the composite business key and supports date-tracked lookups.
  • Premium definition lookups (per Element/ELT earn codes referenced by ELT_EARNED_PREM_ID and ELT_PRIOR_PREM_ID) — Resolve the premium identifiers into descriptive names.
  • Time and Labor Calculation Engine (Fast Formula premium processing) — The runtime consumer that reads these rules during time evaluation.
  • HXT premium/element earning tables — Downstream results where earned and prior premiums are recorded after interactions are resolved.