Search Results hxt_rotation_schedules




Overview
Explore in AI Chat

Overview

HXT_ROTATION_SCHEDULES is a Time and Labor (HXT) table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores the detailed definition of rotating work plan schedules. A rotation plan allows an organization to assign employees to repeating patterns of weekly work schedules — for example, one week on a day shift, the next week on a swing shift, followed by a week off. HXT_ROTATION_SCHEDULES holds the effective-dated rows that tie a rotation plan to the specific weekly work schedule that applies from a given start date forward. The table therefore acts as the bridge between the high-level plan definition and the concrete weekly template used to derive scheduled hours, shift differentials, and eligibility for overtime or premium pay.

Under a heuristic Data Vault classification mined from the foreign-key structure, this object is best modeled as a link. It records a many-to-many style association between two business entities — rotation plans and weekly work schedules — and each row is a relationship instance rather than a standalone business concept. It carries descriptive attributes such as creation and update audit columns, but its principal identity is associative, joining the plan to the weekly template at a particular effective point in time.

Key Information Stored

The documented physical schema exposes eight columns. The most significant are:

  • RTP_ID — Foreign key to HXT_ROTATION_PLANS. Identifies the parent rotation plan to which this schedule step belongs. It is also part of the composite primary key.
  • START_DATE — The effective date from which the associated weekly schedule applies within the rotation. It is the second component of the composite primary key, making the pair (RTP_ID, START_DATE) unique.
  • TWS_ID — Foreign key to HXT_WEEKLY_WORK_SCHEDULES. Identifies the weekly work schedule (shift pattern, day-on/day-off structure, and hours per day) that is in force beginning on START_DATE.
  • CREATED_BY, CREATION_DATE — Standard audit columns recording the user and timestamp of initial row creation.
  • LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard audit columns recording the most recent modification and the login session responsible.

The surrogate/primary key is defined by the unique index HXT_ROTATION_SCHEDULES_PK on (RTP_ID, START_DATE). Because the key is composite and business-meaningful, (RTP_ID, START_DATE) also serves as the business-key candidate: no rotation plan may have two schedule steps with the same effective start date.

Common Use Cases and Queries

Typical scenarios include reconstructing the historical rotation for an employee group, validating that no overlapping effective dates exist within a plan, and reporting which weekly schedule was active on a given date. A representative query lists each schedule step for a plan along with its weekly template:

  • SELECT rs.RTP_ID, rs.START_DATE, rs.TWS_ID FROM HXT_ROTATION_SCHEDULES rs WHERE rs.RTP_ID = :p_rtp_id ORDER BY rs.START_DATE;
  • Join to HXT_ROTATION_PLANS rp ON rp.RTP_ID = rs.RTP_ID to retrieve the plan name and description.
  • Join to HXT_WEEKLY_WORK_SCHEDULES tvs ON tvs.TWS_ID = rs.TWS_ID to retrieve the weekly pattern details for each effective step.
  • Determine the applicable schedule for a date by selecting the row with the greatest START_DATE that is less than or equal to the target date, partitioned by RTP_ID.

Because effective dating drives Time and Labor calculations, reporting tools and interfaces should always resolve the correct row by START_DATE rather than assuming a single schedule per plan.

Related Objects

  • HXT_ROTATION_PLANS — Parent plan definition; joined on RTP_ID (RTP_ID in this table references it).
  • HXT_WEEKLY_WORK_SCHEDULES — Weekly template referenced by TWS_ID.
  • HXT_ROTATION_SCHEDULES_PK — Primary key unique index on (RTP_ID, START_DATE).
  • Time and Labor retrieval and schedule-assignment processes that consume rotation plans for timecard generation.
  • Reporting and extraction views in the HXT schema that expose rotation assignments to payroll and HR consumers.