Results for “org_structure_element_id”

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

Overview

The PER_ORG_STRUCTURE_ELEMENTS table resides in the HR schema and belongs to the PER – Human Resources product family within Oracle E-Business Suite 12.1.1 and 12.2.2. It stores organization hierarchy details, capturing the parent-child reporting relationships between organizations defined in the enterprise. Each row represents a single structural element within a versioned organization hierarchy, linking one parent organization to one child organization.

The table operates under Oracle's multi-version hierarchy model, where hierarchy definitions are published as discrete versioned structures. Every element is scoped both to a business group and to a specific organization structure version, allowing historical hierarchies to coexist with the active configuration. This versioning design lets organizations model reorganizations over time without destroying prior structure definitions.

Using heuristic Data Vault classification mined from its foreign key topology, this table is best modeled as a link object. It resolves a many-to-many style relationship between organization units (parent and child) within the context of a structure version, and therefore behaves as an associative entity rather than an independent hub or a descriptive satellite.

Key Information Stored

The table contains seventeen documented columns. The most significant are summarized below.

• ORG_STRUCTURE_ELEMENT_ID — Surrogate primary key, enforced by the unique index PER_ORG_STRUCTURE_ELEMENTS_PK. Each structural relationship receives a system-generated identifier.
• BUSINESS_GROUP_ID — Foreign key to HR_ALL_ORGANIZATION_UNITS, identifying the business group that owns the hierarchy element.
• ORGANIZATION_ID_PARENT — Foreign key to HR_ALL_ORGANIZATION_UNITS, referencing the parent organization in the reporting relationship.
• ORGANIZATION_ID_CHILD — Foreign key to HR_ALL_ORGANIZATION_UNITS, referencing the child organization subordinate to the parent.
• ORG_STRUCTURE_VERSION_ID — Foreign key to PER_ORG_STRUCTURE_VERSIONS, tying the element to a specific published version of the hierarchy.
• POSITION_CONTROL_ENABLED_FLAG — Indicates whether position control applies to this element, influencing how positions and incumbents are managed in the hierarchy.
• PARTY_ID — References the party associated with the structural element, supporting integration with trading community and party-based models.
• OBJECT_VERSION_NUMBER — Supports optimistic locking for concurrent updates.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Standard concurrent program audit columns identifying the process that created or last modified the row.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY — Standard WHO audit columns.

A second unique index, PER_ORG_STRUCTURE_ELEMENTS_UK2, covers ORG_STRUCTURE_VERSION_ID and ORGANIZATION_ID_CHILD. This is the principal business-key candidate: within any single structure version, a child organization may appear only once, guaranteeing that a child has exactly one parent per version and preventing recursive ambiguity in the hierarchy.

Common Use Cases and Queries

Applications and reports use this table to render organization charts, drive security profile inheritance, and traverse reporting lines. Typical SQL patterns join to PER_ORG_STRUCTURE_VERSIONS to isolate the currently active version.

• Retrieving the direct children of a given parent organization:

SELECT organization_id_child
FROM   per_org_structure_elements
WHERE  org_structure_version_id = :version_id
AND    organization_id_parent   = :parent_org_id;

• Identifying the parent of a specific child within an active version, exploiting the UK2 uniqueness guarantee.
• Building recursive hierarchical queries using CONNECT BY PRIOR to walk multi-level reporting structures.
• Detecting orphaned or unassigned organizations by comparing HR_ALL_ORGANIZATION_UNITS against this table for a given version.
• Auditing reorganizations by comparing parent-child pairs across consecutive structure versions.

Related Objects

• HR_ALL_ORGANIZATION_UNITS — Referenced three times (BUSINESS_GROUP_ID, ORGANIZATION_ID_PARENT, ORGANIZATION_ID_CHILD); supplies organization names, types, and validity dates.
• PER_ORG_STRUCTURE_VERSIONS — Referenced via ORG_STRUCTURE_VERSION_ID; defines each hierarchy version, including its status and date range.
• PER_CAL_ENTRY_VALUES — References this table through ORG_STRUCTURE_ELEMENT_ID, linking payroll calendar entries to organizational hierarchy elements.
• PER_ORG_STRUCTURE_ELEMENTS_PK and PER_ORG_STRUCTURE_ELEMENTS_UK2 — Unique indexes enforcing primary and business-key integrity.

Together these objects form the core of organization hierarchy modeling in Oracle HRMS, supporting security, reporting, and payroll context resolution.