Results for “per_establishments”

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

Overview

PER_ESTABLISHMENTS is a Human Resources (PER) module table in the Oracle E-Business Suite, owned by the HR schema. It maintains a master list of educational establishments, such as schools, colleges, and universities, that are referenced during the recording of an employee's or applicant's academic history. Within the Oracle EBS 12.1.1 and 12.2.2 data model, the table functions as a reference/lookup entity: rather than storing free-text institution names on attendance records, the application stores a foreign-key reference to a canonical establishment row, ensuring consistent values across person records and downstream reporting.

From a Data Vault modeling perspective, the heuristic classification of PER_ESTABLISHMENTS is hub-leaning. It behaves as a hub because it holds a stable business key (establishment identity) and serves as the parent that many other objects reference, rather than as a transactional or attribute-history satellite. This classification is a modeling suggestion derived from the foreign-key topology, not a formal Oracle designation.

Key Information Stored

The documented physical schema in ETRM 12.2.2 shows 52 columns, comprising the identifying and descriptive attributes plus the standard Oracle EBS audit and descriptive-flexfield columns.

• ESTABLISHMENT_ID — Surrogate primary key, enforced by PER_ESTABLISHMENTS_PK. All dependent tables join on this column.
• NAME — The establishment name (e.g., institution or school). Part of the unique business key.
• LOCATION — The geographic location of the establishment. Together with NAME it forms the business-key candidate PER_ESTABLISHMENTS_UK (NAME, LOCATION), preventing duplicate entries for the same institution at the same location.
• PARTY_ID — Links the establishment to the Trading Community Architecture (TCA) party model, allowing the institution to be treated as a party entity.
• EST_INFORMATION_CATEGORY and EST_INFORMATION1 through EST_INFORMATION20 — A dedicated descriptive flexfield (DFF) block for capturing client-specific establishment attributes (e.g., accreditation, type, region) without schema changes.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE20 — The standard Oracle EBS descriptive flexfield columns available on the table.
• OBJECT_VERSION_NUMBER — Optimistic locking column supporting concurrent update control in the 12.2.x (OAF/ADF) application stack.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard who-columns used for auditing, incremental ETL extraction, and change tracking.

Common Use Cases and Queries

Typical scenarios include validating and de-duplicating institution lists, joining establishment data to attendance records for HR reporting, and extracting reference data for downstream tax or statutory reporting where an establishment identifier is required.

• List all establishments ordered by name for a value-set or LOV implementation:
  SELECT establishment_id, name, location FROM per_establishments ORDER BY name;
• Join establishments to recorded attendance for an employee's academic history:
  SELECT a.person_id, e.name, e.location, a.* FROM per_establishment_attendances a JOIN per_establishments e ON a.establishment_id = e.establishment_id;
• Detect duplicate business keys: SELECT name, location, COUNT(*) FROM per_establishments GROUP BY name, location HAVING COUNT(*) > 1;
• Incremental extraction using audit columns: SELECT * FROM per_establishments WHERE last_update_date >= :last_run;
• Reporting on DFF attributes: query EST_INFORMATION_CATEGORY and the relevant EST_INFORMATIONn columns to surface client-defined attributes.

Related Objects

The foreign-key metadata identifies numerous dependent objects that reference PER_ESTABLISHMENTS.ESTABLISHMENT_ID. The most significant include:

• PER_ESTABLISHMENT_ATTENDANCES — Records attendance/academic history; joins on ESTABLISHMENT_ID. This is the primary HR consumer.
• ZX_LINES, ZX_LINES_SUMMARY, ZX_LINES_DET_FACTORS, ZX_TRANSACTION, ZX_TRX_HEADERS_GT, ZX_REP_TRX_DETAIL_T — E-Business Tax (ZX) tables carrying ESTABLISHMENT_ID for tax determination and reporting.
• XLE_ETB_PROFILES — Legal Entity E-Business Tax profile referencing the establishment.
• JE_FR_DAS2_* tables (e.g., JE_FR_DAS2_ETB_HEADERS_T, JE_FR_DAS2_VEN_SUMMARY_T) — French DAS2 statutory reporting structures.
• JG_ZZ_VAT_TRX_DETAILS — VAT transaction details referencing the establishment for tax reporting.
• HR_EDW_WRK_ACTVTY_F and HR_EDW_WRK_ACTVTY_FSTG — HR Enterprise Data Warehouse fact and staging tables used for analytics.

Because ESTABLISHMENT_ID is referenced so broadly, records should be treated as master data: changes propagate to tax, statutory, and warehouse layers, and the NAME/LOCATION unique key should be maintained to preserve data integrity.