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.
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
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.
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.
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.
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.
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.
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.
