Oracle Learning Management – OLM Tables

By | December 16, 2014

Summary of Article


Introduction
Big List of OTA Tables
ER Diagram
Sample Catalog Objects Hierarchy
Explanation with Sample Data Links
SQL Queries for Catalog Objects
SQL Queries for External Learning
Download PDF Version of this Article

Oracle Learning Management – OLM Tables

In Oracle HRMS Overview Series of Articles, This is the first to one to talk technical aspects of OLM (Oracle Learning Management). In this article, main focus is to explain OLM Tables, their description and high level ER Diagram.

Short name of OLM Application is OTA. Therefore all its tables and APIs start with prefix of OTA. Terms used in this document OTA and OLM refers to same thing. Catalog related tables of OTA are little confusing by its name. It is difficult to understand the linking of tables on the basis of their names.  Therefore i have given the ER diagram that shows the relations between main tables of catalog objects. Also i have provided a sample data sheet just to make the things more simpler and easy to understand. Top

Following big list contains all tables related to OTA Module. In next section, short description of tables is provided. Description is provided only for selective tables.

Table Name
OTA_ACTIVITY_DEFINITIONS
OTA_ACTIVITY_VERSIONS
OTA_ACT_CAT_INCLUSIONS
OTA_ANNOUNCEMENTS
OTA_ATTEMPTS
OTA_BKNG_JUSTIFICATIONS_B
OTA_BKNG_JUSTIFICATIONS_TL
OTA_BOOKING_DEALS
OTA_BOOKING_STATUS_EXCL
OTA_BOOKING_STATUS_HISTORIES
OTA_BOOKING_STATUS_TYPES
OTA_BOOKING_STATUS_TYPES_TL
OTA_BULK_ENR_REQUESTS
OTA_BULK_ENR_REQ_MEMBERS
OTA_CATEGORY_USAGES
OTA_CATEGORY_USAGES_TL
OTA_CERTIFICATIONS_B
OTA_CERTIFICATIONS_TL
OTA_CERTIFICATION_MEMBERS
OTA_CERT_CAT_INCLUSIONS
OTA_CERT_ENROLLMENTS
OTA_CERT_MBR_ENROLLMENTS
OTA_CERT_PRD_ENROLLMENTS
OTA_CHATS_B
OTA_CHATS_TL
OTA_CHAT_MESSAGES
OTA_CHAT_OBJ_INCLUSIONS
OTA_CHAT_USERS
OTA_CMI_ADAPTERS
OTA_COMPETENCE_LANGUAGES
OTA_CONFERENCES
OTA_CONTENT_SERVERS
OTA_COURSE_PREREQUISITES
OTA_CROSS_CHARGES
OTA_DELEGATE_BOOKINGS
OTA_EVALUATIONS
OTA_EVENTS
OTA_EVENTS_TL
OTA_EVENT_ASSOCIATIONS
OTA_FINANCE_HEADERS
OTA_FINANCE_LINES
OTA_FORUMS_B
OTA_FORUMS_TL
OTA_FORUM_MESSAGES
OTA_FORUM_THREADS
OTA_FRM_NOTIF_SUBSCRIBERS
OTA_FRM_OBJ_INCLUSIONS
OTA_HR_GL_FLEX_MAPS
OTA_ILN_XML_PROCESSES
OTA_IMPORT_HISTORIES
OTA_LEARNING_OBJECTS
OTA_LEARNING_PATHS
OTA_LEARNING_PATH_MEMBERS
OTA_LO_FOLDERS
OTA_LO_SCORM_OBJECTIVES
OTA_LP_CAT_INCLUSIONS

OTA_LP_MEMBER_ENROLLMENTS
OTA_LP_SECTIONS
OTA_LP_SECTIONS_TL
OTA_MANDATORY_ENR_REQUESTS
OTA_MANDATORY_ENR_REQ_MEMBERS
OTA_NOTRNG_HISTORIES
OTA_OFFERINGS
OTA_OFFERINGS_TL
OTA_OPEN_FC_ENROLLMENTS
OTA_PERFORMANCES
OTA_PREREQUISITES
OTA_PRICE_LISTS
OTA_PRICE_LIST_ENTRIES
OTA_PROGRAM_MEMBERSHIPS
OTA_PVT_FRM_THREAD_USERS
OTA_QBANK_QUESTIONS
OTA_QUESTIONS
OTA_QUESTION_BANKS
OTA_RESOURCE_ALLOCATIONS
OTA_RESOURCE_BOOKINGS
OTA_RESOURCE_DEFINITIONS
OTA_RESOURCE_USAGES
OTA_RESPONSE_TYPES
OTA_RESPONSE_VALUES
OTA_SCORM_OBJECTIVES
OTA_SCORM_OBJ_ATTEMPTS
OTA_SCORM_OBJ_PERFS
OTA_SECTION_RULES
OTA_SKILL_PROVISIONS
OTA_SUPPLIABLE_RESOURCES
OTA_SUPPLIABLE_RESOURCES_TL
OTA_TESTS
OTA_TEST_QUESTIONS
OTA_TEST_SECTIONS
OTA_TITLE_CONVERSIONS
OTA_TP_MEASUREMENT_TYPES
OTA_TRAINING_PLANS
OTA_TRAINING_PLAN_COSTS
OTA_TRAINING_PLAN_MEMBERS
OTA_UPGRADE_LOG
OTA_USER_GROUPS_B
OTA_USER_GROUPS_TL
OTA_USER_GROUP_ELEMENTS
OTA_UTEST_QUESTIONS
OTA_UTEST_RESPONSES
OTA_VENDOR_SUPPLIES
Top


OTA.OTA_ACTIVITY_VERSIONS

A course is the highest level of learning that can be prescribed to a learner. It also contains the objectives and competencies a learner will achieve by completing any class that belongs underneath.

This contains metadata of courses, such as objectives, intended audience , numbers of students that may attend any class based on the course, and actual and budgeted costs. A Course may be superseded by another version or it may supersede one itself. The validity dates on the course is available for scheduling as a class.

A course may belong to one or more categories. A course can therefore be classified for searching elsewhere in the system, it can be identified as being part of a program of courses, or it can be identified as part of a package of course that are to have price discounts applied. A course can also be part of a learning path.Top

OTA.OTA_OFFERINGS

An offering is a child of a course. An offering is used to create a particular ‘flavor’ of a course by assigning attributes such as the delivery mode (online/ offline,synchronous/ asynchronous), and the language in which the offering is delivered. For online offerings, content is associated with the offering. A course may have several offerings beneath it, to allow for circumstances where the same learning is delivered via several methods or via several languages.Top

OTA.OTA_CATEGORY_USAGES

Category usages indicate the role in which an activity category is used. Categories are entities that allow administrative users to organize their course catalog. Categories are containers for courses and learning paths, and have few attributes. The administrative user can define start and end dates for categories, select which courses appear in which categories in the course catalog, and define learner access based on the category level allowing access to be inherited by all the courses the category contains (though learner access does not need to be defined here, it can be defined at the levels of Course, Offering, and Class as well).

You can use a category to define:
1. Searching criteria
2. Program specifications
3. Packaged discounts
Each category can be used for all or none of the above. A category usage record indicates which of these usages is valid for a particular activity category.

OTA.OTA_EVENTS

An event can be scheduled, one time, developmental, program or session

There are several different types of event:
1. A scheduled event is a specific instance of an activity version on which you can enroll students and customers, and for which you can book resources.
2. A session is a unit of time within a scheduled event for which you can independently book resources.
3. A development event is a mechanism for scheduling employee time and other resources required to develop new training activities or to enhance existing ones.
4. A program is a group of events that are scheduled together. Students enroll on the program which may require that they enroll on some or all of the events within the program.

OTA_RESOURCE_BOOKINGS

You book a resource (people, equipment or facilities) for a scheduled or development event, or for
a session. You can book a resource for a specific event, or for a number of events. Each resource booking can have a start and end dates, as well a start and end times. You can enter start and end times to support resource bookings that are not required for the full duration of an event.A resource booking can be one of the following:
1. Planned
2. Confirmed
You can double- book supplied resources if the booking is Planned, but you cannot double-book for Confirmed bookings. You can add finance information for resource bookings to indicate that
the booking has incurred a cost.Top

OTA_SUPPLIABLE_RESOURCES

A resource is any facility, person or equipment that you need to book to run an event. You need to set up a key flexfield structure during setup for each resource type required. Subsequently, you can
enter specific supplied resources that use the resource key flexfield structure applicable to the type of resource. You can enter additional values that are specific to venues and trainers.

OTA_ACTIVITY_DEFINITIONS

An activity is any educational offering designed to improve a students qualifications, competencies or experience. An activity is any educational offering that is designed to improve a students qualification, competencies, or experience. Examples: a training course, on-job-training, structured work experience.

An activity definition is made up of a number of versions, each of which defines the nature of the educational offering. The activity definition determines whether the version may run concurrently or not.

OTA_NOTRNG_HISTORIES

Records attendance data for external training events initiated outside of OTA.

OTA_ACT_CAT_INCLUSIONS

A course category inclusion specifies that a course is within a particular type of category.

OTA_ANNOUNCEMENTS

Announcements can contain text or HTML to deliver timely information to learners. They are only displayed to learners during the start and end dates specified by the administrator. The text is displayed to all learners in the business group after logging into Oracle iLearning.

OTA_ATTEMPTS

This table is only used for online learning against learning objects and tests. It contains status, score, time, and other system information that describes what a learner achieved when playing a particular learning object or test.

There is one row created for each learning object/test a learner plays from within the Oracle iLearning player. The last attempt is sometimes used to calculate the most recent status for the performance record. It is also used to determine when a learner can take a test (after their last failed attempt) when an administrator has defined maximum number of attempts against a test. An internal_state of ‘A’ indicates not all information was written to the attempt.

OTA_BULK_ENR_REQUESTS

Bulk Enrollment request consist of any request which is made from the bulk enrollment functionality.

 OTA_BULK_ENR_REQ_MEMBERS

Bulk Enrollment request member hold the data of the leaner whom is requested to be enrolled into Class, Learning Path or Learning Certification via Bulk Enrollment.

OTA_CONFERENCES

A web conference belong to only one class. A class can only have one web conference. this is only used for integration with Oracle Collaboration Suite.Top

OTA_COURSE_PREREQUISITES

This hold the information of which courses need to be completed before learners can enroll into a specific course. A course can have one or more courses as prerequisite. The prerequisite can be mandatory or advisory.

OTA_CROSS_CHARGES

Details to enable internal Cross Charging by mapping Chart of Account details from Oracle General Ledger to Oracle Human Resources.

This table stores the header information (i.e., Set of Books, Business Groups, Effective period) of the mappings between Oracle General Ledger’s Chart of Account elements and Oracle Human Resources’ Cost Allocation elements. Oracle Training Self Serivce modules use the information stored in both OTA_CROSS_CHARGES and OTA_HR_GL_FLEX_MAPS to perform cross charging of internal enrollments.

OTA_HR_GL_FLEX_MAPS

Details to enable internal Cross Charging by mapping Chart of Account details from Oracle General Ledger to Oracle Human Resources.

This table stores the detail mappings between Oracle General Ledger’s Chart of Account keyflex segments and Oracle Human Resources’ Cost Allocation keyflex segments. Oracle Training Self Serivce modules use the information stored in both OTA_CROSS_CHARGES and OTA_HR_GL_FLEX_MAPS to perform cross charging of internal enrollments.

OTA_ILN_XML_PROCESSES

iLearning integration data exchange process. This table stores the summary of the data exchange between iLearning and OTA.

OTA_IMPORT_HISTORIES

Contain the logs of all import or export functions performed from within the Oracle iLearning interface

OTA_LEARNING_OBJECTS

A learning object holds metadata relating to a piece of online content or test, and are created in the Content tree by administrators. A learning object holds metadata relating to a piece of online content or test, and are created in the Content tree by administrators. Some metadata is displayed to learners to describe the content, such as description, audience, and keywords. Other metadata is used internally by Oracle iLearning, such as the content URL and unique identifiers. Learning objects can also contain other learning objects to establish a content hierarchy.

 OTA_LEARNING_PATHS

A learning path defines a sequence of courses that should be completed in order to obtain specific knowledge. This table only stores learning paths created by administrators. A learning path consists of one or more courses. A learning path can subscribe to a learner from within the learner interface.

The learner can then enroll in the associated classes for each course. A learning path can also have assigned competencies that a learner will obtain upon completion of the learning path.

OTA_LEARNING_PATH_MEMBERS

A learning path must have at least one course. This table stores each of the courses that have been assigned to the learning path by administrators. OTA.OTA_LEARNING_PATH_MEMBERS does not reference any database object.Top

OTA_LO_FOLDERS

A folder contains learning objects and other folders, and are created in the Content tree by administrators. A folder contains learning objects and other folders, and are created in the Content tree by administrators. The primary use of folders is to organize related content. For example, the Math Faculty folder contains Algebra and Calculus folders. Learning Objects related to each area are then created under their respective folders.

 OTA_LO_SCORM_OBJECTIVES

This table is an intersection table which hold relationship between OTA_SCORM_OBJECTIVES and OTA_LEARNING_OBJECTS.

OTA_LP_CAT_INCLUSIONS

A Learning Path category inclusion specifies that a learning path is within a particular type of category.

OTA_LP_ENROLLMENTS

This record all learner subscriptions into a learning path. There is one row for each learner who subscribe to a learning path.

OTA_OPEN_FC_ENROLLMENTS

This stores the enrollment for all category based chat and forum

 OTA_TESTS

This table contains additional meta data about the test. The behavior of test test is determined using some of the properties set in this table. There exists a 1:1 relation between the OTA_TESTS and OTA_LEARNING_OBJECTS table.

OTA_TEST_QUESTIONS

This table stores acts as a bridge table between the OTA_TEST_SECTIONS and OTA_QUESTIONS table for test which have a type_flag set to S( Pre Selected)test. This stores which questions will be displayed under which test sections.

 OTA_TEST_SECTIONS

A test can have one or more test sections. For a test to be offered it must have atleast one test section associated with it which contains atleast one question. There is a 1:N relation between the OTA_TESTS and OTA_TEST_SECTIONS

 OTA_USER_GROUPS_B

This table stores the Learner Group Information. The Learner Group is primarily built by adding person list, assignment or learner group

OTA_USER_GROUP_ELEMENTS

This table stores the Learner Group Elements Information. An element can be a Person ID OR Organization ID, Job ID, Position ID OR Learner Group ID

OTA_UTEST_QUESTIONS

Whenever a user takes a test a new test instance is generated for the user. The test definition (test questions that make up the test) are stored in this table. If the test is resumable and the user has resumed an old test, the attempt_id for the user is updated and the existing test definition is used.

OTA_UTEST_RESPONSES

When the test is assembled for the user, each response to all questions are created in this table. The table is updated as per the response by the user.Top

OLM Tables ER Diagram (w.r.t Catalog related tables)

Diagram below shows only the relation of tables related to catalog management. Purpose of this diagram is to give an idea to starters so that they should know what are the initial tables they should be looking at.

OTA Tables

OTA Tables Pictorial hierarchy

Top

Tables Further Explanation Through Data and Data Links

To make initial level understanding, let’s assume we create a catalog object model as shown in diagram below.

OTA Tables

OLM Catalog Objects Hierarchy

Once we shall create the category, sub category and few courses as shown in diagram above, data will be generated in relevant tables and will look like as is shown in diagram below.

For better understanding, tables are linked with red arrows.

 OLM Tables With Data

OLM Catalog Tables With DataTop

 Basic Queries to get Catalog Objects

While working in OLM, At many places, you need to get the catalog objects. As shown above that catalog objects relations are not as such straight forward and involves linking of different tables so i suggest to create basic views from the start itself. It will simplify the things and make the development work very easy.

As per catalog objects hierarchy, following are sample queries to create views for category, sub category and course. Creating View is totally optional, alternatively you can use the queries also.Top

Query to get ‘Category’ Data

create or replace view  LSG_CATL_OBJ_PARNT_CATGRY_V
as select distinct ocu.PARENT_CAT_USAGE_ID,ocup.category parent_ctagory–,ocu.CATEGORY_USAGE_ID,ocu.category,ocu.type,ocu.DATA_SOURCE
from ota_category_usages ocu, ota_category_usages ocup
where ocup.CATEGORY_USAGE_ID = ocu.PARENT_CAT_USAGE_ID
order by 2

Top

Query to get ‘Sub Category’ Data

 

create or replace view  LSG_CATL_OBJ_SUB_CATGRY_V
as select ocu.PARENT_CAT_USAGE_ID,ocup.category parent_category,ocu.CATEGORY_USAGE_ID,ocu.category,ocu.type,ocu.DATA_SOURCE
from ota_category_usages ocu, ota_category_usages ocup
where ocup.CATEGORY_USAGE_ID = ocu.PARENT_CAT_USAGE_ID
order by 2

Top

Query to get ‘Course’ Data

 

Catalog Objects: Category->Sub Category–>Courses

create or replace view  LSG_CATL_OBJ_COURSES_V
as select ocu.PARENT_CAT_USAGE_ID,ocup.category parent_ctagory,ocu.CATEGORY_USAGE_ID,ocu.category,ocu.type,ocu.DATA_SOURCE, oav.version_code,oav.version_name,oav.activity_version_id
from ota_category_usages ocu, ota_category_usages ocup,ota_activity_definitions oad,ota_activity_versions oav
where 1=1
and ocup.CATEGORY_USAGE_ID = ocu.PARENT_CAT_USAGE_ID
and ocu.CATEGORY_USAGE_ID = oad.CATEGORY_USAGE_ID
and oav.activity_id = oad.activity_id

Top

Query to get ‘External Learning’ Data – Based on Views

If you are following the approach to make the view for catalog objects, following is the query to get the data for external learning. Catalog Objects Views are used in where clause of this query.

select NOTA_HISTORY_ID
,onh.PERSON_ID,ppf.employee_number,ppf.full_name,onh.NTH_INFORMATION1 Course_Code,onh.TRNG_TITLE,NULL equivalent_Course_code, NULL equivalent_Course_name,onh.PROVIDER,
hr_general.DECODE_LOOKUP(‘OTA_TRAINING_TYPES’,onh.TYPE) TYPE,onh.CENTRE,onh.COMPLETION_DATE,onh.AWARD,onh.RATING
,onh.DURATION,hr_general.DECODE_LOOKUP(‘OTA_DURATION_UNITS’,onh.DURATION_UNITS) DURATION_UNITS
,hr_general.DECODE_LOOKUP(‘OTA_TRAINING_STATUSES’,onh.STATUS) STATUS
,onh.NTH_INFORMATION2 Instructor ,onh.NTH_INFORMATION3
,onh.NTH_INFORMATION8,onh.NTH_INFORMATION9
,onh.CUSTOMER_ID,onh.ORGANIZATION_ID
from ota_notrng_histories onh, per_all_people_f ppf–,XXALB_CATL_OBJ_COURSES_V oav
where onh.PERSON_ID = ppf.person_id
and onh.ACTIVITY_VERSION_ID is null
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and onh.NTH_INFORMATION8 = nvl((select distinct parent_category from LSG_CATL_OBJ_SUB_CATGRY_V where PARENT_CAT_USAGE_ID = :P_PARENT_CAT_USAGE_ID),onh.NTH_INFORMATION8) 
and onh.NTH_INFORMATION9 = nvl((select distinct category from LSG_CATL_OBJ_SUB_CATGRY_V where CATEGORY_USAGE_ID = :P_CATEGORY_USAGE_ID),onh.NTH_INFORMATION9)
and ppf.employee_number = nvl(:P_employee_number, ppf.employee_number)

Top

Query to get ‘External Learning’ Data – Based on OTA Tables

select NOTA_HISTORY_ID,onh.PERSON_ID,ppf.employee_number,ppf.full_name,onh.NTH_INFORMATION1 Course_Code,onh.TRNG_TITLE
,oav.version_code equivalent_Course_code, oav.version_name equivalent_Course_name,onh.PROVIDER,hr_general.DECODE_LOOKUP(‘OTA_TRAINING_TYPES’,onh.TYPE) TYPE
,onh.CENTRE,onh.COMPLETION_DATE,onh.AWARD,onh.RATING,onh.DURATION,hr_general.DECODE_LOOKUP(‘OTA_DURATION_UNITS’,onh.DURATION_UNITS) DURATION_UNITS
,hr_general.DECODE_LOOKUP(‘OTA_TRAINING_STATUSES’,onh.STATUS) STATUS,onh.NTH_INFORMATION2 Instructor ,onh.NTH_INFORMATION3,onh.NTH_INFORMATION4
from ota_notrng_histories onh, per_all_people_f ppf,XXALB_CATL_OBJ_COURSES_V oav
where onh.PERSON_ID = ppf.person_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and oav.ACTIVITY_VERSION_ID = onh.ACTIVITY_VERSION_ID
and oav.PARENT_CAT_USAGE_ID = nvl(:P_PARENT_CAT_USAGE_ID,oav.PARENT_CAT_USAGE_ID)
and oav.CATEGORY_USAGE_ID =  NVL(:P_CATEGORY_USAGE_ID,oav.CATEGORY_USAGE_ID)
and ppf.employee_number = nvl(:P_employee_number, ppf.employee_number)
 
 

Top

Query to get ‘Course , Offering, Class and its details


select oav.version_code course_code,oav.version_name course_name,oo.duration offering_duration, oo.duration_units offering_duration_units,oo.actual_cost offering_Cost
,oo.BUDGET_CURRENCY_CODE offering_currency,clas.*
from ota_offerings oo,ota_activity_versions oav,ota_events clas
where 1=1
and oav.activity_version_id = 128079
and oo.activity_version_id = oav.activity_version_id
and clas.activity_version_id = clas.ACTIVITY_VERSION_ID
and clas.parent_offering_id= oo.OFFERING_ID

Top


Download PDF Version of This Article

Related:
OLM APIs
Interview Questions
9 Steps to become Apps Consultant
Get Profile Values in Oracle Forms Personalization
2 simple Steps to make a responsibility read only
Functions Used in Oracle HRMS Fast Formula
Element Termination Rules in Oracle HRMS