Search Results igs_uc_crse_keywrds_u2

Overview

The IGS_UC_CRSE_KEYWRDS table is a core data structure within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically for the IGS (iGrads) product family. It functions as the master repository for keywords linked to specific courses during the UCAS (Universities and Colleges Admissions Service) admission cycle. Its primary role is to manage and synchronize course keyword data with the external UCAS system, acting as the system of record for the 'uvCourseKeyword' UCAS view. The table is essential for configuring and transmitting course-specific search criteria and attributes used in the UK higher education admissions process.

Key Information Stored

The table stores the association between a course definition and its descriptive keywords. Key columns include the composite business key (UCAS_PROGRAM_CODE, INSTITUTE, UCAS_CAMPUS, OPTION_CODE, SYSTEM_CODE) which uniquely identifies a course variant, and the associated KEYWORD. The PREFERENCE column indicates the priority order (1 to 6) of the keyword for a given course. The CRSE_KEYWORD_ID column is a sequence-generated surrogate primary key. Operational flags such as DELETED and SENT_TO_UCAS manage the lifecycle and synchronization status of each record. Standard EBS WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) provide audit trails. Notably, columns like DATETIMESTAMP, UPDATER, and ACTIVE are documented as obsolete or no longer used.

Common Use Cases and Queries

A primary use case is generating reports or interfaces for UCAS to list all active keywords for advertised courses. Support staff may query the table to validate or troubleshoot keyword assignments. Common SQL patterns include retrieving keywords for a specific course or identifying unsynchronized updates.

• Fetching all keywords for a course: SELECT PREFERENCE, KEYWORD FROM IGS.IGS_UC_CRSE_KEYWRDS WHERE UCAS_PROGRAM_CODE = '&CODE' AND INSTITUTE = '&INST' AND DELETED = 'N' ORDER BY PREFERENCE;
• Identifying updates pending synchronization with UCAS: SELECT * FROM IGS.IGS_UC_CRSE_KEYWRDS WHERE SENT_TO_UCAS = 'N';
• Validating keyword references: SELECT ck.* FROM IGS.IGS_UC_CRSE_KEYWRDS ck WHERE NOT EXISTS (SELECT 1 FROM IGS.IGS_UC_REF_KEYWORDS rk WHERE rk.KEYWORD = ck.KEYWORD);

Related Objects

The table maintains defined relationships with other critical UCAS setup tables, primarily through foreign key constraints.

• Primary Key: IGS_UC_CRSE_KEYWRDS_PK on the CRSE_KEYWORD_ID column.
• Foreign Key (Reference): The SYSTEM_CODE column references the IGS_UC_CRSE_DETS table, linking the keyword to its parent course detail record.
• Foreign Key (Reference): The KEYWORD column references the IGS_UC_REF_KEYWORDS table, ensuring the keyword value exists in the controlled reference list.
• The unique index IGS_UC_CRSE_KEYWRDS_U1 enforces business key integrity, while IGS_UC_CRSE_KEYWRDS_U2 supports queries and integrity checks involving the SYSTEM_CODE.