Search Results get_sql_from_vset_id

Overview

The APPS.HR_CAL_ENTRY_VALUE_API package body provides the supported programmatic interface for creating, maintaining, and querying calendar entry values within Oracle E-Business Suite HRMS. Calendar entries represent discrete, date-effective events or values attached to a calendar — for example, shift patterns, scheduled work events, or other calendar-defined constructs that drive time and attendance, payroll, and workforce scheduling logic. The package encapsulates the business rules and validation required to insert, change, or remove these values safely, so that any consumer — Oracle Forms, concurrent programs, or custom PL/SQL — operates against a consistent, controlled data model rather than manipulating the underlying tables directly.

The package is classified as an API in the ETRM repository and is referenced by five other packages, confirming its role as a reusable building block within the HR calendar infrastructure rather than an end-user-facing component.

Key Procedures and Functions

• CREATE_ENTRY_VALUE — Creates a new calendar entry value record. The header comment identifies it as the primary entry point of the package, and it accepts a validation flag, an effective date, the parent calendar entry, usage and value attributes, hierarchy and organization structure information, override details, and an identifier key. It returns the newly created entry value identifier and object version number.
• UPDATE_ENTRY_VALUE — Modifies an existing calendar entry value, enforcing date-effective update semantics and object version number checking.
• DELETE_ENTRY_VALUE — Removes a calendar entry value record through the supported API path.
• GET_DISPLAY_VALUE — Returns a formatted, user-readable representation of an entry value, typically used by forms or reports.
• GET_G_CURRENT_ENTRY_ID / SET_G_CURRENT_ENTRY_ID — Accessor and mutator for the package-level global that tracks the calendar entry currently being processed.
• GET_G_CURRENT_OSV_ID / SET_G_CURRENT_OSV_ID — Accessor and mutator for the package-level global holding the current organization structure element identifier context.
• GET_NODE_LEVEL — Returns the level of a given node within the generated hierarchy.
• GET_ELE_LEVEL — Returns the level of an organization structure element in the hierarchy.
• GET_SQL_FROM_VSET_ID — Derives or returns the SQL text associated with a given value set identifier. This function supports dynamic value list generation, where the value set definition drives the query used to validate or present allowable entry values.

Tables Accessed

• PER_CAL_ENTRY_VALUES — The principal table written by this package; stores the entry value rows themselves.
• PER_CALENDAR_ENTRIES — The parent calendar entry records to which values are attached.
• PER_GEN_HIERARCHY_NODES and PER_GEN_HIER_NODE_TYPES — Supply hierarchy node and node-type metadata used when an entry value is associated with a hierarchy node.
• PER_ORG_STRUCTURE_ELEMENTS — Provides organization structure element context when the entry value is scoped to a specific organizational unit.
• FND_FLEX_VALUE_SETS — Read to resolve value set definitions referenced by the key flexfield behind value validation and the GET_SQL_FROM_VSET_ID function.

Usage Notes

The package is invoked whenever calendar entry values must be maintained with full validation against HRMS date-tracking, hierarchy, and value set rules. Typical callers include Oracle HRMS forms that manage calendar definitions, concurrent programs that mass-load or regenerate calendar data, and custom extensions that need to create or amend entry values without bypassing business logic. The procedural header confirms the standard HRMS API pattern: a savepoint is issued on entry, and a Before Process user hook (HR_CAL_ENTRY_VALUE_BK1) is called ahead of the core DML, allowing customers to attach custom validation. The object version number returned by create and update operations supports optimistic locking and is required for subsequent updates. Because the package exposes global state through setter and getter pairs, callers operating in multi-record scenarios should set the current entry and organization structure element identifiers explicitly to avoid context leakage between operations.