Search Results replaced_sequence_number

Overview

The IGS_RE_SPRVSR table is a core data object within the Oracle E-Business Suite (EBS) Release 12.1.1 and 12.2.3, specifically for the Oracle Student Management (OSM) or related educational modules under the IGS (iGrants/Student Systems) schema. It serves as the master table for storing and managing the supervisory relationships for research student candidatures. Each record defines a specific supervisor assigned to a student's research program, capturing the assignment's temporal validity, the supervisor's role type, and associated administrative details such as load and funding percentages. Its primary role is to maintain the complex, often multi-supervisor structure associated with higher-degree research programs within the institution.

Key Information Stored

The table's structure is centered on linking a student candidature to a supervisor with detailed contextual attributes. The primary key is a composite of four columns: CA_PERSON_ID and CA_SEQUENCE_NUMBER (identifying the student candidature), and PERSON_ID and SEQUENCE_NUMBER (identifying the specific supervisor record for that person). Critical columns include START_DT and END_DT, which manage the effective period of the supervisory assignment. The RESEARCH_SUPERVISOR_TYPE categorizes the supervisor's role (e.g., principal, associate). Notably, the REPLACED_PERSON_ID and REPLACED_SEQUENCE_NUMBER columns, referenced in the IGS_RE_SPRVSR_N2 index, are pivotal for tracking historical changes, indicating when one supervisor has been formally replaced by another in a given role, thereby maintaining an audit trail of supervisory changes.

Common Use Cases and Queries

A primary use case is generating reports on supervisory load by academic unit or individual, often utilizing the SUPERVISION_PERCENTAGE and ORG_UNIT_CD. Another critical scenario is managing supervisor changes, where queries involving the REPLACED_PERSON_ID column are essential to understand succession history. Common SQL patterns include listing all current supervisors for a candidature and identifying replacement chains.

• Find all current principal supervisors for a specific organizational unit:
  SELECT * FROM IGS.IGS_RE_SPRVSR WHERE ORG_UNIT_CD = '<CODE>' AND RESEARCH_SUPERVISOR_TYPE = 'PRINCIPAL' AND (END_DT IS NULL OR END_DT > SYSDATE);
• Trace the replacement history for a supervisor role:
  SELECT s2.* FROM IGS.IGS_RE_SPRVSR s1 JOIN IGS.IGS_RE_SPRVSR s2 ON s1.PERSON_ID = s2.REPLACED_PERSON_ID AND s1.SEQUENCE_NUMBER = s2.REPLACED_SEQUENCE_NUMBER WHERE s1.PERSON_ID = <ID>;

Related Objects

The table is integral within the research candidature data model, with defined foreign key relationships ensuring referential integrity. Key related objects include:

• IGS_RE_CANDIDATURE_ALL: The table is primarily linked to the student research candidature via the foreign key on (CA_PERSON_ID, CA_SEQUENCE_NUMBER).
• HZ_PARTIES: The PERSON_ID column references the HZ_PARTIES table in the Trading Community Architecture (TCA) model, linking to the master person record for the supervisor.
• IGS_RE_SPRVSR_TYPE: The RESEARCH_SUPERVISOR_TYPE column references this lookup table to validate supervisor role codes.
• Self-Referential Relationship: The table contains a foreign key referencing itself (IGS_RE_SPRVSR.CA_PERSON_ID → IGS.IGS_RE_SPRVSR), which, along with the REPLACED_PERSON_ID columns, supports hierarchical or historical tracking of supervisory assignments within the same table.