Results for “cost_level”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

OTA_TRAINING_COST_LEVELS_V is an APPS-owned database view in the Oracle E-Business Suite 12.1.1 and 12.2.2 Learning Management (OTA) module. Its purpose is to consolidate training plan cost data into a single, denormalized result set that exposes the applicable rollup level for each cost record. The view is the queryable interface that reporting tools, Oracle Learning Management cost analysis concurrent programs, and custom integrations rely upon when they need to see costs in the context of the reporting hierarchy in which they were rolled up.

The object is central to any search involving the keyword "rollup_level," because ROLLUP_LEVEL is a literal column generated directly by the view definition rather than being stored in a base table. The view assigns this value contextually, so a single SELECT against OTA_TRAINING_COST_LEVELS_V can retrieve plan-level costs and activity-version costs side by side without the caller having to reconcile different source tables or apply complex outer joins. It is defined as a compound query (UNION of multiple branches), which means Oracle Database materializes and de-duplicates the results at runtime rather than storing them physically.

Underlying Base Objects

The documented base objects referenced by the view are OTA_ACTIVITY_DEFINITIONS, OTA_ACTIVITY_VERSIONS, OTA_DELEGATE_BOOKINGS, OTA_EVENTS, OTA_TP_MEASUREMENT_TYPES, OTA_TRAINING_PLANS_V, and OTA_TRAINING_PLAN_COSTS. All except OTA_TRAINING_PLANS_V are synonyms pointing to the underlying OTA tables, and OTA_TRAINING_PLANS_V is itself a view over the training plan structure.

The core driving table is OTA_TRAINING_PLAN_COSTS, which stores individual cost rows with their measurement type, amount, currency, event, booking, and training plan references. OTA_TP_MEASUREMENT_TYPES supplies the measurement definition and, critically, a COST_LEVEL attribute that determines whether a measurement is a PLAN cost or an EVENT cost. OTA_TRAINING_PLANS_V provides the PERIOD_START_DATE for plan-level rows, while OTA_EVENTS contributes COURSE_START_DATE for event-level rows. OTA_ACTIVITY_VERSIONS supplies VERSION_NAME, which the view aliases as ITEM_NAME for activity-version records. OTA_ACTIVITY_DEFINITIONS and OTA_DELEGATE_BOOKINGS participate in the additional UNION branches that handle delegate bookings and definition-level cost attribution.

Key Columns

• ROLLUP_LEVEL — A literal discriminator identifying the granularity of the row. Documented values include 'PLAN', representing training plan level, and 'ACTIVITY_VERSION', representing rollups tied to a specific activity version.
• COST_LEVEL — A second literal discriminator distinguishing 'PLAN' costs from 'EVENT' costs, derived from the measurement type's COST_LEVEL.
• MEASUREMENT_TYPE_ID — Foreign key to OTA_TP_MEASUREMENT_TYPES; identifies which cost measurement (for example, a currency amount or a numeric quantity) the row represents.
• ITEM_NAME — Populated with VERSION_NAME for activity-version rows and padded with a blank for plan-level rows.
• AMOUNT and CURRENCY_CODE — The monetary value and currency of the cost record.
• UNIT — The unit of measure carried from the measurement type definition.
• PERIOD_START_DATE — The reporting period start; sourced from the training plan for plan rows and from the event course start date for event rows.
• EVENT_ID, BOOKING_ID, and TRAINING_PLAN_ID — Contextual identifiers linking the cost to its plan, event, or delegate booking.

Common Use Cases and Queries

Because the view normalizes rollup levels, it is the natural source for cost rollup reports that must present both plan-level and activity-version-level figures. A typical query filters on a specific plan and orders by rollup level:

• SELECT training_plan_id, rollup_level, cost_level, item_name, amount, currency_code FROM ota_training_cost_levels_v WHERE training_plan_id = :p_plan_id ORDER BY rollup_level, cost_level;
• SELECT rollup_level, SUM(amount) total FROM ota_training_cost_levels_v WHERE currency_code = 'USD' GROUP BY rollup_level;
• SELECT measurement_type_id, item_name, amount FROM ota_training_cost_levels_v WHERE rollup_level = 'ACTIVITY_VERSION' AND event_id IS NOT NULL;

These patterns support cost aggregation dashboards, budget-versus-actual comparisons, and extracts feeding external financial systems. When tuning, note that the UNION branches filter on OTPC.EVENT_ID IS NOT NULL and OTPC.BOOKING_ID IS NULL to separate event costs from delegate booking costs; queries that include these predicates align with the view's internal access paths. The ROLLUP_LEVEL and COST_LEVEL columns should always be selected in report output so consumers can correctly interpret the grain of each returned amount.