Search Results per_cal_org_hier_values_v




Overview
Explore in AI Chat

Overview

The APPS.PER_CAL_ORG_HIER_VALUES_V view is a Human Resources (PER) reporting object that exposes calendar entry values associated with the elements of an organization hierarchy structure. It joins organization structure definitions, their versions, the parent-child elements that make up each version, the organization units themselves, and the calendar entry values assigned to those elements. The view is defined as a UNION ALL of two query blocks, the first returning elements that have a matching calendar entry value at the currently active structure version and calendar entry context, and the second returning a synthetic "root" row (denoted by the literal parent identifier -987123654 and an element level of '0..') so that the hierarchy is presented with a consistent top anchor.

Because the view filters on the API function HR_CAL_ENTRY_VALUE_API.GET_G_CURRENT_OSV_ID and HR_CAL_ENTRY_VALUE_API.GET_G_CURRENT_ENTRY_ID, its output is bound to the "current" organization structure version and calendar entry as maintained by session-level globals in the package. This makes the view well suited to Oracle EBS reporting and integration scenarios that need the effective, currently selected hierarchy rather than historical versions. The view is documented as VALID in the APPS schema under the PER product for both 12.1.1 and 12.2.2.

Underlying Base Objects

The documented object references for the view are:

The wiring is a chain of inner joins from structure to version to element to organization unit, with outer joins (indicated by the (+) markers) to the calendar entry values so that hierarchy elements lacking an explicit entry value still appear, with a DECODE that returns 'N' when no value exists and 'Y' when one is present.

Key Columns

  • ORGANIZATION_STRUCTURE_ID / NAME / BUSINESS_GROUP_ID — identity and business group of the owning structure definition.
  • ORG_STRUCTURE_VERSION_ID / VERSION_NUMBER — the structure version being reported; the version is always the current one per the API filter.
  • ORG_STRUCTURE_ELEMENT_ID — the hierarchy element (parent-child pair) key.
  • ORGANIZATION_ID_PARENT / ORGANIZATION_ID_CHILD — the two organization units that the element connects.
  • NAME — descriptive name of the organization unit (child in the first branch, parent in the synthetic branch).
  • CAL_ENTRY_VALUE_ID — the calendar entry value identifier, NULL when no value is assigned.
  • DECODE(...) — an inline flag yielding 'Y' or 'N' indicating whether a calendar entry value exists.
  • PARENT_ENTRY_VALUE_ID, OVERRIDE_NAME, OVERRIDE_TYPE, USAGE_FLAG — calendar entry attributes governing inheritance, naming overrides, and applicability.
  • HR_CAL_ENTRY_VALUE_API.GET_ELE_LEVEL(...) — the computed level of the element within the hierarchy (literal '0..' for the synthetic root row).

Common Use Cases and Queries

Typical uses include generating hierarchical listings of calendar entry values for the active structure version, validating that all elements carry the intended entry values, and feeding integrations that need the current hierarchy flattened with its calendar attributes.

Sample query returning elements with their level and value flag:

  • SELECT organization_structure_id, version_number, organization_id_parent, organization_id_child, name, cal_entry_value_id, org_structure_element_id FROM per_cal_org_hier_values_v WHERE business_group_id = :p_bg_id ORDER BY organization_id_parent, organization_id_child;

To isolate elements missing a calendar entry value:

  • SELECT organization_id_child, name FROM per_cal_org_hier_values_v WHERE cal_entry_value_id IS NULL;

To list entries for a specific structure name and version:

  • SELECT version_number, name, override_name, usage_flag FROM per_cal_org_hier_values_v WHERE name = :p_structure_name ORDER BY version_number;

Because the view is driven by session globals in HR_CAL_ENTRY_VALUE_API, callers should ensure the concurrent request or form context has set the current structure version and calendar entry before relying on the returned rows; otherwise the result set may be empty or reflect a different selection than expected.