Search Results pos_level

Overview

The APPS.HRBV_POSITION_DESCENDENTS view is a hierarchical reporting object within the PER (Human Resources) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It flattens the recursive parent-child relationships defined in the position structure into an explicit, query-friendly hierarchy, exposing each subordinate position together with its parent, the governing structure version, and a computed depth level. The suffix "BV" follows Oracle's business view naming convention, and the view is documented in ETRM with the note "- Retrofitted", indicating that the object was re-created or re-pointed to align with a later data model while preserving the earlier interface contract.

The view is primarily consumed by Oracle HRMS forms, concurrent programs, and customer extensions that need to traverse a position hierarchy without writing CONNECT BY logic themselves. Because it pre-resolves the secure business group and the correct structure version, it isolates callers from the underlying versioning model, which is significant since a single position can participate in multiple position structures and each structure can have more than one active version. The view is read-only, as confirmed by the WITH READ ONLY clause in its definition, so it is strictly a query and reporting surface.

Underlying Base Objects

The ETRM metadata lists two referenced base objects: the package HR_BIS and the synonym PER_POS_STRUCTURE_ELEMENTS. The synonym resolves to the PER_POS_STRUCTURE_ELEMENTS base table, which stores the position structure element rows: one row per parent position, subordinate position, business group, and position structure version. The view is a thin, recursive projection over that single table; no joins to PER_POSITIONS or PER_POSITION_STRUCTURES appear in the documented view text.

HR_BIS is the Business Intelligence System security package. The view invokes HR_BIS.GET_SEC_PROFILE_BG_ID inside the WHERE clause to apply the caller's security profile. The predicate STR.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, STR.BUSINESS_GROUP_ID) restricts rows to the business group granted by the user's security profile, or returns all business groups when the function yields NULL, which is the typical behavior for a global or unrestricted profile. This design ensures that hierarchy queries executed through the view automatically honor HR security.

Key Columns

• BUSINESS_GROUP_ID — The business group owning the position structure element; the primary partitioning key for security and for correct multi-organization results.
• PARENT_POSITION_ID — The superior position in the relationship for the current row, i.e., the immediate parent of POSITION_ID at this level of the hierarchy.
• POSITION_ID — The subordinate position identifier. This is the column named SUBORDINATE_POSITION_ID in the base table, aliased to POSITION_ID for convenience.
• POS_STRUCTURE_VERSION_ID — The specific position structure version under which the relationship exists. This is the column most frequently used to constrain queries, because a position's parent may differ between structure versions.
• POS_LEVEL — The hierarchy depth, returned by the CONNECT BY LEVEL pseudocolumn. Top-level positions begin at level 1.

Common Use Cases and Queries

Typical usage includes determining the reporting chain above a position, enumerating all positions reporting beneath a given manager within a specific structure version, and feeding HRMS extracts or third-party integrations. Because the hierarchy logic is embedded, callers typically filter on POS_STRUCTURE_VERSION_ID, which is precisely the column users search for when exploring this object.

The following query lists all descendants of a given parent within one structure version:

• SELECT pos_level, parent_position_id, position_id FROM apps.hrbv_position_descendents WHERE pos_structure_version_id = :version_id AND parent_position_id = :parent_id ORDER BY pos_level;
• SELECT position_id, MIN(pos_level) lvl FROM apps.hrbv_position_descendents WHERE pos_structure_version_id = :version_id GROUP BY position_id;

Because POS_STRUCTURE_VERSION_ID is exposed on every row, it serves as the natural partition for version-specific hierarchy reporting, and joins back to PER_POS_STRUCTURE_VERSIONS enable date-effective and status-aware reporting on top of the view.