Search Results per_cagr_grade_structures




Overview
Explore in AI Chat

Overview

PER_CAGR_GRADE_STRUCTURES is a table in the HR schema of Oracle E-Business Suite (validated for 12.1.1 and 12.2.2) that belongs to the PER — Human Resources product family. It stores the valid collectively agreed grade key flexfield structures for a particular collective agreement. In practical terms, the table acts as the bridge between a collective agreement (PER_COLLECTIVE_AGREEMENTS) and the grade structures defined through a key flexfield, controlling which grade flexfield structures are permitted for each agreement.

Each row represents one valid combination of a collective agreement and a grade key flexfield structure, identified by the ID_FLEX_NUM column. This design supports environments where multiple collective agreements coexist, each potentially using a different key flexfield structure or set of grade definitions. The table therefore plays a configuration and validation role: application logic can verify that a grade structure is legitimately associated with an agreement before allowing grade assignment or grade-related processing.

From a Data Vault modeling perspective, the mined foreign key structure suggests a satellite-leaning classification. The table carries descriptive and contextual attributes (such as dynamic insert controls and flexfield attributes) tied to the parent collective agreement rather than acting as a standalone hub or as a pure associative link, though the COLLECTIVE_AGREEMENT_ID foreign key gives it a link-like quality.

Key Information Stored

The table contains 31 documented columns. The most significant are:

  • CAGR_GRADE_STRUCTURE_ID — surrogate primary key, enforced by the index PER_CAGR_GRADE_STRUCTURES_PK. Uniquely identifies each grade structure record.
  • COLLECTIVE_AGREEMENT_ID — foreign key to PER_COLLECTIVE_AGREEMENTS, identifying the agreement to which the grade structure belongs.
  • ID_FLEX_NUM — the key flexfield structure number that defines the grade flexfield configuration.
  • DYNAMIC_INSERT_ALLOWED — flag controlling whether new grade combinations may be created dynamically during data entry.
  • OBJECT_VERSION_NUMBER — optimistic locking column used by the ORM layer to detect concurrent updates.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE20 — the standard EBS descriptive flexfield (DFF) storage, available for customer-specific extensions.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard WHO audit columns capturing creation and modification metadata.

Two unique indexes are documented. PER_CAGR_GRADE_STRUCTURES_PK enforces uniqueness on CAGR_GRADE_STRUCTURE_ID. PER_CAGR_GRADE_STRUCTURES_UK1 enforces a business-key uniqueness constraint on the combination of COLLECTIVE_AGREEMENT_ID and ID_FLEX_NUM, ensuring that a given agreement cannot be associated with the same flexfield structure more than once — the practical business rule of the table.

Common Use Cases and Queries

Typical usage centers on validating and reporting the relationship between collective agreements and grade structures. A developer or analyst resolving the grade structures available for a specific agreement might write:

  • SELECT cags.cagr_grade_structure_id, cags.collective_agreement_id, cags.id_flex_num FROM per_cagr_grade_structures cags WHERE cags.collective_agreement_id = :agreement_id;
  • Joining to PER_COLLECTIVE_AGREEMENTS to retrieve agreement names alongside structure identifiers for reporting.
  • Joining to PER_CAGR_GRADES on CAGR_GRADE_STRUCTURE_ID to enumerate the grades defined within each valid structure.

Common scenarios include auditing which agreements use which flexfield structures, diagnosing grade entry failures caused by an unregistered structure, and validating the UK1 business key before bulk loading agreement-structure associations. The DYNAMIC_INSERT_ALLOWED column is often inspected when troubleshooting why users cannot create new grade combinations during data entry.

Related Objects

The following objects are most significant in relation to this table:

  • PER_COLLECTIVE_AGREEMENTS — parent table; joined via PER_CAGR_GRADE_STRUCTURES.COLLECTIVE_AGREEMENT_ID.
  • PER_CAGR_GRADES — child table; references this table through PER_CAGR_GRADES.CAGR_GRADE_STRUCTURE_ID, holding the grade definitions belonging to each structure.
  • FND_ID_FLEX_STRUCTURES — the standard key flexfield structure definitions in the Application Object Library, matched by ID_FLEX_NUM.
  • FND_ID_FLEX_SEGMENTS — segment definitions for the flexfield structure referenced by ID_FLEX_NUM.
  • PER_COLLECTIVE_AGREEMENTS_TL — translated agreement names, useful for multilingual reporting.
  • HR_GRADE_KEY_FLEX API / HR_GRADE_BALANCE-related interfaces — patterns that read configured grade structures during validation.

Collectively, these objects form the grade configuration chain: agreements define structures, structures define grades, and grades are assigned to employees and payroll processes.