Search Results programme_id




Overview
Explore in AI Chat

Overview

The OTA_CATEGORY_USAGES_V view is a reporting and integration construct owned by the APPS schema within the OTA – Learning Management module of Oracle E-Business Suite. It is delivered with a status of VALID and is documented for both release 12.1.1 and 12.2.2. Its stated purpose is to list all the uses of a category, exposing the relationship between an activity category and the records that consume it. The view is relevant to users who search on the term "activity_category", because the lookup type driving its content is exactly ACTIVITY_CATEGORY.

The view converts the internal, type-coded rows of the base usage table into a readable form by joining to HR_LOOKUPS, which supplies the display meaning of each category code. Three category usage types are reconciled in a single row per business group and category: 'C', 'P', and 'D'. The view returns the translated meaning alongside the usage identifiers, row identifiers, and object version numbers for each of the three types.

Underlying Base Objects

The documented base objects referenced by this view are HR_API (PACKAGE), HR_LOOKUPS (VIEW), and OTA_CATEGORY_USAGES (SYNONYM). The view definition joins three aliases of OTA_CATEGORY_USAGES — TCUC for type 'C', TCUP for type 'P', and TCUD for type 'D' — against HR_LOOKUPS filtered on LOOKUP_TYPE = 'ACTIVITY_CATEGORY'. The HR_API package supports the underlying lookup and security infrastructure that the view depends upon.

Because the view is built from three self-joins plus a lookup join, and uses UNION to cover categories that lack one or more usage types, it presents a normalized picture of usage without requiring the caller to write the type-decoding logic. Rows are keyed by BUSINESS_GROUP_ID and CATEGORY.

Key Columns

  • BUSINESS_GROUP_ID — the operating unit/HR business group scope for the category usage. Join criteria enforce that all three usage types share the same business group.
  • CATEGORY — the ACTIVITY_CATEGORY lookup code whose uses are being listed.
  • MEANING — the translated display value of the category, sourced from HR_LOOKUPS.
  • CATEGORY_USAGE_ID — the primary key of the usage record for each type ('C', 'P', 'D').
  • ROWID — the physical row identifier of the base usage record, useful for updates or debugging.
  • OBJECT_VERSION_NUMBER — the optimistic locking version column for each usage record.
  • Indicator flags — the view returns 'Y' or 'N' flags (and NULL placeholders in the UNION legs) to indicate whether the 'C', 'P', or 'D' usage type is present for the category.

Common Use Cases and Queries

Typical use cases include auditing which categories are actively consumed, reconciliation between the three usage types, and reporting of category metadata by business group. A representative query follows:

  • SELECT BUSINESS_GROUP_ID, CATEGORY, MEANING, CATEGORY_USAGE_ID, OBJECT_VERSION_NUMBER FROM APPS.OTA_CATEGORY_USAGES_V WHERE CATEGORY = :category; — retrieve all usages for a specific ACTIVITY_CATEGORY code.
  • SELECT CATEGORY, MEANING FROM APPS.OTA_CATEGORY_USAGES_V WHERE BUSINESS_GROUP_ID = :bg_id ORDER BY CATEGORY; — list categories and their meanings for a business group.
  • SELECT CATEGORY FROM APPS.OTA_CATEGORY_USAGES_V WHERE CATEGORY_USAGE_ID IS NULL; — identify categories missing one of the usage types, given the UNION logic that emits NULL placeholders.