Search Results igs_co_ou_co_ref_all




Overview
Explore in AI Chat

Overview

The table IGS_CO_OU_CO_REF_ALL is a core data object within the Oracle E-Business Suite Student System (IGS). It functions as a detailed reference table, specifically designed to store and manage person-level references linked to an outgoing correspondence item. In the context of student administration, outgoing correspondence can include official letters, notifications, or documents sent to students, faculty, or other parties. This table provides the granular link between a specific piece of correspondence and the precise academic context for a person, such as their course or unit enrollment at a particular point in time. Its role is critical for maintaining an auditable and detailed record of who a correspondence item pertains to and under which specific academic parameters it was generated, supporting complex student communications and reporting.

Key Information Stored

The table's structure is defined by its composite primary key and several foreign key relationships. The primary key uniquely identifies each reference record through a combination of: PERSON_ID (the individual), CORRESPONDENCE_TYPE and REFERENCE_NUMBER (identifying the parent correspondence item), ISSUE_DT (the date of the correspondence), and a SEQUENCE_NUMBER. Beyond these identifiers, the table holds key columns that link the correspondence to the person's academic record. These include CAL_TYPE and CI_SEQUENCE_NUMBER (linking to a calendar instance), COURSE_CD and CV_VERSION_NUMBER (linking to a specific course version), and UNIT_CD and UV_VERSION_NUMBER (linking to a specific unit version). This design allows a single correspondence item to have multiple, context-specific references for a person.

Common Use Cases and Queries

A primary use case is generating detailed audit reports for all correspondence sent regarding a specific student's enrollment in a particular course or unit. Administrators may need to query this table to verify what official communications were sent in relation to a student's academic progression, appeals, or fee statements. Another scenario involves data validation, ensuring that correspondence references point to valid academic structures. A typical query pattern involves joining to the parent correspondence table (IGS_CO_OU_CO_ALL) and the relevant academic tables.

Sample Query Pattern:
SELECT ref.*, co.actual_dt
FROM igs_co_ou_co_ref_all ref,
igs_co_ou_co_all co
WHERE ref.person_id = :p_person_id
AND ref.course_cd = :p_course_cd
AND ref.person_id = co.person_id
AND ref.correspondence_type = co.correspondence_type
AND ref.reference_number = co.reference_number
AND ref.issue_dt = co.issue_dt;

Related Objects

The table maintains integral relationships with several other Student System tables, as documented by its foreign key constraints:

  • IGS_CO_OU_CO_ALL: This is the parent table for the outgoing correspondence header. The foreign key on (PERSON_ID, CORRESPONDENCE_TYPE, REFERENCE_NUMBER, ISSUE_DT) enforces that every detail record in IGS_CO_OU_CO_REF_ALL must relate to a valid master correspondence record.
  • IGS_CA_INST_ALL (Calendar Instance): Linked via columns CAL_TYPE and CI_SEQUENCE_NUMBER, this ties the correspondence reference to a specific academic period.
  • IGS_PS_VER_ALL (Course Version): Linked via columns COURSE_CD and CV_VERSION_NUMBER, this associates the reference with the exact version of a course the person was enrolled in.
  • IGS_PS_UNIT_VER_ALL (Unit Version): Linked via columns UNIT_CD and UV_VERSION_NUMBER, this associates the reference with the exact version of a unit.

These relationships ensure data integrity and enable comprehensive reporting across the student lifecycle.