Search Results date_cef_sent




Overview
Explore in AI Chat

Overview

The IGS_UC_APP_CLEARING table is a core data object within the Oracle E-Business Suite (EBS) 12.1.1/12.2.2, specifically in the IGS (iGrad Student) product family. Its primary function is to store detailed records related to the university admissions clearing process, which manages applicants who are not holding offers from their initial choices. The table serves as the system of record for clearing-related applicant details and is designed to synchronize data with the external UCAS (Universities and Colleges Admissions Service) view named 'ivStarZ1'. This integration ensures that the institution's EBS data remains aligned with the central UCAS clearing system.

Key Information Stored

The table captures the complete lifecycle of a clearing application. The mandatory CLEARING_APP_ID column is the unique primary key identifier for each clearing record. Critical linking columns include APP_ID (foreign key to the main applicant) and ENQUIRY_NO, which is indexed for performance and serves as the key enquiry identifier. The table stores the core clearing proposal: INSTITUTION, COURSE, and CAMPUS codes, along with the planned ENTRY_MONTH and ENTRY_YEAR. Operational status is tracked via fields such as DATE_CEF_SENT (Clearing Entry Form), CEF_RECEIVED, and the final RESULT. The IMPORTED flag indicates if the record originated from a UCAS data import, while standard WHO columns track creation and update audit history.

Common Use Cases and Queries

A primary use case is tracking the status of clearing applications, often filtered by the ENQUIRY_NO, which was the user's search term. Reports frequently join this table with IGS_UC_APPLICANTS to build a complete applicant profile. Common SQL patterns include querying pending actions, such as forms sent but not yet received, or generating lists of successful clearing entries for a specific intake cycle.

  • Finding clearing records for a specific enquiry: SELECT * FROM IGS.IGS_UC_APP_CLEARING WHERE ENQUIRY_NO = <value>;
  • Joining with applicant details for a consolidated report: SELECT a.*, c.INSTITUTION, c.COURSE, c.RESULT FROM IGS.IGS_UC_APPLICANTS a, IGS.IGS_UC_APP_CLEARING c WHERE a.APP_ID = c.APP_ID AND c.ENTRY_YEAR = '2023';
  • Identifying clearing forms awaiting receipt: SELECT APP_NO, DATE_CEF_SENT FROM IGS.IGS_UC_APP_CLEARING WHERE CEF_RECEIVED = 'N' AND DATE_CEF_SENT IS NOT NULL;

Related Objects

The IGS_UC_APP_CLEARING table is central to a defined data model with specific foreign key relationships, ensuring referential integrity.

  • Primary Key: IGS_UC_APP_CLEARING_PK on the CLEARING_APP_ID column.
  • Referenced Foreign Key (Outbound): The APP_ID column is a foreign key referencing the IGS_UC_APPLICANTS table, linking each clearing record to a master applicant.
  • Referencing Foreign Key (Inbound): The table is referenced by the IGS_UC_APP_CLR_RND table via its CLEARING_APP_ID column, indicating that additional clearing round data may depend on a base clearing application record.