Search Results otfv_cust_restricted_events




Overview
Explore in AI Chat

Overview

OTFV_CUST_RESTRICTED_EVENTS is an APPS-owned database view within the Oracle E-Business Suite OTA (Learning Management) module. It is classified in the ETRM repository with a status of VALID and exists in both the 12.1.1 and 12.2.2 releases. Its documented purpose is that of a business view template from which the flexfield (descriptive flexfield) view is generated. In practice this means the view is not intended for direct application use but serves as a predefined, extensible query shell for reporting against scheduled training events that carry a customer or restricted-access association.

Unlike a transactional entity, OTFV_CUST_RESTRICTED_EVENTS functions as a reporting and integration surface. It flattens event scheduling data, training center ownership, supplier information, customer associations, resource bookings, activity classification, and financial order lines into a single denormalized projection. The presence of the literal attribute tag '_DF:OTA:OTA_EVENT_ASSOCIATIONS:TEA' in the select list indicates that the view is designed to support descriptive flexfield segments defined on the OTA_EVENT_ASSOCIATIONS entity, which is where the customer-to-event relationship is stored. This makes the view a natural foundation for custom reports, concurrent programs, and interfaces needing a customer-scoped event listing.

Underlying Base Objects

The view is defined over a broad set of OTA, HR, HZ, and PO objects. Documented base objects include OTA_EVENTS and OTA_EVENTS_TL (the event header and its translated descriptive columns), OTA_EVENT_ASSOCIATIONS (the customer/event link), OTA_FINANCE_LINES (orders and line references), OTA_OFFERINGS, OTA_RESOURCE_BOOKINGS, OTA_SUPPLIABLE_RESOURCES and its _TL counterpart, OTA_ACTIVITY_DEFINITIONS and OTA_ACTIVITY_VERSIONS (with their translation tables) for activity typing, and OTA_GENERAL and OTA_UTILITY for supporting utility logic. HR sources include HR_ALL_ORGANIZATION_UNITS_TL and PER_ALL_PEOPLE_F for organization and person names, with HR_BIS supplying lookup decodes. Customer-side data is drawn from HZ_CUST_ACCOUNTS and HZ_PARTIES. Currency lookup uses FND_CURRENCIES_VL, supplier reference uses PO_VENDORS, and order header logic references the HRI_BPL_OE_ORDER package. Function calls to OTA_EVT_SHD.RESOURCE_BOOKING_FLAG and HR_BIS.BIS_DECODE_LOOKUP are embedded directly in the select list, so the view is dependent on those packages at runtime. Because most OTA references are synonyms, the view resolves through the APPS synonym layer.

Key Columns

The view exposes a consistent naming convention where event-derived attributes carry an EVENT_ prefix. Notable columns include BUSINESS_GROUP_NAME, EVENT_TITLE, EVENT_COMMENTS, EVENT_START_DATE, EVENT_END_DATE, EVENT_START_TIME, EVENT_END_TIME, TIME_ZONE, and EVENT_DURATION with EVENT_DURATION_UNITS decoded via the FREQUENCY lookup. EVENT_CREATOR and EVENT_OWNER are both populated from PER.FULL_NAME. SECURE_EVENT is a decoded YES_NO flag, and RESOURCE_BOOKINGS is derived by decoding the first character returned by OTA_EVT_SHD.RESOURCE_BOOKING_FLAG. Customer and commercial columns include CUSTOMER_NAME, CUSTOMER_ID, EVENT_ASSOCIATION_ID, EVENT_ASSOCIATION_COMMENTS, STANDARD_PRICE, and CURRENCY. Resource and logistics columns include RESOURCE_TYPE, RESOURCE_NAME, TRAINING_CENTER, and SUPPLIER_NAME. Enrollment windows are exposed as ENROLLMENT_START_DATE and ENROLLMENT_END_DATE, capacity as MINIMUM_ATTENDEES and MAXIMUM_ATTENDEES, and financial linkage as ORDER_NUMBER and LINE_NUMBER from OTA_FINANCE_LINES. EVENT_STATUS and EVENT_USER_STATUS are decoded through SCHEDULED_EVENT_STATUS and EVENT_USER_STATUS lookups, and LANGUAGE is supplied from the translated tables.

Common Use Cases and Queries

Typical uses include customer-facing training catalogs, restricted-event rosters, and extracts into external scheduling or billing systems. Because the view already resolves customer association, event schedule, pricing, and order line data, it removes the need to hand-join OTA_EVENTS, OTA_EVENT_ASSOCIATIONS, and HZ tables in custom code.

  • List all restricted events for a given customer, showing schedule, owner, and price.
  • Report booked resources per event by resource type and training center.
  • Reconcile scheduled events to finance order numbers and line numbers.
  • Drive descriptive flexfield reporting on OTA_EVENT_ASSOCIATIONS using the embedded _DF attribute tag.
  • Populate concurrent program outputs or interface staging tables for external systems.

Sample SQL:

SELECT business_group_name,
       event_title,
       event_start_date,
       event_end_date,
       event_status,
       customer_name,
       resource_name,
       standard_price,
       currency,
       order_number,
       line_number
  FROM apps.otfv_cust_restricted_events
 WHERE customer_name = :p_customer
   AND event_start_date >= :p_from_date
 ORDER BY event_start_date, event_title;

Because the view invokes packaged functions at query time, performance is best served by providing selective predicates, particularly on EVENT_ID, CUSTOMER_ID, or EVENT_START_DATE, to limit row processing. Confirm that any custom flexfield view generated from this template remains synchronized after patches, as the underlying OTA table structures can change between 12.1.1 and 12.2.2.