Search Results us_unscheduled_cl_id




Overview
Explore in AI Chat

Overview

The IGS_PS_US_UNSCHED_CL table is a core data structure within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically for the Oracle Student Management (OSM) or Campus Solutions module. It functions as the master repository for unscheduled contact lists associated with unit sections. In academic administration, a unit section (represented by UOO_ID) may have instructional activities, like tutorials or labs, that are not held at fixed, recurring times. This table captures the logistical and instructional details for such unscheduled contact sessions, linking the academic offering to specific resources, locations, and instructors. Its role is critical for managing flexible teaching arrangements, resource allocation, and tracking instructional effort outside of a standard timetable.

Key Information Stored

The table stores a comprehensive set of attributes defining an unscheduled contact list. The primary identifier is the system-generated US_UNSCHEDULED_CL_ID. The core academic context is provided by the UOO_ID (Unit Offering Option ID) and the ACTIVITY_TYPE_ID, which define the specific unit section and the type of activity (e.g., independent study, make-up session). Physical logistics are captured through foreign keys to location (LOCATION_CD), BUILDING_ID, and ROOM_ID. Instructional details include the assigned INSTRUCTOR_ID and metrics for workload calculation: NUMBER_OF_STUDENTS, HOURS_PER_STUDENT, and HOURS_PER_FACULTY. The table also includes standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) for audit trails.

Common Use Cases and Queries

Primary use cases involve reporting on instructional load, managing room bookings for ad-hoc sessions, and supporting faculty workload calculations. A common administrative query retrieves all unscheduled contact lists for a specific unit section to review resource assignments. For example:

SELECT usc.US_UNSCHEDULED_CL_ID,
       usc.NUMBER_OF_STUDENTS,
       loc.LOCATION_CODE,
       bld.BUILDING_CODE,
       rm.ROOM_NUMBER,
       hp.PARTY_NAME AS INSTRUCTOR_NAME
FROM IGS.IGS_PS_US_UNSCHED_CL usc,
     IGS_AD_LOCATION_ALL loc,
     IGS_AD_BUILDING_ALL bld,
     IGS_AD_ROOM_ALL rm,
     HZ_PARTIES hp
WHERE usc.UOO_ID = :p_uoo_id
  AND usc.LOCATION_CD = loc.LOCATION_CD(+)
  AND usc.BUILDING_ID = bld.BUILDING_ID(+)
  AND usc.ROOM_ID = rm.ROOM_ID(+)
  AND usc.INSTRUCTOR_ID = hp.PARTY_ID(+);

Another critical report aggregates total unscheduled faculty hours by instructor or department, using joins through the UOO_ID to the unit offering and instructor tables.

Related Objects

The table maintains defined relational integrity through foreign key constraints, linking it to several foundational tables in the EBS schema:

  • IGS_PS_UNIT_OFR_OPT_ALL via UOO_ID: Establishes the core link to the academic unit section.
  • IGS_PS_USEC_ACT_TYPE via ACTIVITY_TYPE_ID: References the valid activity type for the unit section.
  • HZ_PARTIES via INSTRUCTOR_ID: Identifies the instructor from the Trading Community Architecture (TCA) registry.
  • IGS_AD_LOCATION_ALL via LOCATION_CD: References the general location.
  • IGS_AD_BUILDING_ALL via BUILDING_ID: References the specific building.
  • IGS_AD_ROOM_ALL via ROOM_ID: References the specific room.

These relationships ensure data consistency across the academic, physical resource, and personnel domains within the application.