Results for “cal_description”

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

Overview

IGS_PS_UNIT_OFR_PAT_V is a reporting view within the Oracle E-Business Suite Student System (IGS) product family. Despite the "IGS - Student System (Obsolete)" designation in the ETRM metadata, the view remains documented in the 12.2.2 reference set and appears in 12.1.1 environments where the Student System schema objects were originally deployed. The name decomposes into its functional intent: IGS (Student System), PS (Public Sector/Student), UNIT_OFR_PAT (unit offering pattern), and the _V suffix indicating a database view rather than a base table.

The view presents a denormalized, human-readable projection of unit offering patterns — the recurring scheduling definitions that describe when and how a teachable unit is offered within a calendar instance. Its primary purpose is to join the pattern record to its parent calendar instance so that downstream reports, self-service pages, and integration extracts can display descriptive calendar text alongside the offering pattern identifiers without requiring the consumer to re-implement the join.

The ETRM metadata records that the view is not implemented in this database, meaning the source system used to generate the documentation did not host the object. This is expected for obsolete or licensing-restricted IGS components. The view definition supplied in the ETRM excerpt is therefore the authoritative implementation reference rather than a live object listing.

Underlying Base Objects

The ETRM metadata states that no base objects are documented and lists the view owner as blank. The view text itself, however, is explicit. The definition selects from two base tables:

• IGS_PS_UNIT_OFR_PAT (aliased UOP) — the driving table holding the unit offering pattern rows, including unit code, version, calendar type, calendar instance sequence, and the ABORT_FLAG attribute.
• IGS_CA_INST (aliased CI) — the calendar instance master, supplying the descriptive instance text.

The join predicate is UOP.CAL_TYPE = CI.CAL_TYPE AND UOP.CI_SEQUENCE_NUMBER = CI.SEQUENCE_NUMBER, with a filter of UOP.DELETE_FLAG = 'N' that suppresses soft-deleted pattern rows. The view also invokes two packaged functions — IGS_EN_GEN_014.ENRS_GET_ACAD_ALT_CD and IGS_CA_GEN_001.CALP_GET_ALT_CD — to derive alternate academic and teaching codes, each truncated to ten characters.

Key Columns

• UNIT_CD, VERSION_NUMBER — identify the teachable unit and its effective version.
• CAL_TYPE, CI_SEQUENCE_NUMBER — the composite key linking the pattern to its calendar instance; also the join keys to IGS_CA_INST.
• CAL_DESCRIPTION — the descriptive text from IGS_CA_INST.DESCRIPTION, providing the readable calendar instance label.
• ACAD_ALTERNATE_CODE, TEACH_ALTERNATE_CODE — function-derived alternate codes used by external or regulatory reporting formats.
• CI_START_DT, CI_END_DT — the start and end dates of the calendar instance in which the pattern applies.
• ABORT_FLAG — the user's search term; this column mirrors UOP.ABORT_FLAG and indicates whether the unit offering pattern has been aborted, i.e. cancelled and no longer available for enrolment, while remaining retained for audit and historical reporting.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns propagated from the base table.

Common Use Cases and Queries

Typical usage includes reporting on active versus aborted offerings, integration extracts feeding timetabling or enrolment systems, and validation reports for calendar setup. Filtering on ABORT_FLAG is the most common access pattern, since consumers generally want either all aborted patterns for audit or only non-aborted patterns for live processing.

Retrieve all aborted patterns with their calendar descriptions:

SELECT unit_cd, version_number, cal_type,
       ci_sequence_number, cal_description,
       ci_start_dt, ci_end_dt, abort_flag
FROM   igs_ps_unit_ofr_pat_v
WHERE  abort_flag = 'Y'
ORDER  BY ci_start_dt DESC;

Count active and aborted patterns by unit:

SELECT unit_cd, version_number, abort_flag, COUNT(*)
FROM   igs_ps_unit_ofr_pat_v
GROUP  BY unit_cd, version_number, abort_flag;

List patterns for a specific calendar instance, including alternate codes:

SELECT unit_cd, acad_alternate_code,
       teach_alternate_code, ci_start_dt, ci_end_dt
FROM   igs_ps_unit_ofr_pat_v
WHERE  cal_type = :cal_type
AND    ci_sequence_number = :ci_seq
AND    abort_flag = 'N';

Because the view encapsulates the DELETE_FLAG filter and the function calls, consumers should query it rather than reconstructing the join, ensuring consistent handling of soft-deleted rows and alternate code derivation across all reports and integrations.