Results for “igs_ps_of_opt_ad_cat”

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

Overview

The IGS_PS_OF_OPT_AD_CAT table is a core configuration entity within the Oracle E-Business Suite Student System (IGS) product family. It defines the set of admission categories that are permitted to enroll in a specific program offering option. In practical terms, this table acts as the eligibility bridge between a deliverable program offering option — identified by course code, version, calendar type, location, attendance mode, and attendance type — and the admission categories that the institution recognizes for that option. Without a row in this table, an admission category has no declared enrolment rights against the corresponding program offering option.

The record is owned by the IGS schema and is marked VALID in the ETRM repository. It is populated and maintained through the Student System's program offering setup flows. Its heuristic Data Vault classification is satellite-leaning, suggesting that it should be modeled as a descriptive satellite keyed to the program offering option hub. This classification reflects the fact that the table stores descriptive eligibility attributes (such as the system default indicator) rather than functioning as an independent business hub or as a pure many-to-many link between two equal hubs. Analysts building dimensional or Data Vault models on IGS data should treat it accordingly.

Key Information Stored

The table contains 14 documented columns in Oracle EBS 12.1.1. The most significant are summarized below.

• COURSE_CD — The course code identifying the program offering option; part of the composite primary key and a foreign key to IGS_PS_OFR_OPT_ALL.
• VERSION_NUMBER — The version of the program offering option.
• CAL_TYPE — The calendar type associated with the offering option (for example, standard academic year calendars).
• LOCATION_CD — The teaching location for which the category eligibility applies.
• ATTENDANCE_MODE — The mode of attendance (for example, full-time or part-time).
• ATTENDANCE_TYPE — The attendance type qualifier used alongside the mode.
• ADMISSION_CAT — The admission category eligible for the option; a foreign key to IGS_AD_CAT_ALL.
• COO_ID — Surrogate identifier joining this row back to IGS_PS_OFR_OPT_ALL, allowing a single-column reference to the parent offering option.
• SYSTEM_DEFAULT_IND — Indicator that the admission category is the system default for the associated option.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard Oracle EBS WHO columns used for audit and concurrency tracking.

The primary key is IGS_PS_OF_OPT_AD_CAT_PK, covering COURSE_CD, VERSION_NUMBER, CAL_TYPE, LOCATION_CD, ATTENDANCE_MODE, ATTENDANCE_TYPE, and ADMISSION_CAT. The unique index IGS_PS_OF_OPT_AD_CAT_U1 mirrors these same seven columns, confirming them as the business-key candidate. No separate surrogate key column is documented; COO_ID serves as a secondary reference to the parent offering option.

Common Use Cases and Queries

The most frequent operational requirement is to determine which admission categories are valid for a given program offering option, typically during admissions processing or prospect validation.

SELECT admission_cat, system_default_ind
FROM   igs.igs_ps_of_opt_ad_cat
WHERE  course_cd       = :course_cd
AND    version_number  = :version_number
AND    cal_type        = :cal_type
AND    location_cd     = :location_cd
AND    attendance_mode = :attendance_mode
AND    attendance_type = :attendance_type;

A second common pattern resolves the descriptive name of each admission category by joining to the reference table:

SELECT ac.admission_cat, ac.description, oac.system_default_ind
FROM   igs.igs_ps_of_opt_ad_cat oac,
       igs.igs_ad_cat_all        ac
WHERE  oac.admission_cat = ac.admission_cat
AND    oac.course_cd     = :course_cd;

Reporting use cases include auditing which options have no admission categories defined, verifying that exactly one system default exists per option, and producing extracts that feed admissions eligibility rules. Because the table is satellite-leaning, it is also a natural source for change-tracking feeds driven by LAST_UPDATE_DATE.

Related Objects

The table participates in a small but well-defined network of IGS objects:

• IGS_PS_OFR_OPT_ALL — The parent program offering option table. Joined via COURSE_CD, VERSION_NUMBER, CAL_TYPE, LOCATION_CD, ATTENDANCE_MODE, and ATTENDANCE_TYPE, and also via COO_ID.
• IGS_AD_CAT_ALL — The admission category reference table, joined on ADMISSION_CAT; supplies the descriptive name and status of each category.
• IGS_PS_COO_AD_UNIT_S — A dependent entity that references this table on the full option-plus-category key, linking admission categories to admission units within a program offering option.
• IGS_PS_OF_OPT_AD_CAT_PK / _U1 — The primary key constraint and unique index enforcing the business key.

Together these objects support the setup and maintenance of program offering option eligibility rules across the Student System.