Search Results per_mm_valid_grades_fk3

Overview

HR.PER_MM_VALID_GRADES is a core transactional table in Oracle EBS Human Resources (versions 12.1.1 and 12.2.2) that stores the set of valid grade assignments permitted for a position during a mass move operation. The table serves as a validation and filtering mechanism within the Mass Move process (functionally associated with the PER_MASS_MOVES module). When an HR administrator initiates a mass move of positions—such as restructuring a department or transferring multiple incumbents—this table defines which grades can be applied to each target position. By linking a specific mass move event (identified by MASS_MOVE_ID) to a position (POSITION_ID) and an allowable grade (TARGET_GRADE_ID), the table enforces business rules that prevent invalid grade assignments. The TARGET_GRADE_ID column, the specific focus of this reference, is a foreign key to PER_GRADES and directly controls which grade values are valid for the moved position. Without entries in this table, the mass move engine would not have a validated list of grades to apply, making it a critical control point for data integrity during organizational changes.

Key Information Stored

The table is defined with a composite primary key (PER_MM_VALID_GRADES_PK) consisting of three mandatory columns, each of type NUMBER(15):

• MASS_MOVE_ID — Foreign key to PER_MASS_MOVES. Identifies the specific mass move batch or event. This column groups all valid grade assignments under a single mass move transaction.
• POSITION_ID — Foreign key to PER_ALL_POSITIONS. Identifies the source position being moved. While the column name refers to "source position," in practice it anchors the relationship between the mass move and the position whose grades are being validated.
• TARGET_GRADE_ID — Foreign key to PER_GRADES. This is the grade that is deemed valid for the position during the move. The user’s search for target_grade_id directly corresponds to this column, which determines which grade values are permissible for the target assignment.

The table also includes a standard set of DESCRIPTIVE FLEXFIELD columns (ATTRIBUTE_CATEGORY through ATTRIBUTE15) of type VARCHAR2(150). These allow custom attribute capture without schema modifications, storing user-defined context data (e.g., approval timestamps, reason codes).

Common Use Cases and Queries

1. Validating grade assignments during mass moves: The primary functional use is to predefine which grades can be assigned to positions being moved. For example, when moving a "Manager" position to a new department, only grades in PER_MM_VALID_GRADES for that mass move will be available in the UI or API.

2. Reporting permitted grades per mass move: Analysts often query this table to audit which grades were authorized. A typical report pattern:

SELECT mmv.mass_move_id,
       mmv.position_id,
       mmv.target_grade_id,
       grd.name grade_name
FROM   hr.per_mm_valid_grades mmv,
       hr.per_grades grd
WHERE  mmv.target_grade_id = grd.grade_id
AND    mmv.mass_move_id = :p_mass_move_id;

3. Checking for missing grade validations: Before executing a mass move, it is prudent to ensure all positions involved have at least one valid grade entry. The following query identifies positions without any valid grades in the move:

SELECT pos.position_id, pos.name
FROM   hr.per_all_positions pos
WHERE  NOT EXISTS (
    SELECT 1
    FROM   hr.per_mm_valid_grades vg
    WHERE  vg.mass_move_id = :p_mass_move_id
    AND    vg.position_id = pos.position_id
);

4. Bulk loading valid grades via APIs: Custom data loading programs use this table with the PER_MM_VALID_GRADES_PK to insert or update records. Because the primary key is composite, a MERGE statement is often used to avoid primary key violations.

5. Integration with descriptive flexfields: When custom attributes are enabled (e.g., ATTRIBUTE1 storing a "Grade Effective Date"), the table is used in conjuction with flexfield value sets to enforce additional grade validity rules.

Related Objects

Based on the documented foreign key relationships from the ETRM metadata, PER_MM_VALID_GRADES references the following tables:

• PER_MASS_MOVES — via column MASS_MOVE_ID. This is the parent mass move header table. Each valid grade entry belongs to exactly one mass move transaction. Deleting a mass move will cascade to invalidate its child grade records (depending on the application's delete policy, not enforced at database level but logically implied).
• PER_ALL_POSITIONS — via column POSITION_ID. This is the position table containing all defined positions. The relationship implies that grades are validated per position, not globally.
• PER_GRADES — via column TARGET_GRADE_ID. This is the core grade lookup table. The TARGET_GRADE_ID links directly to PER_GRADES.GRADE_ID, allowing retrieval of grade names, codes, and hierarchies.

The table also has two non-unique indexes for performance optimization:

• PER_MM_VALID_GRADES_FK2 on POSITION_ID — supports efficient position-based lookups.
• PER_MM_VALID_GRADES_FK3 on TARGET_GRADE_ID — accelerates grade-based queries and joins to PER_GRADES.

No other tables are documented as referencing PER_MM_VALID_GRADES in the provided metadata. In practice, custom programs or Oracle's Mass Move APIs (PER_MM_VALID_GRADES_PKG) may directly manipulate this table as part of the mass move lifecycle.