Search Results igs_ps_cat_u1

Overview

IGS.IGS_PS_CAT is a foundational reference (setup) table within the Oracle E-Business Suite Student Systems / Student Information schema, owned by the IGS schema. It stores institution-defined program categories that are applied across all academic programs offered by the university. Program categories provide a flexible classification mechanism that institutions can tailor to their own academic and reporting structures, allowing programs to be grouped for curriculum planning, reporting, admissions statistics, and regulatory submissions.

The table resides in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10, indicating a transactional-data orientation with moderate update activity. The single unique index, IGS_PS_CAT_U1, is stored in APPS_TS_TX_IDX on the COURSE_CAT column, and the physical primary key is IGS_PS_CAT_PK (COURSE_CAT). Based on the mined foreign-key structure, this object is heuristic-classified as hub-leaning in a Data Vault modeling sense: it functions as a central business-key entity (a "hub") referenced by downstream association tables, rather than as a pure satellite holding descriptive history. This classification is a modeling suggestion, not a physical constraint of the EBS table itself.

Key Information Stored

The table contains eight documented columns in the 12.1.1 physical schema:

• COURSE_CAT (VARCHAR2(10), mandatory) — the unique business identifier for each institution-defined program category. This is both the primary key column and the sole column of the unique index IGS_PS_CAT_U1, making it the single business-key candidate for the table.
• DESCRIPTION (VARCHAR2(60)) — the human-readable description of the program category, used in list-of-values displays, reports, and inquiry forms.
• CLOSED_IND — a flag indicating whether the category is open or closed. When set to closed, the category can no longer be assigned to new or modified program categorization records, providing a soft-retirement mechanism that preserves historical associations.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE — the standard EBS "Who" audit columns, capturing row-level insert/update provenance for compliance and traceability.

No surrogate key column is documented; the primary key is the natural business key COURSE_CAT itself.

Common Use Cases and Queries

Typical uses include validating category values during program setup, generating category-based program listings for academic reporting, and auditing closed versus active categories ahead of publication cycles.

• Retrieve all active categories for a pick-list: SELECT COURSE_CAT, DESCRIPTION FROM IGS.IGS_PS_CAT WHERE CLOSED_IND = 'N' ORDER BY COURSE_CAT;
• Look up a description for a known category: SELECT DESCRIPTION FROM IGS.IGS_PS_CAT WHERE COURSE_CAT = :p_cat;
• Audit recent maintenance: SELECT COURSE_CAT, DESCRIPTION, LAST_UPDATED_BY, LAST_UPDATE_DATE FROM IGS.IGS_PS_CAT WHERE LAST_UPDATE_DATE >= :p_since;
• Join to the categorization table to report which programs belong to each category: SELECT c.COURSE_CAT, c.DESCRIPTION, a.* FROM IGS.IGS_PS_CAT c, IGS.IGS_PS_CATEGORISE_ALL a WHERE c.COURSE_CAT = a.COURSE_CAT;

Related Objects

The principal dependent object is IGS_PS_CATEGORISE_ALL, which stores the actual assignment of categories to programs. Its COURSE_CAT foreign key resolves to IGS.IGS_PS_CAT.COURSE_CAT, forming the hub-to-link relationship that drives category membership reporting. The APPS synonym/view IGS_PS_CAT exposes the base table for application-layer access, and the primary key constraint IGS_PS_CAT_PK together with the unique index IGS_PS_CAT_U1 enforce uniqueness on COURSE_CAT. No other tables are documented as referencing this object, and the table itself references no other database object, confirming its role as a self-contained reference hub.