Results for “igs_ps_ofr_all”

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

Overview

IGS_PS_OFR_ALL is a Student System (IGS) table that describes an offering of a university program within a calendar type. It sits at the center of the academic program offering model, recording which version of a program (course) is offered in a given academic calendar type — for example, a specific program version offered in a particular academic year or term structure. The table is owned by the IGS schema and, per the ETRM 12.1.1 physical schema, contains 30 columns.

In Oracle EBS 12.1.1 and 12.2.2, IGS is delivered as an obsolete product, meaning the module is not implemented in the standard database and no runtime data is maintained. The table remains relevant for reference, historical migrations, and understanding legacy Student System data models. Based on its foreign-key structure, the table is classified heuristically as hub-leaning in a Data Vault model: it holds a stable, uniquely identifiable business entity (the program offering) that other tables reference, rather than acting purely as a transactional satellite. This classification is a modeling suggestion, not a physical constraint of the delivered table.

Key Information Stored

The primary key is IGS_PS_OFR_PK, a composite business key defined on COURSE_CD, VERSION_NUMBER, and CAL_TYPE. The unique index IGS_PS_OFR_ALL_U1 carries the same three columns, confirming them as the business-key candidate for the offering.

• COURSE_CD — the program (course) code being offered; part of the primary key and a foreign key to IGS_PS_VER_ALL.
• VERSION_NUMBER — the program version identifier, distinguishing revisions of the same program; part of the primary key and referencing IGS_PS_VER_ALL.
• CAL_TYPE — the academic calendar type in which the offering occurs; part of the primary key and a foreign key to IGS_CA_TYPE.
• ORG_ID — the operating unit / organization context, supporting multi-org security and filtering.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE20 — the standard EBS descriptive flexfield (DFF) columns, providing extensible, client-configurable attributes for the offering.
• CREATED_BY, CREATION_DATE — standard audit columns recording the creating user and timestamp.
• LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit columns recording the last modification user, timestamp, and login.

The surrogate-vs-business distinction is important: there is no separate single-column surrogate key documented; the composite COURSE_CD, VERSION_NUMBER, CAL_TYPE serves as both the physical primary key and the natural business key.

Common Use Cases and Queries

Typical use cases center on identifying which program versions are offered in which calendar types and joining to related offering detail tables. A representative query lists all offerings for a given program version:

• SELECT course_cd, version_number, cal_type FROM igs_ps_ofr_all WHERE course_cd = :p_course_cd AND version_number = :p_version;
• Validating calendar-type assignments by joining to IGS_CA_TYPE to confirm the calendar type is valid.
• Driving offering detail reports by joining to IGS_PS_OFR_OPT_ALL, IGS_PS_OFR_INST, or IGS_PS_OFR_NOTE on COURSE_CD, VERSION_NUMBER, and CAL_TYPE.
• Historical data audits confirming whether an offering existed for a specific academic calendar.
• Data-warehouse extraction, using the composite key as the natural business key of the program-offering hub.

Related Objects

IGS_PS_OFR_ALL participates in a well-defined web of relationships:

• IGS_PS_VER_ALL — referenced via COURSE_CD and VERSION_NUMBER; defines the program version being offered.
• IGS_CA_TYPE — referenced via CAL_TYPE; defines the academic calendar type.
• IGS_PS_OFR_OPT_ALL — references this table via COURSE_CD, VERSION_NUMBER, CAL_TYPE; holds offering option detail.
• IGS_PS_OFR_INST — references this table; records offering instruction details.
• IGS_PS_OFR_NOTE — references this table; stores offering notes.
• IGS_PS_OFR_UNIT_SET — references this table via COURSE_CD, CRV_VERSION_NUMBER, CAL_TYPE; holds unit set information.
• IGS_PS_PAT_OF_STUDY — references this table; defines program patterns of study.
• IGS_AD_PECRS_OFOP_DT and IGS_AD_PRD_PS_OF_OPT — administrative tables referencing this table for offering option data.

Given the obsolete status of the IGS product line, these relationships should be treated as reference metadata for legacy analysis rather than active runtime dependencies.