Results for “igs_uc_com_inst_u1”

6 results




Overview
Explore in AI Chat

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

Overview

IGS.IGS_UC_COM_INST is a reference (lookup) table in the Oracle E-Business Suite IGS schema that holds the list of all educational institutions recognized within the Universities and Colleges Admissions Service (UCAS) integration. In the Oracle Student System / Recruiting and Admissions context, this table acts as the local staging and synchronization anchor for the UCAS external view cvInstitution, ensuring that institution codes and names used during admissions processing remain consistent with the central UCAS registry.

The object is owned by IGS, carries a status of VALID, and resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10. Its design is deliberately narrow and denormalized: a single unique business key plus a small set of descriptive and sourcing attributes. Based on the FK/PK relationship data supplied, the table is classified heuristically as standalone — it neither references nor is referenced by other database objects. From a Data Vault modeling perspective, this makes IGS_UC_COM_INST a natural candidate for a hub (an institution business key with descriptive attributes attached), rather than a link or satellite, since it stores independent reference entities without resolved foreign-key dependencies.

Key Information Stored

The most significant columns documented in the ETRM metadata are:

  • INST — The institution identifier and the primary key (IGS_UC_COM_INST_PK). It is also the single column of the unique index IGS_UC_COM_INST_U1, making it both the surrogate primary key and the business-key candidate in this schema.
  • INST_CODE — Abbreviated institution name; the practical human-readable code used in admissions data exchange.
  • INST_NAME — Full institution name (documented length 20), used for display and reporting.
  • UCAS — Flag (Y/N) indicating whether the institution participates in the Universities and Colleges Admissions Service.
  • GTTR — Graduate Teacher Training Registry participation flag (reserved/for future use).
  • SWAS — Social Work Admissions System participation flag (reserved).
  • NMAS — Nursing and Midwifery Admissions System participation flag (reserved).
  • IMPORTED — Indicates whether the record originated from a UCAS import ('Y') or was created locally ('N').
  • DATETIMESTAMP — Synchronization timestamp against UCAS view cvInstitution; documented as obsolete.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns providing change tracking.

Of these, INST is the documented primary key and unique business key. INST_CODE and INST_NAME serve as the descriptive business attributes, while the Y/N flags encode routing across the multiple admissions schemes.

Common Use Cases and Queries

Typical scenarios include validating institution codes received from UCAS, generating institution lookup lists for admissions forms, and reconciling imported records against locally defined institutions. A standard synchronization audit query identifies locally created versus imported rows:

  • SELECT INST, INST_CODE, INST_NAME FROM IGS.IGS_UC_COM_INST WHERE IMPORTED = 'N';
  • SELECT INST, INST_NAME FROM IGS.IGS_UC_COM_INST WHERE UCAS = 'Y' ORDER BY INST_NAME;
  • SELECT MAX(DATETIMESTAMP) FROM IGS.IGS_UC_COM_INST; — to check the last synchronization point (noting the column is obsolete).

Because the table is standalone, reporting joins to other admissions tables are performed at the application level using INST or INST_CODE rather than through enforced foreign keys.

Related Objects

The ETRM dependency section records that IGS_UC_COM_INST does not reference any database object and is referenced only by the APPS synonym IGS_UC_COM_INST. The most significant related objects are therefore:

  • IGS.IGS_UC_COM_INST (APPS synonym) — The public access path used by application code and reports.
  • IGS_UC_COM_INST_PK — The primary key constraint on INST.
  • IGS_UC_COM_INST_U1 — The unique index on INST in APPS_TS_TX_IDX, the object referenced in the user's search.
  • UCAS view cvInstitution — The external source view with which this table synchronizes institution data.

No additional tables, views, or APIs are documented as dependent in the supplied metadata; integration is achieved through the UCAS import interface rather than physical referential constraints.