Results for “igs_ps_unit_location”

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

Overview

IGS_PS_UNIT_LOCATION is a transactional entity table owned by the IGS schema within the Oracle E-Business Suite Student System (IGS) product family. Its documented purpose is to capture Unit Locations information, meaning it associates a scheduled or offered unit (a course offering at a specific version) with the physical place where instruction or activity occurs. That place is resolved through three Oracle space-management components: a location code, a building, and a room. The table is therefore a physical-resource assignment object that bridges academic curriculum structures and facilities data.

The ETRM metadata classifies this table, using a heuristic derived from its foreign key structure, as a Data Vault link. This is a modeling suggestion rather than a physical implementation statement: the table sits between academic unit version records and facilities records, and its columns are overwhelmingly foreign key references, which is characteristic of a link or junction entity. Some installations may treat it as an associative entity or a satellite, depending on whether location attributes are considered descriptive of the unit offering or a reusable relationship. In Oracle EBS 12.1.1 the table is documented with 11 columns, and it carries a VALID status in the data dictionary.

Key Information Stored

The table is narrow and highly referential. The most significant columns are:

• UNIT_LOCATION_ID — the surrogate primary key, enforced by the unique index IGS_PS_UNIT_LOCATION_PK. It has no business meaning and should be used only for deterministic joins and de-duplication.
• UNIT_CODE — the academic unit identifier, the versioned unit offering to which the location applies.
• UNIT_VERSION_NUMBER — the version of the unit; together with UNIT_CODE it forms part of the foreign key to IGS_PS_UNIT_VER_ALL.
• LOCATION_CODE — reference to IGS_AD_LOCATION_ALL, identifying the geographic or organizational location (campus, site, or attendance location).
• BUILDING_ID — reference to IGS_AD_BUILDING_ALL, identifying the physical building.
• ROOM_ID — reference to IGS_AD_ROOM_ALL, identifying the specific room within the building.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard Oracle EBS WHO columns used for audit, row-level security, and incremental extraction in warehouse loads.

A second unique index, IGS_PS_UNIT_LOCATION_U2, is defined over (UNIT_CODE, UNIT_VERSION_NUMBER, LOCATION_CODE, BUILDING_ID, ROOM_ID). This is the effective business key: it prevents duplicate assignment of the same room to the same unit version at the same location. Reporting logic and data quality checks should therefore treat this composite as the natural key and UNIT_LOCATION_ID as the technical key.

Common Use Cases and Queries

The primary operational scenario is room and building assignment reporting: determining which rooms host which unit offerings, and identifying unassigned or double-booked rooms. A representative query joins the facilities dimension to the unit version table:

• SELECT ul.unit_code, ul.unit_version_number, ul.location_code, ul.building_id, ul.room_id FROM igs_ps_unit_location ul WHERE ul.unit_code = :unit_code
• Deduplication and integrity checks using the business key: group by UNIT_CODE, UNIT_VERSION_NUMBER, LOCATION_CODE, BUILDING_ID, ROOM_ID and filter for counts greater than one.
• Facilities utilization extracts: aggregate by BUILDING_ID and ROOM_ID to count assigned units per room.
• Incremental warehouse loads: filter on LAST_UPDATE_DATE greater than the last extraction watermark.
• Timetable and scheduling reports that resolve LOCATION_CODE to a campus description through IGS_AD_LOCATION_ALL.

Because the table contains no dates, capacity, or scheduling attributes beyond the WHO columns, time-based class scheduling must be sourced from the unit offering or timetable entities that reference the unit version.

Related Objects

The dominant dependencies are the foreign key targets and the version entity it references:

• IGS_PS_UNIT_VER_ALL — joined on UNIT_CODE and UNIT_VERSION_NUMBER; the parent offering entity.
• IGS_AD_LOCATION_ALL — joined on LOCATION_CODE; supplies location descriptions.
• IGS_AD_BUILDING_ALL — joined on BUILDING_ID; supplies building names and addresses.
• IGS_AD_ROOM_ALL — joined on ROOM_ID; supplies room identity and characteristics.
• Unit offering and teaching timetable tables that inherit unit version keys and frequently union or join to unit location rows for delivery-mode and venue reporting.

No dependent tables are documented for this object in the provided metadata, so referential integrity flows outward from IGS_PS_UNIT_LOCATION to the four parent tables rather than inward from child records.