Results for “sent_to_oss”

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

Overview

IGS_UC_APP_CHOICE_DETS_V is a reporting view within the Oracle E-Business Suite IGS (Student System) product family. It exposes UCAS (Universities and Colleges Admissions Service) application choice details, presenting the individual course choices an applicant has submitted through the UCAS admissions process alongside the associated applicant and person records. In the EBS 12.1.1 and 12.2.2 releases, this object is documented under the obsolete portion of the IGS schema, and the ETRM metadata explicitly states that it is not implemented in the current database. Nonetheless, it remains a documented artifact for environments where the legacy UCAS integration was historically active.

The view serves a reporting and integration role: it flattens UCAS choice-level data and joins it to person biographical information, allowing admissions staff and integration programs to review decisions, replies, and export status without navigating multiple base tables. Its principal integration purpose is visible in the EXPORT_TO_OSS_STATUS, SENT_TO_OSS, and OSS_PERSON_ID columns, which track the transfer of UCAS application data into the core Oracle Student System (OSS).

Underlying Base Objects

The view is defined over the following objects, as documented in the ETRM view text:

• IGS_UC_APP_CHOICES (alias APPCH) — the primary table holding choice-level records; it supplies the application choice identifier, choice number, cycle, program codes, decision, and reply.
• IGS_UC_APPLICANTS (alias APP) — the applicant-level table joined on APP_ID, supplying OSS_PERSON_ID, APPLICATION_DATE, and SENT_TO_OSS.
• IGS_PE_PERSON_BASE_V (alias PE) — the person base view, outer-joined on OSS_PERSON_ID = PERSON_ID, supplying PERSON_NUMBER, FULL_NAME, and LAST_NAME.

The documented ETRM metadata lists no referenced base objects for this view in the 12.2.2 repository, consistent with its obsolete status. The join structure is APPCH.APP_ID = APP.APP_ID as an inner join, with the person view applied via an Oracle outer join (+), meaning choice records are returned even when no corresponding person record exists in the student system.

Key Columns

The view exposes a wide column set that can be grouped functionally:

• Identity: ROW_ID, APP_CHOICE_ID, APP_ID, APP_NO, CHOICE_NO — uniquely identify a choice within an application.
• UCAS context: SYSTEM_CODE, UCAS_CYCLE, INSTITUTE_CODE, UCAS_PROGRAM_CODE, CAMPUS — the inbound UCAS reference data.
• Person: PERSON_NUMBER, FULL_NAME, SURNAME, OSS_PERSON_ID — applicant biographical and linking information.
• OSS mapping: OSS_PROGRAM_CODE, OSS_PROGRAM_VERSION, OSS_ATTENDANCE_TYPE, OSS_ATTENDANCE_MODE, OSS_LOCATION, ENTRY_YEAR, ENTRY_MONTH, POINT_OF_ENTRY — the derived program and admission details used for the OSS hand-off.
• Status: DECISION, REPLY, LAST_CHANGE, SENT_TO_OSS, EXPORT_TO_OSS_STATUS — lifecycle and transfer state.
• Audit: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO columns.

Common Use Cases and Queries

Typical scenarios include auditing pending UCAS-to-OSS transfers, reconciling decisions against exported records, and building applicant-facing reports. A representative query lists all choices for a given UCAS cycle that have not yet been exported:

SELECT app_no, choice_no, ucas_program_code, decision, reply,
       export_to_oss_status
FROM   igs_uc_app_choice_dets_v
WHERE  ucas_cycle = '2015'
AND    export_to_oss_status IS NULL
ORDER BY app_no, choice_no;

A second common pattern joins the view to person and program data to reconcile applicant identity against the student system:

SELECT person_number, full_name, ucas_program_code,
       oss_program_code, entry_year, sent_to_oss
FROM   igs_uc_app_choice_dets_v
WHERE  oss_person_id IS NOT NULL
AND    sent_to_oss = 'Y';

Because the object is documented as obsolete and not implemented, validation against the target instance is advised before reuse; where absent, equivalent reporting should be constructed directly from IGS_UC_APP_CHOICES, IGS_UC_APPLICANTS, and IGS_PE_PERSON_BASE_V.