Search Results reference_title

Overview

The view APPS.OTA_CTRNG_HISTORIES_V belongs to the OTA (Learning Management) product family in Oracle E-Business Suite and is classified as a VALID database view owned by the APPS schema. Its documented purpose is to serve as the data source for the combined training history displayed in the Training Histories form, which is invoked from the "HISTORY" button on the OTA_DELEGATE_BOOKINGS form. By presenting an already-resolved, presentation-ready result set, the view eliminates the need for the form or any downstream report to perform its own joins across the delegate booking, event, version, vendor, and organization tables.

The view's defining characteristic is that it merges two logically distinct sources of training history into a single uniform row shape. The first branch extracts formal event-based training from OTA_DELEGATE_BOOKINGS, OTA_EVENTS, and their associated descriptive tables. The second branch pulls non-event or externally recorded training from OTA_NOTRNG_HISTORIES_V, unioned with the first. A constant discriminator column, TRAINING_REFERENCE, distinguishes the origins: rows sourced from internal events carry the literal value 'OTA', while rows sourced from the unioned non-training history carry 'ADDITIONAL'. This makes the view a consolidated enrollment and completion ledger suitable for reporting, forms, and integration extracts.

Underlying Base Objects

The documented base objects are: HR_ALL_ORGANIZATION_UNITS_TL (synonym), HR_GENERAL (package), OTA_ACTIVITY_VERSIONS_TL (synonym), OTA_BOOKING_STATUS_TYPES_TL (synonym), OTA_DELEGATE_BOOKINGS (synonym), OTA_EVENTS (synonym), OTA_EVENTS_TL (synonym), OTA_NOTRNG_HISTORIES_V (view), and PO_VENDORS (view). The joins are driven from OTA_DELEGATE_BOOKINGS (alias TDB) to OTA_EVENTS (EVT) on EVENT_ID, then outward to the translated event title (OTA_EVENTS_TL), the activity version name (OTA_ACTIVITY_VERSIONS_TL), the booking status (OTA_BOOKING_STATUS_TYPES_TL), the supplier (PO_VENDORS), and the training centre (HR_ALL_ORGANIZATION_UNITS_TL). All description joins use outer joins on LANGUAGE = USERENV('LANG') so that rows remain intact when no translated or transactional description exists. Code meanings such as duration units, failure reason, and attendance flag are resolved at query time through calls to the HR_GENERAL.DECODE_LOOKUP package function rather than through joins to lookup tables.

Key Columns

• TRAINING_REFERENCE — Literal discriminator; 'OTA' for event-sourced rows, 'ADDITIONAL' for rows from the non-training history view.
• REFERENCE_DATE — The event course end date; for the unioned branch, the completion date.
• REFERENCE_TITLE / REFERENCE_STATUS — Event title and decoded booking status.
• DURATION and DURATION_UNITS — The event duration and its decoded unit of measure. DURATION_UNITS is populated via HR_GENERAL.DECODE_LOOKUP('OTA_DURATION_UNITS', EVT.DURATION_UNITS), converting the stored lookup code into a user-readable meaning (for example, hours or days).
• SUCCESSFUL_ATTENDANCE_MEANING, FAILURE_REASON_MEANING, SCHOLASTIC_RATING — Attendance outcome, decoded failure reason, and score.
• PROVIDER, EVENT_CENTER_MEANING, EVENT_ACTIVITY_VERSION_NAME — Vendor name, training centre name, and activity version.
• PERSON_ID / CONTACT_ID — The delegate person and contact identifiers, the primary keys for per-learner history queries.
• Standard WHO and multi-org columns: LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY, ORGANIZATION_ID, CUSTOMER_ID.

Common Use Cases and Queries

A typical query retrieves the full training history for a delegate, with duration expressed in decoded units:

SELECT training_reference, reference_date, reference_title, duration, duration_units, successful_attendance_meaning FROM ota_ctrng_histories_v WHERE person_id = :p_person_id ORDER BY reference_date DESC;

Because DURATION_UNITS is already decoded, the view supports duration aggregation reporting without additional lookup joins:

SELECT person_id, SUM(duration) FROM ota_ctrng_histories_v WHERE duration_units = 'Hours' GROUP BY person_id;

Analysts also use the view to reconcile internal versus externally recorded training by filtering on TRAINING_REFERENCE ('OTA' versus 'ADDITIONAL'), or to produce provider and training centre roll-ups using PROVIDER and EVENT_CENTER_MEANING. Note that the DURATION_UNITS filter value must match the decoded meaning returned by OTA_DURATION_UNITS, not the underlying lookup code.