Search Results ota_views_pkg




Overview
Explore in AI Chat

Overview

OTA_VIEWS_PKG is an Oracle E-Business Suite PL/SQL package owned by the APPS schema and is associated with the Oracle Training Administration (OTA) product family. Its documented purpose is to expose query-style ("view") logic over the OTA booking and event data model, allowing callers to derive calculated values without directly embedding that logic in forms, reports, or other packages. The single documented function, OTA_GET_PLACES_AVAILABLE, returns the number of places still available for a given training event, which is a core capacity-management calculation in the Oracle Learning Management / Training Administration module.

In ETRM the package is classified as an API of type OTHER, meaning it is not a formal public interface such as a business event or concurrent-program API, but rather an internal utility package intended for controlled use by other OTA components. The header comment records a source revision of 120.0 dated 2005/05/29, indicating a long-lived, stable object that has been carried across the 11i and 12.x code lines without significant functional change.

Key Procedures and Functions

The ETRM metadata documents one procedure or function in this package:

  • OTA_GET_PLACES_AVAILABLE — Returns the number of places available for a specified event. The function accepts an event identifier and computes remaining capacity by comparing the event's capacity against current bookings. The declaration includes a PRAGMA RESTRICT_REFERENCES(..., WNDS, WNPS), which asserts that the function performs no writes to database state (Write No Database State) and does not itself modify package state (Write No Package State). This purity level allows the function to be called from SQL statements and from restricted contexts such as function-based indexes and certain form triggers. The exact parameter list is not reproduced here; the signature shown in the source is a single event identifier parameter returning a number.

No other procedures or functions are documented in the ETRM metadata for this package. The naming convention "VIEWS" suggests it may historically have contained further read-only helper functions, but only the above is captured in the delivered documentation.

Tables Accessed

According to the ETRM metadata, APPS.OTA_VIEWS_PKG references the following tables through APPS synonyms:

  • OTA_EVENTS — The master event (course offering/session) table, supplying the event definition and its capacity or maximum-delegates attribute used in the availability calculation.
  • OTA_DELEGATE_BOOKINGS — The delegate booking table, which records individual enrollments against events. Aggregating bookings for the given event yields the number of places consumed.
  • OTA_BOOKING_STATUS_TYPES — The booking status lookup, used to determine which booking records count against capacity (for example, confirmed versus cancelled or waitlisted bookings). This is essential to ensure that cancelled bookings do not reduce availability.

Because the function is declared WNDS/WNPS, these tables are read-only within the package; no inserts, updates, or deletes are performed.

Usage Notes

OTA_VIEWS_PKG is typically invoked from other PL/SQL packages, Oracle Forms, or custom extensions in the Oracle Learning Management module whenever a remaining-capacity figure is required. Because OTA_GET_PLACES_AVAILABLE is a pure read function, it can be called directly within SQL, such as in a SELECT list or WHERE clause of a custom report or view, and it is safe to call repeatedly within a single transaction.

ETRM records that the package is referenced by three other packages, confirming its role as a shared internal utility rather than a standalone API. Customizations should not assume the function's signature is frozen; as an OTHER-classified API it carries no formal compatibility guarantee, and Oracle reserves the right to change it without notice. For new development, consider whether Oracle-delivered OTA APIs offer a supported alternative, and limit direct dependency on this package to read-only capacity queries.