Results for “igs_ad_cat_ps_type”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IGS_AD_CAT_PS_TYPE is a foundational validation table within the Oracle E-Business Suite Student System (IGS) product family. Its documented purpose is to describe the admission category course types that are used to validate program offering option admission category mapping. In operational terms, the table establishes which combinations of admission category and course type are permissible, acting as a control mechanism that prevents invalid mappings from being created elsewhere in the admissions and program offering configuration screens.

Within the IGS schema the object is registered as a VALID table with seven physical columns in the ETRM 12.1.1 documentation. Applying a Data Vault classification heuristic based on the mined foreign key structure, the table is best understood as a link entity: it resolves a many-to-many relationship between two independent reference tables (IGS_AD_CAT_ALL and IGS_PS_TYPE_ALL) rather than storing descriptive attributes of either. This classification should be treated as a modeling suggestion derived from the constraint topology, not as an Oracle-imposed designation.

Key Information Stored

The table’s business content is concentrated in two columns, with the remaining five devoted to standard Oracle EBS audit and concurrency control. The most significant elements are:

• ADMISSION_CAT — the admission category code, forming the first component of the composite primary key and serving as a foreign key to IGS_AD_CAT_ALL.
• COURSE_TYPE — the course type code, forming the second component of the composite primary key and serving as a foreign key to IGS_PS_TYPE_ALL.
• CREATED_BY, CREATION_DATE — standard audit columns recording the user and timestamp of row insertion.
• LAST_UPDATED_BY, LAST_UPDATE_DATE — audit columns recording the most recent modification and its author.
• LAST_UPDATE_LOGIN — the login identifier associated with the last update, supporting multi-session concurrency tracking.

There is no single-column surrogate primary key. The primary key constraint IGS_AD_CAT_PS_TYPE_PK is defined over the composite pair (ADMISSION_CAT, COURSE_TYPE), and a unique index, IGS_AD_CAT_PS_TYPE_U1, exists over the same two columns. Accordingly, the business-key candidate is the (ADMISSION_CAT, COURSE_TYPE) pair itself; the uniqueness guarantee is therefore enforced at the semantic level rather than through a generated identifier.

Common Use Cases and Queries

The principal use case is validation. When a user configures a program offering option and assigns an admission category, the application consults this table to confirm that the admission category is permitted for the course type in question. Reporting and data-migration scenarios commonly need to enumerate valid combinations or detect orphaned mappings.

A typical validation query joins both parent reference tables to resolve codes into descriptions:

• SELECT t.ADMISSION_CAT, a.description, t.COURSE_TYPE, p.description FROM igs.igs_ad_cat_ps_type t JOIN igs.igs_ad_cat_all a ON a.admission_cat = t.admission_cat JOIN igs.igs_ps_type_all p ON p.course_type = t.course_type;

Other practical patterns include counting the number of course types permitted per admission category, identifying rows that were recently modified by referencing LAST_UPDATE_DATE and LAST_UPDATED_BY, and reconciling configuration between a source and target instance during a clone or release migration by comparing the set of (ADMISSION_CAT, COURSE_TYPE) pairs. Because the table is narrow and fully keyed, it also serves as a compact lookup for ad hoc extracts feeding downstream student records systems.

Related Objects

The table’s dependencies are defined by its two foreign keys and the objects that consume the validated combinations:

• IGS_AD_CAT_ALL — the admission category reference table; joined on IGS_AD_CAT_PS_TYPE.ADMISSION_CAT = IGS_AD_CAT_ALL.ADMISSION_CAT.
• IGS_PS_TYPE_ALL — the program/course type reference table; joined on IGS_AD_CAT_PS_TYPE.COURSE_TYPE = IGS_PS_TYPE_ALL.COURSE_TYPE.
• IGS_AD_CAT_PS_TYPE_PK — the composite primary key constraint enforcing uniqueness of the two business columns.
• IGS_AD_CAT_PS_TYPE_U1 — the unique index supporting the same business-key pair.
• Program offering option configuration entities within the IGS admissions and program offerings modules, which reference this validation set when recording admission category mappings.

Any modification to rows in this table directly affects the range of admissible category-to-course-type mappings available to those downstream configuration screens, making it a high-impact maintenance object despite its small column count.