Results for “cl_date”

9 results




Overview
Explore in AI Chat

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

Overview

The IGS_UC_CRSE_VAC_OPS table is a core data object within the Oracle E-Business Suite IGS – Student System product module. As its name implies, it holds the course vacancy options associated with the institutions' UCAS (Universities and Colleges Admissions Service) returns. The records maintained in this table are the transactional source of truth that is propagated to the UCAS interface views used by external admissions processing, in particular the UCAS view uvCourseVacOptions. In this role, the table acts as the staging and synchronization layer between the EBS Student System and the UCAS admissions clearing pipeline.

From a Data Vault modeling perspective, the heuristic classification derived from the foreign-key structure labels this object as satellite-leaning. This suggests that, in a dimensional or Data Vault representation, IGS_UC_CRSE_VAC_OPS is best modeled as a satellite describing descriptive attributes that hang off a parent hub (the course detail record in IGS_UC_CRSE_DETS). The vacancy status, timestamp, and synchronization flags are the mutable, descriptive payload typical of a satellite, while the composite key aligns it to the parent course.

Key Information Stored

The physical schema comprises 16 columns, anchored by a composite primary key, IGS_UC_CRSE_VAC_OPS_PK, defined over SYSTEM_CODE, UCAS_PROGRAM_CODE, INSTITUTE, UCAS_CAMPUS, and OPTION_CODE. A parallel unique index, IGS_UC_CRSE_VAC_OPS_U1, covers the same five columns and therefore serves as the documented business-key candidate. The most significant columns include:

  • SYSTEM_CODE – Identifies the source system/UCAS cycle context; part of the primary key and the FK to IGS_UC_CRSE_DETS.
  • UCAS_PROGRAM_CODE – The UCAS course/program code, one of the principal business identifiers.
  • INSTITUTE and UCAS_CAMPUS – The institution and campus for which the vacancy option applies.
  • OPTION_CODE – The vacancy option code that completes the composite business key.
  • VACANCY_STATUS – The current status of the vacancy option, the key descriptive attribute consumed by UCAS.
  • SENT_TO_UCAS – Flag recording whether the record has already been transmitted to the UCAS interface.
  • DATETIMESTAMP – The effective timestamp attached to the vacancy option record.
  • UPDATER, CL_UPDATED, CL_DATE – Change-tracking columns supporting the Clearing/UCAS update process.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN – Standard EBS audit columns.

Common Use Cases and Queries

Reporting and interface routines typically select vacancy options that have not yet been transmitted, or that have changed since the last UCAS submission. For example, to identify pending synchronization records:

SELECT UCAS_PROGRAM_CODE, INSTITUTE, UCAS_CAMPUS, OPTION_CODE, VACANCY_STATUS
FROM IGS_UC_CRSE_VAC_OPS
WHERE SENT_TO_UCAS = 'N';

To review records for a given course and campus, join to IGS_UC_CRSE_DETS on the shared key columns and filter by VACANCY_STATUS. Auditors and functional analysts frequently query LAST_UPDATE_DATE and CL_DATE to reconcile changes against the UCAS view uvCourseVacOptions.

Related Objects

The most significant related objects are:

  • IGS_UC_CRSE_DETS – Parent table; FK join on SYSTEM_CODE, UCAS_PROGRAM_CODE, INSTITUTE, UCAS_CAMPUS.
  • uvCourseVacOptions – UCAS interface view synchronized by this table.
  • IGS_UC_CRSE_VAC_OPS_PK / _U1 – Composite primary key and unique business-key index.
  • Associated UCAS submission/clearing programs that read SENT_TO_UCAS and VACANCY_STATUS.