Search Results per_cagr_grades_uk1

Overview

HR.PER_CAGR_GRADES is the intersection table that associates collectively agreed grades with collective agreement grade structures in Oracle E-Business Suite Human Resources. In the context of Oracle EBS 12.1.1 and 12.2.2, this table resides in the HR schema and is registered under FND Design Data as PER.PER_CAGR_GRADES. It stores the grades that exist within a Collective Agreement (CAGR). These records act as a reference set of collectively agreed grades which are subsequently used by an assignment, allowing payroll and human resources processes to validate that a worker is placed against a grade defined through collective bargaining rather than a generic grade ladder entry.

The physical table is stored in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10, and its indexes reside in APPS_TS_TX_IDX. It is composed of ten documented columns, all of which are exposed in the standard Query Text. Based on the heuristic Data Vault classification mined from the foreign key structure, PER_CAGR_GRADES is best modeled as a link table. It resolves a many-to-many style association between two parent entities — the grade definition and the grade structure — and carries a descriptive sequencing attribute alongside standard transactional (Who) columns. This classification is a modeling suggestion rather than a documented Oracle designation.

Key Information Stored

Although the table contains ten documented columns, the following are the most significant for functional and technical analysis:

• CAGR_GRADE_ID — NUMBER(15), mandatory, the system-generated surrogate primary key sourced from the sequence PER_CAGR_GRADES_S. It is the single-column unique key PER_CAGR_GRADES_PK.
• CAGR_GRADE_STRUCTURE_ID — NUMBER(15), mandatory, foreign key to PER_CAGR_GRADE_STRUCTURES. Identifies the collective agreement grade structure to which the grade belongs.
• CAGR_GRADE_DEF_ID — NUMBER(15), mandatory, foreign key to PER_CAGR_GRADES_DEF. This is the column the user searched for; it references the underlying collectively agreed grade definition.
• SEQUENCE — NUMBER(15), the sequence number of the grade within the structure, controlling ordering of grades inside a collective agreement.
• OBJECT_VERSION_NUMBER — NUMBER(15), the system-generated row version that increments by one with each update and supports optimistic locking.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — the standard Who columns recording audit and creation attributes for every row.

The business-key candidates documented through unique indexes are PER_CAGR_GRADES_PK on CAGR_GRADE_ID and PER_CAGR_GRADES_UK1 on the composite of CAGR_GRADE_STRUCTURE_ID and CAGR_GRADE_DEF_ID. The composite unique key enforces that a given grade definition appears only once within a given grade structure, distinguishing the surrogate key from the natural business key.

Common Use Cases and Queries

Typical reporting and integration scenarios retrieve all grades attached to a collective agreement structure, resolve the definition behind a grade, or verify that an assignment’s grade is collectively agreed. A representative query joins the intersection table to both parent tables:

• List grades for a structure: SELECT CAGR_GRADE_ID, CAGR_GRADE_DEF_ID, SEQUENCE FROM HR.PER_CAGR_GRADES WHERE CAGR_GRADE_STRUCTURE_ID = :p_structure_id ORDER BY SEQUENCE;
• Resolve grade definitions: join PER_CAGR_GRADES to PER_CAGR_GRADES_DEF on CAGR_GRADE_DEF_ID to retrieve the grade name and attributes.
• Structure context: join to PER_CAGR_GRADE_STRUCTURES on CAGR_GRADE_STRUCTURE_ID to report the parent collective agreement.
• Reverse lookup: given an assignment’s collectively agreed grade, find the structure(s) in which it appears using CAGR_GRADE_DEF_ID.

Because only the Who columns change after creation, incremental extracts typically filter on LAST_UPDATE_DATE to capture newly defined or modified grade associations.

Related Objects

The following objects are the most significant for understanding and joining PER_CAGR_GRADES, derived from the documented foreign keys and dependencies:

• HR.PER_CAGR_GRADES_DEF — parent table referenced by CAGR_GRADE_DEF_ID; holds the collective agreement grade definitions.
• HR.PER_CAGR_GRADE_STRUCTURES — parent table referenced by CAGR_GRADE_STRUCTURE_ID; defines the grade structures within a collective agreement.
• PER_CAGR_GRADES_S — sequence that generates the CAGR_GRADE_ID primary key.
• PER_CAGR_GRADES_PK / PER_CAGR_GRADES_UK1 / PER_CAGR_GRADES_FK2 — the unique and non-unique indexes supporting primary key, composite business key, and the CAGR_GRADE_DEF_ID foreign key respectively.

Collectively, these objects form the collective agreement grade model within Oracle HRMS, with PER_CAGR_GRADES acting as the linking entity between grade definitions and their structures.