Search Results per_collective_agreements




Overview
Explore in AI Chat

Overview

The PER_COLLECTIVE_AGREEMENTS table is a core data object within the Oracle E-Business Suite (EBS) Human Resources (HR) module, specifically for releases 12.1.1 and 12.2.2. It serves as the master repository for defining and storing collective bargaining agreements (CBAs). These are formal contracts negotiated between an employer (or group of employers) and a labor union that govern terms and conditions of employment for a defined group of workers. The table's primary role is to establish a centralized reference for all such agreements within a business group, enabling the system to link employee assignments, entitlements, and grade structures to the specific legal and contractual frameworks that apply to them.

Key Information Stored

The table's structure is designed to capture the essential metadata of a collective agreement. Its primary key is the system-generated unique identifier, COLLECTIVE_AGREEMENT_ID. A unique key constraint on the combination of NAME and BUSINESS_GROUP_ID ensures agreement names are not duplicated within a single business group. Critical foreign key columns establish vital relationships: BUSINESS_GROUP_ID links the agreement to its owning business group, EMPLOYER_ORGANIZATION_ID identifies the employing entity from HR_ALL_ORGANIZATION_UNITS, and BARGAINING_ORGANIZATION_ID identifies the labor union or bargaining body, also from HR_ALL_ORGANIZATION_UNITS. Other typical columns, inferred from standard HR table design, would include effective start and end dates (EFFECTIVE_START_DATE, EFFECTIVE_END_DATE), descriptive fields, and audit information like CREATED_BY and LAST_UPDATE_DATE.

Common Use Cases and Queries

This table is central to processes involving unionized workforces or employees under specific industry agreements. Common use cases include reporting on which employees are covered under which agreements, configuring entitlement calculations (like overtime rates or leave accruals) based on the CBA, and defining grade progressions specific to a bargaining unit. A fundamental query retrieves all active agreements for a business group:

  • SELECT name, collective_agreement_id FROM per_collective_agreements WHERE business_group_id = &bg_id AND SYSDATE BETWEEN effective_start_date AND NVL(effective_end_date, SYSDATE);

Another critical pattern joins to assignments to list covered employees:

  • SELECT ppf.full_name, paaf.assignment_number, pca.name FROM per_all_assignments_f paaf, per_all_people_f ppf, per_collective_agreements pca WHERE paaf.collective_agreement_id = pca.collective_agreement_id AND paaf.person_id = ppf.person_id AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date;

Related Objects

As indicated by the foreign key metadata, PER_COLLECTIVE_AGREEMENTS has significant integration points. It is a parent table to several key HR objects. The PER_ALL_ASSIGNMENTS_F table references it, linking each employee assignment to its governing collective agreement. Entitlement rules and results (PER_CAGR_ENTITLEMENTS, PER_CAGR_ENTITLEMENT_RESULTS) and defined grade structures (PER_CAGR_GRADE_STRUCTURES) are all child entities of a collective agreement. Furthermore, it references the HR_ALL_ORGANIZATION_UNITS table three times to identify the business group, employer organization, and bargaining organization, anchoring the agreement within the enterprise model.