Results for “otv_sessions”

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

Overview

OTV_SESSIONS is a read-only view owned by the APPS schema in Oracle E-Business Suite. It belongs to the OTA (Oracle Training Administration / Learning Management) product family and is classified as VALID in both Release 12.1.1 and 12.2.2. The view is documented in the E-Business Suite Technical Reference Manual (ETRM) with the single-line description "View to list Session event." Its purpose is to expose the session-level records held in the training events repository in a denormalised, human-readable form suitable for reporting, ad-hoc queries, and downstream integration.

In the OTA data model, a course offering is stored as an event of a given event type. Practical delivery of that offering occurs through one or more sessions, each of which is itself an event row in OTA_EVENTS carrying EVENT_TYPE = 'SESSION' and pointing back to its parent offering through PARENT_EVENT_ID. OTV_SESSIONS isolates exactly this subset of events and joins the owning business group so that the reporting consumer does not need to reconstruct the event hierarchy manually. Because the view is defined over the standard APPS synonyms rather than private objects, it is safe to reference from custom reports, concurrent programs, and BI Publisher data templates, subject to the standard APPS read privileges.

Underlying Base Objects

The documented ETRM metadata lists four referenced base objects. Two are synonyms to physical tables:

• OTA_EVENTS (synonym) — the primary training event table, supplying the session rows filtered by EVENT_TYPE = 'SESSION'. This is the driving table of the view.
• HR_ALL_ORGANIZATION_UNITS (synonym) — the HR organisation definition table, joined on BUSINESS_GROUP_ID = ORGANIZATION_ID to resolve the business group name.

Two are PL/SQL program units invoked inside the view definition:

• OTA_GENERAL (package) — supplies OTA_GENERAL.GET_BUSINESS_GROUP_ID, used in the NVL predicate to restrict rows to the caller's current business group when one is defined, thereby enforcing multi-org security.
• HR_GENERAL (package) — supplies HR_GENERAL.DECODE_LOOKUP, which translates the stored TRAINING_CENTRE lookup code into its display meaning for the CENTRE column.

The join to HR_ALL_ORGANIZATION_UNITS is an inner join, so a session whose business group cannot be resolved will not appear in the result set. Because the business group predicate uses NVL, a session with a null BUSINESS_GROUP_ID is still returned when no session-level business group is set.

Key Columns

The view projects the following columns, which are listed in the ETRM documentation:

• EVENT_TITLE — the session title, sourced from OTA_EVENTS.TITLE.
• SESSION_DATE — the session start date (OTA_EVENTS.COURSE_START_DATE).
• SESSION_START_TIME and SESSION_END_TIME — the scheduled start and end times for the session.
• CENTRE — the decoded training centre description, derived via HR_GENERAL.DECODE_LOOKUP('TRAINING_CENTRE', ...).
• PARENT_EVENT_ID — the EVENT_ID of the parent course offering, enabling linkage of a session back to its offering.
• EVENT_ID — the unique identifier of the session event itself.
• BUSINESS_GROUP_ID — the operating unit / business group that owns the session.
• BUSINESS_GROUP_NAME — the resolved name of that business group from HR_ALL_ORGANIZATION_UNITS.
• TRAINING_CENTER_ID and LOCATION_ID — surrogate keys to the training centre and physical location definitions, useful for joining to further reference data.

Common Use Cases and Queries

OTV_SESSIONS is typically used to build session schedules, attendance rosters, and capacity reports without navigating the parent/child event hierarchy directly. A simple listing of sessions for the current business group is:

• SELECT event_id, event_title, session_date, session_start_time, session_end_time, centre FROM otv_sessions ORDER BY session_date, session_start_time;

To report sessions belonging to a specific course offering, filter on the parent key:

• SELECT s.event_id, s.event_title, s.session_date FROM otv_sessions s WHERE s.parent_event_id = :offering_event_id;

For cross-business-group analysis, join the resolved business group name and aggregate session counts per centre:

• SELECT business_group_name, centre, COUNT(*) session_count FROM otv_sessions GROUP BY business_group_name, centre;

Because the view already applies the OTA_GENERAL business group restriction, results are automatically scoped to the caller's operating context, which makes it suitable for self-service and multi-org aware reporting. Analysts should note that the view returns sessions only; offerings and other event types must be sourced separately from OTA_EVENTS.