Search Results assp_get_uapi_ap

Overview

The view APPS.IGS_AS_SUAAI_V is a student assessment outcome repository view within the Oracle E-Business Suite Student System (formerly Oracle Student System, and the assessment/grading component of the Education and Research Transaction Management product family). It presents student unit assessment attempt information — the record of a student's attempt at a specific assessment item within a teaching period, including the resulting mark, grade, performance band, and outcome.

The view is principally consumed by assessment enquiry forms, gradebook-style reporting, and downstream integration interfaces that must resolve a human-readable outcome reference for a student's assessment attempt. It is exposed under the APPS schema, which is the standard convention for EBS reporting views that join, decode, and enrich base transaction tables for presentation-layer consumption.

The user's search term, assp_get_uai_ref, refers directly to one of the API calls embedded in the view's SQL. This confirms the view is a functional (not merely relational) view: it does not only project stored columns but also invokes server-side PL/SQL functions at query time to derive calculated values.

Underlying Base Objects

The documented metadata states that no base objects are formally registered for this view (owner and referenced base objects are unspecified). However, the view text itself reveals its principal sources:

• IGS_AS_SUAAI — the driving table (aliased suaai), a student unit assessment attempt item table; the rowid, person_id, ass_id, attempt_number, mark, grade, and attribute1..N columns originate here.
• IGS_AS_ASSESSMENT_ITEMS (aliased asi) — joined to supply assessment_type and a fallback description.
• IGS_AS_UNITASS_ITEM_ALL (aliased aui) — used in a scalar subquery to resolve the assessment item description.
• IGS_PS_UNITASS_ITEM (aliased ps) — used in an alternate subquery to resolve a unit-section assessment item description.

Because the metadata records no dependencies, the function calls embedded in the SELECT list imply dependencies on several PL/SQL packages that are not physical tables and therefore do not appear as base objects: IGS_AS_GEN_003, IGS_AS_GEN_004, and IGS_AS_GEN_001.

Key Columns

• person_id, course_cd, unit_cd, cal_type, ci_sequence_number — the student persona and the teaching context (course, unit, calendar type, calendar instance).
• ass_id, attempt_number, ass_pattern_id — identify the assessment, the attempt sequence, and the assessment pattern used.
• mark, grade, grading_schema_cd, gs_version_number, performance — the assessed result and the grading context in which it was scored.
• outcome_dt, override_due_dt — the outcome date and any overridden due date.
• dsp_assessment_type, dsp_description — display-oriented decode of the assessment type and a resolved description (from either the unit assessment item or the unit-section assessment item).
• outcome_comment_code and OUTCOME_COMMENT_MEANING — the comment code plus its lookup meaning, decoded via IGS_GE_GEN_004.genp_get_lookup; where the lookup returns 'All', the meaning is deliberately nulled.
• Determined dynamically by IGS_AS_GEN_003.assp_get_uai_due_dt — the calculated assessment due date.
• Determined dynamically by IGS_AS_GEN_003.assp_get_uai_ref — the assessment outcome reference, the value the user searched for. This is the business reference identifier for the unit assessment attempt item.
• Determined dynamically by IGS_AS_GEN_001.assp_clc_suaai_valid (truncated to 7 characters) — a validity/status indicator appended as an additional column; the SQL returns NULL in one position (the commented-out assp_get_uapi_ap call).
• attribute_category and attribute1 through at least attribute14 — the standard EBS DFF (descriptive flexfield) context and segment columns.

Common Use Cases and Queries

Typical usage includes assessment enquiry forms, student result extracts, and integration feeds that need the derived outcome reference. Because the view invokes PL/SQL functions per row, queries should apply selective filters to limit the driving set.

SELECT person_id, unit_cd, ass_id, attempt_number,
       mark, grade, dsp_assessment_type, OUTCOME_COMMENT_MEANING
  FROM apps.igs_as_suaai_v
 WHERE person_id = :p_person_id
   AND unit_cd   = :p_unit_cd
   AND cal_type  = :p_cal_type;

To surface the derived due date and reference explicitly:

SELECT person_id, course_cd, unit_cd, ass_id,
       IGS_AS_GEN_003.assp_get_uai_due_dt(
         person_id, course_cd, unit_cd, cal_type,
         ci_sequence_number, ass_id, uoo_id) due_dt,
       IGS_AS_GEN_003.assp_get_uai_ref(
         person_id, course_cd, unit_cd, cal_type,
         ci_sequence_number, ass_id, uoo_id) uai_ref
  FROM apps.igs_as_suaai_v;

Note that the second example duplicates work already performed by the view. In practice, the derived reference and due date are already exposed as anonymous columns, so direct selection from the view is preferred. Filter on logical_delete_dt IS NULL to exclude logically deleted attempts unless auditing deleted records.