Search Results per_all_positions




Overview
Explore in AI Chat

Overview

The PER_ALL_POSITIONS table is the central repository for position definition information within the Oracle E-Business Suite Human Resources (HRMS) module. It implements the core concept of a position—a specific, budgeted role within an organizational structure that is distinct from the person who may fill it. As indicated by its "dual-maintained" description, this table stores both transactional and master data, supporting the effective-dated tracking of changes to a position's attributes over time. It is a fundamental building block for workforce modeling, budgeting, assignment management, and succession planning.

Key Information Stored

The table's primary key is the system-generated POSITION_ID. Its structure enforces a unique combination of NAME and BUSINESS_GROUP_ID via the PER_POSITIONS_UK2 constraint. Critical foreign key columns define a position's context and relationships. These include BUSINESS_GROUP_ID (linking to HR_ALL_ORGANIZATION_UNITS), JOB_ID (linking to PER_JOBS), ORGANIZATION_ID (linking to HR_ALL_ORGANIZATION_UNITS), and LOCATION_ID (linking to HR_LOCATIONS_ALL). The table also supports hierarchical and succession planning through self-referencing foreign keys: SUCCESSOR_POSITION_ID and RELIEF_POSITION_ID, which both point to POSITION_ID within the same table. Additional columns typically track attributes such as effective start and end dates, position status, grade, and a link to a POSITION_DEFINITION_ID for template-based position creation.

Common Use Cases and Queries

This table is central to reporting and integration. Common use cases include generating position vacancy reports, analyzing headcount against budgeted positions, and creating organizational hierarchy charts that include unfilled roles. A fundamental query retrieves active positions within a specific organization:

  • SELECT pap.position_id, pap.name, pap.effective_start_date, hou.name org_name FROM hr.per_all_positions pap, hr.hr_all_organization_units hou WHERE pap.organization_id = hou.organization_id AND SYSDATE BETWEEN pap.effective_start_date AND NVL(pap.effective_end_date, SYSDATE) AND pap.organization_id = :p_org_id;

Another critical pattern joins PER_ALL_POSITIONS with PER_ALL_ASSIGNMENTS_F to distinguish between budgeted positions and actual incumbent assignments, enabling gap analysis. Integration with the PER_JOBS table is essential for understanding the job family and function associated with each position.

Related Objects

As evidenced by the extensive foreign key relationships, PER_ALL_POSITIONS is a heavily referenced master table. The most significant relationship is with PER_ALL_ASSIGNMENTS_F, which links persons to positions. It is also referenced by competency and job requirement tables (PER_COMPETENCE_ELEMENTS, PER_JOB_REQUIREMENTS), budget components (PER_BUDGET_ELEMENTS), and deployment factors (PER_DEPLOYMENT_FACTORS). For position-specific custom attributes, the PER_POSITION_EXTRA_INFO table provides a flexfield link. In the context of talent management and succession planning, tables like PER_MM_POSITIONS and PER_JOB_EVALUATIONS depend on it. Furthermore, it integrates with other modules, such as Payroll (PAY_ELEMENT_LINKS_F) and Projects (PA_ROLE_PROFILES).