Search Results position_hierarchy_name




Overview
Explore in AI Chat

Overview

APPS.HRFV_POSITION_HIERARCHIES is a read-only Oracle EBS view that exposes the flattened, versioned position hierarchy structure maintained by Oracle Human Resources. Each row represents a single parent-child relationship (an edge) within a position hierarchy for a specific hierarchy version and business group, rather than one row per hierarchy. The view is primarily used for reporting and integration when consumers need to traverse or join position hierarchies by name, level, or effective dates.

Because the object name surfaced in a search for position_hierarchy_name, it is typically queried to resolve the human-readable hierarchy name, the child position name, and the parent position name in a single result set. The view is defined WITH READ ONLY, so it cannot be used as a DML target. It is visible to the APPS schema and is generally accessed through the Oracle EBS security profile mechanism, which restricts returned rows to the business group determined by hr_bis.get_sec_profile_bg_id.

Underlying Base Objects

The view is constructed over six documented objects. The hierarchy definition itself is held in PER_POSITION_STRUCTURES, with effective versioning in PER_POS_STRUCTURE_VERSIONS. The parent-child edges are sourced from HRBV_POSITION_DESCENDENTS, which supplies the position identifier, parent position identifier, and hierarchy level for each edge. Descriptive names are resolved from the translation tables HR_ALL_POSITIONS_F_TL for both child and parent positions, and from HR_ALL_ORGANIZATION_UNITS_TL for the business group name, each filtered by userenv('LANG'). The HR_BIS package supplies the security profile business group identifier used in the WHERE clause.

Joins are enforced on position_structure_version_id, position_structure_id, business_group_id, and position_id, ensuring that the version, structure, position names, and business group are all consistent for a given row.

Key Columns

  • position_hierarchy_name — the name of the position structure, from PER_POSITION_STRUCTURES.NAME; this is the column returned when users search for the hierarchy by name.
  • business_group_name — the business group (organization) name associated with the hierarchy.
  • child_position_name and parent_position_name — the descriptive position names for the edge; the child name is prefixed with a dash-padded string representing the level.
  • position_hierarchy_level — the depth of the child position within the hierarchy, derived from HRBV_POSITION_DESCENDENTS.POS_LEVEL.
  • hierarchy_version_start_date and hierarchy_version_end_date — the effective dating of the hierarchy version.
  • position_hierarchy_version — the version number of the hierarchy.
  • business_group_id, parent_position_id, child_position_id, position_hierarchy_id, and pos_hierarchy_version_id — the surrogate keys for joining to other HR objects.

Common Use Cases and Queries

Typical uses include reporting the full reporting line for a position, validating hierarchy definitions, and extracting hierarchy data for downstream systems. The following query lists all edges for a named hierarchy:

  • SELECT position_hierarchy_name, position_hierarchy_level, parent_position_name, child_position_name FROM apps.hrfv_position_hierarchies WHERE position_hierarchy_name = '&hierarchy_name' ORDER BY position_hierarchy_level, child_position_name;

To identify hierarchies effective on a given date, filter on the version date columns:

  • SELECT DISTINCT position_hierarchy_name, position_hierarchy_version FROM apps.hrfv_position_hierarchies WHERE TRUNC(SYSDATE) BETWEEN hierarchy_version_start_date AND NVL(hierarchy_version_end_date, TRUNC(SYSDATE));

Because rows are already secured to the session business group, no additional business group predicate is normally required, although an explicit business_group_id filter is useful for cross-business-group reporting when the security profile permits it.