Search Results per_standard_holidays




Overview
Explore in AI Chat

Overview

PER_STANDARD_HOLIDAYS is a statutory holiday definition table within the Oracle E-Business Suite Human Resources (PER) module. Owned by the HR schema, it stores the master list of standard or statutory holidays that apply to a legislative jurisdiction, such as national, regional, or sub-legislative public holidays. Each row represents a single holiday definition, including its name, date (or date range), holiday type, and the legislation under which it is recognized. The table is a foundational reference object for absence and leave processing: holiday records are consumed by the absence calculation engine to determine whether a given day is a working day or a non-working statutory holiday.

The table is classified as VALID in the ETRM 12.2.2 physical schema and contains 37 columns. From a heuristic Data Vault modeling perspective, this object is hub-leaning: its single-column primary key, STANDARD_HOLIDAY_ID, is a stable surrogate identifier that anchors holiday identities, while date- and legislation-specific descriptive attributes attach as dependent detail. The classification is a modeling suggestion derived from the foreign-key structure, not a directive of the physical schema.

PER_STANDARD_HOLIDAYS is date-effective in nature: the HOLIDAY_DATE and HOLIDAY_DATE_END columns allow a holiday to span multiple days or recurrences, and the ZD_EDITION_NAME column supports the multi-versioned, edition-based data model used in later EBS releases.

Key Information Stored

The most significant columns in the table are:

  • STANDARD_HOLIDAY_ID — the surrogate primary key (PER_STANDARD_HOLIDAYS_PK) that uniquely identifies each holiday record.
  • ZD_EDITION_NAME — part of the unique index (PER_STANDARD_HOLIDAYS_PK), used as a business-key candidate alongside the primary key for editioned data.
  • NAME — the descriptive name of the holiday (e.g., "New Year's Day").
  • HOLIDAY_DATE — the start date on which the holiday falls.
  • HOLIDAY_DATE_END — the end date for holidays that span more than one day.
  • HOLIDAY_TYPE — classification of the holiday (statutory, floating, etc.).
  • LEGISLATION_CODE — the legislative jurisdiction under which the holiday is recognized.
  • SUB_LEGISLATION_CODE — the finer-grained sub-jurisdiction for the holiday.
  • ALTERNATIVES_PERMITTED — flag indicating whether an alternate date may be substituted for the holiday.
  • BUSINESS_GROUP_ID — the business group to which the holiday definition belongs.
  • OBJECT_VERSION_NUMBER — optimistic locking version for concurrent updates.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE — standard audit columns present on all EBS transactional tables.
  • ATTRIBUTE1–ATTRIBUTE20 and ATTRIBUTE_CATEGORY — the standard descriptive flexfield (DFF) columns enabling customer-defined extensions.

The surrogate primary key is STANDARD_HOLIDAY_ID; the unique business-key candidate is the composite of STANDARD_HOLIDAY_ID and ZD_EDITION_NAME. No separate human-readable unique constraint is documented on NAME or the date columns.

Common Use Cases and Queries

The most common application of PER_STANDARD_HOLIDAYS is holiday lookups during absence and leave administration. A typical query retrieves all holidays for a given period and jurisdiction:

  • Holiday calendar extract: SELECT STANDARD_HOLIDAY_ID, NAME, HOLIDAY_DATE, HOLIDAY_DATE_END, HOLIDAY_TYPE FROM PER_STANDARD_HOLIDAYS WHERE LEGISLATION_CODE = :p_legislation AND HOLIDAY_DATE BETWEEN :p_start AND :p_end ORDER BY HOLIDAY_DATE;
  • Business-group-scoped report: SELECT NAME, HOLIDAY_DATE FROM PER_STANDARD_HOLIDAYS WHERE BUSINESS_GROUP_ID = :p_bg_id AND TRUNC(HOLIDAY_DATE) = TRUNC(SYSDATE);
  • Alternate-date audit: query rows where ALTERNATIVES_PERMITTED is enabled to review flexible holiday substitution rules.
  • Absence linkage: join to PER_STD_HOLIDAY_ABSENCES on STANDARD_HOLIDAY_ID to reconcile which absences were recorded against a given statutory holiday.

Reporting use cases include statutory holiday liability reporting, absence accrual validation, and reconciliation of public holiday calendars by legal entity. Because the table carries DFF attributes, customers commonly expose ATTRIBUTE columns in custom holiday interfaces and integrations.

Related Objects

The documented foreign-key relationship identifies PER_STD_HOLIDAY_ABSENCES as the primary dependent object. Its STANDARD_HOLIDAY_ID column references this table, linking recorded absences to the statutory holiday they observe. This join supports reconciliation between holiday definitions and actual absence transactions.

Additional related objects, based on the PER module context in which the table operates, include:

These relationships should be validated against the exact business-group and legislative configuration of the target EBS instance before being used in production queries.