Results for “per_grade_definitions”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PER_GRADE_DEFINITIONS is the grade key flexfield combinations table in the Oracle E-Business Suite Human Resources (PER) product, owned by the HR schema. It stores the complete set of key flexfield segment values that define every grade in the enterprise. In Oracle EBS, grades are a fundamental element of workforce structure and are referenced throughout compensation, budgeting, and progression processes. Rather than storing descriptive grade attributes directly, Oracle HRMS models a grade primarily through this flexfield combination table, with PER_GRADES holding the descriptive and administrative attributes and pointing back to PER_GRADE_DEFINITIONS through the GRADE_DEFINITION_ID column.

From a dimensional modeling perspective, the mined foreign key structure suggests a hub-leaning classification. The table carries a single-column surrogate primary key—GRADE_DEFINITION_ID—and is referenced by multiple downstream tables, which is the pattern typical of a hub that anchors business keys. Note that this Data Vault classification is a heuristic derived from relationship structure and should be treated as a modeling suggestion rather than a documented design fact.

Key Information Stored

The table contains 46 documented columns in the 12.2.2 physical schema. The most significant are the following:

• GRADE_DEFINITION_ID — The surrogate primary key, enforced by PER_GRADE_DEFINITIONS_PK. This is the value stamped onto PER_GRADES.GRADE_DEFINITION_ID and other referencing tables.
• ID_FLEX_NUM — Identifies the key flexfield structure (the grade flexfield definition) that governs how the segments in this row are interpreted. This is essential for resolving a combination to its owning flexfield structure.
• SEGMENT1 through SEGMENT30 — The concatenated segments of the grade key flexfield. In practice only a small subset is typically enabled (for example a grade name segment and one or more validation segments), while the remainder are null. Together these segments form the business key of the combination.
• SUMMARY_FLAG / ENABLED_FLAG — Standard key flexfield control columns indicating whether the combination is a summary (parent) row and whether it is enabled for use.
• START_DATE_ACTIVE / END_DATE_ACTIVE — The date range during which the combination is valid.
• OBJECT_VERSION_NUMBER — The optimistic locking column used by the Oracle Application Object Library (AOL) to detect concurrent updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard AOL who-columns for audit and data lineage.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent program context columns recording which request and program last created or modified the row.

The heuristic business-key candidate is the unique index PER_GRADE_DEFINITIONS_PK on GRADE_DEFINITION_ID; the true natural business key is the flexfield segment combination scoped by ID_FLEX_NUM.

Common Use Cases and Queries

The table is queried whenever a grade's flexfield combination must be resolved, validated, or joined to descriptive grade data. Typical patterns include:

• Joining PER_GRADES to PER_GRADE_DEFINITIONS to retrieve both descriptive attributes and segment values for a grade.
• Validating that a given segment combination exists and is enabled, using ENABLED_FLAG and the active date range.
• Reporting on grade structures by concatenating the non-null segments.
• Feeding warehouse staging tables that extract grade combinations for HR analytics.

A representative query follows:

SELECT g.grade_id, g.name, d.segment1, d.segment2, d.segment3, d.enabled_flag
FROM per_grades g, per_grade_definitions d
WHERE g.grade_definition_id = d.grade_definition_id
AND d.enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL(d.start_date_active, SYSDATE)
AND NVL(d.end_date_active, SYSDATE);

For extraction consumers, resolve the flexfield structure through FND_ID_FLEX_STRUCTURES using ID_FLEX_NUM to correctly label each segment.

Related Objects

The following objects are the most significant referencees and dependencies of PER_GRADE_DEFINITIONS:

• PER_GRADES — References PER_GRADE_DEFINITIONS through PER_GRADES.GRADE_DEFINITION_ID. This is the principal dependent table, holding the descriptive attributes of each grade.
• EDW_HR_GRD_GRADES_LSTG — A warehouse staging table that carries GRADE_DEFINITION_ID referencing this table.
• EDW_HR_GRD_GRADES_LTC — A warehouse change-capture table that also references GRADE_DEFINITION_ID.
• FND_ID_FLEX_STRUCTURES — Not a foreign key dependency, but joins on ID_FLEX_NUM to identify the owning grade flexfield structure and its segment definitions.
• FND_FLEX_VALUES / FND_FLEX_VALUES_TL — The flexfield value tables that define the valid values appearing in SEGMENT1 through SEGMENT30.

Because PER_GRADES is a foreign keyed dependent, any grade definition row should be created and maintained in coordination with the grade it supports; orphaned grade definitions without a corresponding PER_GRADES row typically indicate incomplete setup.