Search Results igs_ps_fac_usec_tch_hist_v




Overview
Explore in AI Chat

Overview

IGS_PS_FAC_USEC_TCH_HIST_V is a reporting view owned by the APPS schema within the Oracle E-Business Suite Student System (IGS) product family. It is documented as VALID in ETRM for releases 12.1.1 and 12.2.2. The view presents the historical record of instructor teaching activity at an institution, consolidating the units taught, the delivery location, calendar and teaching-period context, and the instructor's assigned teaching responsibilities for each teaching occurrence. Its principal purpose is to expose faculty workload information in a denormalised, query-friendly form so that institutional reporting, workload analysis, and downstream integrations can retrieve instructional load values without re-joining the underlying transactional and reference tables.

The object is especially relevant to searches concerning instructional_load_lecture, since that column is exposed directly by this view together with the related lecture, laboratory, and total load measures. Where the same teaching occurrence is associated with a credit-point snapshot record, the view also surfaces the enrolled credit points applicable to the offering.

Underlying Base Objects

The view is defined over five documented base objects joined on their natural keys:

  • IGS_PS_UNIT_OFR_OPT (alias UOF) — the unit offering option, supplying the UOO_ID, calendar type, teaching period sequence, unit code, version, and unit class.
  • IGS_PS_USEC_TCH_RESP (alias UTR) — the teaching responsibility record, supplying instructor identity, instructional load values, and audit columns.
  • IGS_PS_UNIT_VER (alias UV) — the unit version, supplying the unit title and a fallback enrolled credit points value.
  • IGS_AD_LOCATION (alias AL) — the location reference, supplying the location description.
  • IGS_PS_USEC_CPS (alias CPS) — the credit point snapshot for the occurrence, outer-joined on UOO_ID to allow for occurrences without a snapshot row.

Joins are made between UOF and UTR on UOO_ID, between UOF and UV on unit code and version number, between UOF and AL on location code, and an outer join between UOF and CPS on UOO_ID. The view also invokes the PL/SQL function IGS_PS_FAC_CREDT_WRKLOAD.PRGP_GET_LEAD_INSTRUCTOR to derive the lead instructor for each occurrence.

Key Columns

Common Use Cases and Queries

Typical uses include faculty workload reporting, teaching history extracts, lead-instructor identification, and reconciliation of lecture and laboratory load against enrolled credit points. A representative query for a single unit and calendar is:

SELECT instructor_id,
       unit_cd,
       cal_type,
       ci_sequence_number,
       unit_title,
       location_description,
       instructional_load_lecture,
       instructional_load_lab,
       instructional_load,
       enrolled_credit_points,
       lead_instructor
  FROM apps.igs_ps_fac_usec_tch_hist_v
 WHERE unit_cd = :p_unit_cd
   AND cal_type = :p_cal_type
   AND ci_sequence_number = :p_seq
ORDER BY instructor_id;

To aggregate lecture load by instructor across a period:

SELECT instructor_id,
       SUM(instructional_load_lecture) lecture_load,
       SUM(instructional_load_lab) lab_load,
       SUM(instructional_load) total_load
  FROM apps.igs_ps_fac_usec_tch_hist_v
 WHERE cal_type = :p_cal_type
 GROUP BY instructor_id;