Search Results internal_booking

Overview

OTFV_TRAINING_AMOUNT is an APPS-owned database view in the Oracle E-Business Suite Learning Management (OTA) module. It is a business view template from which the flexfield view is generated, meaning it serves as the canonical, denormalized projection of training enrollment data used by the flexfield-driven reporting framework. The view is VALID in both 12.1.1 and 12.2.2 and is defined WITH READ ONLY, so it is intended strictly for query and reporting consumption rather than transactional writes.

OTFV_TRAINING_AMOUNT consolidates delegate bookings, event and activity definitions, booking statuses, and both Oracle Learning and Oracle Order Management financial data into a single row-per-enrollment structure. This makes it the primary integration and reporting surface for training revenue and order status. It is the object typically referenced when a user searches on event_status, since that column is exposed directly and is decoded through the HR_BIS lookup package.

Underlying Base Objects

The view is built over the following documented base objects, which are joined through standard Learning Management keys:

• OTA_DELEGATE_BOOKINGS — the driving table, supplying enrollment identity.
• OTA_BOOKING_STATUS_TYPES — booking status description.
• OTA_EVENTS and OTA_EVENTS_TL — scheduled event and title.
• OTA_ACTIVITY_VERSIONS and OTA_ACTIVITY_VERSIONS_TL — activity version and version name.
• OTA_ACTIVITY_DEFINITIONS and OTA_ACTIVITY_DEFINITIONS_TL — activity type and definitions.
• OTA_FINANCE_LINES — OTA monetary amount and currency.
• OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL — order status and OE amount.
• HR_ALL_ORGANIZATION_UNITS_TL — business group name.
• OTA_OFFERINGS — offering reference.
• HR_BIS and OTA_GENERAL — supplied packages used for lookup decoding and business group resolution.

All of these are referenced through APPS synonyms. The joins use outer-join syntax (marked with the (+) operator) on the activity, finance, and order tables, so enrollments without financial or order data still return rows.

Key Columns

• BUSINESS_GROUP_NAME, ACTIVITY_TYPE, ACTIVITY_NAME, EVENT_TITLE — descriptive context for the training.
• EVENT_STATUS — the event status decoded via HR_BIS.BIS_DECODE_LOOKUP('SCHEDULED_EVENT_STATUS', EVT.EVENT_STATUS). This is the column most commonly used when searching on event status.
• BOOKING_STATUS — decoded from OTA_BOOKING_STATUS_TYPES (ATTENDED, PLACED, WAITLISTED, CANCELLED, RESTRICTED).
• ORDER_HEADER_STATUS, ORDER_LINE_STATUS — OE flow status codes.
• PLACES_BOOKED — number of places taken.
• OTA_AMOUNT, OTA_AMOUNT_CURRENCY — NVL'd OTA finance amount and currency.
• OE_AMOUNT, OE_AMOUNT_CURRENCY — ordered quantity times unit selling price, and transactional currency.
• INTERNAL_BOOKING, EXTERNAL_BOOKING — YES/NO flags derived from the internal booking flag.
• EVENT_ID, ACTIVITY_VERSION_ID, ACTIVITY_ID, ENROLLMENT_ID, BUSINESS_GROUP_ID, RCO_ID, OFFERING_ID — identifiers for joins and drill-downs.

Common Use Cases and Queries

The view is used for enrollment revenue analysis, reconciliation between OTA finance lines and OE order amounts, and status reporting across events and bookings. A representative query filtering on event status follows:

SELECT EVENT_TITLE, EVENT_STATUS, BOOKING_STATUS,
      OTF_AMOUNT, OTA_AMOUNT_CURRENCY
  FROM APPS.OTFV_TRAINING_AMOUNT
 WHERE EVENT_STATUS = 'Scheduled'
   AND BUSINESS_GROUP_NAME = :bg_name;

Other common patterns include aggregating OTA_AMOUNT by ACTIVITY_TYPE for revenue reporting, comparing OTA_AMOUNT against OE_AMOUNT for order-to-training reconciliations, and using ENROLLMENT_ID as a drill-down key into delegate booking detail. Because the view restricts the business group through OTA_GENERAL.GET_BUSINESS_GROUP_ID and enforces USERENV('LANG') on the organization name, always constrain on the appropriate business group context to avoid cross-group results.