Search Results hxt_shift_diff_rules_uk

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:

• ID — the surrogate primary key, enforced by HXT_SHIFT_DIFF_RULES_PK, used to uniquely identify each rule row.
• NAME — the descriptive name of the rule; combined with SDP_ID, it forms the unique business key candidate HXT_SHIFT_DIFF_RULES_UK (NAME, SDP_ID), ensuring rule names are unique within a policy.
• SDP_ID — the foreign key to HXT_SHIFT_DIFF_POLICIES, identifying the parent shift differential policy that owns the rule.
• ELEMENT_TYPE_ID — references the pay or time element type to which the rule applies, driving the earning or calculation element used at payroll.
• START_TIME and STOP_TIME — define the daily time window during which the differential rule is active.
• CARRYOVER_TIME — governs how time is carried across a shift boundary where the rule window crosses midnight.
• EFFECTIVE_START_DATE and EFFECTIVE_END_DATE — the date-effective range controlling when the rule is valid.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN provide standard EBS audit tracking.

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.