Search Results igs_ps_us_unsched_cl

Overview

The table IGS_PS_US_UNSCHED_CL is a core data object within the Oracle E-Business Suite (EBS) Student System (IGS) module, specifically in versions 12.1.1 and 12.2.2. It functions as the master repository for unscheduled contact lists associated with unit sections. In the academic context of Oracle EBS, a unit section is a specific offering of a course unit, and its schedule may include both regular classes and unscheduled contact activities. This table is essential for managing the logistical details of these unscheduled instructional events, such as ad-hoc tutorials, lab sessions, or faculty consultations, which are not bound by a fixed weekly timetable. Its role is to maintain the relationships between the unit section, the type of activity, the assigned instructor, and the physical or virtual location.

Key Information Stored

The table stores the defining attributes for each unscheduled contact list entry. Its structure is centered around a unique surrogate key, US_UNSCHEDULED_CL_ID, which is the primary identifier. The logical key, enforced by a unique constraint (IGS_PS_US_UNSHED_CL_UK1), combines the unit offering option (UOO_ID) and the specific activity type (ACTIVITY_TYPE_ID). Critical foreign key columns link to other master tables: INSTRUCTOR_ID references the party (instructor) in HZ_PARTIES, while LOCATION_CD, BUILDING_ID, and ROOM_ID define the venue by referencing tables in the location hierarchy (IGS_AD_LOCATION_ALL, IGS_AD_BUILDING_ALL, IGS_AD_ROOM_ALL). This design ensures data integrity for scheduling and resource allocation.

Common Use Cases and Queries

This table is primarily accessed for administrative reporting, scheduling conflict resolution, and instructor workload analysis. A common operational use case is generating a list of all unscheduled activities for a specific unit section to facilitate room booking or student communication. For reporting, a frequent requirement is to aggregate unscheduled contact hours by instructor or department. Sample SQL patterns often involve joining to related master tables to resolve IDs into meaningful names.

• Listing Unscheduled Activities for a Section: SELECT usc.* FROM igs_ps_us_unsched_cl usc JOIN igs_ps_unit_ofr_opt_all uoo ON usc.uoo_id = uoo.uoo_id WHERE uoo.unit_cd = 'MATH101' AND uoo.version_number = 2024;
• Instructor Workload Report: SELECT hp.party_name, COUNT(usc.us_unscheduled_cl_id) FROM igs_ps_us_unsched_cl usc JOIN hz_parties hp ON usc.instructor_id = hp.party_id GROUP BY hp.party_name;

Related Objects

The table IGS_PS_US_UNSCHED_CL is integral to the Student System's data model, with defined foreign key relationships to several foundational tables. These relationships are critical for enforcing business rules and enabling comprehensive joins in queries.

• IGS_PS_UNIT_OFR_OPT_ALL: Linked via UOO_ID. This is the master table for unit offering options, anchoring the unscheduled contact to a specific academic unit section.
• IGS_PS_USEC_ACT_TYPE: Linked via ACTIVITY_TYPE_ID. This table defines the valid types of unit section activities (e.g., "Tutorial," "Lab").
• HZ_PARTIES: Linked via INSTRUCTOR_ID. This central Oracle Trading Community Architecture table stores information about the instructor assigned to the activity.
• Location Hierarchy (IGS_AD_LOCATION_ALL, IGS_AD_BUILDING_ALL, IGS_AD_ROOM_ALL): Linked via LOCATION_CD, BUILDING_ID, and ROOM_ID, respectively. These tables provide the physical venue details for the unscheduled contact.