Search Results level_number_path




Overview
Explore in AI Chat

Overview

APPS.PER_CAL_ORG_HIER_VALUES_V is a supplementary database view in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 releases. It belongs to the PER (Human Resources) product family and is registered as FND Design Data PER.PER_CAL_ORG_HIER_VALUES_V with a status of VALID. The view is explicitly classified as a "supplementary view used to simplify forms coding," meaning it was created to support the Oracle Forms-based user interface rather than as a public integration or reporting interface. Oracle Corporation documents this object as Internal Use Only and does not support direct access to application data through it except from standard Oracle Applications programs. Oracle further warns that querying or altering data through this view is not recommended because its definition may change dramatically across minor or major releases.

In practice, the view exposes a flattened, denormalized projection of organization hierarchy definitions tied to calendar entry values. It presents the organization structure, its version, the parent and child organization identifiers, and a path-style level descriptor, allowing forms and downstream logic to traverse organizational roll-up structures without issuing multiple recursive queries.

Underlying Base Objects

The ETRM metadata records that PER_CAL_ORG_HIER_VALUES_V is defined over the following objects, all owned or resolved through the APPS schema:

The view is not referenced by any other database object, confirming its role as a terminal, presentation-oriented construct rather than a building block for other views or packages.

Key Columns

The view exposes sixteen columns. The principal identifiers include ORGANIZATION_STRUCTURE_ID, ORG_STRUCTURE_VERSION_ID, VERSION_NUMBER, ORG_STRUCTURE_ELEMENT_ID, ORGANIZATION_ID_PARENT, and ORGANIZATION_ID_CHILD, which together describe a hierarchical relationship within a versioned organization structure. BUSINESS_GROUP_ID scopes the structure to a business group. DISPLAY_VALUE (VARCHAR2 240) carries the human-readable label, while OVERRIDE_NAME and OVERRIDE_TYPE allow alternate naming. CAL_ENTRY_VALUE_ID and PARENT_ENTRY_VALUE_ID link the hierarchy node to calendar entry values and their parent entries. SELECT_FLAG and USAGE_FLAG are control indicators used by the forms layer.

The column of particular interest to users searching on "level_number_path" is LEVEL_NUMBER_PATH, a VARCHAR2(4000) column. This column materializes the hierarchical path of level numbers for each node, enabling reports and forms to display or sort organization hierarchies by depth without recursive SQL.

Common Use Cases and Queries

Because the view is internal and unsupported for direct access, the sanctioned use cases are limited to diagnostics, data lineage verification, and controlled reporting within a development or support context. A typical query retrieves the hierarchy path for a given structure version:

  • SELECT organization_structure_id, organization_id_parent, organization_id_child, display_value, level_number_path FROM apps.per_cal_org_hier_values_v WHERE organization_structure_id = :p_structure_id ORDER BY level_number_path;
  • SELECT business_group_id, name, version_number, level_number_path FROM apps.per_cal_org_hier_values_v WHERE business_group_id = :p_bg_id AND usage_flag = 'Y';
  • SELECT cal_entry_value_id, parent_entry_value_id, override_name, level_number_path FROM apps.per_cal_org_hier_values_v WHERE org_structure_element_id = :p_element_id;

These queries illustrate how LEVEL_NUMBER_PATH supports ordered traversal and reporting of organization hierarchies. Given Oracle's stated warning, production code should rely on supported HR APIs rather than this view.