Results for “establishment_id”

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

Overview

PER_QUALIFICATIONS_V2 is a view owned by the APPS schema in Oracle E-Business Suite, residing in the PER (Human Resources) product module. The view carries a status of VALID in both 12.1.1 and 12.2.2 and is documented in the ETRM repository with the description "Used to support user interface." Despite its name, which implies a relationship to qualifications data, the view text defines a projection over establishment attendance records rather than a traditional qualification or competency table. It surfaces a consolidated set of person-level attendance rows joined to establishment master data, making it useful for reporting and integration where a single, denormalized access path to attendance and establishment details is required.

Underlying Base Objects

The view is defined over two documented base objects, both exposed to APPS as synonyms:

• PER_ESTABLISHMENT_ATTENDANCES (aliased ESA) — the primary driving table, supplying PERSON_ID, ATTENDANCE_ID, ESTABLISHMENT_ID, and the attended start and end dates.
• PER_ESTABLISHMENTS (aliased EST) — the establishment master table, supplying the establishment NAME (aliased as ESTABLISHMENT) and LOCATION.

The join is an outer join from attendances to establishments (ESA.ESTABLISHMENT_ID = EST.ESTABLISHMENT_ID (+)), so attendance rows are preserved even when no matching establishment exists. The WHERE clause applies a filter of ESA.ATTENDANCE_ID IS NOT NULL, restricting output to genuine attendance records. Establishment name is resolved with an NVL against ESA.ESTABLISHMENT, meaning a stored establishment value on the attendance row takes precedence over the master name.

Key Columns

• PERSON_ID — identifies the person to whom the attendance record belongs; the principal join key back to PER_ALL_PEOPLE_F.
• ATTENDANCE_ID — the unique identifier of the establishment attendance record. This is the column most relevant to the user's search term "attendance_id," as it is the primary correlation key for downstream queries against PER_ESTABLISHMENT_ATTENDANCES.
• ESTABLISHMENT_ID — foreign key to the establishment attended.
• ESTABLISHMENT — resolved establishment name (ESA value preferred, otherwise EST.NAME).
• LOCATION — the location associated with the establishment.
• ATTENDED_START_DATE / ATTENDED_END_DATE — the period during which the person attended the establishment.

Common Use Cases and Queries

Typical usage includes HR reporting on establishment attendance per person, integration extracts keyed by attendance_id, and validation lookups resolving an attendance to its establishment and location. The view is not effective-dated for person records; date filtering should use the attended dates.

Retrieve all attendance records for a person:

SELECT person_id, attendance_id, establishment, location,
       attended_start_date, attended_end_date
FROM   apps.per_qualifications_v2
WHERE  person_id = :p_person_id;

Resolve a specific attendance record and its establishment:

SELECT attendance_id, person_id, establishment_id,
       establishment, location
FROM   apps.per_qualifications_v2
WHERE  attendance_id = :p_attendance_id;

Filter attendance active within a date range:

SELECT person_id, attendance_id, attended_start_date, attended_end_date
FROM   apps.per_qualifications_v2
WHERE  attended_start_date BETWEEN :p_start AND :p_end;

Because the view enforces the ATTENDANCE_ID IS NOT NULL predicate, callers can rely on attendance_id being present in every returned row.