Search Results hr_all_positions_f

Overview

The HR_ALL_POSITIONS_F table is the central repository for position definition information within the Oracle E-Business Suite (EBS) Human Resources (HR) module. It is a core transactional table that stores the complete definition and history of every position within an organization. As a date-effective table, it maintains a full audit trail of changes to a position's attributes over time, enabling organizations to track the evolution of roles, budgets, and reporting structures. Its role is fundamental to the HRMS foundation, supporting key processes such as workforce planning, budgeting, recruitment, and assignment management. The table is owned by the HR schema and is integral to the PER (Personnel) product family.

Key Information Stored

The table stores comprehensive details for each position. Its primary key is a composite of POSITION_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE, which uniquely identifies each row and its valid date range. Key descriptive columns include NAME and POSITION_DEFINITION_ID, which links to a template. Critical relational columns define the position's context: JOB_ID links to the associated job, ORGANIZATION_ID defines its parent organizational unit, and LOCATION_ID specifies its physical or logical location. The table also holds important HR policy data, such as ENTRY_GRADE_ID for the default grade, PAY_BASIS_ID for the compensation basis, and AVAILABILITY_STATUS_ID to indicate if the position is open or frozen. The BUSINESS_GROUP_ID column anchors the position within the correct security and data partitioning context.

Common Use Cases and Queries

This table is central to numerous HR operations and reports. A primary use case is generating a list of all active positions within a specific organization or business group for headcount and budget analysis. Another common scenario involves identifying open positions (based on AVAILABILITY_STATUS_ID) for recruitment purposes. Date-effective queries are essential; to retrieve the current snapshot of a position, one must filter for the row where the system date falls between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. Sample SQL to find current positions in an organization would be:

• SELECT hp.name AS position_name, pj.name AS job_name, hou.name AS org_name FROM hr_all_positions_f hp JOIN per_jobs pj ON hp.job_id = pj.job_id JOIN hr_all_organization_units hou ON hp.organization_id = hou.organization_id WHERE hp.organization_id = :p_org_id AND SYSDATE BETWEEN hp.effective_start_date AND hp.effective_end_date;

It is also frequently joined with assignment tables (e.g., PER_ALL_ASSIGNMENTS_F) to analyze incumbent data against position definitions.

Related Objects

The HR_ALL_POSITIONS_F table has extensive relationships with other core HR tables, as documented by its foreign key constraints. It references the following key objects:

• PER_JOBS via JOB_ID: Links the position to its generic job classification.
• HR_ALL_ORGANIZATION_UNITS via ORGANIZATION_ID: Defines the organizational unit to which the position belongs.
• HR_ALL_ORGANIZATION_UNITS via BUSINESS_GROUP_ID: Associates the position with its governing business group.
• PER_POSITION_DEFINITIONS via POSITION_DEFINITION_ID: References the template used to create the position.
• HR_LOCATIONS_ALL via LOCATION_ID: Specifies the position's work location.
• PER_GRADES via ENTRY_GRADE_ID: Defines the default grade for the position.
• PER_PAY_BASES via PAY_BASIS_ID: Specifies the basis for compensation (e.g., Salary, Hourly).
• PER_SHARED_TYPES via AVAILABILITY_STATUS_ID: Indicates the position's availability status (e.g., 'Active', 'Frozen').

This table is also a primary parent for assignment-related data, though those foreign keys are defined on the child tables referencing HR_ALL_POSITIONS_F.POSITION_ID.