Search Results hr_establishments_v




Overview
Explore in AI Chat

Overview

HR_ESTABLISHMENTS_V is a PL/SQL view owned by the APPS schema in Oracle E-Business Suite, validated in both 12.1.1 and 12.2.2. It belongs to the PER (Human Resources) product family and, according to ETRM metadata, exists primarily "to support user interface" functionality. In practice the view presents a denormalized, establishment-centric projection of organization data, exposing attributes required by the Establishment and EEO-1 / VETS-100 reporting setup forms in Oracle HRMS. Rather than requiring callers to join the organization units table to multiple rows of the generic HR_ORGANIZATION_INFORMATION structure, the view performs those joins internally and pivots the required descriptive flexfield segments into named columns, simplifying both form-level and reporting access.

Underlying Base Objects

The view is defined over HR_ORGANIZATION_UNITS (exposed through the HR_ALL_ORGANIZATION_UNITS synonym) and three aliased references to HR_ORGANIZATION_INFORMATION (documented as the HR_ORGANIZATION_INFORMATION synonym). Joined by ORGANIZATION_ID, the query returns one row per organization classified as an establishment. The driving filter is applied through the O2 instance, where ORG_INFORMATION_CONTEXT concatenated with a null string equals 'CLASS', ORG_INFORMATION1 equals 'HR_ESTAB', and ORG_INFORMATION2 equals 'Y'. The O3 and O4 instances supply EEO-1 and VETS-100 filing attributes respectively, joined via outer joins and constrained by the contexts 'ESTABLISHMENT EEO-1 FILING' and 'ESTABLISHMENT VETS-100 FILING'. Because these two joins are outer, an establishment without EEO-1 or VETS-100 details is still returned; the CLASS join to O2 is inner, so only organizations explicitly flagged as establishments qualify.

Key Columns

Common Use Cases and Queries

The most frequent use is a direct lookup of establishment header and filing data by organization or business group, particularly for EEO-1 and VETS-100 preparation. A representative query is:

  • SELECT establishment_id, name, eeo1_unit_number, vets100_unit_number, sic FROM apps.hr_establishments_v WHERE business_group_id = :p_business_group_id;
  • SELECT establishment_id, name, location_id, date_from, date_to FROM apps.hr_establishments_v WHERE establishment_id = :p_org_id;
  • SELECT business_group_id, COUNT(*) FROM apps.hr_establishments_v GROUP BY business_group_id;

Because the view encapsulates the establishment classification filter, consumers avoid reproducing the ORG_INFORMATION_CONTEXT logic. Note that, as with most HRMS views, row-level security through business group and security profiles applies, and only establishments with an active CLASS row of HR_ESTAB / Y are returned.