Search Results hxt_shift_diff_rules




Overview
Explore in AI Chat

Overview

HXT_SHIFT_DIFF_RULES is a Time and Labor (HXT) configuration table in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the details of the time ranges that carry separate pay rules for a specific shift, allowing an implementation to define differential pay treatment — such as shift differential premiums — across distinct windows of a shift. Each row defines a single rule, anchored to a parent shift differential policy, and specifies the time boundaries within which that rule applies.

The reference material classifies the object with a heuristic Data Vault classification of "satellite-leaning," suggesting that HXT_SHIFT_DIFF_RULES behaves as a descriptive satellite of the HXT_SHIFT_DIFF_POLICIES hub. In that modeling view, the policy supplies the business key (hub) and this table carries the time-varying descriptive attributes (the time ranges and associated pay attributes) that qualify the policy.

Key Information Stored

The table is owned by the HXT schema and, as documented in ETRM 12.2.2, contains 14 columns. The most significant columns are:

Practically, the surrogate key ID identifies a row, while the unique combination of NAME and SDP_ID provides the natural business identity used for validation and lookups.

Common Use Cases and Queries

Typical scenarios include auditing the configured pay windows for a shift differential policy, verifying overlap or gaps between START_TIME and STOP_TIME ranges within a policy, and confirming that date-effective ranges do not collide. A common reporting query joins the rules to their parent policy:

  • SELECT r.NAME, r.SDP_ID, r.START_TIME, r.STOP_TIME, r.CARRYOVER_TIME, r.EFFECTIVE_START_DATE, r.EFFECTIVE_END_DATE FROM HXT.HXT_SHIFT_DIFF_RULES r WHERE r.SDP_ID = :policy_id AND SYSDATE BETWEEN r.EFFECTIVE_START_DATE AND NVL(r.EFFECTIVE_END_DATE, SYSDATE) — returns the currently effective rules for a given policy.
  • A join to HXT_SHIFT_DIFF_POLICIES retrieves the policy name and business group alongside each rule, useful for cross-policy reporting across a payroll period.
  • Overlap detection uses START_TIME < nvl(STOP_TIME, ...) comparisons partitioned by SDP_ID to flag conflicting windows.

These queries support payroll configuration audits, timecard validation troubleshooting, and documentation of differential pay setup before period close.

Related Objects

The following objects are the most significant dependencies and references for HXT_SHIFT_DIFF_RULES:

  • HXT_SHIFT_DIFF_POLICIES — the parent table; joined via HXT_SHIFT_DIFF_RULES.SDP_ID = HXT_SHIFT_DIFF_POLICIES.SDP_ID (the documented foreign key), it supplies the policy-level business context.
  • HXT_SHIFT_DIFF_RULES_PK — the primary-key index on ID.
  • HXT_SHIFT_DIFF_RULES_UK — the unique index on NAME and SDP_ID, enforcing the business key.
  • Time element / element type definitions — referenced through ELEMENT_TYPE_ID, linking rules to the earning elements applied during calculation.
  • HXT time calculation and processing objects — rules are consumed at runtime when constructing timecards and calculating shift differential earnings.

Together these relationships place HXT_SHIFT_DIFF_RULES at the descriptive layer of shift differential configuration, subordinate to its governing policy.