Search Results igs_ps_usec_tch_resp_pk

Overview

IGS_PS_USEC_TCH_RESP is a transactional table within the Oracle E-Business Suite IGS — Student System product family, which Oracle documents as obsolete in release 12.1.1 and 12.2.2. The table records the teaching responsibilities assigned to instructors for a specific unit section offering option. In the Student System data model, a "unit section" is the smallest deliverable instance of a unit of study (a class, tutorial, or lab group), and this table links the instructors who bear responsibility for delivering that section, along with workload and confirmation attributes.

Because the ETRM metadata classifies the object heuristically as a link table in Data Vault terms (mined from its foreign-key structure), it is best modeled as an associative entity that resolves a many-to-many relationship between instructors and unit section offerings. The table is documented as not implemented in this database in the ETRM repository, meaning the schema definition is available for reference but the object is not physically deployed in that environment. Implementations upgrading from earlier Student System releases should treat it as a legacy responsibility-tracking artifact rather than an active integration point in 12.2.2.

Key Information Stored

The table carries 14 documented columns in the ETRM 12.1.1 physical schema, owned by the IGS schema. The most significant are:

• UNIT_SECTION_TEACH_RESP_ID — the surrogate primary key of the table, defined by the constraint IGS_PS_USEC_TCH_RESP_PK and backed by unique index IGS_PS_USEC_TCH_RESP_U1.
• UOO_ID — the foreign key to the unit offering option (the section offering instance) for which teaching responsibility is recorded. This column participates in the second unique index, IGS_PS_USEC_TCH_RESP_U2.
• INSTRUCTOR_ID — the instructor assigned to the responsibility. Together with UOO_ID it forms the business-key uniqueness constraint IGS_PS_USEC_TCH_RESP_U2, preventing duplicate instructor assignments for the same section offering.
• CONFIRMED_FLAG — indicates whether the teaching assignment has been confirmed.
• PERCENTAGE_ALLOCATION — the proportion of the instructor's responsibility allocated to the section, used for workload distribution.
• INSTRUCTIONAL_LOAD — the aggregate instructional workload value attributed to the responsibility.
• LEAD_INSTRUCTOR_FLAG — identifies the instructor holding the lead or coordinating role for the section.
• INSTRUCTIONAL_LOAD_LAB and INSTRUCTIONAL_LOAD_LECTURE — component breakdowns of instructional load by delivery mode (laboratory versus lecture).
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Oracle EBS who-column audit attributes.

The distinction between the surrogate key (UNIT_SECTION_TEACH_RESP_ID) and the business key (UOO_ID plus INSTRUCTOR_ID) is important for ETL and de-duplication logic.

Common Use Cases and Queries

Typical usage centers on instructor workload reporting, section staffing analysis, and teaching load reconciliation against unit offering options. A representative query joining the table to its parent offering option is:

• SELECT r.unit_section_teach_resp_id, r.uoO_id, r.instructor_id, r.percentage_allocation, r.instructional_load, r.lead_instructor_flag FROM igs_ps_usec_tch_resp r WHERE r.uoO_id = :p_uoO_id ORDER BY r.lead_instructor_flag DESC;
• Workload aggregation by instructor: SELECT instructor_id, SUM(instructional_load) FROM igs_ps_usec_tch_resp GROUP BY instructor_id;
• Delivery-mode analysis: SELECT uoO_id, SUM(instructional_load_lecture), SUM(instructional_load_lab) FROM igs_ps_usec_tch_resp WHERE confirmed_flag = 'Y' GROUP BY uoO_id;

Reporting use cases include identifying unconfirmed assignments (CONFIRMED_FLAG = 'N') ahead of a teaching period, and detecting sections whose allocated percentages do not total 100 per unit offering option.

Related Objects

The documented foreign key establishes a direct dependency on the offering option table, with a duplicate FK entry in the metadata indicating the relationship is recorded twice:

• IGS_PS_UNIT_OFR_OPT_ALL — parent object referenced by IGS_PS_USEC_TCH_RESP.UOO_ID; the primary join target for section-level reporting.
• IGS_PS_UNIT_OFR_OPT_ALL (second documented FK reference) — the same parent table, reinforcing the UOO_ID linkage used in both unique index IGS_PS_USEC_TCH_RESP_U2 and workload aggregation queries.
• Instructor and person reference tables implied by INSTRUCTOR_ID — commonly joined to resolve instructor names for report output.

Given the table's obsolete status and absence from the implementation database, related Student System objects such as unit offering option tables are the more reliable integration surface in 12.2.2. New development should avoid building dependencies on IGS_PS_USEC_TCH_RESP.