Results for “hr_fr_estab_coll_agrs_v”

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

Overview

The HR_FR_ESTAB_COLL_AGRS_V view belongs to the PER – Human Resources product family within Oracle E-Business Suite and resides in the APPS schema. It is a French localization object that presents the association between French establishments (établissements) and collective agreements (conventions collectives) that apply to them. In French HR statutory reporting, every establishment must be linked to one or more applicable collective agreements, and this view surfaces that relationship in a denormalized, user-friendly form suitable for reporting and integration.

The view is exposed through the ETRM (E-Business Tax and Reporting Model) repository and carries a VALID status in Oracle EBS 12.1.1 and 12.2.2. When users search for the term establishment_name, this view is returned because the column ESTABLISHMENT_NAME is one of its key exposed attributes, resolved from the translated organization name table.

Underlying Base Objects

The view is defined over four synonym-based base objects in the APPS schema:

• HR_ALL_ORGANIZATION_UNITS — the core organization definition, aliased as OU1, providing the primary organization identifier.
• HR_ALL_ORGANIZATION_UNITS_TL — the translated organization units table, aliased as OUT1, supplying the language-dependent organization/establishment name.
• HR_ORGANIZATION_INFORMATION — referenced twice (OI1 and OI2) to retrieve flexfield-based attribute values from two different descriptive contexts.
• PER_COLLECTIVE_AGREEMENTS — aliased as C, the transactional table that stores collective agreement definitions, names, and effective dates.

The join logic is central to the view's behavior. Alias OI2 filters on ORG_INFORMATION_CONTEXT = 'CLASS' with ORG_INFORMATION1 = 'FR_ETABLISSEMENT' and ORG_INFORMATION2 = 'Y', meaning it selects only organizations classified as French establishments. Alias OI1 uses the context FR_ESTAB_COLL_AGRS to hold the collective agreement identifier associated with each establishment, and joins to PER_COLLECTIVE_AGREEMENTS via TO_NUMBER(OI1.ORG_INFORMATION1) = C.COLLECTIVE_AGREEMENT_ID. Note that OI1 is an outer-joined alias ((+)), so establishments without an agreement still appear. The final join to OUT1 is restricted by USERENV('LANG') = OUT1.LANGUAGE, ensuring the establishment name is returned in the session's current language.

Key Columns

• COLLECTIVE_AGREEMENT_ID — the identifier of the collective agreement sourced from PER_COLLECTIVE_AGREEMENTS.
• BUSINESS_GROUP_ID — the business group owning the agreement.
• NAME — the name of the collective agreement.
• ESTABLISHMENT_ORGANIZATION_ID — the organization_id of the French establishment (OU1.ORGANIZATION_ID).
• ESTABLISHMENT_NAME — the translated name of the establishment from HR_ALL_ORGANIZATION_UNITS_TL.
• START_DATE and END_DATE — the effective date range of the collective agreement.

Common Use Cases and Queries

This view supports French HR compliance reporting, establishment-level collective agreement audits, and data migration or integration extracts that require the establishment/agreement mapping. A typical query retrieving agreement assignments by establishment is:

SELECT establishment_organization_id,
       establishment_name,
       collective_agreement_id,
       name,
       start_date,
       end_date
  FROM apps.hr_fr_estab_coll_agrs_v
 WHERE business_group_id = :p_business_group_id
 ORDER BY establishment_name;

To find establishments with no linked collective agreement (relying on the outer join to OI1), filter where COLLECTIVE_AGREEMENT_ID IS NULL. Because the name resolution depends on USERENV('LANG'), ensure the reporting session's language is set appropriately to obtain the expected establishment name.