Results for “per_pos_structure_elements”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The PER_POS_STRUCTURE_ELEMENTS table, owned by the HR schema within the PER (Human Resources) product family, stores the individual elements that define position hierarchies in Oracle EBS 12.1.1 and 12.2.2. Each row represents a parent-child (superior-subordinate) reporting relationship between two positions, allowing the application to reconstruct an entire position hierarchy tree for a given structure version. Position hierarchies in Oracle HRMS drive approvals routing, security profile enforcement at the position level, succession planning, and organizational reporting.

From a dimensional modeling perspective, the Data Vault classification for this table is link. This is a heuristic classification derived from the foreign key topology: the table resolves multiple hub references (positions, structure versions, and the business group) into association records, with little descriptive satellite content of its own. The row itself is the relationship, not the entity, which is the defining characteristic of a link table.

Key Information Stored

• POS_STRUCTURE_ELEMENT_ID — surrogate primary key (PER_POS_STRUCTURE_ELEMENTS_PK) uniquely identifying each hierarchy element row.
• POS_STRUCTURE_VERSION_ID — the position hierarchy (structure) version to which this element belongs. Combined with SUBORDINATE_POSITION_ID, this forms the business-key unique index PER_POS_STRUCTURE_ELEMENTS_UK2, ensuring a subordinate position appears only once per structure version.
• SUBORDINATE_POSITION_ID — the child position in the reporting relationship; the "lower" node of the link.
• PARENT_POSITION_ID — the superior position; the "upper" node of the link, which may itself be a subordinate in another row, producing the recursive tree.
• BUSINESS_GROUP_ID — the enterprise business group context, scoping the hierarchy element and enforcing multi-tenant separation.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent request and program audit columns identifying the process that last populated or refreshed the row.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard WHO audit columns tracking row creation and change.
• OBJECT_VERSION_NUMBER — optimistic-locking version token used by the HRMS APIs to detect concurrent modifications.

Common Use Cases and Queries

Typical usage involves traversing the hierarchy to derive reporting paths, list all descendants of a position, or validate that a proposed move does not introduce a cycle. A representative query retrieving the immediate children of a given parent position is:

• SELECT SUBORDINATE_POSITION_ID FROM PER_POS_STRUCTURE_ELEMENTS WHERE PARENT_POSITION_ID = :parent_pos_id AND POS_STRUCTURE_VERSION_ID = :version_id;

Recursive downward expansion (using CONNECT BY) is common for producing full reporting trees:

• SELECT PARENT_POSITION_ID, SUBORDINATE_POSITION_ID FROM PER_POS_STRUCTURE_ELEMENTS WHERE POS_STRUCTURE_VERSION_ID = :v START WITH PARENT_POSITION_ID = :root_pos CONNECT BY PRIOR SUBORDINATE_POSITION_ID = PARENT_POSITION_ID;

Reporting scenarios include position-based approval routing, headcount roll-ups by superior position, security profile validation (top position and its descendents), and auditing hierarchy changes between structure versions. Joins to PER_ALL_POSITIONS resolve position names, while joins to PER_POS_STRUCTURE_VERSIONS identify the hierarchy version and its date-effective status.

Related Objects

• PER_ALL_POSITIONS — referenced twice; PARENT_POSITION_ID and SUBORDINATE_POSITION_ID both join to this table to resolve the superior and child position records.
• PER_POS_STRUCTURE_VERSIONS — parent of POS_STRUCTURE_VERSION_ID; defines the named hierarchy and its validity dates.
• HR_ALL_ORGANIZATION_UNITS — parent of BUSINESS_GROUP_ID; identifies the business group owning the hierarchy element.
• PER_POS_STRUCTURE_ELEMENTS_PK / _UK2 — the primary and unique indexes enforcing row identity and the version-plus-subordinate rule.
• HRMS position hierarchy APIs and the Position Hierarchy form — consume this table to create, copy, and date-track position structures.