Search Results hr_calendars_uk1

Overview

HR.HR_CALENDARS is a transactional table in the Oracle E-Business Suite HR schema that stores calendar definitions built from repeating patterns. A calendar is constructed by anchoring a reusable pattern (defined in HR_PATTERNS) to a specific start date and time; the pattern can then be derived for any given date after that start point. This design allows the same underlying pattern to be reused across many calendars, optionally time-shifted — for example, by eight hours — to represent different shift patterns without duplicating pattern construction data.

The table is registered in FND Design Data as PER.HR_CALENDARS, confirming its role as a core object within the Oracle Time and Labor / PER (Human Resources) date-and-time framework. It resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 20. Under the heuristic Data Vault classification mined from its foreign key structure, HR_CALENDARS is classified as hub-leaning: it carries a system-generated surrogate key, a business-key unique constraint, and outbound references to a parent pattern, making it a natural candidate for modeling as a hub entity with associated descriptive attributes.

Key Information Stored

HR_CALENDARS contains 11 documented columns. The most significant are:

• CALENDAR_ID (NUMBER, mandatory) — System-generated primary key, enforced by the HR_CALENDARS_PK unique index. This is the surrogate identifier used by all dependent tables.
• CALENDAR_NAME (VARCHAR2(80), mandatory) — Unique name identifying the calendar. This is the business-key candidate, enforced by the HR_CALENDARS_UK1 unique index (also described historically as HR_CALENDARS_UK).
• PATTERN_ID (NUMBER) — Foreign key to HR_PATTERNS, defining which repeating pattern the calendar derives from. Indexed non-uniquely by HR_CALENDARS_FK1.
• PATTERN_START_POSITION (NUMBER) — The sequence number within the pattern construction at which the calendar begins, allowing a calendar to start partway through a pattern cycle.
• CALENDAR_START_TIME (DATE) — The start date and time of the calendar; combined with the pattern, this anchors all subsequent derived dates.
• OBJECT_VERSION_NUMBER (NUMBER) — System-generated row version, incremented by one on each update, supporting optimistic locking.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard Who columns capturing audit and provenance information.

The separation of CALENDAR_ID (surrogate) from CALENDAR_NAME (unique business key) means applications should resolve calendars by ID internally while exposing NAME to users.

Common Use Cases and Queries

Typical scenarios include retrieving the pattern and start anchor for a named calendar, reporting on calendars sharing a pattern, and joining calendar definitions to their usages.

• Resolve a calendar by business key:
  SELECT CALENDAR_ID, PATTERN_ID, CALENDAR_START_TIME FROM HR.HR_CALENDARS WHERE CALENDAR_NAME = :name;
• List all calendars derived from one pattern, using the FK index:
  SELECT CALENDAR_NAME, CALENDAR_START_TIME, PATTERN_START_POSITION FROM HR.HR_CALENDARS WHERE PATTERN_ID = :pattern_id ORDER BY CALENDAR_START_TIME;
• Join to usages to find where a calendar is assigned:
  SELECT c.CALENDAR_NAME, u.* FROM HR.HR_CALENDARS c JOIN HR.HR_CALENDAR_USAGES u ON u.CALENDAR_ID = c.CALENDAR_ID;
• Detect stale rows for integration monitoring:
  SELECT CALENDAR_ID, LAST_UPDATE_DATE FROM HR.HR_CALENDARS WHERE LAST_UPDATE_DATE >= :since;

Because calendars are frequently referenced by Time and Labor and scheduling logic, queries filtering on CALENDAR_ID benefit from HR_CALENDARS_PK, while lookups by CALENDAR_NAME are served by HR_CALENDARS_UK1.

Related Objects

The following objects are most significant to HR_CALENDARS based on the documented relationship data:

• HR.HR_PATTERNS — Parent of HR_CALENDARS via PATTERN_ID; the pattern that supplies the recurring construction.
• HR.HR_CALENDAR_USAGES — Child table referencing HR_CALENDARS.CALENDAR_ID; records assignments of a calendar to organizational or resource contexts.
• HR.HR_EXCEPTION_USAGES — Child table referencing HR_CALENDARS.CALENDAR_ID; associates exception definitions with a calendar.
• HR_CALENDARS_PK / HR_CALENDARS_UK1 / HR_CALENDARS_FK1 — Indexes supporting primary, unique, and foreign-key access respectively.
• PER (FND Design Data) — The product family owning the underlying calendar and pattern business logic and APIs.

Together, these objects form the calendar hub-and-satellite cluster within the HR schema, anchored by HR_CALENDARS as the central definition table.