Search Results hxt_earning_rules_uk

Overview

HXT_EARNING_RULES is a table in the HXT schema (Oracle Time and Labor) that stores the definitions of earning rules used during time calculation and payroll-facing earnings generation. In Oracle EBS 12.1.1 and 12.2.2, earning rules act as the mapping between the time entries captured against timecards and the earning elements that are ultimately passed to Oracle Payroll or to downstream cost distribution. Each row in this table represents one discrete earning rule configuration, including the earning type, the earning element it resolves to, and the effective-dated thresholds or quantities that qualify a time entry for that earning.

The table is documented in ETRM as a VALID object owned by HXT, with a 15-column physical schema in 12.2.2. From a Data Vault modeling perspective, the mined FK structure suggests a satellite-leaning classification: the table carries descriptive, effective-dated attributes that change over time, attached to the parent earning policy. The foreign key to HXT_EARNING_POLICIES (via EGP_ID) positions it as a child of the earning policy, which acts as the hub-like anchor.

Key Information Stored

The table's surrogate primary key is ID, enforced by the HXT_EARNING_RULES_PK constraint. Two business-key candidate constraints are documented: HXT_EARNING_RULES_UK, defined over the combination of EGP_ID and SEQ_NO. This unique index confirms that a given earning policy cannot contain two rules occupying the same sequence position, and it provides the natural lookup path for retrieving rules in their defined processing order.

• EGP_ID — Foreign key to HXT_EARNING_POLICIES, identifying the parent earning policy that owns the rule. This is the primary grouping column for all rule retrieval.
• SEQ_NO — Sequence number establishing the evaluation order of rules within the earning policy; part of the unique business key.
• NAME — Descriptive name of the earning rule, used for identification in setup and reporting.
• EGR_TYPE — Earning rule type, distinguishing the calculation or categorization behavior applied by the rule.
• ELEMENT_TYPE_ID — Identifier of the earning element type the rule resolves to, linking time entries to payroll earnings elements.
• HOURS — Hours-based quantity or threshold associated with the rule's calculation.
• DAYS — Days-based quantity or threshold associated with the rule's calculation.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Effective-dating range controlling when the rule definition is valid for calculation.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard EBS WHO columns providing audit lineage.

Common Use Cases and Queries

The most frequent access pattern is retrieving all rules for a specific earning policy in processing sequence order. A representative query joins the rule table to its parent policy:

• SELECT r.id, r.seq_no, r.name, r.egr_type, r.hours, r.days FROM hxt_earning_rules r WHERE r.egp_id = :p_egp_id AND TRUNC(SYSDATE) BETWEEN r.effective_start_date AND NVL(r.effective_end_date, TRUNC(SYSDATE)) ORDER BY r.seq_no;
• Earnings audit and reconciliation reports typically join HXT_EARNING_RULES to HXT_EARNING_POLICIES to validate that every policy has at least one active rule, and to detect sequence gaps or overlaps.
• Because the table is effective-dated, historical queries reconstruct the rule set in effect for a given payroll period by comparing the period dates against EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
• Setup validation extracts frequently compare EGR_TYPE and HOURS/DAYS combinations to verify that threshold-based rules do not conflict within the same policy.

Related Objects

The dominant dependency is the parent earning policy, and most related functionality flows through that association.

• HXT_EARNING_POLICIES — Parent table joined via HXT_EARNING_RULES.EGP_ID = HXT_EARNING_POLICIES.ID; defines the policy under which rules are grouped.
• Payroll earning element definitions — Referenced through ELEMENT_TYPE_ID to resolve earning rule output to a payroll element type.
• Time and Labor calculation processes — Consume HXT_EARNING_RULES rows during time calculation to produce earnings from timecard entries.
• HXT_EARNING_RULES_PK / HXT_EARNING_RULES_UK — The primary and unique constraints governing row identity and business uniqueness (EGP_ID, SEQ_NO).
• Earning rule setup and validation forms/APIs — Create, update, and effective-date rule rows, enforcing the unique business key.

Together these objects form the earning-rule layer of the OTL earning pipeline, with HXT_EARNING_RULES serving as the effective-dated, child-level definition behind each earning policy.