Search Results igs_ps_govt_spl_type_u1

Overview

The IGS_PS_GOVT_SPL_TYPE table is a core reference table within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically for the Oracle Student Management (OSM) or Campus Solutions modules, which reside under the IGS schema. Its primary function is to maintain the government-defined set of special program types. These standardized codes classify academic programs of special interest for official reporting and compliance purposes to government education bodies. The table acts as a master list, ensuring data integrity and consistency when tagging institutional course offerings with their appropriate government classification, which is critical for accurate statutory reporting and funding calculations.

Key Information Stored

The table stores the definitive list of government special program type codes along with their controlling attributes. The key columns are:

• GOVT_SPECIAL_COURSE_TYPE (VARCHAR2): The primary key column. It stores the unique alphanumeric code that identifies a program type (e.g., '1' for 'Not a program of special interest', '11' for 'Basic nurse education program').
• DESCRIPTION (VARCHAR2(60)): Provides the descriptive name or explanation for the corresponding code.
• CLOSED_IND (VARCHAR2): A flag, typically 'Y' or 'N', that indicates whether the code is active for use. Setting it to closed ('Y') prevents this type from being assigned to new or modified institutional records.
• Standard WHO Columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN): Audit columns that track the user and timestamp for record creation and modification, following standard EBS conventions.

Common Use Cases and Queries

This table is primarily used in two contexts: administrative setup and regulatory reporting. During system configuration, administrators populate this table with the valid codes mandated by the relevant government education authority. These codes are then made available in list of values (LOVs) when defining or updating program records elsewhere in the application. For reporting, the codes are joined to course or program data to generate submissions for government agencies.

A common query retrieves all active codes for use in a validation list or report:
SELECT govt_special_course_type, description FROM igs.igs_ps_govt_spl_type WHERE NVL(closed_ind, 'N') = 'N' ORDER BY 1;

Another typical use is to join this reference table to transactional data (e.g., course offerings) to decode the special program type for a detailed report:
SELECT c.course_code, c.version_number, g.description AS govt_special_type FROM igs_ps_course c, igs_ps_govt_spl_type g WHERE c.govt_special_course_type = g.govt_special_course_type;

Related Objects

Based on the provided dependency information, the IGS_PS_GOVT_SPL_TYPE table is referenced by other objects within the APPS and IGS schemas. While the specific foreign key relationships and column names are not detailed in the excerpt, the table's primary key (GOVT_SPECIAL_COURSE_TYPE) is almost certainly referenced as a foreign key in transactional tables related to programs or courses. A logical candidate would be a table such as IGS_PS_COURSE or a similar entity that stores course definitions, where a column like GOVT_SPECIAL_COURSE_TYPE would store a value validated against this reference table. The unique index IGS_PS_GOVT_SPL_TYPE_U1 on the GOVT_SPECIAL_COURSE_TYPE column enforces the primary key constraint required for these relationships.