Search Results igs_ps_usec_as_v




Overview
Explore in AI Chat

Overview

IGS_PS_USEC_AS_V is a denormalized reporting view within the Oracle E-Business Suite Student System (IGS) module, which is documented as obsolete in release 12.1.1 and 12.2.2. The view presents assessment scheduling data for unit offerings, joining the base assessment record in IGS_PS_USEC_AS to descriptive reference data for physical location, building, and room. It exposes exam logistics — final exam date, start and end times, and the assigned campus location, building, and room — alongside the resolved descriptions for each of those codes.

Because it pre-joins the lookup tables, the view is intended for reporting and integration rather than transactional entry. The ETRM metadata notes that this view is "Not implemented in this database," meaning institutions that have not installed or have de-supported the Student System (IGS) module will not find the object or its underlying tables present. Where the module remains deployed, the view serves as a convenient, flat data source for student records extracts, exam timetable reports, and downstream integration interfaces that require human-readable location detail rather than raw codes.

Underlying Base Objects

The view is defined in its view text over four tables. The driving table is IGS_PS_USEC_AS (aliased USAS), which holds the unit section assessment schedule, keyed by UNIT_SECTION_ASSESSMENT_ID and including the UOO_ID linking the assessment to its unit offering option. Three lookup tables are outer-joined. IGS_AD_LOCATION (LOC) is joined on LOCATION_CD via an inner join, so every row returned must resolve to a valid location. IGS_AD_BUILDING (BLD) is joined on BUILDING_CODE with the Oracle outer-join operator (+), and IGS_AD_ROOM (ROM) is joined on ROOM_CODE, also with the (+) operator. Consequently, building and room descriptions are optional: an assessment row is returned even when the building or room code is null or unresolved, but a missing location code excludes the row.

Although the ETRM metadata lists "none documented" for referenced base objects, the supplied view text confirms the four tables above. Note that BUILDING_CODE is joined to BLD.BUILDING_ID and ROOM_CODE to ROM.ROOM_ID, while LOCATION_CD is joined to LOC.LOCATION_CD — a naming inconsistency to be aware of when tracing data lineage.

Key Columns

  • ROW_ID — the ROWID of the IGS_PS_USEC_AS record, useful for back-referencing the base row.
  • UNIT_SECTION_ASSESSMENT_ID — primary identifier of the assessment schedule record.
  • UOO_ID — unit offering option identifier linking the assessment to the unit offering.
  • FINAL_EXAM_DATE, EXAM_START_TIME, EXAM_END_TIME — the exam scheduling attributes.
  • LOCATION_CD / LOCATION_DESC — campus or location code and its description from IGS_AD_LOCATION.
  • BUILDING_CODE / BUILDING_DESC — building identifier and description; description may be null due to the outer join.
  • ROOM_CODE / ROOM_DESC — the room identifier and its description, the columns most directly associated with a "room_code" search; ROOM_DESC may be null.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns propagated from the base table.

Common Use Cases and Queries

Typical uses include generating exam timetable listings, validating that assessments have valid room assignments, and extracting location detail for student-facing reports. A representative query filtering on room code is:

  • SELECT UNIT_SECTION_ASSESSMENT_ID, UOO_ID, FINAL_EXAM_DATE, EXAM_START_TIME, EXAM_END_TIME, LOCATION_CD, LOCATION_DESC, BUILDING_CODE, BUILDING_DESC, ROOM_CODE, ROOM_DESC FROM IGS_PS_USEC_AS_V WHERE ROOM_CODE = :room_code;
  • SELECT LOCATION_DESC, BUILDING_DESC, ROOM_DESC, COUNT(*) FROM IGS_PS_USEC_AS_V GROUP BY LOCATION_DESC, BUILDING_DESC, ROOM_DESC;
  • SELECT * FROM IGS_PS_USEC_AS_V WHERE ROOM_CODE IS NULL OR ROOM_DESC IS NULL; — identifying assessments with missing or unresolved room data.

Because the tables are part of the obsolete Student System, confirm module installation before relying on the view; otherwise query the base tables directly or migrate to the current student information solution.