Search Results per_establishments

Overview

The PER_ESTABLISHMENTS table is a core reference data entity within the Oracle E-Business Suite Human Resources (HR) module, specifically in releases 12.1.1 and 12.2.2. Owned by the HR schema, its primary function is to maintain a master list of educational institutions. This includes establishments such as schools, colleges, universities, and other accredited bodies. The table serves as a standardized repository, enabling the consistent recording and validation of educational history for employees and applicants across the HRMS system. Its role is foundational for managing qualification and background information within an organization's talent management processes.

Key Information Stored

The table's structure is designed to uniquely identify and describe each educational establishment. The primary key is the system-generated ESTABLISHMENT_ID, which serves as the unique internal identifier. A critical composite unique key constraint ensures that the combination of NAME and LOCATION is not duplicated, enforcing data integrity at the business level. While the provided metadata specifies these core columns, typical implementations of this table often include additional descriptive attributes such as establishment type (e.g., university, technical college), accreditation status, and address details, though these are not explicitly listed in the excerpt.

Common Use Cases and Queries

The primary use case for PER_ESTABLISHMENTS is to support the tracking of employee and candidate education within the PER_ESTABLISHMENT_ATTENDANCES table. Common operational and reporting activities include validating new establishment entries, generating lists of institutions for dropdowns in self-service forms, and creating educational background reports. A typical query to retrieve all establishments for a specific location would be:

SELECT establishment_id, name, location
FROM hr.per_establishments
WHERE location = '&city_name'
ORDER BY name;

Another frequent pattern is joining to attendance records to analyze the educational background of the workforce:

SELECT pea.person_id, pe.name AS institution, pea.attended_from, pea.attended_to
FROM hr.per_establishment_attendances pea,
     hr.per_establishments pe
WHERE pea.establishment_id = pe.establishment_id
AND pea.person_id = &employee_id;

Related Objects

The most direct and critical relationship for the PER_ESTABLISHMENTS table is with the PER_ESTABLISHMENT_ATTENDANCES table. The ESTABLISHMENT_ID column acts as a foreign key in PER_ESTABLISHMENT_ATTENDANCES, linking each attendance record (a person's period of study) to a specific institution in the master list. This relationship is enforced by the foreign key constraint named in the metadata. While not listed in the excerpt, this table is also likely referenced by various HRMS APIs, public views (potentially prefixed with PER_ESTABLISHMENTS_VL for translated data), and is integral to the underlying data model for qualifications and competencies within Oracle HR.