Search Results igs_ps_unit_ofr_opt_ass_pat_v




Overview
Explore in AI Chat

Overview

The IGS_PS_UNIT_OFR_OPT_ASS_PAT_V view is a seeded, read-only database object owned by the APPS schema within the Oracle E-Business Suite Student System (IGS) product family. It exposes the relationship between the assessment patterns defined for a unit and the offering options and unit classes through which that unit is delivered. In ETRM terms, the entity represents the assessment patterns that make up the assessment requirement for a unit, and it enforces the business rule that a unit's assessment pattern must map to a valid combination of unit offering option and unit class. Because the object is a view and not a base table, it carries no independent storage; it consolidates data from three underlying tables and applies a validation function to filter out combinations that are not legal. This makes it the primary reference source for reporting and integration processes that need to reconcile assessment requirements against the way a unit is actually offered in a given teaching period.

Underlying Base Objects

The view is defined over three base tables: IGS_AS_UNTAS_PATTERN (aliased UAP), which stores the unit assessment patterns; IGS_PS_UNIT_OFR_OPT (aliased UOO), which stores the unit offering options; and IGS_AS_UNIT_CLASS (aliased UC), which supplies the unit mode attributes for the unit class. The join is performed on the common unit identity columns — UNIT_CD, VERSION_NUMBER, CAL_TYPE, and CI_SEQUENCE_NUMBER — linking each assessment pattern to its corresponding offering option. A further join on UNIT_CLASS connects the unit class record so that UNIT_MODE is available for validation. The view additionally invokes the database function IGS_AS_VAL_UAI.ASSP_VAL_SUA_UAI, which compares the location, unit class, and unit mode of the offering option against the equivalent attributes of the assessment pattern. Only rows where the function returns 'TRUE' are returned, so the view presents a validated, not merely a joined, result set. Note that ETRM documents no other referenced base objects beyond these three tables and the validation function.

Key Columns

The columns exposed by the view fall into three groups. Unit identity columns comprise UNIT_CD, VERSION_NUMBER, CAL_TYPE, and CI_SEQUENCE_NUMBER, which together identify the unit version within a calendar type. Assessment pattern columns include ASS_PATTERN_CD, ASS_PATTERN_ID, and DESCRIPTION, along with the audit columns UAP_LOGICAL_DELETE_DT, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN, all sourced from the assessment pattern table. Offering option columns comprise UOO_LOCATION_CD, UOO_UNIT_CLASS, UOO_UNIT_MODE, and the surrogate key UOO_ID. The audit and logical delete columns are significant because consumers should generally filter on UAP_LOGICAL_DELETE_DT IS NULL to exclude assessment patterns that have been soft-deleted.

Common Use Cases and Queries

Typical uses include validating that every active unit offering option has a mapped assessment pattern, generating assessment pattern listings for student records and results processing, and feeding integration extracts into downstream assessment systems. The following query lists all active assessment patterns for a specific unit offering:

  • SELECT unit_cd, version_number, cal_type, ci_sequence_number, ass_pattern_cd, description, uoo_location_cd, uoo_unit_class, uoo_unit_mode FROM igs_ps_unit_ofr_opt_ass_pat_v WHERE unit_cd = :unit_cd AND version_number = :version_number AND cal_type = :cal_type AND uap_logical_delete_dt IS NULL;
  • SELECT uoo_id, ass_pattern_id, ass_pattern_cd FROM igs_ps_unit_ofr_opt_ass_pat_v WHERE uoo_id = :uoo_id AND uap_logical_delete_dt IS NULL ORDER BY ass_pattern_cd;
  • SELECT COUNT(*) FROM igs_ps_unit_ofr_opt_ass_pat_v WHERE unit_cd = :unit_cd AND cal_type = :cal_type AND uap_logical_delete_dt IS NULL;

Because the view applies the ASSP_VAL_SUA_UAI validation, query results already exclude offering option and assessment pattern combinations that fail the location, unit class, and unit mode check, which reduces the need for additional filtering in reporting logic. Performance can be affected by the function call within the view definition, so restricting queries by unit identity columns is recommended.