Search Results total_purchased

Overview

The view APPS.OTFV_ENROLLMENT_AGREEMENTS is a business view template shipped within the Oracle E-Business Suite Release 12.1.1 and 12.2.2 environment under the OTA (Learning Management) product family. It is owned by the APPS schema and holds a VALID status. In the Oracle ETRM documentation the object is described as a "business view template from which the flexfield view is generated," which positions it as a foundational data access layer rather than an end-user application form. The template pattern is significant: Oracle Learning Management uses generated flexfield views for descriptive flexfield (DFF) support, and the literal fragment '_DF:OTA:OTA_BOOKING_DEALS:TBD' embedded in the view text is the DFF context marker that signals the generated view to attach OTA booking-deal descriptive flexfield segments.

Functionally, the view denormalizes enrollment agreements — internally represented as booking deals on the OTA_BOOKING_DEALS table — together with customer, event, activity, price list, and approval information. It therefore acts as a reporting and integration surface for training agreement data, exposing human-readable names in place of internal IDs and decoding lookup codes through the HR_BIS.BIS_DECODE_LOOKUP API.

Underlying Base Objects

The documented referenced base objects are: FND_USER, HR_ALL_ORGANIZATION_UNITS_TL, HZ_CUST_ACCOUNTS, HZ_PARTIES, OTA_ACTIVITY_DEFINITIONS, OTA_ACTIVITY_DEFINITIONS_TL, OTA_ACTIVITY_VERSIONS, OTA_ACTIVITY_VERSIONS_TL, OTA_BOOKING_DEALS, OTA_EVENTS, OTA_EVENTS_TL, OTA_PRICE_LISTS, plus the packages HR_BIS, OTA_GENERAL, and OTA_TBD_API. All tables are accessed through APPS synonyms.

The central driving table is OTA_BOOKING_DEALS (aliased TBD), which supplies agreement name, type, category, dates, discount percentage, place limits, overdraft limit, and the approval flag columns. Translated name columns are resolved from the _TL tables, and customer identity is joined from HZ_CUST_ACCOUNTS and HZ_PARTIES. Currency display and lookup decoding are delegated to the OTA_GENERAL.FND_CURRENCY_NAME and HR_BIS.BIS_DECODE_LOOKUP functions, while purchased-quantity and balance computations are performed by the OTA_TBD_API package.

Key Columns

• APPROVED_BY — the FND_USER.USER_NAME of the user who approved the agreement; this is the column most commonly located when searching for "approved_by."
• APPROVED_BY_PERSON_ID — the underlying person identifier from the booking deal, supporting joins to HR person data.
• AGREEMENT_NAME, AGREEMENT_TYPE, AGREEMENT_COMMENTS, CATEGORY — descriptive attributes of the booking deal, with type and category decoded via lookups.
• AGREEMENT_START_DATE, AGREEMENT_END_DATE — validity window of the agreement.
• TOTAL_PURCHASED and BALANCE — computed values returned by OTA_TBD_API.TFL_PURCHASED and OTA_TBD_API.TFL_BALANCE, populated only where the deal type is 'P' (purchased).
• CUSTOMER_NAME, EVENT_TITLE, ACTIVITY_NAME, ACTIVITY_TYPE — contextual identities of the enrollment.
• Price list columns (PRICE_LIST_NAME, PRICE_LIST_TYPE, DEFAULT_PRICE_LIST, TRAINING_UNITS, UNIT_PRICE, PRICE_LIST_CURRENCY, PRICE_LIST_DATE_FROM, PRICE_LIST_DATE_TO) describe the associated commercial terms.
• Identifier columns including BUSINESS_GROUP_ID, CUSTOMER_ID, EVENT_ID, ACTIVITY_VERSION_ID, and ACTIVITY_ID support joins and filters.

Common Use Cases and Queries

Typical uses include auditing which agreements were approved and by whom, reconciling purchased versus remaining training places, and extracting agreement data for reporting or integration. A search for "approved_by" is satisfied directly by the view.

List approved agreements with approver and customer:

SELECT agreement_name, customer_name, approved_by, agreement_start_date
FROM   apps.otfv_enrollment_agreements
WHERE  approved_by IS NOT NULL;

Reconcile balances per agreement:

SELECT agreement_name, total_purchased, balance, overdraft_limit
FROM   apps.otfv_enrollment_agreements
WHERE  agreement_type IS NOT NULL;