Search Results preferred_region_code

Overview

The IGS_PS_USEC_OCCURS_V view is a reporting and integration object in the Oracle E-Business Suite Student System (IGS) product family, owned by the APPS schema. It presents scheduling and room-allocation detail for unit section occurrences — the individual meeting instances associated with a unit section occurrence in the Student System. In EBS 12.1.1 and 12.2.2 the view is delivered in VALID status and is intended for read-only consumption by reports, concurrent programs, and external integrations rather than for direct DML.

The view resolves coded identifiers into human-readable descriptions by joining the occurrence record to building, room, and lookup tables. This makes it particularly useful where a report or interface must display descriptive text — building names, room descriptions, and schedule status meanings — instead of raw codes. It also exposes the descriptive flexfield (ATTRIBUTE1 through ATTRIBUTE20) and standard WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE), supporting auditing and attribute-driven reporting.

Underlying Base Objects

The view is defined over the unit section occurrence base table, referenced in the view text as USO, which supplies the scheduling columns: day-of-week flags, start and end times, room and building codes, schedule status, instructor identifier, error text, and the descriptive flexfield attributes. The ETRM metadata lists no formally documented referenced base objects, but the view text itself reveals the following dependencies:

• USO — the primary unit section occurrence table, providing all driving columns and the ROWID exposed as ROW_ID.
• BLD1, BLD2, BLD3 — building tables joined to resolve scheduled, dedicated, and preferred building descriptions.
• ROM1, ROM2, ROM3 — room tables joined to resolve scheduled, dedicated, and preferred room descriptions.
• LKUP — a lookup table joined on SCHEDULE_STATUS to supply SCHEDULE_STATUS_DESC via the MEANING column.

Because the view reuses BLD1 and ROM1 for both the scheduled and general building/room descriptors, the same lookup rows supply multiple output columns. Surname and person number are projected as NULL in the view text, indicating that the instructor name is not resolved at this layer and must be obtained elsewhere.

Key Columns

• UNIT_SECTION_OCCURRENCE_ID / UOO_ID — primary identifiers for the occurrence record.
• MONDAY through SUNDAY — day-of-week indicator flags defining when the occurrence meets.
• START_TIME / END_TIME — the scheduled meeting window.
• BUILDING_CODE / BUILDING_DESC / ROOM_CODE / ROOM_DESC — the assigned location and its description.
• SCHEDULED_BUILDING_CODE / SCHEDULED_BUILDING_DESC, SCHEDULED_ROOM_CODE / SCHEDULED_ROOM_DESC — the scheduled location set.
• DEDICATED_BUILDING_CODE / DEDICATED_BUILDING_DESC, DEDICATED_ROOM_CODE / DEDICATED_ROOM_DESCRIPTION — the dedicated location set.
• PREFERRED_BUILDING_CODE / PREFERRED_BUILDING_DESC, PREFERRED_ROOM_CODE / PREFERRED_ROOM_DESC — the preferred location set.
• SCHEDULE_STATUS / SCHEDULE_STATUS_DESC — the status code and its lookup meaning; note that preferred_region_code is not a column of this view, so searches for that term return no matching attribute here.
• INSTRUCTOR_ID — the instructor identifier, with SURNAME and PERSON_NUMBER exposed as NULL.
• ERROR_TEXT — diagnostic text populated during scheduling processing.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE20 — descriptive flexfield context and segments.

Common Use Cases and Queries

Typical usage covers timetable reporting, room utilisation analysis, and validation of preferred versus scheduled locations prior to publishing a schedule.

SELECT unit_section_occurrence_id, uoo_id,
       start_time, end_time,
       building_code, building_desc,
       room_code, room_desc,
       preferred_building_code, preferred_room_desc,
       schedule_status_desc
FROM   apps.igs_ps_usec_occurs_v
WHERE  schedule_status = :p_status;

To reconcile preferred allocations against scheduled allocations:

SELECT unit_section_occurrence_id,
       preferred_building_code, preferred_room_code,
       scheduled_building_code, scheduled_room_code
FROM   apps.igs_ps_usec_occurs_v
WHERE  preferred_room_code IS NOT NULL
AND    scheduled_room_code IS NOT NULL
AND    (preferred_room_code <> scheduled_room_code
        OR preferred_building_code <> scheduled_building_code);

Day-of-week distribution and error review follow the same pattern, filtering on the MONDAY–SUNDAY flags or on ERROR_TEXT IS NOT NULL. All queries should be issued with the APPS schema context and appropriate IGS security applied.