Search Results ota_students_v




Overview
Explore in AI Chat

Overview

OTA_STUDENTS_V is a seeded Oracle E-Business Suite database view owned by the APPS schema and classified under the OTA - Learning Management product. The documented description states that it is a "View to list all Internal Persons." In practice the view acts as a learner-facing projection of the HR person model, exposing a curated set of personal, identification, and contact attributes required by Oracle Learning Management (OLM) when resolving the individuals who enroll in, attend, and complete catalog offerings.

Because the view resolves to a synonym of the HR person table rather than a Learning Management base table, it serves as the standard interface point between OLM and Oracle HRMS. Registration, enrollment, transcript, and certification processes that must display or validate a learner reference APPS.OTA_STUDENTS_V instead of querying PER_ALL_PEOPLE_F directly, which insulates the Learning Management code from changes in the HR schema. The view carries a VALID status in both 12.1.1 and 12.2.2, and ETRM documents it as a supported, upgradeable object. It should be treated as a read-only reporting and integration surface; DML against it is not supported.

Underlying Base Objects

The ETRM metadata lists three referenced base objects:

  • PER_ALL_PEOPLE_F (referenced as a synonym, APPS.PER_ALL_PEOPLE_F) — the dated, effective-dated HR person table that supplies person identifiers, names, dates of birth, titles, employee and applicant numbers, and work telephone.
  • PER_BUSINESS_GROUPS (view) — joined on BUSINESS_GROUP_ID to obtain the legislation code used for name resolution.
  • HR_GENERAL (package) — used only through HR_GENERAL.DECODE_LOOKUP to translate the TITLE lookup code into its meaning.

The defining query joins PER_ALL_PEOPLE_F to PER_BUSINESS_GROUPS on BUSINESS_GROUP_ID. Name columns are resolved with a DECODE on BG.LEGISLATION_CODE: for Japanese business groups the view substitutes PER_INFORMATION18 and PER_INFORMATION19 for the last and first name respectively, preserving the Japanese naming convention; for all other legislations it returns LAST_NAME and FIRST_NAME. The ROWID of PER_ALL_PEOPLE_F is passed through as ROW_ID, and no filter is applied, so the view returns the full effective-dated person set.

Key Columns

  • ROW_ID — ROWID of the underlying PER_ALL_PEOPLE_F row.
  • PERSON_ID — primary person identifier, the key used throughout OLM enrollment and transcript tables.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-track boundaries from the HR record.
  • BUSINESS_GROUP_ID — the business group the person belongs to.
  • LAST_NAME / FIRST_NAME / FULL_NAME / MIDDLE_NAMES — person name attributes, with legislation-aware resolution for Japan.
  • DATE_OF_BIRTH, TITLE, TITLE_MEANING — demographic and lookup-decoded title information.
  • EMPLOYEE_NUMBER, APPLICANT_NUMBER — HR identifiers useful for reconciliation with external systems.
  • WORK_TELEPHONE — the person's work contact number.
  • CUSTOMER_ID, CUSTOMER_NAME — projected as NULL in the documented view text; these are placeholders that are not populated by this definition and should not be relied upon.

Common Use Cases and Queries

Typical uses include building learner rosters, resolving PERSON_ID to a display name on enrollment or transcript reports, and validating that a selected learner exists in HR before creating an OLM enrollment. Because the view is effective-dated, queries for current learners should restrict on SYSDATE.

List current internal learners for a business group:

  • SELECT person_id, full_name, employee_number, work_telephone FROM apps.ota_students_v WHERE business_group_id = :p_bg_id AND SYSDATE BETWEEN effective_start_date AND effective_end_date ORDER BY full_name;

Resolve a learner's name and title for a transcript:

  • SELECT person_id, full_name, title, title_meaning FROM apps.ota_students_v WHERE person_id = :p_person_id AND SYSDATE BETWEEN effective_start_date AND effective_end_date;

Search by name fragment across all business groups:

  • SELECT person_id, full_name, employee_number FROM apps.ota_students_v WHERE UPPER(full_name) LIKE UPPER('%' || :p_search || '%') AND SYSDATE BETWEEN effective_start_date AND effective_end_date;

All queries should be issued with the APPS schema or an appropriate synonym and grants, and should filter on the date track to avoid returning historical row versions.