Results for “jl_br_local_holiday_sets”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

JL_BR_LOCAL_HOLIDAY_SETS is a table in the JL schema belonging to the Latin America Localizations product (JL), specifically supporting the Brazilian localization within Oracle E-Business Suite 12.1.1 and 12.2.2. The object represents logical groupings of Brazilian local holidays. Rather than storing individual holiday dates directly, the table functions as a header or container record: each row defines a named holiday set that can be associated with a state, and the individual holiday dates are held in a child table. This design allows a single named set (for example, a regional or state-specific calendar) to be reused across multiple date entries, and to be referenced by downstream payroll, HR, and reporting logic that needs to determine whether a given business day is a local holiday.

Based on the foreign-key structure mined from the metadata, the table is classified heuristically as hub-leaning in a Data Vault model. This is a modeling suggestion: the table primarily acts as a stable, business-key-identified entity (a hub) whose surrounding attributes and dependent date records sit in related structures. Treated as such, LOCAL_HOLIDAY_SET_ID would serve as the hub key, with the name/state combination acting as the business key and the descriptive and audit columns behaving satellite-like attributes.

Key Information Stored

The table contains 30 documented columns. The most significant are:

• LOCAL_HOLIDAY_SET_ID — The surrogate primary key, defined by the JL_BR_LOCAL_HOLIDAY_SETS_PK constraint and mirrored by the unique index JL_BR_LOCAL_HOLIDAY_SETS_U1. This is the value that child date records reference.
• LOCAL_HOLIDAY_SET_NAME and STATE — Together these form the documented business-key candidate via the unique index JL_BR_LOCAL_HOLIDAY_SETS_U2. The name identifies the holiday set, while STATE scopes it to a Brazilian state, ensuring that names are unique within a state.
• DESCRIPTION — A free-text description of the holiday set.
• DISABLE_DATE — Records a date on which the holiday set becomes inactive or is no longer applied, supporting time-effective selection of holiday calendars.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — The standard Oracle EBS descriptive flexfield (DFF) columns, providing extensibility for localization-specific or customer-defined attributes.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE — The standard concurrent program context columns, identifying the batch process that last created or updated the row.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN — Standard Oracle EBS who-columns providing audit and change-tracking information.

Common Use Cases and Queries

The principal use case is holiday determination for Brazilian payroll and HR processing. The application resolves a local holiday set by name and state, then joins to the date table to test whether a given day is a holiday. A representative query joins the header to its dates:

• SELECT s.local_holiday_set_name, s.state, d.holiday_date FROM jl.jl_br_local_holiday_sets s, jl.jl_br_local_holiday_dates d WHERE s.local_holiday_set_id = d.local_holiday_set_id AND s.state = :p_state;
• Reporting on active calendars uses DISABLE_DATE IS NULL OR DISABLE_DATE > SYSDATE to exclude retired sets.
• DFF extraction reports read ATTRIBUTE_CATEGORY and the ATTRIBUTE1..15 columns for localization-specific values.
• Concurrent-program auditing filters on REQUEST_ID or PROGRAM_ID to trace which process populated or modified a holiday set.

Related Objects

The most significant dependent object is the child date table, which carries the actual holiday dates and the foreign key back to this table:

• JL_BR_LOCAL_HOLIDAY_DATES — Child table joined on JL_BR_LOCAL_HOLIDAY_DATES.LOCAL_HOLIDAY_SET_ID = JL_BR_LOCAL_HOLIDAY_SETS.LOCAL_HOLIDAY_SET_ID. This is the only documented foreign-key relationship, and it establishes the header/detail pattern central to the object's role.
• JL_BR_LOCAL_HOLIDAY_SETS_PK and JL_BR_LOCAL_HOLIDAY_SETS_U1 — Key and unique constraints enforcing the surrogate identifier.
• JL_BR_LOCAL_HOLIDAY_SETS_U2 — The unique index on LOCAL_HOLIDAY_SET_NAME, STATE enforcing the business key.

Additional consumers of the local holiday calendar—payroll calendar and HR absence logic—reference these sets indirectly through the related date table and the localization setup that assigns a set to a state or organization. Together, JL_BR_LOCAL_HOLIDAY_SETS and JL_BR_LOCAL_HOLIDAY_DATES form the persistent definition of Brazilian local holiday calendars in EBS.