Search Results note_type_id




Overview
Explore in AI Chat

Overview

The IGS_PS_CATLG_NOTES_ALL table is a core data repository within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically for the Oracle Student Management (formerly Campus Solutions) product family. Owned by the IGS (iGrants) schema, it functions as the master table for storing descriptive notes associated with academic catalog versions. Its primary role is to provide a structured, version-controlled mechanism for attaching various types of textual information—such as course descriptions, prerequisites, or administrative details—to catalog entries, which are fundamental to curriculum definition and student academic planning. The inclusion of the `ORG_ID` column signifies it is a multi-org enabled table, allowing data partitioning by operating unit for shared-instance implementations.

Key Information Stored

The table's design centers on linking a note to a specific catalog version and classifying it by type. The key columns include:

  • CATALOG_NOTE_ID: The primary key, a unique system-generated identifier for each note record.
  • CATALOG_VERSION_ID: A foreign key linking the note to a specific version of a catalog entry in a related table (e.g., IGS_PS_CATLG_VERS).
  • NOTE_TYPE_ID: A critical foreign key that classifies the note (e.g., description, objective, restriction). This references a lookup table defining valid note types, central to filtering and organizing note content.
  • SEQUENCE: Enforces ordering of multiple notes of the same type for a single catalog version, working in conjunction with CATALOG_VERSION_ID and NOTE_TYPE_ID to form a unique key (IGS_PS_CATLG_NOTES_ALL_U2).
  • NOTE_TEXT: Holds the formatted textual content of the note, up to 2000 characters.
  • CREATE_DATE & END_DATE: Define the active date range for the note's applicability.
  • ORG_ID: The operating unit identifier for multi-org security and data segregation.
The table is indexed for performance on its primary key (CATALOG_NOTE_ID) and on the composite unique key (CATALOG_VERSION_ID, NOTE_TYPE_ID, SEQUENCE), with an additional non-unique index on NOTE_TYPE_ID for efficient lookups.

Common Use Cases and Queries

This table is essential for generating official course catalogs, academic advisement reports, and course search interfaces. A frequent requirement is retrieving all active notes of a specific type for a set of catalog versions. For example, to extract the primary description (assuming NOTE_TYPE_ID=10) for active catalog versions, one might use:

SELECT cv.catalog_code, cn.note_text
FROM igs.igs_ps_catlg_notes_all cn,
     igs.igs_ps_catlg_vers_all cv
WHERE cn.catalog_version_id = cv.catalog_version_id
  AND cn.note_type_id = 10
  AND SYSDATE BETWEEN cn.create_date AND NVL(cn.end_date, SYSDATE+1)
  AND cv.org_id = :p_org_id;

Another common pattern involves sequencing notes, such as listing all note types in order for a given course: SELECT note_type_id, sequence, note_text FROM igs.igs_ps_catlg_notes_all WHERE catalog_version_id = :p_cat_ver_id ORDER BY note_type_id, sequence;. Data maintenance operations, such as inactivating notes or updating text, would target this table via its key columns, typically performed through the standard EBS forms or APIs.

Related Objects

While the provided dependency information shows no outward references from this table, its structure implies critical relationships. The CATALOG_VERSION_ID column is a foreign key to a catalog version table, likely named IGS_PS_CATLG_VERS_ALL. The NOTE_TYPE_ID column references a lookup table for note type definitions, such as IGS_LOOKUPS_VIEW with a specific lookup type. The table is referenced by the APPS synonym IGS_PS_CATLG_NOTES_ALL, which is the access point for all application code and standard reports. Custom interfaces and integrations that consume or populate catalog notes will directly query or modify this table via its synonym.