Results for “source_function”

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

Overview

OTA_TRAINING_PLAN_MEMBERS is a transactional table within the Oracle Learning Management (OTA) module of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. It stores the membership rows that associate an Activity or Activity version with a parent Training Plan. In the Oracle EBS learning model, a training plan defines a curriculum, program, or certification path, and its members define which discrete learning activities constitute that plan. Each row represents a single planned activity, optionally pinned to a specific version of an activity definition, with a target completion date governing the expected delivery timeline.

From a Data Vault modeling perspective, the mined foreign-key structure classifies this table heuristically as a link. It resolves the many-to-many relationship between training plans and activity definitions/versions, carrying descriptive context (status, target date, assignment linkage) alongside the relationship. Practitioners designing downstream integration or warehouse models should treat it as a link entity rather than a pure satellite.

Key Information Stored

The table is defined in the OTA schema with 50 documented columns. The most significant are:

• TRAINING_PLAN_MEMBER_ID — surrogate primary key (OTA_TRAINING_PLAN_MEMBERS_PK); uniquely identifies each membership row.
• TRAINING_PLAN_ID — foreign key to OTA_TRAINING_PLANS; identifies the parent plan.
• ACTIVITY_DEFINITION_ID — foreign key to OTA_ACTIVITY_DEFINITIONS; the activity referenced.
• ACTIVITY_VERSION_ID — foreign key to OTA_ACTIVITY_VERSIONS; the specific version of the activity.
• TARGET_COMPLETION_DATE — planned completion date for the member activity.
• EARLIEST_START_DATE — earliest permitted start for the member.
• MEMBER_STATUS_TYPE_ID — lookup reference to the member's status.
• ASSIGNMENT_ID — link to the HR assignment for learner-specific memberships.
• CREATOR_PERSON_ID — person who created the membership.
• CANCELLATION_REASON — reason recorded when a membership is cancelled.
• SOURCE_ID and SOURCE_FUNCTION — provenance of the row's creation.
• BUSINESS_GROUP_ID — multi-tenant partition key (HR business group).
• OBJECT_VERSION_NUMBER — optimistic locking column.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns.

The business-key candidate is the composite unique index OTA_TRAINING_PLAN_MEMBERS_UK2 over (TRAINING_PLAN_ID, ACTIVITY_VERSION_ID, ACTIVITY_DEFINITION_ID, TARGET_COMPLETION_DATE). This prevents duplicate plan-activity-version-date combinations. The remaining ATTRIBUTE1 through ATTRIBUTE30 columns form the standard EBS descriptive flexfield storage.

Common Use Cases and Queries

Typical reporting includes listing the activities in a plan, tracking member status, and reconciling against learner enrolments.

• Plan curriculum listing: join OTA_TRAINING_PLAN_MEMBERS to OTA_ACTIVITY_DEFINITIONS to render the ordered set of activities within a plan.
• Overdue members: filter on TARGET_COMPLETION_DATE while excluding completed statuses via MEMBER_STATUS_TYPE_ID.
• Assignment-linked members: query by ASSIGNMENT_ID to find plan members assigned to a specific employee.
• Version-aware reporting: group by ACTIVITY_VERSION_ID to compare planned versus published activity versions.

A representative query:

SELECT m.training_plan_member_id, m.training_plan_id,
       m.activity_definition_id, m.target_completion_date,
       m.member_status_type_id
  FROM ota_training_plan_members m
 WHERE m.training_plan_id = :plan_id
   AND m.business_group_id = :bg_id
 ORDER BY m.target_completion_date;

Related Objects

• OTA_TRAINING_PLANS — parent plan; joined via TRAINING_PLAN_ID.
• OTA_ACTIVITY_DEFINITIONS — joined via ACTIVITY_DEFINITION_ID.
• OTA_ACTIVITY_VERSIONS — joined via ACTIVITY_VERSION_ID.
• PER_BUDGET_ELEMENTS — references OTA_TRAINING_PLAN_MEMBERS.TRAINING_PLAN_MEMBER_ID, tying plan members to budget elements.
• OTA_LEARNER_ENROLMENTS — downstream enrolment records derived from plan memberships.
• OTA_TRAINING_PLAN_APIs — public PL/SQL APIs for creating and maintaining plans and members.