Search Results tfl_purchased

Overview

OTA_BOOKING_DEALS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, defined within the OTA (Oracle Training Administration / Learning Management) product family. As documented in the ETRM metadata, the view is described as a "View to list all information about an Enrollment Agreement." In OTA terminology, an enrollment agreement is represented internally as a booking deal — a contractual arrangement between a customer and the training organization that governs pricing, place allocations, and discounts across one or more events. The view consolidates this booking deal data into a single, denormalized read interface suitable for reporting, Oracle Discoverer workbooks, BI Publisher data templates, and custom concurrent programs.

The view is marked VALID in the ETRM registry, with APPS as the owner. Its purpose is to expose the booking deal header attributes alongside decoded lookup meanings and denormalized customer, price list, event, and version descriptions, so that downstream consumers do not have to perform those joins or decode lookups independently. This makes it a primary extraction point for enrollment agreement reporting in both 12.1.1 and 12.2.2 environments.

Underlying Base Objects

The view draws from the following documented base objects, most of which are accessed through APPS synonyms:

• OTA_BOOKING_DEALS — the primary table holding booking deal header rows (aliased TBD in the view text).
• HZ_CUST_ACCOUNTS and HZ_PARTIES — used to resolve the customer identifier to a party name, truncated to 50 characters as CUS_NAME.
• FND_USER — joined to expose the username of the approving person (PPF_FULL_NAME).
• OTA_PRICE_LISTS — supplies price list name, type, training unit type, and single unit price.
• OTA_EVENTS and OTA_EVENTS_TL — provide the event title and default currency.
• OTA_ACTIVITY_VERSIONS_TL — supplies the activity version name for the associated event.
• OTA_CATEGORY_USAGES_TL — referenced for category decode context.
• HR_GENERAL (package) — used via HR_GENERAL.DECODE_LOOKUP to translate lookup codes such as BOOKING_DEAL_TYPE, PRICE_LIST_TYPE, TRAINING_UNIT, and ACTIVITY_CATEGORY into their meaning strings.
• OTA_TBD_API (package) — supplies the training fund logic (TFL_PURCHASED, TFL_BALANCE, TFL_FLAG) computed at query time.

Because several of the decoded and computed columns are derived through function calls and PL/SQL packages, the view is not purely a join product; portions of the output are evaluated per row at runtime.

Key Columns

• BOOKING_DEAL_ID, OBJECT_VERSION_NUMBER, BUSINESS_GROUP_ID — primary identity and multitenancy/version control columns.
• NAME, CUSTOMER_ID, CUS_NAME — agreement identifier and customer identity.
• TYPE and TBD_TYPE_MEANING — the booking deal type code and its decoded meaning (for example, the value 'P' driving the training fund logic).
• APPROVED_BY_PERSON_ID and PPF_FULL_NAME — the approver and the corresponding FND_USER username.
• PRICE_LIST_ID, TPL_NAME, TPL_TYPE, TPL_TYPE_MEANING, TPL_UNIT, TPL_UNIT_PRICE — the associated price list and its unit price data.
• EVENT_ID, EVT_TITLE, ACTIVITY_VERSION_ID, TAV_NAME — event and activity version context.
• CATEGORY and CAT_NAME — the category code and decoded description.
• START_DATE, END_DATE, CURRENCY_CODE — validity window and currency (price list currency, falling back to event currency via NVL).
• DISCOUNT_PERCENTAGE, NUMBER_OF_PLACES, LIMIT_EACH_EVENT_FLAG, OVERDRAFT_LIMIT — commercial terms of the agreement.
• TFL_PURCHASED, TFL_BALANCE, TFL_FLAG — training fund consumption metrics returned by OTA_TBD_API.
• DESCRIPTION, COMMENTS, and the TBD_INFORMATION1..20 descriptive flexfield columns.
• CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN — standard audit columns.

Common Use Cases and Queries

Typical uses include auditing enrollment agreements, reporting training fund balances per customer, and feeding outbound interfaces with agreement terms. A representative query lists active agreements with customer and price list context:

• SELECT booking_deal_id, name, cus_name, tbd_type_meaning, tpl_name, start_date, end_date FROM ota_booking_deals_v WHERE business_group_id = :bg_id;
• SELECT name, cus_name, tfl_purchased, tfl_balance, tfl_flag FROM ota_booking_deals_v WHERE type = 'P';
• SELECT name, evt_title, tav_name, number_of_places, discount_percentage FROM ota_booking_deals_v WHERE event_id = :event_id;

Because function-based columns invoke OTA_TBD_API, queries returning TFL_* values should be used judiciously in high-volume extracts. Restricting by BUSINESS_GROUP_ID, TYPE, or date range is recommended to limit the per-row package invocation overhead.