Search Results hxt_weekly_work_schedules




Overview
Explore in AI Chat

Overview

HXT.HXT_WEEKLY_WORK_SCHEDULES is a core reference table within the Oracle E-Business Suite Time and Labor module (product code HXT). As documented in the ETRM metadata, it serves as "a location to put a company's various employee work plans." In practical terms, the table stores the definition of weekly work schedules — the recurring patterns that describe which days employees are expected to work and how those schedules are anchored on a calendar. These work plans form the backbone against which Time and Labor evaluates reported time, derives expected hours, and applies schedule-based rules during time entry validation, overtime calculation, and payroll costing.

Based on the heuristic Data Vault classification mined from its foreign key structure, HXT_WEEKLY_WORK_SCHEDULES is hub-leaning. This implies that the table behaves primarily as a business concept hub, holding the durable identity of a work schedule (its surrogate key ID and its business key NAME within a business group), with dependent relationships (rotation schedules and work shifts) referencing it. The classification is a modeling suggestion, indicating that downstream satellite-style attributes and link-style associations naturally attach to this central entity.

Key Information Stored

The table contains 12 documented columns. The most significant are the following:

  • ID — the surrogate primary key, enforced by HXT_WEEKLY_WORK_SCHEDULES_PK. It uniquely identifies each weekly work schedule row and is the column referenced by dependent tables.
  • NAME — the user-defined schedule name; together with BUSINESS_GROUP_ID it forms the unique business key enforced by HXT_WEEKLY_WORK_SCHEDULES_UK. This guarantees that schedule names are unique within a business group.
  • BUSINESS_GROUP_ID — the multi-tenant partitioning column. Every schedule is owned by exactly one business group, supporting data security and reporting segmentation.
  • START_DAY — identifies the day of the week that the schedule is anchored to (for example, the first working day of the weekly pattern). This drives how the recurring weekly plan is aligned to the calendar.
  • DATE_FROM and DATE_TO — define the effective date range during which the schedule is active. These enable date-effective resolution of which schedule applies to an employee on any given day.
  • DESCRIPTION — free-text descriptive information to help administrators identify the purpose or characteristics of the schedule.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard Oracle EBS who-columns providing auditability of who created and last modified each schedule row.

The distinction between the surrogate key (ID) and the business key (NAME, BUSINESS_GROUP_ID) is important: joins between tables should use ID for referential integrity, while natural-key lookups and duplicate detection use the unique index columns.

Common Use Cases and Queries

The table is typically queried to resolve which weekly schedule applies to employees, to report on schedule definitions, and to audit configuration. A representative query listing active schedules for a business group is: 

SELECT id, name, start_day, date_from, date_to, description
FROM   hxt_weekly_work_schedules
WHERE  business_group_id = :p_bg_id
AND    TRUNC(SYSDATE) BETWEEN date_from
       AND NVL(date_to, TRUNC(SYSDATE));

Because dependent entities reference the schedule by TWS_ID, a common reporting pattern joins the schedule to its shifts and rotations to present a complete work plan: 

SELECT ws.name AS work_schedule, s.name AS shift_name
FROM   hxt_weekly_work_schedules ws,
       hxt_work_shifts s
WHERE  s.tws_id = ws.id
AND    ws.business_group_id = :p_bg_id;

Typical scenarios include auditing which schedules lack an END_DATE (open-ended plans), verifying that no two schedules within the same business group overlap for a given employee population, and feeding work-plan data into overtime and absence reporting extracts.

Related Objects

The FK metadata documents two dependent tables that reference this schedule hub by the column TWS_ID:

  • HXT_ROTATION_SCHEDULES — joins on HXT_ROTATION_SCHEDULES.TWS_ID = HXT_WEEKLY_WORK_SCHEDULES.ID, allowing weekly patterns to be combined into rotation-based plans.
  • HXT_WORK_SHIFTS — joins on HXT_WORK_SHIFTS.TWS_ID = HXT_WEEKLY_WORK_SCHEDULES.ID, linking individual shifts to the weekly schedule that governs them.

Together, these related objects complete the work-plan configuration model: a weekly work schedule provides the container, and its associated shifts and rotations define the detailed working pattern referenced throughout Time and Labor processing.