Search Results ota_booking_status_histories




Overview
Explore in AI Chat

Overview

The OTA_BOOKING_STATUS_HISTORIES table is a core transactional entity within the Oracle E-Business Suite Learning Management (OTA) module, specifically in versions 12.1.1 and 12.2.2. It functions as a historical audit trail, systematically recording every status change for a student's enrollment (or booking) in a training event. This object is critical for maintaining a complete, time-sequenced log of a delegate's enrollment lifecycle, from initial application through confirmation, attendance, cancellation, or transfer. Its role is to provide an immutable record for compliance, reporting, and process analysis, enabling administrators to reconstruct the exact status history of any enrollment.

Key Information Stored

The table's structure is designed to capture the essential elements of a status transition. Its primary key is a composite of BOOKING_ID, BOOKING_STATUS_TYPE_ID, and START_DATE, ensuring a unique record for each status applied to a specific booking at a precise moment. The BOOKING_ID column links the history record to the specific enrollment in the OTA_DELEGATE_BOOKINGS table. The BOOKING_STATUS_TYPE_ID column stores a foreign key reference to the OTA_BOOKING_STATUS_TYPES table, which defines the meaning of the status (e.g., 'CONFIRMED', 'WAITLISTED', 'CANCELLED'). The START_DATE column is pivotal, marking the effective date and time when that particular status became active for the enrollment. While not detailed in the provided metadata, related columns typically include CREATED_BY and CREATION_DATE for audit purposes.

Common Use Cases and Queries

This table is central to enrollment auditing and trend reporting. A primary use case is generating a complete history for a specific student or booking to resolve disputes or understand progression. Another is analyzing the volume and flow of enrollments through different statuses over time for operational reporting. Common SQL patterns include retrieving the chronological history for a booking or finding all enrollments that reached a specific status within a date range.

  • Status History for a Booking: SELECT * FROM ota.ota_booking_status_histories WHERE booking_id = <id> ORDER BY start_date;
  • Current Status Lookup: SELECT * FROM ota.ota_booking_status_histories h WHERE booking_id = <id> AND start_date = (SELECT MAX(start_date) FROM ota.ota_booking_status_histories WHERE booking_id = h.booking_id);
  • Enrollments Cancelled in a Period: SELECT DISTINCT booking_id FROM ota.ota_booking_status_histories WHERE booking_status_type_id = (SELECT booking_status_type_id FROM ota.ota_booking_status_types WHERE name = 'CANCELLED') AND TRUNC(start_date) BETWEEN :p_start_date AND :p_end_date;

Related Objects

The OTA_BOOKING_STATUS_HISTORIES table has defined dependencies within the OTA schema, as per the provided metadata. It is a child table to OTA_DELEGATE_BOOKINGS via the BOOKING_ID foreign key, which is the master record of the enrollment itself. It also references the OTA_BOOKING_STATUS_TYPES table via the BOOKING_STATUS_TYPE_ID foreign key to obtain the human-readable name and system meaning of each status code. While not listed, this table is likely referenced by various OTA forms, concurrent programs, and standard API packages (e.g., OTA_BOOKING_API) that manage the enrollment lifecycle and maintain this historical log.