Search Results hxt_shifts




Overview
Explore in AI Chat

Overview

HXT_SHIFTS is a core configuration table within the Oracle E-Business Suite Time and Labor (HXT) module. It defines company shifts based on a twenty-four hour clock, providing the shift-level building blocks that timecard entry, shift differential calculation, and workforce scheduling logic rely upon. Each row represents a discrete named shift — such as a day, evening, or night shift — together with its standard start and stop boundaries and effective dating.

From a dimensional modeling perspective, the metadata's Data Vault classification heuristic labels this object hub-leaning. This is because HXT_SHIFTS carries a stable business key (NAME, enforced by the HXT_SHIFTS_UK unique index) and is referenced as a lookup target by dependent transactional and assignment tables. In Data Vault terms it would therefore be modeled as a hub, with descriptive shift attributes (hours, start/stop boundaries, descriptions) placed in an associated satellite. This classification is a modeling suggestion rather than a documented Oracle design decision.

Key Information Stored

The table contains 16 documented columns in the 12.2.2 schema. The most significant are:

  • ID — the surrogate primary key (HXT_SHIFTS_PK) that uniquely identifies each shift and is the column referenced by foreign keys elsewhere.
  • NAME — the business-key candidate, protected by the HXT_SHIFTS_UK unique index. This is the human-readable shift identifier.
  • CODE — a short code used for classification or display in timecard and reporting interfaces.
  • DESCRIPTION — free-text elaboration of the shift's purpose or coverage.
  • HOURS — the total number of hours the shift spans.
  • STANDARD_START — the standard clock time the shift begins.
  • STANDARD_STOP — the standard clock time the shift ends.
  • EARLY_START — the earliest permissible start time, used for tolerance and eligibility checks.
  • LATE_STOP — the latest permissible stop time.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-bounded validity, supporting shift definition versioning over time.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns.

The distinction between ID (technical/surrogate key) and NAME (business/natural key) is important when writing queries that join or deduplicate shift data.

Common Use Cases and Queries

Typical uses include resolving a worker's assigned shift to its start/stop attributes for timecard validation, driving shift differential rules, and populating shift-level reporting. A representative query joins HXT_SHIFTS to the assignment table via the SHT_ID column:

  • Joining HXT_WORK_SHIFTS to HXT_SHIFTS on HXT_WORK_SHIFTS.SHT_ID = HXT_SHIFTS.ID to retrieve shift attributes for a given work shift.
  • Filtering by effective dates (SYSDATE BETWEEN EFFECTIVE_START_DATE AND NVL(EFFECTIVE_END_DATE, SYSDATE)) to return only the currently active shift definition.
  • Listing distinct shifts by NAME using the unique index for lookup and validation screens.
  • Aggregating HOURS to compute planned coverage across defined shifts.

Sample SQL: SELECT s.ID, s.NAME, s.STANDARD_START, s.STANDARD_STOP, s.HOURS FROM HXT.HXT_SHIFTS s WHERE s.EFFECTIVE_START_DATE <= SYSDATE AND NVL(s.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE ORDER BY s.NAME;

Related Objects

The documented foreign-key relationship shows that HXT_WORK_SHIFTS references this table through HXT_WORK_SHIFTS.SHT_ID pointing at HXT_SHIFTS. HXT_WORK_SHIFTS is therefore the primary dependent object and the key integration point between shift definitions and work-shift assignments. Beyond this documented relationship, the identification of HXT_SHIFTS.ID as the foreign key target means any Time and Labor object that carries an SHT_ID-style column — timecard entries, shift assignments, and shift differential configuration — depends on the rows defined here. Because the metadata lists only one referencing table, HXT_SHIFTS should be regarded as an upstream configuration hub consumed by downstream Time and Labor assignment and timecard processing objects.