Results for “hr_fr_establishments_v”

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

Overview

HR_FR_ESTABLISHMENTS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the PER (Human Resources) product family. Its purpose is narrow and locale-specific: it lists the French organizations that have been flagged as Establishments (établissements) within the legislative framework used in France. The view is marked VALID in both Oracle EBS 12.1.1 and 12.2.2, and its definition is effectively identical across the two releases.

In Oracle HRMS, every organization unit carries descriptive, jurisdiction-specific attributes stored as name-value pairs in HR_ORGANIZATION_INFORMATION, keyed by an ORG_INFORMATION_CONTEXT. The French localization uses a set of dedicated contexts — FR_ESTAB_INFO, FR_ESTAB_URSSAF, FR_ESTAB_ASSEDIC, and FR_ESTAB_PREV_INFO — to hold establishment identifiers, social security (URSSAF) references, unemployment insurance (ASSEDIC) references, and previous-establishment data. Because these attributes are stored transversally rather than in dedicated columns, consumers must join the information table multiple times. HR_FR_ESTABLISHMENTS_V encapsulates that pivoting work and exposes the results as ordinary columns, which makes the view the practical entry point for French establishment reporting, extracts, and integrations.

Underlying Base Objects

ETRM documents the following referenced base objects, all resolved through APPS synonyms or views:

• HR_ALL_ORGANIZATION_UNITS — the primary driving table, appearing five times (aliased OU1 through OU5) to supply the establishment name, business group, dates, address line, location, comments, and the names of related organizations referenced by the FR_ESTAB_* attributes.
• HR_ORGANIZATION_INFORMATION — instantiated five times (OI1 through OI5), supplying the pivoted French establishment attributes and the classification context.
• HR_LOCATIONS — provides LOCATION_CODE for the establishment's location.
• HR_GENERAL — the HRMS package used internally for organization-related logic and resolution.

The row filter is decisive: OI5 is joined on context 'CLASS' with ORG_INFORMATION1 = 'FR_ETABLISSEMENT' and ORG_INFORMATION2 = 'Y'. That join is an inner join, so only organizations explicitly classified as French establishments qualify. All FR_ESTAB_* joins are outer (+) joins, meaning an organization still appears if some or all of its establishment attributes are unmaintained. Note that because OI5 is not outer-joined, the view is not a simple list of all French organizations — it is strictly the classified establishment subset.

Key Columns

• ORGANIZATION_ID, BUSINESS_GROUP_ID, NAME, DATE_FROM, DATE_TO — identity and effective-dating of the establishment organization unit.
• INTERNAL_ADDRESS_LINE, LOCATION_ID, LOCATION_CODE, COMMENTS — address and free-text descriptive information.
• ORG_INFORMATION1 through ORG_INFORMATION8 (from OI1, context FR_ESTAB_INFO) — establishment identifiers, where ORG_INFORMATION1 carries the related organization ID resolved to a name via OU2.
• ORG_INFORMATION1 through ORG_INFORMATION4 (from OI2, context FR_ESTAB_URSSAF) — URSSAF registration data, with ORG_INFORMATION1 linked to OU3.
• ORG_INFORMATION1 and ORG_INFORMATION2 (from OI3, context FR_ESTAB_ASSEDIC) — ASSEDIC identifiers, ORG_INFORMATION1 linked to OU4.
• ORG_INFORMATION1 through ORG_INFORMATION4 (from OI4, context FR_ESTAB_PREV_INFO) — previous-establishment details.
• ORG_INFORMATION5 and ORG_INFORMATION2 (from OI5) — the classification values 'FR_ETABLISSEMENT' and 'Y' that define the population.

Because several ORG_INFORMATIONn columns originate from different contexts, the same column name can carry unrelated semantics depending on its source alias. Consumers should always reason in terms of the context, not the column ordinal alone.

Common Use Cases and Queries

Typical scenarios include producing French statutory establishment listings, feeding payroll and social-charge interfaces, auditing which organizations have been classified as establishments, and reconciling URSSAF or ASSEDIC registrations against organization units.

• Validate classification coverage: select rows where ORG_INFORMATION1 from the URSSAF context is null to find establishments missing social security references.
• Report establishments by business group and effective date, filtering on SYSDATE BETWEEN DATE_FROM AND NVL(DATE_TO, SYSDATE).
• Join ORGANIZATION_ID to PER_ALL_ASSIGNMENTS_F for headcount reporting per establishment.

A representative query:

SELECT e.organization_id, e.name, e.location_code, e.org_information1 urssaf_ref, e.date_from, e.date_to FROM apps.hr_fr_establishments_v e WHERE TRUNC(SYSDATE) BETWEEN e.date_from AND NVL(e.date_to, TRUNC(SYSDATE)) ORDER BY e.name;

Because the definition pivots HR_ORGANIZATION_INFORMATION with outer joins, performance depends on indexes on ORGANIZATION_ID and ORG_INFORMATION_CONTEXT; queries filtered by business group should include BUSINESS_GROUP_ID to constrain the driving scan.