Results for “per_all_positions”

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

Overview

PER_ALL_POSITIONS is the core position definition table in the Oracle EBS Human Resources (PER) module, owned by the HR schema. It stores the dual-maintained definition of every position created within an enterprise, capturing both the business attributes of a position (name, job, organization, location, working hours) and its structural relationships to other positions. In Oracle EBS 12.1.1 and 12.2.2, this table is central to position management, headcount budgeting, vacancy tracking, and assignment of employees to positions.

From a Data Vault modeling perspective, the FK structure suggests classifying PER_ALL_POSITIONS as a hub. It anchors a stable business key (the position) and is heavily referenced by numerous satellite and transactional tables across HR, Payroll, and Projects. Its primary key, PER_POSITIONS_PK, is defined on POSITION_ID, a system-generated surrogate that uniquely identifies each row regardless of business attribute changes.

Key Information Stored

The table contains 61 documented columns. The most significant are:

• POSITION_ID — surrogate primary key (PER_POSITIONS_PK) that uniquely identifies each position record.
• NAME and BUSINESS_GROUP_ID — together form the unique business key PER_POSITIONS_UK2, ensuring position names are unique within a business group.
• JOB_ID — foreign key to PER_JOBS, indicating the job associated with the position.
• ORGANIZATION_ID — foreign key to HR_ALL_ORGANIZATION_UNITS, identifying the organization (typically the business group or internal organization) that owns the position.
• LOCATION_ID — foreign key to HR_LOCATIONS_ALL, defining the work location.
• POSITION_DEFINITION_ID — foreign key to PER_POSITION_DEFINITIONS, linking to the position definition template.
• SUCCESSOR_POSITION_ID and RELIEF_POSITION_ID — self-referencing foreign keys that establish succession and relief relationships between positions.
• DATE_EFFECTIVE and DATE_END — define the effective date range of the position record.
• STATUS — indicates whether the position is active or inactive.
• WORKING_HOURS, TIME_NORMAL_START, TIME_NORMAL_FINISH, FREQUENCY, and PROBATION_PERIOD — capture scheduling and probation attributes.
• REPLACEMENT_REQUIRED_FLAG — indicates whether the position requires replacement upon vacancy.
• Descriptive and audit columns including COMMENTS, LAST_UPDATE_DATE, CREATED_BY, OBJECT_VERSION_NUMBER, and the ATTRIBUTE1–ATTRIBUTE30 (flexfield) columns.

Common Use Cases and Queries

PER_ALL_POSITIONS is queried extensively for headcount reporting, vacancy analysis, organizational hierarchy, and security profiling. A typical query to retrieve active positions with their job and organization details:

• SELECT p.position_id, p.name, p.status, j.name job_name, o.name org_name FROM per_all_positions p, per_jobs j, hr_all_organization_units o WHERE p.job_id = j.job_id AND p.organization_id = o.organization_id AND p.status = 'ACTIVE';
• To find vacant positions (positions with no active assignment): SELECT p.position_id, p.name FROM per_all_positions p WHERE NOT EXISTS (SELECT 1 FROM per_all_assignments_f a WHERE a.position_id = p.position_id AND SYSDATE BETWEEN a.effective_start_date AND a.effective_end_date);
• To report on successor and relief relationships: SELECT p.name, s.name successor_name FROM per_all_positions p, per_all_positions s WHERE p.successor_position_id = s.position_id;

Reporting use cases include position budgeting, grade validation, competence requirements, and integration with payroll element links via PAY_ELEMENT_LINKS_F. Security profiles (PER_SECURITY_PROFILES) also reference POSITION_ID to restrict data access.

Related Objects

PER_ALL_POSITIONS is referenced by numerous dependent tables. The most significant include:

• PER_ALL_ASSIGNMENTS_F — references POSITION_ID; links employees to positions.
• PER_POS_STRUCTURE_ELEMENTS — references both PARENT_POSITION_ID and SUBORDINATE_POSITION_ID, defining the position hierarchy.
• PER_ALL_VACANCIES — references POSITION_ID, tracking open vacancies.
• PER_BUDGET_ELEMENTS and PER_JOB_EVALUATIONS — reference POSITION_ID for budgeting and evaluation.
• PER_COMPETENCE_ELEMENTS, PER_JOB_REQUIREMENTS, and PER_VALID_GRADES — reference POSITION_ID for requirement and grade validation.
• PER_MM_POSITIONS, PER_MM_ASSIGNMENTS, and PER_MM_VALID_GRADES — related to mass movement operations.
• PAY_ELEMENT_LINKS_F and PA_ROLE_PROFILES — reference POSITION_ID to associate payroll and role profile information.
• PER_SECURITY_PROFILES and PER_SUCCESSION_PLANNING — reference POSITION_ID for security and succession planning.

Self-referencing foreign keys to SUCCESSOR_POSITION_ID and RELIEF_POSITION_ID also exist, enabling succession and relief relationships within the same table.