Results for “igs_as_exam_session_all”

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

Overview

IGS_AS_EXAM_SESSION_ALL is a core table within the Oracle E-Business Suite Student System (IGS) product family. It describes the available examination sessions that are linked to a date alias instance within a nominated examination calendar instance, defining the discrete windows of time during which examinations may be conducted. Each row represents one schedulable session, bound to a specific examination calendar, a specific calendar instance sequence, a date alias, and a date alias instance, together with explicit start and end times. The table is owned by the IGS schema and is marked VALID in the documented release. It carries an ORG_ID column, indicating it participates in the multi-org (Operating Unit) security model.

From a dimensional modeling perspective, the heuristic Data Vault classification for this table is hub-leaning. This classification reflects that the table's identity is anchored on a composite business key of calendar, instance, date alias, and time attributes, with descriptive and audit columns attached. Readers should treat this as a modeling suggestion rather than a normative statement, since the physical design predates Data Vault conventions and is optimized for transactional integrity within the Student System.

Key Information Stored

The documented physical schema contains 17 columns. The most significant are summarized below.

• EXAM_CAL_TYPE — Identifies the examination calendar type to which the session belongs. This column is part of the primary key and the unique business-key index.
• EXAM_CI_SEQUENCE_NUMBER — The calendar instance sequence number, qualifying the calendar to a specific delivery instance.
• DT_ALIAS — The date alias associated with the session, typically denoting a named date pattern such as a booking or attendance date.
• DAI_SEQUENCE_NUMBER — The date alias instance sequence number, resolving the date alias to a concrete dated occurrence.
• START_TIME / END_TIME — The wall-clock boundaries of the examination session window.
• CI_START_DT / CI_END_DT — The start and end dates of the parent calendar instance, providing temporal context for the session.
• ESE_ID — A surrogate identifier for the examination session record.
• EXAM_SESSION_NUMBER — A business-facing session number used to distinguish multiple sessions within the same date alias instance.
• ORG_ID — The Operating Unit that owns the record, enforcing multi-org data segregation.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN provide standard EBS who-did-what auditing.
• COMMENTS — Free-text commentary attached to the session definition.

The surrogate key is IGS_AS_EXAM_SESSION_ALL_PK, defined over EXAM_CAL_TYPE, EXAM_CI_SEQUENCE_NUMBER, DT_ALIAS, DAI_SEQUENCE_NUMBER, START_TIME, and END_TIME. The unique index IGS_AS_EXAM_SESSION_ALL_U1 covers the same column set, making it the documented business-key candidate. ESE_ID, where populated, offers a single-column surrogate alternative for joins and application lookups.

Common Use Cases and Queries

Typical usages include generating examination timetables, validating that a proposed examination slot falls within an available session, feeding seating and attendance processes, and reporting on session utilization across calendar instances. A representative query retrieves all sessions for a given examination calendar instance:

• SELECT exam_cal_type, exam_ci_sequence_number, dt_alias, dai_sequence_number, start_time, end_time, exam_session_number FROM igs_as_exam_session_all WHERE exam_cal_type = :cal_type AND exam_ci_sequence_number = :ci_seq ORDER BY dai_sequence_number, start_time;
• Joining to IGS_AS_EXAM_INSTANCE_ALL to confirm which actual examinations are scheduled into each available session, filtered by ORG_ID for operating-unit security.
• Reporting on session availability by DATE to identify gaps or overlaps in the examination calendar using CI_START_DT and CI_END_DT.

Because the table holds both START_TIME and END_TIME, interval-overlap detection is commonly implemented in SQL to prevent double-booking of a session when scheduling examination instances.

Related Objects

• IGS_CA_INST_ALL — Referenced by EXAM_CAL_TYPE and EXAM_CI_SEQUENCE_NUMBER; the parent calendar instance definition.
• IGS_AS_EXAM_INSTANCE_ALL — References this table through EXAM_CAL_TYPE, EXAM_CI_SEQUENCE_NUMBER, DT_ALIAS, DAI_SEQUENCE_NUMBER, START_TIME, and END_TIME. It records the actual examinations conducted within the available sessions.
• IGS_AS_EXMVNU_SESAVL_ALL — A dependent object (view or availability table) referencing the same composite key columns, used in session availability processing.
• IGS_AS_EXAM_SESSION_ALL_PK / IGS_AS_EXAM_SESSION_ALL_U1 — The primary key constraint and unique business-key index that enforce uniqueness across the composite key.

Together these objects form the scheduling backbone that links examination calendars, date alias instances, and session availability within the Oracle Student System.