Results for “igs_uc_crse_dets”

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

Overview

IGS_UC_CRSE_DETS is a staging and synchronization table within the Oracle E-Business Suite IGS (Student System) product family, historically associated with the UK Universities and Colleges Admissions Service (UCAS) integration. The object holds supplementary course detail records that are published to UCAS views, and it also stores records describing courses delivered by partner or external institutes so that these can be reconciled against the UCAS views cvCourse and uvCourse. Within the ETRM 12.1.1 documentation the object is flagged as obsolete and is not implemented in the corresponding database, meaning it remains relevant only for reference, legacy analysis, and upgrade impact assessment.

From a heuristic Data Vault perspective, the FK topology suggests a hub-leaning classification. The primary key of four descriptive business attributes, together with multiple downstream references from choice, clearing and vacancy tables, is characteristic of a core entity hub rather than a transactional link or a pure descriptive satellite.

Key Information Stored

The physical table contains 49 documented columns. The primary key, enforced by IGS_UC_CRSE_DETS_PK, is composed of SYSTEM_CODE, UCAS_PROGRAM_CODE, INSTITUTE and UCAS_CAMPUS. The unique index IGS_UC_CRSE_DETS_U1 shares the identical four-column signature, indicating that no separate surrogate key is maintained; the composite is simultaneously the physical and business key.

Of the remaining attributes, the most operationally significant are:

• OSS_PROGRAM_CODE and OSS_PROGRAM_VERSION – map the UCAS course to the internal Student System program definition.
• SHORT_TITLE and LONG_TITLE – the published course names transmitted to UCAS.
• OSS_LOCATION and UCAS_CAMPUS – delivery location identifiers linking internal and external campus conventions.
• TOTAL_NO_OF_SEATS, MIN_ENTRY_POINTS and MAX_ENTRY_POINTS – capacity and admission threshold data.
• CURRENT_VALIDITY and DEFERRED_VALIDITY – validity windows governing which cycle the course applies to.
• TERM_1_START through TERM_4_END – term date boundaries for the academic year.
• VACANCY_STATUS and NO_OF_VACANCY – clearing vacancy position.
• CVCOURSE_TIMESTAMP, UVCOURSE_TIMESTAMP and UVCRSEVAC_TIMESTAMP – synchronization markers for the UCAS views.
• SENT_TO_UCAS, IMPORTED and KEYWRDS_CHANGED – status flags controlling transmission and change detection.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE and LAST_UPDATE_LOGIN – standard EBS audit columns.

Common Use Cases and Queries

Typical reporting revolves around identifying courses that are stale relative to UCAS, verifying vacancy counts, and auditing what was transmitted during a given admissions cycle.

• Detecting unsent courses: SELECT UCAS_PROGRAM_CODE, INSTITUTE, UCAS_CAMPUS FROM IGS_UC_CRSE_DETS WHERE SENT_TO_UCAS = 'N' AND CURRENT_VALIDITY >= TRUNC(SYSDATE);
• Vacancy reconciliation: aggregating NO_OF_VACANCY grouped by INSTITUTE and UCAS_CAMPUS and comparing against the clearing round tables.
• Timestamp drift analysis: comparing CVCOURSE_TIMESTAMP and UVCOURSE_TIMESTAMP to UVCOURSE_UPDATER to determine whether the internal or external representation is fresher.
• Entry-point profiling: bracketing MIN_ENTRY_POINTS and MAX_ENTRY_POINTS by FACULTY to support recruitment analytics.

Related Objects

The table participates in a hub-and-spoke pattern with several UCAS integration objects:

• IGS_UC_DEFAULTS – referenced through SYSTEM_CODE, supplying the UCAS system configuration defaults.
• IGS_UC_APP_CHOICES – applicant choices joined on SYSTEM_CODE, UCAS_PROGRAM_CODE, INSTITUTE_CODE and CAMPUS.
• IGS_UC_APP_CLR_RND – clearing round applications joined on SYSTEM_CODE, UCAS_PROGRAM_CODE, INSTITUTION and UCAS_CAMPUS.
• IGS_UC_CRSE_KEYWRDS – keyword assignments linked on the same four-column composite.
• IGS_UC_CRSE_VAC_OPS – vacancy operations referencing the course through SYSTEM_CODE, UCAS_PROGRAM_CODE, INSTITUTE and UCAS_CAMPUS.

These relationships confirm that IGS_UC_CRSE_DETS functions as the central course reference point for the UCAS synchronization subsystem, notwithstanding its obsolete status.