Search Results hr_calendars_pk




Overview
Explore in AI Chat

Overview

HR_CALENDARS is a core Human Resources (PER) table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores dated instances of a repeating pattern. In Oracle HRMS, a "pattern" (defined in HR_PATTERNS) describes a recurring schedule of work and non-work periods, while HR_CALENDARS anchors that pattern to a concrete, usable calendar with a defined start point and start time. This table therefore bridges abstract scheduling definitions and the operational calendars consumed by leave accruals, payroll periods, work schedules, and exception handling.

Under the heuristic Data Vault classification mined from the foreign key structure, HR_CALENDARS is hub-leaning. It behaves as a business entity hub: its own identity is defined by CALENDAR_ID, its descriptive attributes are minimal, and it functions primarily as the anchor to which dependent satellite and link-style records (calendar usages and exception usages) attach. Modelers should treat it as a hub reference point rather than as a transactional or history-bearing table.

Key Information Stored

The documented physical schema (ETRM 12.2.2, owner HR) contains 11 columns. The most significant are:

The surrogate key (CALENDAR_ID) and the business key (CALENDAR_NAME) are distinct: integrations and lookups should ideally resolve by name, while all physical joins use CALENDAR_ID.

Common Use Cases and Queries

Typical uses include resolving a calendar name to its ID for a leave or payroll process, listing all exceptions applied to a calendar, and reporting which calendars are in active use. A representative lookup:

  • SELECT CALENDAR_ID, PATTERN_ID, PATTERN_START_POSITION, CALENDAR_START_TIME FROM HR_CALENDARS WHERE CALENDAR_NAME = :p_name;
  • SELECT c.CALENDAR_NAME, p.PATTERN_NAME FROM HR_CALENDARS c JOIN HR_PATTERNS p ON p.PATTERN_ID = c.PATTERN_ID;
  • SELECT c.CALENDAR_NAME, u.* FROM HR_CALENDARS c JOIN HR_CALENDAR_USAGES u ON u.CALENDAR_ID = c.CALENDAR_ID;

Reporting on exception usage is also common, joining HR_EXCEPTION_USAGES to filter calendars by the exceptions applied to them.

Related Objects

The following objects are the most significant dependencies based on the documented key relationships:

  • HR_PATTERNS — referenced by HR_CALENDARS.PATTERN_ID; the source repeating pattern.
  • HR_CALENDAR_USAGES — references HR_CALENDARS.CALENDAR_ID; records where each calendar is used.
  • HR_EXCEPTION_USAGES — references HR_CALENDARS.CALENDAR_ID; records exceptions applied to each calendar.

Joins between these tables are consistently made on CALENDAR_ID, with PATTERN_ID linking back to the pattern definition.