Results for “ota_training_plans_v”

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

Overview

OTA_TRAINING_PLANS_V is a secured, denormalized reporting view owned by the APPS schema in Oracle E-Business Suite, belonging to the OTA (Learning Management) product family. It exposes the contents of the OTA_TRAINING_PLANS base table while enriching each row with human-readable descriptors drawn from Oracle HRMS and Oracle Payroll reference entities. The view exists to support both Oracle Forms-based Learning Management screens and custom reporting or integration layers that require plan information without the burden of reconstructing foreign key relationships at runtime.

Two characteristics distinguish the view. First, it is a session-aware view: it joins FND_SESSIONS on USERENV('SESSIONID') and constrains the person record using the session effective date, so person-related attributes reflect the row as it existed on the session date rather than the current date. Second, it deliberately resolves the polymorphic ownership model of a training plan — a plan may belong to an organization or to an individual — into a single PLAN_TYPE column and parallel PERSON and ORGANIZATION columns.

Underlying Base Objects

The view is defined over OTA_TRAINING_PLANS (aliased TPS) as its driving table, with outer and inner joins to the following documented base objects:

• OTA_TRAINING_PLANS — the primary table holding plan identity, budget currency, descriptive flexfields (ATTRIBUTE1–30), and audit columns.
• HR_LOOKUPS — supplies the decoded plan status meaning through lookup type OTA_PLAN_USER_STATUS_TYPE, joined on PLAN_STATUS_TYPE_ID = LOOKUP_CODE.
• PER_TIME_PERIODS and PAY_CALENDARS — supply the period name, period set name, and start/end dates for the plan's time period.
• HR_ALL_ORGANIZATION_UNITS — supplies the organization name where a plan is organization-owned (outer join).
• PER_ALL_PEOPLE_F — supplies full name and employee number where a plan is person-owned (outer join).
• FND_SESSIONS — drives session-scoped effective dating for the person join.

Note that the ETRM 12.2.2 metadata lists FND_LOOKUP_VALUES as the referenced base object for status decoding, whereas the view text preserved above references HR_LOOKUPS; both provide the same decoded MEANING value and are functionally interchangeable in the join predicate.

Key Columns

Identity and ownership columns include ROW_ID, TRAINING_PLAN_ID, PLAN_TYPE (which returns the literal 'PERSON' or 'ORGANIZATION' via DECODE on ORGANIZATION_ID), ORGANIZATION_ID with ORG.NAME, and PERSON_ID with FULL_NAME and EMPLOYEE_NUMBER. The presence of both PERSON_ID and ORGANIZATION_ID, combined with the outer joins, allows a single query to report on both plan categories without union logic.

Time columns are TIME_PERIOD_ID, PERIOD_SET_NAME, PERIOD_NAME, and the aliased PERIOD_START_DATE and PERIOD_END_DATE. Status is surfaced twice: PLAN_STATUS_TYPE_ID (the lookup code) and MEANING (the decoded status). Descriptive columns include NAME, DESCRIPTION, and BUDGET_CURRENCY. Standard WHO audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, BUSINESS_GROUP_ID, and OBJECT_VERSION_NUMBER — are also exposed, along with the complete ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE30 descriptive flexfield set.

Common Use Cases and Queries

Typical usages include operational reports of active training plans by status, budget analysis by organization and currency, and integration extracts feeding external learning or HR systems.

List plan statuses for the current period, filtered by organization:

• SELECT TRAINING_PLAN_ID, NAME, PLAN_TYPE, ORG_NAME, PERIOD_NAME, MEANING FROM APPS.OTA_TRAINING_PLANS_V WHERE MEANING = 'Active';

Because person attributes are resolved against the session date, reports expecting today's person details should call FND_GLOBAL.SET_POLICY_CONTEXT or run in an initialized session. Plan status is decoded from a lookup, so custom statuses added to OTA_PLAN_USER_STATUS_TYPE flow through automatically. Finally, always qualify the view with the APPS schema and avoid selecting ROW_ID into downstream tables, as it is a physical row identifier, not a stable business key.