Search Results per_collective_agreements




Overview
Explore in AI Chat

Overview

PER_COLLECTIVE_AGREEMENTS is a core Human Resources (PER) table in Oracle E-Business Suite 12.1.1 and 12.2.2, owned by the HR schema. It stores collective agreement information — the formal negotiated contracts between an employer organization and a bargaining organization (union or employee association) that govern terms and conditions of employment. In EBS, this table acts as the master definition of a collective agreement, providing the anchor against which assignments, entitlements, grade structures, and retained-rights records are linked.

The ETRM metadata classifies this object, using a heuristic Data Vault analysis mined from its foreign key structure, as hub-leaning. This is a modeling suggestion only: the table behaves as a business key hub, defined by a stable surrogate primary key and a natural business key, with dependent descriptive attributes attached. Surrounding tables such as PER_CAGR_ENTITLEMENTS and PER_CAGR_ENTITLEMENT_RESULTS function more like satellites or link-style dependents that reference the hub.

Key Information Stored

The documented physical schema for 12.2.2 contains 64 columns, including standard WHO columns and descriptive flexfield (DFF) segments. The most significant columns are:

Common Use Cases and Queries

Typical reporting and integration scenarios include identifying active agreements for a business group, listing assignments covered by a given agreement, and reconciling entitlements and grade structures against their governing contract.

  • Retrieve agreements currently in force: SELECT collective_agreement_id, name, cag_number, start_date, end_date FROM per_collective_agreements WHERE business_group_id = :p_bg AND TRUNC(SYSDATE) BETWEEN start_date AND NVL(end_date, TRUNC(SYSDATE));
  • List employees covered by an agreement by joining through assignments: SELECT a.assignment_id, a.person_id FROM per_all_assignments_f a WHERE a.collective_agreement_id = :p_cag AND TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date;
  • Aggregate entitlement results per agreement for bargaining reporting, joining PER_CAGR_ENTITLEMENT_RESULTS to PER_COLLECTIVE_AGREEMENTS on COLLECTIVE_AGREEMENT_ID.
  • Extract to the HR data warehouse via HR_EDW_WRK_ACTVTY_F for headcount-by-agreement analytics.
  • Validate the business key before insert or load using PER_COLLECTIVE_AGREEMENTS_UK1 (NAME, BUSINESS_GROUP_ID).

Related Objects

The following objects reference or are referenced by PER_COLLECTIVE_AGREEMENTS through documented foreign key relationships:

  • HR_ALL_ORGANIZATION_UNITS — joined three times, via EMPLOYER_ORGANIZATION_ID, BARGAINING_ORGANIZATION_ID, and BUSINESS_GROUP_ID.
  • PER_ALL_ASSIGNMENTS_F — references COLLECTIVE_AGREEMENT_ID, tying individual assignments to their governing agreement.
  • PER_CAGR_ENTITLEMENTS — entitlement definitions scoped to a collective agreement.
  • PER_CAGR_ENTITLEMENT_RESULTS — calculated entitlement outcomes per agreement.
  • PER_CAGR_GRADE_STRUCTURES — grade structures aligned to the agreement.
  • PER_CAGR_REQUESTS — agreement-related process requests.
  • PER_CAGR_RETAINED_RIGHTS — retained-rights provisions linked to the agreement.
  • HR_EDW_WRK_ACTVTY_F and HR_EDW_WRK_ACTVTY_FSTG — HR data warehouse fact and staging tables carrying COLLECTIVE_AGREEMENT_ID for workforce activity reporting.