Results for “per_position_structures”

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

Overview

PER_POSITION_STRUCTURES is the HR schema table that stores the definitions of position hierarchies within Oracle E-Business Suite. Each row represents a named position structure — a container that groups positions into a reporting or organizational hierarchy used throughout Oracle Human Resources, Purchasing (via position approval routing), Performance Management, and the Public Sector / Financials position-control modules. Because positions are one of the core entities of the HR data model, this table acts as the anchor point for every versioned rendering of a hierarchy, for security profile assignments that restrict which structures a user can see, and for routing categories that drive approval workflows.

In heuristic Data Vault terms, the table's FK topology is hub-leaning: it carries a single-column surrogate primary key with no dependent attributes describing another business entity, and it is referenced by multiple downstream tables rather than functioning as a transaction or change-capture record. A Data Vault model of this area would most naturally place PER_POSITION_STRUCTURES as a hub, with the referencing tables treated as links or satellites.

Key Information Stored

The documented physical schema comprises 36 columns. The columns with the greatest functional significance are:

• POSITION_STRUCTURE_ID — the system-generated surrogate primary key, enforced by PER_POSITION_STRUCTURES_PK. Every foreign key from dependent tables points at this column.
• NAME — the user-visible name of the hierarchy. Together with BUSINESS_GROUP_ID it forms the unique business key PER_POSITION_STRUCTURES_UK2, so structure names must be unique within a business group.
• BUSINESS_GROUP_ID — identifies the business group (enterprise) that owns the structure; foreign key to HR_ALL_ORGANIZATION_UNITS.
• PRIMARY_POSITION_FLAG — flags whether this structure is the primary one for its business group, which affects default selection in many HR windows.
• COMMENTS — free-text description of the structure's purpose.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — the standard concurrent-program audit stamp, identifying the request and program that last modified the row.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — the WHO columns maintained by Oracle Application Object Library.
• OBJECT_VERSION_NUMBER — optimistic-locking counter used by the OAF/BC4J layer.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE20 — the DFF/descriptive flexfield segment set, available for client-specific extension.

Common Use Cases and Queries

Reporting and integration scenarios typically resolve a structure ID into a name for display, or resolve a name back into an ID before inserting into a dependent table. A representative lookup joins the structure to its business group:

• Listing all structures with their owning business group: SELECT pps.position_structure_id, pps.name, hou.name FROM hr.per_position_structures pps, hr.hr_all_organization_units hou WHERE pps.business_group_id = hou.organization_id;
• Finding the primary structure for a business group: SELECT position_structure_id FROM hr.per_position_structures WHERE business_group_id = :bg AND primary_position_flag = 'Y';
• Identifying structures that have routing configured: SELECT DISTINCT pps.position_structure_id, pps.name FROM hr.per_position_structures pps, pqh_routing_categories prc WHERE pps.position_structure_id = prc.position_structure_id;
• Auditing security exposure: SELECT pps.name, psp.security_profile_id FROM hr.per_position_structures pps, per_security_profiles psp WHERE pps.position_structure_id = psp.position_structure_id;

Because the table is small and slowly changing, it is a common driver in extract programs that warehouse the position hierarchy dimension along with its versions from PER_POS_STRUCTURE_VERSIONS.

Related Objects

• HR_ALL_ORGANIZATION_UNITS — referenced by BUSINESS_GROUP_ID; supplies the enterprise that owns each structure.
• PER_POS_STRUCTURE_VERSIONS — holds each dated version of the hierarchy; joined on POSITION_STRUCTURE_ID.
• PER_SECURITY_PROFILES — assigns structures to security profiles, joined on POSITION_STRUCTURE_ID.
• PQH_ROUTING_CATEGORIES — defines approval-routing categories per structure, joined on POSITION_STRUCTURE_ID.
• PER_PERF_MGMT_PLANS — performance management plans that reference a structure by POSITION_STRUCTURE_ID.
• IGI_EXP_POS_STRUCTURES and IGI_EXP_WKF_STEP_ALL — the position-control and expense/workflow structures maintained in the IGI (Public Sector) applications, both keyed by POSITION_STRUCTURE_ID.

Taken together these references confirm that PER_POSITION_STRUCTURES functions as an integration hub for hierarchy-based processing across HR, routing, security, and public-sector extensions.