Search Results hxt_work_shifts




Overview
Explore in AI Chat

Overview

HXT_WORK_SHIFTS is a core configuration table in the Oracle EBS Time and Labor (HXT) module. It defines the individual work shifts assigned to a weekly work schedule, serving as the foundation upon which employee earnings, timecard processing, and shift differential calculations are based. Each row represents a single shift position within a weekly schedule, capturing where and when the shift occurs and how early or late an employee may clock in or out relative to it.

The table resides in the HXT schema and is documented as VALID in both EBS 12.1.1 and 12.2.2. Its physical structure spans 13 columns. Based on the foreign key topology mined from its constraints, the object is heuristically classified in a Data Vault model as a link, since it resolves a many-to-many association between shifts and weekly work schedules. The heuristic classification should be treated as a modeling suggestion rather than a hard rule; the table may also be modeled as a satellite keyed against the weekly schedule if the business prefers a schedule-centric grain.

Key Information Stored

The primary key is defined by the unique index HXT_WORK_SHIFTS_PK, composed of three columns: SHT_ID, TWS_ID, and SEQ_NO. This composite key establishes the grain of the table, ensuring each shift is uniquely positioned within its parent weekly schedule at a defined sequence.

  • SHT_ID — Foreign key to HXT_SHIFTS, identifying the shift definition (such as a shift name or code) being assigned.
  • TWS_ID — Foreign key to HXT_WEEKLY_WORK_SCHEDULES, identifying the weekly schedule that owns this shift position.
  • SEQ_NO — The sequence number placing this shift within the weekly schedule, part of the primary key.
  • WEEK_DAY — Indicates the day of the week on which the shift falls.
  • EARLY_START — The tolerance window allowing an employee to begin work before the scheduled shift start.
  • LATE_STOP — The tolerance window allowing an employee to remain on the clock after the scheduled shift end.
  • OFF_SHIFT_PREM_ID — Reference to an off-shift premium, used to drive shift differential earnings.
  • SHIFT_DIFF_OVRRD_ID — Reference to a shift differential override applied to this shift instance.
  • LAST_UPDATE_DATE / LAST_UPDATED_BY — Standard audit columns recording the most recent modification and the user who made it.
  • CREATED_BY / CREATION_DATE — Standard audit columns capturing creation metadata.
  • LAST_UPDATE_LOGIN — Audit column capturing the login identifier associated with the last update.

The EARLY_START and LATE_STOP columns are notable in that they determine the punch tolerance logic applied during timecard evaluation, while OFF_SHIFT_PREM_ID and SHIFT_DIFF_OVRRD_ID link the shift to the earnings rules that ultimately compute pay.

Common Use Cases and Queries

Typical reporting scenarios include reconciling shifts against their weekly schedule, auditing tolerance windows, and tracing shift differential configuration through to payroll earnings. A representative join pattern follows:

  • Listing all shifts for a given weekly schedule — SELECT * FROM hxt_work_shifts WHERE tws_id = :p_tws_id ORDER BY seq_no;
  • Joining to shift definitions to resolve shift names — SELECT a.tws_id, a.week_day, a.seq_no, s.shift_name FROM hxt_work_shifts a JOIN hxt_shifts s ON a.sht_id = s.sht_id;
  • Auditing tolerance windows — filtering on early_start or late_stop to identify shifts with extended or minimal punch tolerance.
  • Reconciling differential configuration — joining off_shift_prem_id and shift_diff_ovrrd_id to premium and override tables to validate earnings behavior.

Related Objects

The most significant dependencies flow through the documented foreign keys and the schedule hierarchy that consumes these shift assignments:

  • HXT_SHIFTS — joined via HXT_WORK_SHIFTS.SHT_ID; provides the shift definition being placed.
  • HXT_WEEKLY_WORK_SCHEDULES — joined via HXT_WORK_SHIFTS.TWS_ID; the parent schedule owning the shift rows.
  • HXT_SHIFT_PREMIUMS / premium definitions — referenced by OFF_SHIFT_PREM_ID for differential eligibility.
  • HXT_SHIFT_DIFF_OVERRIDES — referenced by SHIFT_DIFF_OVRRD_ID for shift-level overrides.
  • Work schedule assignment and timecard tables that inherit shift attributes for earnings computation.

Together these objects form the shift-to-schedule-to-earnings chain that underlies Time and Labor processing.