Results for “igs_ps_unit_ofr_opt_all_u2”

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

Overview

IGS.IGS_PS_UNIT_OFR_OPT_ALL is the master transactional table in the Oracle EBS Student System (IGS) that describes the available options for each unit offering. It sits at the center of the academic scheduling and enrollment model, holding one row per unit offering option (UOO) — the specific instance in which a unit may be studied, defined by calendar type, calendar instance, location, unit class, and section attributes. In Oracle EBS 12.1.1 and 12.2.2 this table is registered as an FND Design Data object owned by the IGS schema and stored in the APPS_TS_TX_DATA tablespace with PCTFREE 10.

Because nearly every enrollment, assessment, fee, and eligibility process point references a unit offering option, the table acts as a principal integration point between curriculum definition (unit offerings and patterns) and student activity (attempts, classes, charges, and grading). From a Data Vault modeling perspective, the foreign-key topology suggests this object behaves as a hub: UOO_ID is a stable, surrogate business key referenced by a large number of dependent tables, while descriptive attributes are distributed to surrounding satellites.

Key Information Stored

The table contains 54 documented columns. The most functionally significant are:

• UOO_ID — surrogate unique identifier for the unit offering option; enforced by unique index IGS_PS_UNIT_OFR_OPT_ALL_U2 and used as the join key by virtually all dependent tables.
• UNIT_CD — unique identifier of the unit being offered.
• VERSION_NUMBER — version of the unit; students are normally enrolled in the current version, with prior versions end-dated.
• CAL_TYPE and CI_SEQUENCE_NUMBER — the teaching calendar type and its specific calendar instance in which the unit is offered.
• LOCATION_CD — institution-defined location code, typically a campus.
• UNIT_CLASS — institution-defined unit class code.
• CALL_NUMBER — publication/reference number used for the offering option.
• UNIT_SECTION_STATUS, UNIT_SECTION_START_DATE, UNIT_SECTION_END_DATE — section lifecycle and date bounds.
• ENROLLMENT_ACTUAL and WAITLIST_ACTUAL — actual enrollment and waitlist counts.
• UNIT_QUOTA and UNIT_QUOTA_RESERVED_PLACES — capacity and reserved-seating limits.
• GRADING_SCHEMA_CD and GS_VERSION_NUMBER — grading schema applied to the option.
• SUP_UOO_ID, RELATION_TYPE, DEFAULT_ENROLL_FLAG — supporting relationships to parent or alternative options.

The business-key candidate is index IGS_PS_UNIT_OFR_OPT_ALL_U1 on (UNIT_CD, VERSION_NUMBER, CAL_TYPE, CI_SEQUENCE_NUMBER, LOCATION_CD, UNIT_CLASS); the primary key documented for the ETRM dependency model is IGS_PS_UNIT_OFR_OPT_ALL_UK2 on (CAL_TYPE, CI_SEQUENCE_NUMBER, CALL_NUMBER). The remaining seven indexes (N1–N7) accelerate lookups on UNIT_CLASS, LOCATION_CD, UNIT_CONTACT, grading schema, calendar instance, SUP_UOO_ID, and CALL_NUMBER.

Common Use Cases and Queries

Typical usage centers on resolving a UOO_ID from academic scheduling attributes, then drilling into student and fee activity.

• Offering catalog reporting — join to IGS_PS_UNIT_OFR_PAT_ALL on UNIT_CD to list all options for a unit and version.
• Enrollment capacity monitoring — compare ENROLLMENT_ACTUAL against UNIT_QUOTA to identify full or over-subscribed sections.
• Calendar-instance extraction — filter by CAL_TYPE and CI_SEQUENCE_NUMBER to produce timetabling and room-booking extracts.
• Student attempt analysis — join IGS_EN_SU_ATTEMPT_ALL on UOO_ID for enrollment history.

A representative query pattern resolving options for a teaching period:

SELECT o.unit_cd, o.version_number, o.call_number, o.location_cd, o.unit_class, o.enrollment_actual, o.unit_quota
FROM igs_ps_unit_ofr_opt_all o
WHERE o.cal_type = :p_cal_type
  AND o.ci_sequence_number = :p_ci_seq
  AND o.offered_ind = 'Y';

Because the table is a transactional core, reports should always scope by calendar instance or org before aggregating to avoid full scans on a large installation.

Related Objects

The FK topology shows both upstream masters and extensive downstream dependents:

• IGS_PS_UNIT_OFR_PAT_ALL — parent unit offering pattern; joined on UNIT_CD.
• IGS_AD_LOCATION_ALL — location master; joined on LOCATION_CD.
• IGS_AS_UNIT_CLASS_ALL — unit class master; joined on UNIT_CLASS.
• IGS_AS_GRD_SCHEMA — grading schema definition; joined on GRADING_SCHEMA_CD.
• HZ_PARTIES — unit contact party; joined on UNIT_CONTACT.
• IGS_EN_SU_ATTEMPT_ALL — student unit attempts referencing UOO_ID and UNIT_CD.
• IGS_PS_USEC_ACCTS_ALL, IGS_PS_USEC_CHARGE, IGS_PS_USEC_SP_FEES — fee and account associations per option.
• IGS_EN_ELGB_OVR_UOO and IGS_EN_PLAN_UNITS — eligibility overrides and study plan units referencing UOO_ID.
• IGS_PS_USEC_CLAS_MEET / IGS_PS_US_EXAM_MEET — class and exam meeting scheduling.
• IGS_PS_UNT_OFR_OPT_N — additional option attributes referencing UNIT_CD and UOO_ID.

These relationships confirm the table's role as the enrollment-time hub through which scheduling, assessment, and financial data converge.