Search Results igs_ca_inst_rel

Overview

The IGS_CA_INST_REL table is a core data structure within the Oracle E-Business Suite Student System (IGS), designed to define hierarchical and associative relationships between different calendar instances. Its primary role is to establish and enforce logical links, such as parent-child or superior-subordinate relationships, between academic calendar periods (e.g., linking a term instance to a session instance or an admission calendar to an academic calendar). This relational framework is critical for maintaining data integrity and enabling complex scheduling, enrollment, and administrative processes. It is important to note that the IGS product line is marked as obsolete in the provided ETRM documentation, indicating this table is part of a legacy system. Furthermore, the metadata explicitly states the table was "Not implemented in this database," suggesting it may have been a planned component not deployed in all instances or superseded by other structures.

Key Information Stored

The table's structure is defined by a composite primary key that uniquely identifies each relationship between two calendar instances. The key columns are SUB_CAL_TYPE and SUB_CI_SEQUENCE_NUMBER, which together identify the subordinate (or child) calendar instance, and SUP_CAL_TYPE and SUP_CI_SEQUENCE_NUMBER, which identify the superior (or parent) calendar instance. This design allows a single calendar instance to participate in multiple relationships, potentially as a subordinate in one context and a superior in another. The table itself likely contains minimal descriptive columns beyond these identifiers, functioning primarily as a junction table to map the relationships, with detailed information about each calendar instance stored in the related IGS_CA_INST_ALL table.

Common Use Cases and Queries

A primary use case is validating and navigating academic hierarchies for processes like enrollment and admissions. For instance, the system can determine all subordinate teaching periods within a given academic term for scheduling purposes. Common reporting queries involve joining this table to the calendar instance table twice to retrieve descriptive information for both sides of the relationship. A typical SQL pattern would be:

• Finding all child sessions for a specific parent term:

  SELECT sub_ci.*
  FROM igs_ca_inst_rel rel,
       igs_ca_inst_all sub_ci,
       igs_ca_inst_all sup_ci
  WHERE rel.sup_cal_type = 'TERM'
    AND rel.sup_ci_sequence_number = 101
    AND rel.sub_cal_type = sub_ci.cal_type
    AND rel.sub_ci_sequence_number = sub_ci.sequence_number
    AND rel.sup_cal_type = sup_ci.cal_type
    AND rel.sup_ci_sequence_number = sup_ci.sequence_number;

This relationship data is also essential for APIs and batch processes that require a complete understanding of the academic calendar structure to execute correctly.

Related Objects

The IGS_CA_INST_REL table sits at the center of a network of foreign key relationships with other critical Student System tables. Its primary dependencies are:

• IGS_CA_INST_ALL: The table is referenced twice by IGS_CA_INST_REL, providing the detailed definition for both the subordinate (SUB_CAL_TYPE, SUB_CI_SEQUENCE_NUMBER) and superior (SUP_CAL_TYPE, SUP_CI_SEQUENCE_NUMBER) calendar instances. This is the foundational relationship.
• IGS_AD_APPL_ALL (Admissions Applications): This table references IGS_CA_INST_REL, using the relationship to link an application to both an admission calendar (ADM_CAL_TYPE, ADM_CI_SEQUENCE_NUMBER) and an academic calendar (ACAD_CAL_TYPE, ACAD_CI_SEQUENCE_NUMBER).
• IGS_RC_I_APPL_ALL (Recruitment Applications): Similar to the admissions table, this recruitment application table references IGS_CA_INST_REL to associate applications with specific admission and academic calendar instances.

These relationships demonstrate how the calendar instance relationships defined in IGS_CA_INST_REL are consumed by key transactional tables to ensure consistency across the student lifecycle.