Search Results igs_en_sua_year_v




Overview
Explore in AI Chat

Overview

IGS_EN_SUA_YEAR_V is a read-only database view owned by the APPS schema in the Oracle E-Business Suite 12.1.1 and 12.2.2 environments, delivered as part of the IGS (Student System) product family. Its documented purpose is to support logical grouping of student unit attempts beneath a unit set when the unit set is configured in "year of program" mode, providing the data foundation for the Unit Set Inquiry form.

Functionally, the view resolves the relationship between a student's program enrollment, their unit set selection for a given year, and the individual unit attempts that fall within that selection. It is a reporting and forms-support object rather than a transactional table, so no direct DML should be performed against it. Because it joins attempt data with effective census date logic, it is most relevant to enrolment confirmation, progression checking, and year-level unit set completion analysis.

In EBS integration terms, the view exposes a flattened, denormalised projection that combines person, course, unit set, unit version, attempt status, and calculated grade or mark values. This makes it suitable for both Oracle Forms-based inquiry and external reporting via BI Publisher or custom SQL extracts.

Underlying Base Objects

The view is defined over the following objects, per the ETRM view text:

The join is additionally constrained by the effective census date returned from IGS_EN_GEN_015.GET_EFFECTIVE_CENSUS_DATE, which must fall between the unit set selection date and the requirements completion or end date. The view also calls IGS_EN_PLAN_UTILS.GET_SUA_FIN_MARK, IGS_EN_PLAN_UTILS.GET_SUA_FIN_GRADE, IGS_EN_GEN_014.ENRS_GET_ACAD_ALT_CD and IGS_CA_GEN_001.CALP_GET_ALT_CD to derive mark, grade, and calendar alternative codes at query time.

Key Columns

  • PERSON_ID, COURSE_CD, CAL_TYPE — identify the student and their program attempt context.
  • UNIT_SET_CD, US_VERSION_NUMBER, TITLE, UNIT_SET_CAT — the unit set and its category (constrained to PRENRL_YR).
  • SELECTION_DT, RQRMNTS_COMPLETE_DT, SEQUENCE_NUMBER — unit set attempt lifecycle data.
  • UNIT_CD, VERSION_NUMBER, uv.TITLE, UNIT_CLASS — the individual unit attempt details.
  • CI_SEQUENCE_NUMBER, CI_START_DT, LOCATION_CD — teaching calendar instance and campus.
  • UNIT_ATTEMPT_STATUS / MEANING — the lookup code and its decoded meaning.
  • GET_SUA_FIN_MARK / GET_SUA_FIN_GRADE results — calculated final mark and grade per attempt.
  • Academic alternative expression — concatenation of the alt calendar code and calendar type returned by the two function calls.
  • UOO_ID, CORE_INDICATOR_CODE — unit offering option identifier and core unit indicator.
  • Standard audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

The primary use case is the Unit Set Inquiry form, but the view is equally useful for reporting on year-of-program unit set completion. A typical query filters by student and course:

  • Student year unit set report: SELECT unit_set_cd, unit_cd, unit_attempt_status, meaning FROM igs_en_sua_year_v WHERE person_id = :p_person AND course_cd = :p_course ORDER BY unit_set_cd, sequence_number;
  • Completion analysis: SELECT unit_set_cd, selection_dt, rqrments_complete_dt, COUNT(unit_cd) FROM igs_en_sua_year_v GROUP BY unit_set_cd, selection_dt, rqrments_complete_dt;
  • Grade extract for a calendar instance: SELECT person_id, unit_cd, cal_type, ci_sequence_number FROM igs_en_sua_year_v WHERE cal_type = :p_cal AND ci_sequence_number = :p_seq;
  • Inventory of attempts by status: SELECT meaning, COUNT(*) FROM igs_en_sua_year_v GROUP BY meaning ORDER BY 2 DESC;

Because the view invokes PL/SQL functions per row, performance is sensitive to row volume; restrict queries by PERSON_ID, COURSE_CD, CAL_TYPE or CI_SEQUENCE_NUMBER wherever possible, and avoid unfiltered full scans in interactive reporting.