Search Results otfv_activity_categories




Overview
Explore in AI Chat

Overview

OTFV_ACTIVITY_CATEGORIES is an APPS-owned, VALID business view in the Oracle E-Business Suite 12.1.1 / 12.2.2 environment, delivered under the OTA — Learning Management product. Its stated purpose in the ETRM repository is that of a "business view template from which the flexfield view is generated," which places it within the descriptive flexfield (DFF) framework used by Oracle iLearning and Oracle Learning Management. Rather than serving as a transactional entity, the view presents a denormalized, read-only projection of activity category inclusions joined to their associated category usages, activities, activity versions, and events. It is a read-only view (the definition terminates with WITH READ ONLY), so it is intended strictly for reporting and integration consumption, not for DML.

Underlying Base Objects

The view is defined over five primary OTA synonyms — OTA_ACT_CAT_INCLUSIONS (ACI), OTA_CATEGORY_USAGES (TCU), OTA_EVENTS (EVT), OTA_ACTIVITY_VERSIONS (TAV), and OTA_ACTIVITY_DEFINITIONS (TAD). It also references the HR_BIS package, which supplies the BIS_DECODE_LOOKUP function used for translating lookup codes into user-facing descriptions. The documented referenced base objects additionally include the translation tables OTA_ACTIVITY_DEFINITIONS_TL, OTA_ACTIVITY_VERSIONS_TL, OTA_CATEGORY_USAGES_TL, and OTA_EVENTS_TL, plus OTA_OFFERINGS. The join structure is driven by ACI: ACI.ACTIVITY_VERSION_ID joins to TAV, TAV.ACTIVITY_ID joins to TAD, and ACI.EVENT_ID joins to EVT with an outer join (+). ACI.CATEGORY_USAGE_ID joins to TCU with an outer join. The OFFERING_ID column is resolved through the offering relationship associated with the activity inclusion.

Key Columns

The column list is anchored by categorization attributes. CATEGORY_NAME is derived through BIS_DECODE_LOOKUP on the 'ACTIVITY_CATEGORY' lookup type, while CATEGORY_TYPE is decoded against 'CATEGORY_TYPE' and PRIMARY_FLAG against 'YES_NO'. Validity of the inclusion is bounded by ACT_CAT_INCLUSION_FROM and ACT_CAT_INCLUSION_TO, mapped to ACI.START_DATE_ACTIVE and ACI.END_DATE_ACTIVE respectively. Descriptive context is provided by ACTIVITY_TYPE (TAD.NAME), ACTIVITY_NAME (TAV.VERSION_NAME), and the column central to the user's search — ACTIVITY_DESCRIPTION, sourced directly from TAV.DESCRIPTION, which holds the free-text description of the activity version. EVENT_TITLE and TIME_ZONE derive from OTA_EVENTS. The "_DF" column carries the contextual descriptor '_DF:OTA:OTA_ACT_CAT_INCLUSIONS:ACI', identifying the DFF context used when the flexfield view is generated. Surrogate identifiers exposed include CATEGORY_USAGE_ID, ACTIVITY_ID, ACTIVITY_VERSION_ID, EVENT_ID, RCO_ID, and OFFERING_ID, all of which support drill-down and integration keys.

Common Use Cases and Queries

The view is typically queried to report which activities are assigned to which categories, and to surface the activity description alongside category and event metadata. A representative query:

  • SELECT category_name, activity_name, activity_description, event_title FROM otfv_activity_categories WHERE activity_description IS NOT NULL;
  • Filtering by category type: SELECT activity_name, category_type FROM otfv_activity_categories WHERE category_type = 'CERTIFICATION';
  • Checking current inclusions: SELECT category_name, act_cat_inclusion_from, act_cat_inclusion_to FROM otfv_activity_categories WHERE SYSDATE BETWEEN act_cat_inclusion_from AND NVL(act_cat_inclusion_to, SYSDATE);
  • Joining to OTA_OFFERINGS on OFFERING_ID to enrich catalog reporting.

Because the view is read-only and decodes lookups through HR_BIS at runtime, queries should be tuned with appropriate bind filters and should avoid heavy sorts on the decoded columns when large activity catalogs exist.