Search Results activity_user_status




Overview
Explore in AI Chat

Overview

APPS.OTV_ACTIVITIES is a reporting view within the Oracle E-Business Suite Training Administration (OTM/ETRM) module. It presents a consolidated, business-friendly projection of training activities, joining activity version data to definition, language, person, vendor, and organization information. Its principal role is to expose activity metadata—such as version name, description, duration, objectives, intended audience, controlling person, and vendor—in a single queryable object so that reports, concurrent programs, and integrations do not need to reconstruct the underlying multi-table joins themselves.

The view is significant in the context of the user search term activity_user_status. Because the underlying tables store a raw status code, the view applies HR_GENERAL.DECODE_LOOKUP('ACTIVITY_USER_STATUS', OAV.USER_STATUS) to translate the coded value into a meaningful, translatable display string. This makes OTV_ACTIVITIES the natural surface for reporting on an activity's user-facing status without requiring the consumer to independently decode the lookup. The view is documented under both Oracle EBS 12.1.1 and 12.2.2, remaining a stable ETRM reporting object across releases.

Underlying Base Objects

The view is defined over a set of synonyms, views, and packages owned primarily by APPS. The documented referenced base objects are:

  • OTA_ACTIVITY_VERSIONS (SYNONYM) — the primary driver, supplying version name, description, duration, user status, objectives, audience, and version identifiers.
  • OTA_ACTIVITY_DEFINITIONS (SYNONYM) — supplies the activity name, business group, and the activity identifier linking versions to definitions.
  • FND_LANGUAGES_VL (VIEW) — provides the language description via an outer join on language ID.
  • PER_PEOPLE_F (VIEW) — resolves the controlling person's full name, joined on person ID with an effective-date constraint.
  • PO_VENDORS (VIEW) — supplies the vendor name for externally delivered activities.
  • HR_ALL_ORGANIZATION_UNITS (SYNONYM) — provides the business group (organization) name.
  • OTV_SCHEDULED_EVENTS (VIEW) — used in an EXISTS subquery to restrict the view to activities that have at least one scheduled event.
  • HR_GENERAL (PACKAGE) — supplies DECODE_LOOKUP for frequency and activity user status.
  • Additional referenced objects include HR_PERSON_NAME, HR_SECURITY, OTA_GENERAL, OTA_UTILITY, and OTA_VIEWS_PKG packages.

The joins are largely outer joins (noted by the (+) syntax), so activities lacking a person, vendor, or language record are still returned. The view is further scoped by TRUNC(SYSDATE) falling within the version's effective start and end dates, and by the business group resolved through OTA_GENERAL.GET_BUSINESS_GROUP_ID.

Key Columns

  • VERSION_NAME / DESCRIPTION — the activity version name and its description.
  • NAME (activity definition) — the base activity name from OTA_ACTIVITY_DEFINITIONS.
  • DURATION and decoded DURATION_UNITS — the activity length with its frequency unit decoded via the FREQUENCY lookup.
  • USER_STATUS (decoded) — the activity user status translated through the ACTIVITY_USER_STATUS lookup, the column of interest for the searched term.
  • OBJECTIVES / INTENDED_AUDIENCE — descriptive attributes of the activity.
  • FULL_NAME — the controlling person's name.
  • VENDOR_NAME — the supplier for externally delivered activities.
  • CONTROLLING_PERSON_ID, ACTIVITY_ID, ACTIVITY_VERSION_ID, BUSINESS_GROUP_ID — surrogate keys useful for joining and filtering.
  • Business group NAME — the organization name of the owning business group.

Common Use Cases and Queries

Typical uses include activity catalog reporting, status dashboards filtered on activity user status, and vendor-delivered training analyses. The view is well suited to reporting only activities that have scheduled events, since the EXISTS clause inherently filters to those.

Sample query retrieving activities by decoded user status:

  • SELECT activity_id, version_name, user_status, duration, full_name, vendor_name FROM apps.otv_activities WHERE user_status = 'Active';

Because USER_STATUS is already decoded, filtering on the display value is practical in reporting, though consumers should be aware that lookup meanings may be translated. To obtain the raw code, the underlying OTA_ACTIVITY_VERSIONS.USER_STATUS must be queried directly.

Additional scenario—listing activities by business group with their controlling person:

  • SELECT name, business_group_id, full_name, vendor_name FROM apps.otv_activities ORDER BY name;

Integration consumers commonly use ACTIVITY_VERSION_ID from this view to correlate with OTV_SCHEDULED_EVENTS and other ETRM event objects.