Search Results dynamic_insert_allowed

Overview

PER_COLL_AGREE_GRADES_V is a read-only reporting view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the PER (Human Resources) product family and exposes the grade structures associated with collective agreements, resolved against the flexfield definition that governs grade structures. In ETRM terms the object is registered with an object type of VIEW and a status of VALID.

The view serves as a denormalized presentation layer: rather than requiring report authors and integrators to join PER_CAGR_GRADE_STRUCTURES, PER_COLLECTIVE_AGREEMENTS, and FND_ID_FLEX_STRUCTURES_VL manually, the view performs all three joins and returns the descriptive name of the collective agreement, the grade structure flexfield name, and the descriptive flexfield (DFF) attributes in a single result set. This makes it suitable for concurrent program data sources, Oracle Business Intelligence Publisher data models, and inbound/outbound interface queries.

The view carries no DML capability. Because it includes a ROWID column projected from PER_CAGR_GRADE_STRUCTURES, it is commonly used by Oracle Forms-based maintenance screens (notably the Collective Agreement Grade Structure window) as the queryable block source, where the ROWID permits the form to identify the underlying row for update through the base table rather than through the view itself.

Underlying Base Objects

The view is defined over three documented objects:

• PER_CAGR_GRADE_STRUCTURES (referenced via a SYNONYM) — the primary base table holding one row per grade structure attached to a collective agreement. The view projects the ROWID and all descriptive and DFF columns from this table.
• PER_COLLECTIVE_AGREEMENTS (referenced via a SYNONYM) — supplies the collective agreement name and the business group identifier. The join is CGS.COLLECTIVE_AGREEMENT_ID = CA.COLLECTIVE_AGREEMENT_ID.
• FND_ID_FLEX_STRUCTURES_VL (a VIEW) — supplies the key flexfield structure name. The join is CGS.ID_FLEX_NUM = FX.ID_FLEX_NUM, restricted to FX.ID_FLEX_CODE = 'CAGR' and FX.APPLICATION_ID = 800 (the Human Resources application).

The CAGR flexfield code and application ID 800 restriction ensure that only grade structure flexfields are returned, preventing accidental correlation with unrelated key flexfields that might share an ID_FLEX_NUM value.

Key Columns

• ROW_ID — the physical ROWID of the PER_CAGR_GRADE_STRUCTURES row; used by Forms for row identification.
• CA_NAME — the collective agreement name, derived from PER_COLLECTIVE_AGREEMENTS.NAME.
• BUSINESS_GROUP_ID — the business group that owns the collective agreement; the primary partitioning key for multi-organization HR queries.
• D_GRADE_TYPE_NAME — the descriptive name of the grade structure key flexfield structure, sourced from FND_ID_FLEX_STRUCTURES_VL.
• COLLECTIVE_AGREEMENT_ID — foreign key to the collective agreement.
• CAGR_GRADE_STRUCTURE_ID — the surrogate primary key of the grade structure record.
• ID_FLEX_NUM — the key flexfield structure number identifying the grade structure definition.
• DYNAMIC_INSERT_ALLOWED — a control flag indicating whether new grade combinations may be created dynamically at entry time without predefinition.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE20 — the descriptive flexfield context and segment values captured on the grade structure.
• OBJECT_VERSION_NUMBER — the optimistic locking column used by the underlying framework.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard WHO audit columns.

Common Use Cases and Queries

Typical usages include validating grade structure setup per collective agreement, extracting DFF segment values for downstream payroll or compensation interfaces, and driving LOVs in custom Forms or OAF pages.

List all grade structures for a business group, showing the dynamic insert flag:

• SELECT CA_NAME, D_GRADE_TYPE_NAME, ID_FLEX_NUM, DYNAMIC_INSERT_ALLOWED FROM APPS.PER_COLL_AGREE_GRADES_V WHERE BUSINESS_GROUP_ID = :p_bg_id ORDER BY CA_NAME;

Retrieve descriptive flexfield context and segments for a specific grade structure:

• SELECT CAGR_GRADE_STRUCTURE_ID, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2 FROM APPS.PER_COLL_AGREE_GRADES_V WHERE COLLECTIVE_AGREEMENT_ID = :p_ca_id;

Because the view is a join of three objects, queries should always filter on BUSINESS_GROUP_ID or COLLECTIVE_AGREEMENT_ID to avoid full scans. The view is read-only; updates must target PER_CAGR_GRADE_STRUCTURES directly.