Results for “per_collective_agreements_v”

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

Overview

PER_COLLECTIVE_AGREEMENTS_V is a Human Resources (PER) view owned by the APPS schema. It exposes the collective agreements that are negotiated between employers and bargaining units, presenting configuration and reference data used to govern terms of employment for represented employee populations. The view consolidates the columns of the base entity PER_COLLECTIVE_AGREEMENTS together with denormalized organization names resolved from HR_ORGANIZATION_UNITS, so that a single query returns both the agreement attributes and the readable names of the employer organization and the bargaining organization. Status is VALID in both Oracle EBS 12.1.1 and 12.2.2.

Because collective agreements are date-effective entities, the view applies an effective-date filter rather than exposing all versions indiscriminately: rows are returned only where PCA.START_DATE is less than or equal to the effective date of the current FND_SESSIONS session. This makes the view appropriate for runtime reporting and integration, since it naturally returns the agreement versions in force as of the session context. It is intended for read access; maintenance of collective agreements is performed through the underlying base entity and its associated APIs.

Underlying Base Objects

The documented referenced objects for this view are PER_COLLECTIVE_AGREEMENTS (synonym), HR_ORGANIZATION_UNITS (view), FND_SESSIONS (synonym), HR_GENERAL (package), and HR_SECURITY (package). Although the physical SQL joins only the first three objects directly, HR_GENERAL and HR_SECURITY are documented dependencies that participate in the effective-date and security behaviors exposed through the view definition.

• PER_COLLECTIVE_AGREEMENTS — primary source of all collective agreement attributes, including identifiers, dates, status, and descriptive flexfield columns.
• HR_ORGANIZATION_UNITS — joined twice with outer joins to resolve the employer organization name (HR1) and the bargaining organization name (HR2). Because the joins are outer (+), agreements with an unset employer or bargaining organization still appear.
• FND_SESSIONS — supplies SS.EFFECTIVE_DATE for the correlated subquery that limits rows to agreements effective on or before the session effective date.

Key Columns

• COLLECTIVE_AGREEMENT_ID — primary identifier of the agreement row.
• BUSINESS_GROUP_ID — the business group owning the record; relevant for multi-organization filtering.
• NAME, CAG_NUMBER, DESCRIPTION — the agreement name, negotiated agreement number, and free-text description.
• START_DATE, END_DATE — the validity window of the agreement version.
• EMPLOYER_ORGANIZATION_ID, HR1.NAME — employer organization identifier and its resolved name.
• BARGAINING_ORGANIZATION_ID, HR2.NAME — bargaining organization identifier and resolved name.
• EMPLOYER_SIGNATORY, BARGAINING_UNIT_SIGNATORY — signatories for each party.
• JURISDICTION, AUTHORIZING_BODY, AUTHORIZED_DATE, STATUS — governing jurisdiction, the authorizing body, the authorization date, and the agreement status.
• CAG_INFORMATION_CATEGORY and CAG_INFORMATION1 through CAG_INFORMATION20 — the descriptive flexfield context and segment values for the collective agreement. CAG_INFORMATION1 is the first such attribute segment and typically carries the first user-defined descriptive element configured for the CAG flexfield context.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE20 — the standard descriptive flexfield columns, distinct from the CAG_INFORMATION series.
• Row audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER, and ROWID.

Common Use Cases and Queries

Typical usage includes listing active agreements for a business group, reporting on the parties to each agreement, and retrieving flexfield content such as CAG_INFORMATION1 for downstream integrations. The effective-date predicate means queries executed within a normal session return only the agreements in force at the session effective date.

• Basic listing of agreements and their parties:

SELECT collective_agreement_id,
       name,
       cag_number,
       start_date,
       end_date,
       status
FROM   apps.per_collective_agreements_v
WHERE  business_group_id = :p_business_group_id
ORDER  BY start_date DESC;

• Resolving employer and bargaining organization names:

SELECT ca.name,
       ca.employer_organization_id,
       ca.hr1_name,
       ca.bargaining_organization_id,
       ca.hr2_name
FROM   apps.per_collective_agreements_v ca;

• Retrieving flexfield content, including the segment referenced by the search term cag_information1:

SELECT collective_agreement_id,
       name,
       cag_information_category,
       cag_information1,
       cag_information2
FROM   apps.per_collective_agreements_v
WHERE  cag_information_category IS NOT NULL;

Consumers should note that the view is intended for query access; inserts and updates must be directed to the base collective agreement entity. Reports should filter on BUSINESS_GROUP_ID and inspect START_DATE and END_DATE to interpret date-effective results correctly.