Search Results otv_activities




Overview
Explore in AI Chat

Overview

OTV_ACTIVITIES is a valid, Oracle-owned view residing in the APPS schema within the OTA – Learning Management product module. It is delivered as part of the Oracle E-Business Suite 12.1.1 and 12.2.2 technology stack and is defined as a "View to list all information about an Activity and Activity Version." In Oracle Learning Management (OTM), an activity represents a catalogued learning or training offering (for example, a course, curriculum, or certification), while an activity version represents a specific, time-bounded revision of that offering. OTV_ACTIVITIES consolidates both levels of this hierarchy into a single denormalized result set, exposing descriptive, scheduling, vendor, and organizational attributes side by side.

The view's role in the EBS reporting and integration layer is essentially that of a reporting convenience object. Rather than requiring report authors to join OTA_ACTIVITY_DEFINITIONS, OTA_ACTIVITY_VERSIONS, per-person records, and vendor records manually, OTV_ACTIVITIES performs those joins internally and resolves several coded values into descriptive text. It therefore serves as a common source for custom Oracle Reports, BI Publisher data templates, Discoverer workbooks, and outbound integration extracts focused on the learning catalog.

Underlying Base Objects

The view text establishes the following principal join relationships:

  • OTA_ACTIVITY_VERSIONS (OAV) — the driving table; supplies version-level attributes such as version name, description, duration, duration units, user status, objectives, intended audience, controlling person, vendor, and effective start/end dates.
  • OTA_ACTIVITY_DEFINITIONS (OAD) — joined on ACTIVITY_ID; supplies the activity-level name and the BUSINESS_GROUP_ID.
  • FND_LANGUAGES_VL (LNG) — outer-joined on LANGUAGE_ID to provide the language description.
  • PER_PEOPLE_F (PEO) — outer-joined by CONTROLLING_PERSON_ID and effective-dated to the activity version's start date; supplies the manager's full name.
  • PO_VENDORS (VEN) — outer-joined on VENDOR_ID to supply the supplier/vendor name.
  • HR_ALL_ORGANIZATION_UNITS (BGR) — joined on BUSINESS_GROUP_ID to supply the business group name.
  • HR_GENERAL — a package used to decode the FREQUENCY and ACTIVITY_USER_STATUS lookup values.
  • OTA_GENERAL — provides GET_BUSINESS_GROUP_ID, used to restrict rows to the caller's business group via an NVL condition.
  • OTV_SCHEDULED_EVENTS — referenced in an EXISTS clause to return only activity versions that have at least one scheduled event.

Additional documented references include HR_PERSON_NAME, HR_SECURITY, OTA_UTILITY, and OTA_VIEWS_PKG, which are typically invoked indirectly through the constituent views and packages rather than appearing explicitly in the view text.

Key Columns

  • ACTIVITY_NAME / DESCRIPTION — the activity definition name and version description.
  • ACTIVITY_TYPE — classification of the activity.
  • LANGUAGE — language description of the version.
  • DURATION / DURATION_UNITS — nominal length of the version and its decoded unit of measure.
  • USER_STATUS — decoded status of the activity version for the learner.
  • OBJECTIVES / TARGET_AUDIENCE — learning objectives and the intended audience text.
  • MANAGED_BY — full name of the controlling person.
  • VENDOR_NAME — name of the associated supplier.
  • CONTROLLING_PERSON_ID, ACTIVITY_ID, ACTIVITY_VERSION_ID — surrogate keys for integration and drill-down.
  • BUSINESS_GROUP_ID / BUSINESS_GROUP_NAME — organizational ownership context.

Common Use Cases and Queries

Typical scenarios include catalog extracts, activity-to-vendor audits, and reporting on currently effective offerings. Because the view is already restricted by SYSDATE, the caller's business group, and the existence of scheduled events, results are naturally scoped.

A basic listing by business group:

  • SELECT activity_name, description, user_status, duration, duration_units, managed_by, vendor_name FROM otv_activities ORDER BY activity_name;

Filtering for a specific activity version:

  • SELECT activity_name, duration, language, target_audience, objectives FROM otv_activities WHERE activity_id = :p_activity_id AND activity_version_id = :p_version_id;

Vendor-oriented reporting:

  • SELECT vendor_name, COUNT(*) FROM otv_activities GROUP BY vendor_name ORDER BY 2 DESC;

Because security predicates are embedded through OTA_GENERAL and HR security packages, direct SQL against this view returns only data the querying responsibility is entitled to see.