Search Results igs_ps_cat




Overview
Explore in AI Chat

Overview

IGS_PS_CAT is a reference (lookup) table within the Oracle E-Business Suite Student System (IGS) product family. It stores user-definable program categories that are applied to all academic programs offered by the institution. Because the IGS schema governs student records — program enrollments, admissions, curriculum tracking, and progression — this table acts as the controlled vocabulary that classifies every program into institution-defined groupings such as undergraduate, postgraduate, research, or any other category the institution chooses to maintain.

From a Data Vault modeling perspective, the FK-structure heuristic suggests classifying IGS_PS_CAT as hub-leaning. In practical terms, this means the table behaves primarily as a durable list of business keys (program categories) that other tables reference, rather than as a transactional or descriptive satellite of some other parent entity. A Data Vault implementation would typically model COURSE_CAT as a hub key, with DESCRIPTION and CLOSED_IND flowing into an adjacent satellite.

Key Information Stored

The documented physical schema in ETRM 12.1.1 exposes eight columns on the IGS schema. The most significant are:

  • COURSE_CAT — the business key and single column comprising the primary key constraint IGS_PS_CAT_PK and the unique index IGS_PS_CAT_U1. It holds the short code that identifies a program category.
  • DESCRIPTION — the descriptive text associated with the category code, used on forms, reports, and self-service pages.
  • CLOSED_IND — a flag indicating whether the category has been end-dated or closed to new assignments while remaining valid for historical records.
  • CREATED_BY / CREATION_DATE — standard EBS audit columns recording who created the category and when.
  • LAST_UPDATED_BY / LAST_UPDATE_LOGIN / LAST_UPDATE_DATE — standard audit columns tracking the most recent modification, its author, and the login context.

As with most IGS reference tables, the primary key here is the business key itself (COURSE_CAT) rather than a separate surrogate ID; there is no numeric surrogate key documented. The unique index IGS_PS_CAT_U1 is functionally equivalent to the PK and can be treated as an alternate business-key candidate during integration work.

Common Use Cases and Queries

Typical usage centres on classifying, filtering, and validating program records. A simple lookup of active categories is a frequent starting point:

SELECT course_cat, description
FROM   igs.igs_ps_cat
WHERE  closed_ind = 'N'
ORDER BY description;

Validating that a program is assigned to a permitted category joins this table to the categorisation table:

SELECT p.course_cat,
       c.description,
       p.program_code
FROM   igs.igs_ps_categorise_all p,
       igs.igs_ps_cat             c
WHERE  p.course_cat = c.course_cat(+)
ORDER BY c.description, p.program_code;

Reporting scenarios include program catalogs grouped by category, admissions funnel analysis by program type, and enrolment or completion reporting rolled up by category. Because CLOSED_IND is present, historical reporting should include closed categories while operational validation should restrict to CLOSED_IND = 'N'. Extraction into a warehouse typically loads IGS_PS_CAT as a dimension and the categorisation table as a factless association fact.

Related Objects

The principal dependent object is IGS_PS_CATEGORISE_ALL, which carries the foreign key COURSE_CAT referencing IGS_PS_CAT.COURSE_CAT. This table records the actual assignment of categories to programs, and it is the join path used in nearly every query involving IGS_PS_CAT.

  • IGS_PS_CATEGORISE_ALL — the direct child, joined on COURSE_CAT.
  • IGS_PS_VERSION and associated program version tables — the program records whose classifications are resolved through IGS_PS_CATEGORISE_ALL.
  • IGS_PS_ENROLL_ALL — enrolment records often aggregated by program category for reporting.
  • IGS_PS_APPL_ALL — admissions applications analysed by program category.
  • IGS_PS_CAT_PK / IGS_PS_CAT_U1 — the primary key and unique index that enforce uniqueness of COURSE_CAT and guarantee referential integrity from the child table.

No seed or validation APIs are documented specifically for IGS_PS_CAT in the ETRM extract; maintenance is normally performed through the IGS setup forms that populate this table directly. Any change to COURSE_CAT values must respect the FK from IGS_PS_CATEGORISE_ALL to avoid orphaning program categorisations.