Search Results sent_to_oss_flag




Overview
Explore in AI Chat

Overview

The IGS.IGS_UC_APP_NAMES table is a transactional data object within the Oracle E-Business Suite IGS schema, which supports the Student Systems / Universities and Colleges Admissions Service (UCAS) integration components of Oracle EBS 12.1.1 and 12.2.2. Its documented purpose is to hold UCAS Applicant Name details — the personal identity information associated with an applicant record submitted through the UCAS admissions process. In practice, this table stores the name-bearing attributes of an applicant, including the applicant number, title, forenames, surname, date of birth, and gender, together with administrative flags and standard WHO audit columns.

The object resides in the APPS_TS_TX_DATA tablespace, with its two indexes in APPS_TS_TX_IDX, reflecting a standard Oracle EBS transactional storage configuration (PCT Free 10). The heuristic Data Vault classification for this object is standalone, which is a modeling suggestion rather than a physical constraint. Because the table is not documented as referencing any other database object via a foreign key, it behaves as an independent hub-like structure keyed on the applicant number, with the descriptive name attributes acting as satellite-style context. Designers building a Data Vault layer over this table may therefore treat APP_NO as the hub business key, with TITLE, FORE_NAMES, SURNAME, BIRTH_DATE, and SEX as descriptive satellite attributes.

Key Information Stored

The table comprises 14 documented columns. The most significant are:

  • APP_NO (NUMBER, 22) — the unique identifier representing the UCAS Applicant Number. This is the surrogate primary key, enforced by the unique index IGS_UC_APP_NAMES_PK. It is the principal business-key candidate and the join anchor for this table.
  • CHECK_DIGIT (NUMBER, 22) — the check digit associated with the application number, used for validation of the applicant identifier.
  • FORE_NAMES (VARCHAR2, 50) — the applicant's first name (the searched term "fore_names" maps directly here); one of the primary name attributes.
  • SURNAME (VARCHAR2, 30) — the applicant's surname.
  • TITLE (VARCHAR2) — the applicant's title.
  • NAME_CHANGE_DATE (DATE) — the date on which the name record was changed.
  • BIRTH_DATE (DATE) — the applicant's date of birth.
  • SEX (VARCHAR2) — the applicant's gender.
  • SENT_TO_OSS_FLAG (VARCHAR2) — an indicator recording whether the record has been sent to OSS; this column is the basis of the non-unique index IGS_UC_APP_NAMES_N1, supporting efficient filtering on transmission status.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard Oracle EBS WHO audit columns tracking record creation and last modification.

No additional unique indexes beyond the primary key are documented, so APP_NO is the sole documented business-key candidate.

Common Use Cases and Queries

Typical reporting and integration scenarios include retrieving applicant names for admissions correspondence, reconciling applicant identities against UCAS submissions, and monitoring outbound transmission status via SENT_TO_OSS_FLAG. A standard retrieval pattern is:

  • SELECT APP_NO, TITLE, FORE_NAMES, SURNAME, BIRTH_DATE FROM IGS.IGS_UC_APP_NAMES WHERE APP_NO = :p_app_no; — single-applicant lookup by primary key.
  • SELECT APP_NO, FORE_NAMES, SURNAME FROM IGS.IGS_UC_APP_NAMES WHERE SENT_TO_OSS_FLAG = 'N'; — identify records not yet transmitted to OSS, leveraging index IGS_UC_APP_NAMES_N1.
  • SELECT APP_NO, UPPER(SURNAME) || ', ' || FORE_NAMES AS FULL_NAME FROM IGS.IGS_UC_APP_NAMES; — name formatting for reporting.
  • Audit queries filtering on LAST_UPDATE_DATE to identify recently amended name records.

Related Objects

The documented relationship data indicate that IGS.IGS_UC_APP_NAMES does not reference any database object, and that it is referenced by the APPS.IGS_UC_APP_NAMES synonym or view layer. As a standalone entity in the mined dependency graph, its principal associations are structural rather than foreign-key driven:

  • APPS.IGS_UC_APP_NAMES — the APPS-layer object referencing this table; queries are normally issued against this synonym, joined on APP_NO.
  • IGS_UC_APP_NAMES_PK — the unique primary-key index on APP_NO, the principal business-key candidate and join column.
  • IGS_UC_APP_NAMES_N1 — the non-unique index on SENT_TO_OSS_FLAG, used for transmission-status filtering.
  • Other IGS.IGS_UC_* objects in the UCAS integration family may be correlated on APP_NO in practice, though no foreign key is documented.