Stores information on the course iterations for which trainees have registered.
There is one record
for each request to
in a particular course iteration. REG_REC_ID stores the number that uniquely
identifies the record.
An LMS_REGISTRATION record stores the trainee’s name,
and USER_REC_ID; data pertaining to the course iteration for which the trainee
information on the registration status and course completion status for the registration
transaction; and various flags, indicating such things as whether the registration
transaction is the latest one for the trainee and course iteration.
A trainee registers for a course iteration (ITERATION_ID), which
offering of a particular version (CO_REC_ID) of a course (CO_MAS_ID). A trainee
have multiple LMS_REGISTRATION records for the same course iteration. For example,
say that Jane Smith (USER_REC_ID 12345) registers
for an iteration
Assessment - EHRS to be held on the first of next month at 8:30 a.m.
in the Left
EHRS Conference Room. (ITERATION_ID 98765). A record
is created for this registration request with REG_REC_ID 3000003.
Wait-Listed. When a seat in the course iteration becomes available, the
record's registration status changes to Enrolled. Then Jane is asked
to attend a meeting that will be held at the same time as the course iteration.
drops the course
(changing the record's registration status to Dropped). However,
the meeting is cancelled, so Jane registers again for ITERATION_ID 98765. Another record
is created for this new registration request for USER_REC_ID 12345
and ITERATION_ID 98765, with REG_REC_ID 3000045.
The LMS_REGISTRATION table is an enhanced version of the REGISTRATION
and was developed to support both ad hoc reporting and some of the Web-based,
parameter-driven reports available in
- Generating class rosters for specified course iterations.
- Counting trainees by their registration status or completion status for
specified course iterations.
- Selecting registration records to be included in
a report based on whether they reflect the trainee’s latest
registration with regard to the course iteration or the course,
or whether they store information on the trainee’s latest completion
of the course.
- Reporting on a trainee's registration history for a course iteration, course
version, or course.
||Indexed Data Elements
- The LMS_REGISTRATION
table stores a record for every Knowledge Link registration request ever
logged. However, it stores only current information for a given registration
For example, if the REGISTRATION_STATUS_REASON for a particular REG_REC_ID
was once 'data correction', but is now 'Dropped for new version', there is
no way to know that it was ever 'data correction'.
- The LMS_REGISTRATION
table stores registration requests both for currently required courses and optional
courses. A course is currently required for a trainee if: (1) it is actively
assigned as a mandatory course to a business unit that currently includes
the trainee, and (2) the trainee is not grandfathered for the course. A
record may have CURRENTLY_REQUIRED set to 'Y', but that does not mean that
has always been required for the trainee.
- The LMS_REGISTRATION table stores one record per registration request (REG_REC_ID),
which specifies a trainee (USER_REC_ID) and a course iteration (ITERATION_ID).
- Whether trainees register themselves for course iterations, or a Knowledge
Link Administrator sets up the registrations for them, there may be
multiple records for a given trainee and course. For example,
- a trainee may have registered for more than one iteration of the
- the trainee may have asked to enroll in the same course
iteration more than once. This is commonly the case for Web-based
courses that trainees are required to re-take periodically.
- The LMS_REGISTRATION table includes Yes/No flags to facilitate selecting
the record you want from a set of records for a given trainee and course
iteration, or from a set of records for a given trainee and course.
- CURRENT_CRS_ITERATION_REG_FLAG is 'Y' if the record is the
one that reflects the trainee’s latest registration status with
regard to the course iteration (ITERATION_ID).
- CURRENT_COURSE_REG_FLAG is 'Y' if the record is the
one that reflects the trainee’s latest Enrolled or Wait-Listed
registration for the course (CO_MAS_ID). Note: if the trainee
does not have an Enrolled or Wait-Listed registration for the course,
is no record for the trainee and course that has CURRENT_COURSE_REG_FLAG
- LAST_COURSE_COMPLETION_FLAG is 'Y' if the record is the
one with information on the trainee’s latest completion of the course. Note:
if the trainee has never completed an iteration of the
course, there is no record for the trainee and course that has LAST_COURSE_COMPLETION_FLAG
- LAST_CRS_COMPL_OR_REG_FLAG is 'Y' if the record is the
one that is reflected in the LMS_CURRENT_TRAINEE_CRS_STATUS
record for the trainee and course. Note: if the trainee does not have
an Enrolled or Wait-Listed registration for the course, and has never completed
an iteration of the
course, there is no record for the trainee and course that has LAST_CRS_COMPL_OR_REG_FLAG
- There are several data elements that provide information
about the trainee and course for which the trainee has registered:
- The USER_REC_ID and the PENN_ID each uniquely identify the trainee.
- The USER_REC_ID is the number used within the Knowledge Link
system as the unique identifier for the trainee. Some tables in
the Data Warehouse's
Learning Management data collection store data about Knowledge
Link users but do not store the users'
Penn IDs. USER_REC_ID enables the LMS_REGISTRATION table to be
- The PENN_ID is an 8-digit identification number
assigned to the trainee by the Penn Community system. No two persons
same Penn ID. Most tables in the Data Warehouse use PENN_ID
as the unique identifier for a person. This facilitates joining tables
with data that pertains
to people, regardless of whether those tables are in the same data
- The LMS_REGISTRATION table stores the trainee's FIRST_NAME, MIDDLE_INITIAL,
and LAST_NAME. Further information about the trainee is available
in the LMS_PERSON table. The LMS_REGISTRATION table may be joined to
the LMS_PERSON table by USER_REC_ID or by PENN_ID.
- The REGISTRATION_BU_NUMBER, REGISTRATION_BU_ID, and REGISTRATION_BU_NAME
provide information on the business unit that, at the time
of the registration request, included the trainee (USER_REC_ID) and
had the course version (CO_REC_ID) assigned to it. A trainee cannot
register for an iteration of a course version if that course version
have an active assignment to a business unit
that includes the trainee. A business unit (also known as a BU, role,
Knowledge Node, or KNode) is a set of one or more Knowledge Link users.
unit X includes
a trainee if that trainee is actively assigned to X (see the KNODE_USER_JOIN
table) or to a business unit that that falls below X in the business
unit hierarchy. (For more information on the business unit hierarchy,
see the documentation on PARENT_ID and TYPE in the KNODE table.)
- The ITERATION_ID is the number used
within the Knowledge Link system as the unique identifier for the course
which the trainee has registered. The COURSE_ITERATION_TITLE is name
of the course iteration. It is common for
more than one course iteration to have the same value for COURSE_ITERATION_TITLE. To
retrieve the other attributes of the course iteration, the
LMS_REGISTRATION table may be joined to the COURSE_ITERATION table
- The CO_REC_ID is the number used
within the Knowledge Link system as the unique identifier for the
course version presented in the course iteration. To retrieve the attributes
of the course version, the LMS_REGISTRATION table
may be joined to the COURSE_OBJECT table by CO_REC_ID.
- The COURSE_OWNER_BU_NUMBER, COURSE_OWNER_BU_ID, and COURSE_OWNER_BU_NAME
provide information on the business unit that owns the course
- The owner of the course version is one of the criteria used
to determine what course versions (if any) are included on
the list of course versions that a given user can assign. For
more information, see the data element documentation for COURSE_OBJECT.KNODE_ID.
- Knowledge Communities (KC) and Knowledge Microcommunities
(KMCs) are the types of business units that are course version
owners. The KCs that own course versions correspond to University
orgs. or to University of Pennsylvania Health System (UPHS)
entities (process levels) or departments (cost centers or accounting
units). A KMC identifies a group of trainees from unrelated
UPHS departments. (For further information on business units,
see the documentation for the KNODE table. The business unit
hierarchy is described in the documentation for PARENT_ID in
the KNODE table.)
- Note: CO_REC_ID_AS_IS stores
the REGISTRATION.CO_REC_ID for this record (REG_REC_ID). CO_REC_ID_AS_IS
might not accurately reflect the course version offered in
the course iteration (ITERATION_ID).
- The COURSE_MASTER_ID and the CO_MAS_ID each
uniquely identify the course that includes the course iteration
(ITERATION_ID) for which
(USER_REC_ID) has registered. Most
Knowledge Link users are familiar with the COURSE_MASTER_ID (such
as UNIV_ALL_ALL_PROFILER). CO_MAS_ID
is the number (such as 12437) that identifies the course within
the Knowledge Link system, and is used to join tables with course
retrieve the other attributes of the course, the LMS_REGISTRATION
may be joined to the LMS_COURSE_MASTER table by COURSE_MASTER_ID
- Note: CO_MAS_ID_AS_IS stores
the REGISTRATION.CO_MAS_ID for this record
(REG_REC_ID). CO_MAS_ID_AS_IS might not accurately reflect
the course to which the course iteration (ITERATION_ID) belongs.
- The status of the trainee's registration request for the course iteration
is stored in REGISTRATION_STATUS_DESC and REGISTRATION_STATUS_CODE. REGISTRATION_STATUS_DESC
(such as 'Enrolled') is useful for displaying in reports. If you know the
REGISTRATION_STATUS_CODE values (for example, 39 means 'Enrolled'), you might
prefer to use it in your record selection condition statements.
- REGISTRATION_STATUS_REASON stores comments
about the registration request.
- The value is usually null, but when comments
are stored, they may describe
any characteristic of the registration request (such as how the record
for the request
was created); they do not necessarily have to do with the registration
- Note: the value for REGISTRATION_STATUS_REASON
might not describe the registration request in its current state.
For example, some records have a value for REGISTRATION_STATUS_REASON
that begins with 'Completed' but have COURSE_COMPLETED = 'N'.
- When the status of the trainee's registration request for
the course iteration is Wait-Listed, the number stored in WAIT_LIST_PRIORITY
priority the trainee has for becoming
enrolled in the course iteration if another trainee drops the
course iteration. If WAIT_LIST_PRIORITY = 0,
the trainee will become enrolled the next time someone drops the course
- Wait lists are kept only for instructor-led
courses. (The code identifying the distribution mode is the COURSE_OBJECT.DIST_MODE
where LMS_REGISTRATION.CO_REC_ID = COURSE_OBJECT.CO_REC_ID.)
- In some records, WAIT_LIST_PRIORITY is null even
though the registration status is Wait-Listed.
- In some records, WAIT_LIST_PRIORITY is not
null even though the registration status is not Wait-Listed.
- The LMS_REGISTRATION table stores COURSE_COMPLETED,
which indicates whether the trainee has ('Y') or has
not ('N') completed the course iteration (ITERATION_ID) for which the trainee
has registered per
this registration request (REG_REC_ID).
- When a trainee has enrolled in a curriculum,
and has completed all of the courses in the curriculum, Knowledge Link
automatically updates the record for the trainee and curriculum, setting
the COURSE_COMPLETED to 'Y'.
- A trainee may legitimately be on record as having
completed the course iteration even if the registration status is not
- If COURSE_COMPLETED is set to 'Y', DATE_COURSE_COMPLETED
indicates when the trainee completed the course iteration for
which the trainee has registered per
this registration request.
- Note: some records have a non-null value for DATE_COMPLETED,
even though COURSE_COMPLETED = 'N'. If COURSE_COMPLETED = 'N',
DATE_COMPLETED should be ignored.
- TIME_COURSE_COMPLETED is a string indicating
how much time the trainee has spent on taking the course
per this registration request.
- For instructor-led courses, TIME_COURSE_COMPLETED
indicates how much time it took the trainee to complete the course
iteration. For Web-based courses, it indicates how much time the trainee
has spent on taking the course iteration so far, regardless of whether
or not the trainee has completed the course iteration. (The code identifying
the distribution mode is the COURSE_OBJECT.DIST_MODE where LMS_REGISTRATION.CO_REC_ID
- TIME_COURSE_COMPLETED is null for many records.
When it is not null, the value may represent the amount of time in
hours (when the value includes a decimal point) or the time in hours,
minutes, and seconds (when the value is in the format HH:MM:SS).
- TIME_COURSE_COMPLETED values may be formatted
inconsistently. For example, some records for a particular
'0.25' for TIME_COURSE_COMPLETED, while other records for that
same course iteration have '.25'.
- In some cases, TIME_COURSE_COMPLETED
is null, even though COURSE_COMPLETED is 'Y'.
- If the course offered in the course iteration is currently required
for the trainee, and the trainee has completed the course iteration,
and is not overdue to re-take the course, the trainee is compliant with
the course. For compliance information regarding the trainee and
course, see the LMS_CURRENT_TRAINEE_CRS_STATUS table.
- Note: NEXT_COURSE_DATE currently is not used, even when its
value is not null. See LMS_TRAINEE_COURSE_ASSIGN.DUE_DATE or LMS_CURRENT_TRAINEE_CRS_STATUS.DUE_BY_DATE.
- The LMS_REGISTRATION table stores DATE_REGISTRATION_CREATED,
DATE_REGISTRATION_MODIFIED and DATE_REGISTRATION_DROPPED for each registration
request (REG_REC_ID). Use these dates
with caution, if at all.
- The values for these dates
might or might not include the time. Business Objects users that
wish to select records based on one of these dates--say, records
that were created on 7/21/2008--should include a query filter stipulating
that DATE_REGISTRATION_CREATED is greater than or equal to 7/21/2008
and DATE_REGISTRATION_CREATED is less than 7/22/2008. (The system
will supply the time--12:00:00 AM--for both dates.)
- DATE_REGISTRATION_DROPPED may have a non-null
value even if the registration status is not Dropped. This might be because
the status changed after it became Dropped. When a trainee (USER_REC_ID)
decides to cancel his or her registration for
iteration (ITERTATION_ID), he or she drops the course in Knowledge Link.
The record for the current registration request for
the trainee and course iteration (CURRENT_CRS_ITERATION_REG_FLAG = ‘Y’)
has its registration status changed to Dropped. However, an instructor
may later override the registration status (often to change it to Enrolled).
- The values for these dates might not be reliable.
- have a value for DATE_REGISTRATION_CREATED
that is later than the value for DATE_REGISTRATION_MODIFIED.
- have a value for DATE_REGISTRATION_CREATED
that is later than the value for DATE_REGISTRATION_DROPPED.
a value for DATE_REGISTRATION_DROPPED that is later than the value
- have a null value for DATE_REGISTRATION_DROPPED even
though their registration status is Dropped.
- The list of related tables shown above
includes some that may be joined to the LMS_REGISTRATION table only with
caution, if at all. For example,
- PKG_CRS_JOIN may be joined to LMS_REGISTRATION based on CO_REC_ID or
CO_MAS_ID, but the fact that an LMS_REGISTRATION record has a corresponding
PKG_CRS_JOIN record does not mean that the course was assigned to
the trainee as part of a package.
- PROFILER_ASSIGNMENTS may be joined to LMS_REGISTRATION where the PENN_IDs
match and the COURSE_ID matches the COURSE_MASTER_ID, but the fact that
Profiler assigned the course to the trainee does not necessarily mean
that the trainee registered for the course because of that assignment.
(You might want to compare DATE_FED_TO_LMS to DATE_REGISTRATION_CREATED.)
Also, if the only conditions are on the trainee and the course, PROFILER_ASSIGNMENTS
and LMS_REGISTRATION have a many-to-many relationship; a query on the
joined tables would get a Cartesian Product warning.
Knowledge Link, the learning management system used by the University and
by the University of Pennsylvania Health System (UPHS) since March, 2005.
Knowledge Link enables authorized administrators to schedule training courses,
assign resources to courses, assign groups of Knowledge Link users to courses,
and to create online courses. It enables trainees to find out what courses
have been assigned to them, to register for those courses (whether those courses
are on-line or instructor-led), to take on-line courses, and to provide feedback
via course evaluations. Knowledge Link was first used to track information
on training required by regulation, but it now tracks information on a variety
of training courses.
Questions about this page? Email us at email@example.com