Search Results ota_event_search_v2




Overview
Explore in AI Chat

Overview

OTA_EVENT_SEARCH_V2 is a database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the OTA (Learning Management) product family. The view is documented as VALID in ETRM for both 12.1.1 and 12.2.2 and carries a single, well-defined purpose: to list all Events (that is, scheduled offerings of a course) for consumption by the Event Search Folder Form in the Oracle Learning Management application. Rather than acting as a transactional interface, the view serves as a denormalized read model. It joins the core event entity to its descriptive title, associated administrative organization, currency, language, and a series of coded lookup values, then resolves each code into its user-facing meaning through HR_GENERAL.DECODE_LOOKUP. This means a single query against the view returns business-friendly values that would otherwise require the calling form or report to perform multiple joins and lookup translations.

In reporting and integration terms, the view provides a stable, presentation-ready projection of event data. Because it exposes the row identifier alongside the primary key and the object version number, it is also suitable for folder-style result grids and Can/OLTP-aware screens that require optimistic locking semantics.

Underlying Base Objects

The view is documented as referencing the following base objects: OTA_EVENTS, OTA_EVENTS_TL, OTA_ACTIVITY_VERSIONS, OTA_ACTIVITY_VERSIONS_TL, OTA_PROGRAM_MEMBERSHIPS, OTA_RESOURCE_BOOKINGS, OTA_OFFERINGS_VL, OTA_SUPPLIABLE_RESOURCES_TL, HR_ALL_ORGANIZATION_UNITS_TL, FND_CURRENCIES_VL, PO_VENDORS, plus the packages HR_GENERAL (for lookup decoding), OTA_EVT_SHD (for events and their translations), and OTA_UTILITY (for shared utility logic). The central object is OTA_EVENTS, which stores the event schedule, pricing, capacity, and descriptive flexfield attributes; OTA_EVENTS_TL supplies the language-dependent title. HR_ALL_ORGANIZATION_UNITS_TL provides the owning organization name, FND_CURRENCIES_VL supplies the currency code and name, and HR_GENERAL.DECODE_LOOKUP translates stored codes such as TRAINING_CENTRE, DEV_EVENT_TYPE, OTA_DURATION_UNITS, and EVENT_PRICE_BASIS into their display meanings. The remaining objects, including the activity version, offering, program membership, resource booking, suppliable resource, and vendor views, are referenced in support of the event's broader scheduling and resource context.

Key Columns

The view exposes the event's identifying and operational attributes. EVENT_ID is the primary key, and ROWID and OBJECT_VERSION_NUMBER are included to support folder forms and concurrent update detection. BUSINESS_GROUP_ID scopes the row to a multitenant business group. TITLE provides the human-readable event description from the translated events table. COURSE_START_DATE, COURSE_START_TIME, COURSE_END_DATE, and COURSE_END_TIME define the delivery window, while WEEK_NUMBER is derived from the start date via the ISO week format mask 'IW'. DURATION and DURATION_UNITS describe the length of the event, with DURATION_UNITS_MEANING resolving the code through OTA_DURATION_UNITS. Enrolment is governed by ENROLMENT_START_DATE and ENROLMENT_END_DATE, and capacity by MINIMUM_ATTENDEES, MAXIMUM_ATTENDEES, and MAXIMUM_INTERNAL_ATTENDEES. Financial columns include STANDARD_PRICE, ACTUAL_COST, BUDGET_COST, BUDGET_CURRENCY_CODE, CURRENCY_CODE, CURRENCY_NAME, and PRICE_BASIS with its decoded PRICE_BASIS_MEANING. ORGANIZATION_ID and ORGANIZATION_NAME identify the delivering organization, while CENTRE and CENTRE_MEANING identify the training centre. Flags such as PUBLIC_EVENT_FLAG, SECURE_EVENT_FLAG, and BOOK_INDEPENDENT_FLAG control visibility and booking behavior, and the EVT_INFORMATION1 through EVT_INFORMATION20 columns expose descriptive flexfield segments.

Common Use Cases and Queries

The most common use is powering the Event Search Folder Form, where users filter events by title, date range, centre, organization, or public flag. The same projection is useful for ad hoc reporting on scheduled training, capacity utilization, and pricing. A representative query listing upcoming public events with decoded meanings is shown below.

  • SELECT event_id, title, course_start_date, course_end_date, organization_name, centre_meaning, standard_price, currency_code FROM ota_event_search_v2 WHERE public_event_flag = 'Y' AND course_start_date >= SYSDATE ORDER BY course_start_date;
  • SELECT week_number, COUNT(*) FROM ota_event_search_v2 WHERE course_start_date BETWEEN :p_start AND :p_end GROUP BY week_number ORDER BY week_number; — summarizes event volume by ISO week.
  • SELECT event_id, title, maximum_attendees, minimum_attendees, duration, duration_units_meaning FROM ota_event_search_v2 WHERE organization_id = :p_org_id; — supports capacity planning for a delivering organization.
  • SELECT event_id, title, price_basis_meaning, standard_price, budget_cost, currency_name FROM ota_event_search_v2 WHERE secure_event_flag = 'Y'; — identifies restricted events for compliance review.

Because the view is read-only and defined in the APPS schema, it should be used through a synonym or fully qualified name in custom reports rather than being modified. Joins back to OTA_EVENTS on EVENT_ID remain the correct pattern when transactional updates are required.