Search Results igs_tr_step_note




Overview
Explore in AI Chat

Overview

The IGS_TR_STEP_NOTE table is a core data structure within the Oracle E-Business Suite Student System (IGS) modules for releases 12.1.1 and 12.2.2. It functions as a junction or association table, centralizing the relationship between tracking steps and the notes attached to them. Its primary role is to store references that link a specific step in a student or administrative tracking process to one or more textual notes, which are stored in a separate, generic notes repository. This design promotes data normalization and reusability of the notes infrastructure across the application.

Key Information Stored

The table's structure is defined by its composite primary key, which uniquely identifies each note association for a tracking step. The critical columns are the foreign keys that establish these relationships. The TRACKING_ID and TRACKING_STEP_ID columns together uniquely identify the specific step within a tracking record, linking to the IGS_TR_STEP table. The REFERENCE_NUMBER column is a foreign key that points to the actual note text and metadata stored in the generic IGS_GE_NOTE table. Additionally, the TRK_NOTE_TYPE column links to the IGS_TR_NOTE_TYPE table, classifying the note (e.g., internal comment, student communication, administrative alert) within the context of the tracking system.

Common Use Cases and Queries

This table is essential for any process requiring the retrieval, auditing, or reporting of notes associated with a student's progression through a defined workflow, such as application review, academic advising, or disciplinary tracking. A common reporting use case is to generate a complete audit trail of all comments and notes for a specific tracking case. A typical query would join IGS_TR_STEP_NOTE to IGS_TR_STEP for step details, IGS_GE_NOTE for the note text and creation date, and IGS_TR_NOTE_TYPE for the note type description.

Sample SQL Pattern:
SELECT st.tracking_id,
st.tracking_step_id,
nt.note_type,
gn.note_text,
gn.created_by,
gn.creation_date
FROM igs_tr_step_note sn,
igs_tr_step st,
igs_ge_note gn,
igs_tr_note_type nt
WHERE sn.tracking_id = st.tracking_id
AND sn.tracking_step_id = st.tracking_step_id
AND sn.reference_number = gn.reference_number
AND sn.trk_note_type = nt.trk_note_type
AND st.tracking_id = :p_tracking_id
ORDER BY st.tracking_step_id, gn.creation_date;

Related Objects

The IGS_TR_STEP_NOTE table sits at the intersection of several key objects in the Student System's tracking architecture, as defined by its documented foreign key relationships:

  • IGS_TR_STEP: The table is a child of IGS_TR_STEP, referencing it via the composite foreign key on columns TRACKING_ID and TRACKING_STEP_ID. This enforces that a note can only be associated with an existing tracking step.
  • IGS_GE_NOTE: The table references IGS_GE_NOTE via the REFERENCE_NUMBER column. This links the association record to the actual note content, author, and timestamps stored in the generic notes table.
  • IGS_TR_NOTE_TYPE: The table references IGS_TR_NOTE_TYPE via the TRK_NOTE_TYPE column, ensuring the note is categorized using a valid, predefined type within the tracking system.

The primary key constraint IGS_TR_STEP_NOTE_PK on (TRACKING_ID, TRACKING_STEP_ID, REFERENCE_NUMBER) ensures that a specific note is not duplicated against the same tracking step.