Results for “igs_ps_uso_facility”

37 results




AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IGS_PS_USO_FACILITY is a transactional table in the IGS — Student System product of Oracle E-Business Suite (validated across 12.1.1 and 12.2.2). It stores information about Unit Section Occurrence Facilities — that is, the physical or media facilities assigned to a specific occurrence of a unit section (the scheduled teaching event). In ETRM terms, a "Unit Section Occurrence" is the concrete instance of a section being taught, and this table records which facility (rooms, media equipment, and similar capacity-bearing resources) is reserved or associated against it.

Under a heuristic Data Vault classification mined from the foreign-key structure, this object is best modeled as a link. It sits at the intersection of two hubs: the occurrence (via UNIT_SECTION_OCCURRENCE_ID) and the facility (via FACILITY_CODE). The occurrence-facility pairing is a legitimate many-to-many resolution, which is exactly the shape a link table assumes. This classification is a modeling suggestion only and does not reflect a delivered Data Vault implementation.

Key Information Stored

The documented physical schema for 12.1.1 lists eight columns. The most significant are:

Note that the business-key candidate is the composite unique index IGS_PS_USO_FACILITY_U2 on UNIT_SECTION_OCCURRENCE_ID and FACILITY_CODE. This means a given facility can appear at most once per occurrence, preventing duplicate allocation of the same resource to the same teaching event — a critical integrity constraint for scheduling.

Common Use Cases and Queries

Typical reporting scenarios include facility utilisation, room booking validation, and conflict detection across occurrences. A standard join retrieves the facility assigned to an occurrence:

SELECT u.FACILITY_CODE, o.UNIT_SECTION_OCCURRENCE_ID
FROM   IGS.IGS_PS_USO_FACILITY u
JOIN   IGS.IGS_PS_USEC_OCCURS_ALL o
       ON u.UNIT_SECTION_OCCURRENCE_ID = o.UNIT_SECTION_OCCURRENCE_ID;

To detect duplicate assignments (which should never exist because of U2), an aggregation on the composite key suffices. For workload analysis, group by FACILITY_CODE and count distinct occurrences. Interface and integration checks commonly filter on LAST_UPDATE_DATE to extract rows changed since a prior run.

Related Objects

  • IGS_PS_USEC_OCCURS_ALL — the parent occurrence table; joined on UNIT_SECTION_OCCURRENCE_ID. This is the dominant access path.
  • IGS_PS_MEDIA_EQUIP_ALL — the facility/media reference table; joined on FACILITY_CODE, supplying facility descriptions and attributes.
  • IGS_PS_USO_FACILITY_PK — the primary-key constraint on USO_FACILITY_ID.
  • IGS_PS_USO_FACILITY_UK1 / U1 / U2 — the unique constraints and indexes enforcing identifier uniqueness and the occurrence-facility business key.
  • Any downstream scheduling or attendance views built atop IGS_PS_USEC_OCCURS_ALL implicitly depend on this table's facility assignments.

Together these objects form the facility-allocation layer of the Student System's scheduling model.