LMS_CURRENT_TRAINEE_CRS_STATUS Table - Data Element Index    Tables and Data Elements   Learning Management Home   Data Warehouse Home

LMS_CURRENT_TRAINEE_CRS_STATUS Table

DWLMS Schema

Explanation
Tracks trainees' current compliance status with regard to courses. There is one record for each trainee, for each course that is currently required for the trainee (regardless of whether the trainee has ever registered for the course). There is also one record for each trainee, for each course that is currently optional for trainee, if the trainee has completed the course or has an Enrolled or Wait-Listed registration for the course.

An LMS_CURRENT_TRAINEE_CRS_STATUS record stores:

• some attributes of the course, such as the COURSE_MASTER_ID and the title of the latest version of the course
• the trainee’s name, Penn ID, and USER_REC_ID
• information on whether the course is currently required for the trainee, the trainee's current compliance status with regard to each currently required course, and some of the factors affecting that compliance status
• data from the registration record (if any) that reflects the trainee's current compliance status with regard to the course, including the registration status and the attributes of the course version and iteration

The LMS_CURRENT_TRAINEE_CRS_STATUS table combines information from the LMS_TRAINEE_COURSE_ASSIGN and LMS_REGISTRATION tables, and was developed to support both ad hoc reporting and some of the Web-based, parameter-driven reports available in BEN Reports.

Common Uses

• Reporting on the current training status for all trainees in a business unit, including all of their currently required courses, as well as the optional courses for which they have registered
• Monitoring the trainees' compliance status for all currently required courses, by trainee, by the trainees' business unit, or by the course
  

CO_MAS_ID
USER_REC_ID

COMPLIANT
COURSE_COMPLETED
COURSE_MASTER_ID
COURSE_OR_ITERATION_TITLE
COURSE_OWNER_BU_ID
COURSE_OWNER_BU_NAME
COURSE_OWNER_BU_NUMBER
COURSE_TITLE_AND_ID
CO_MAS_ID
CO_MAS_ID_AS_IS
CO_REC_ID
CO_REC_ID_AS_IS
CURRENTLY_REQUIRED
CURRENT_COURSE_REG_FLAG
CURRENT_CRS_ITERATION_REG_FLAG
CURRENT_PERIODICITY
DATE_COURSE_COMPLETED
DATE_REGISTRATION_CREATED
DUE_BY_DATE
FIRST_NAME
ITERATION_ID
LAST_COURSE_COMPLETION_FLAG
LAST_CRS_COMPL_OR_REG_FLAG
LAST_NAME
NEXT_COURSE_DATE
OVERDUE
PENN_ID
REGISTRATION_BU_ID
REGISTRATION_BU_NAME
REGISTRATION_BU_NUMBER
REGISTRATION_STATUS_CODE
REGISTRATION_STATUS_DESC
REG_REC_ID
STATUS_DUEDATE_DESCRIPTION
USER_REC_ID

COURSE_ITERATION
COURSE_MASTER
COURSE_OBJECT
CUSTOM_DATA
GEN21_USER
GRADEBOOK
KNODE
KNODE_CRS_JOIN
KNODE_USER_JOIN
LMS_BUSINESS_UNIT_GROUP
LMS_COURSE_ITER_INSTRUCTOR
LMS_COURSE_MASTER
LMS_CURRENT_CRS_ITER_REG_INSTR
LMS_DEFINITION
LMS_PERSON
LMS_REGISTRATION
LMS_REGISTRATION_STATUS
LMS_STAFF_ASSIGN
LMS_STAFF_QUAL
LMS_TRAINEE_BU_COURSE_ASSIGN
LMS_TRAINEE_COURSE_ASSIGN
LMS_TRAINEE_COURSE_COMPLIANCE
PKG
PKG_CRS_JOIN
PROFILER_ASSIGNMENTS
PROFILER_USER_ANSWER
PROFILER_USER_QUESTIONNAIRE
REGISTRATION
TEST
TRACK_TEST
UPHS_CI_PATHLORE
UPHS_COST_CENTER
UPHS_RAW_DATA
UPHS_USER_ENTITY
USER_COURSES

Cautions

• As mentioned above, the LMS_CURRENT_TRAINEE_CRS_STATUS table stores information on trainees' current compliance status with regard to courses. If a trainee was once required to take a particular course, but is not currently required to take it, there is no way to tell that the course was ever required for the trainee.
• The LMS_CURRENT_TRAINEE_CRS_STATUS table stores some, but not all, of the attributes of the course, which is either a course that is currently required for the trainee, or an optional course for which the trainee has registered.
  • The COURSE_MASTER_ID and the CO_MAS_ID each uniquely identify the course. 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 information.
    • Note: If the trainee has completed the course, or has a currently Enrolled or Wait-Listed registration for an iteration of the course, CO_MAS_ID_AS_IS stores the REGISTRATION.CO_MAS_ID per the registration record (REG_REC_ID) that reflects the trainee's current compliance status with regard to the course. A trainee registers for a course iteration (ITERATION_ID), not for a course. CO_MAS_ID_AS_IS might not accurately reflect the course to which the course iteration belongs.
• The COURSE_OR_ITERATION_TITLE and COURSE_TITLE_AND_ID each provide a name for the course.
  • If REG_REC_ID is null, COURSE_OR_ITERATION_TITLE is the title of the version of the course that has the highest version number; otherwise, it is the title of the course iteration per the registration record (REG_REC_ID) that reflects the trainee's current compliance status with regard to the course. An example is Atlas Upgrade February 2011. Note: it is common for more than one course to have the same value for COURSE_OR_ITERATION_TITLE
  • COURSE_TITLE_AND_ID stores the latest title and Course Master ID for the course--for instance, Atlas Upgrade February 2011 (UNIV_DOF_DAR_ATS02726).
• To retrieve the other attributes of the course, the LMS_CURRENT_TRAINEE_CRS_STATUS table may be joined to the LMS_COURSE_MASTER table by COURSE_MASTER_ID or by CO_MAS_ID.
• Some basic information about the trainee is provided.
  • 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_CURRENT_TRAINEE_CRS_STATUS table to be joined to those tables.
    • The PENN_ID is an 8-digit identification number assigned to the trainee by the Penn Community system. No two persons have the 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 collection.
  • The LMS_CURRENT_TRAINEE_CRS_STATUS 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_CURRENT_TRAINEE_CRS_STATUS table may be joined to the LMS_PERSON table by USER_REC_ID or by PENN_ID.
• If the trainee has completed the course, or has a currently Enrolled or Wait-Listed registration for an iteration of the course, the LMS_CURRENT_TRAINEE_CRS_STATUS table stores information from the registration record that reflects the trainee's current compliance status with regard to the course.
  • That registration record is identified by the REG_REC_ID. If the trainee has completed the course and is not overdue to complete it again, this is the REG_REC_ID for the trainee's last (latest) completion of the course. If the trainee is overdue to complete the course, or has never completed it, this is the REG_REC_ID for the trainee's latest Enrolled or Wait-Listed registration for an iteration of the course.
    • Note: if the course is currently optional for the trainee, and the trainee has completed it, and has registered planning to complete it again, the registration information in the LMS_CURRENT_TRAINEE_CRS_STATUS record reflects the trainee's latest completion of the course, not the latest Enrolled or Wait-Listed registration for the course.
  • The LMS_CURRENT_TRAINEE_CRS_STATUS table includes Yes/No flags from the registration record identified by the REG_REC_ID, indicating how it stands among the set of registration records for a given trainee and course iteration. If REG_REC_ID is null, these flags are all set to 'N'.
    • LAST_COURSE_COMPLETION_FLAG indicates whether or not the registration data in this LMS_CURRENT_TRAINEE_CRS_STATUS record reflects the trainee's latest completion information of the course (CO_MAS_ID).
    • CURRENT_COURSE_REG_FLAG indicates whether or not the registration data in this LMS_CURRENT_TRAINEE_CRS_STATUS record reflects the trainee's latest Enrolled or Wait-Listed registration for the course (CO_MAS_ID).
    • The data from the registration record identified by the REG_REC_ID is stored in the LMS_CURRENT_TRAINEE_CRS_STATUS table because that record's LAST_CRS_COMPL_OR_REG_FLAG is 'Y'. If the course is currently required for the trainee and the trainee is in compliance with the requirement, or the course is optional for the trainee and the trainee has completed it, LAST_COURSE_COMPLETION_FLAG is 'Y' because LAST_CRS_COMPL_OR_REG_FLAG is 'Y'. If the course is currently required for the trainee and the trainee is not in compliance with the requirement, or the course is optional for the trainee and the trainee is Enrolled in it (or Wait-Listed for it) but has not completed it, LAST_COURSE_COMPLETION_FLAG is 'Y' because CURRENT_COURSE_REG_FLAG is 'Y'.
    • CURRENT_CRS_ITERATION_REG_FLAG indicates whether or not the registration data in this LMS_CURRENT_TRAINEE_CRS_STATUS record reflects the trainee’s current registration status with regard to the course iteration (ITERATION_ID). If REG_REC_ID is not null, but CURRENT_CRS_ITERATION_REG_FLAG is 'N', the registration record identified by the REG_REC_ID has been superseded by a later registration record pertaining to the trainee and the course iteration. For example, if the course iteration is not instructor led, the trainee may have registered to take it again after completing it.
      
  • An LMS_CURRENT_TRAINEE_CRS_STATUS record includes course completion information from the registration record identified by the REG_REC_ID.
    • Note: if LAST_COURSE_COMPLETION_FLAG is 'N', it is possible that the trainee completed the course in the past, but is overdue to complete it again. It is best to use the course completion information in the LMS_CURRENT_TRAINEE_CRS_STATUS record only when LAST_COURSE_COMPLETION_FLAG is 'Y'.
    • A trainee may legitimately be on record as having completed the course iteration even if the registration status is not Enrolled.
    • If COURSE_COMPLETED is set to 'Y',
      • DATE_COURSE_COMPLETED indicates when the trainee last completed the course. 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.
      • Note: NEXT_COURSE_DATE currently is not used, even when its value is not null. Use DUE_BY_DATE instead. Further information on DUE_BY_DATE is given below.
      • TIME_COURSE_COMPLETED is a string indicating how much time the trainee has spent on taking the course iteration 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 = COURSE_OBJECT.CO_REC_ID.)
        • TIME_COURSE_COMPLETED is null for many records (even some that have COURSE_COMPLETED set to 'Y'). 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).
        • Note: TIME_COURSE_COMPLETED values may be formatted inconsistently. For example, some records for a particular ITERATION_ID have '0.25' for TIME_COURSE_COMPLETED, while other records for that same course iteration have '.25'.
  • According to the registration record identified by the REG_REC_ID, the trainee has registered for a course iteration (ITERATION_ID), which is an offering of a particular version (CO_REC_ID) of the course (CO_MAS_ID).
    • The ITERATION_ID is the number used within the Knowledge Link system as the unique identifier for the course iteration for which the trainee has registered. To retrieve the attributes of the course iteration, the LMS_CURRENT_TRAINEE_CRS_STATUS table may be joined to the COURSE_ITERATION table by ITERATION_ID.
    • The CO_REC_ID is the number used within the Knowledge Link system as the unique identifier for the course version for which the trainee has registered. To retrieve the attributes of the course version, the LMS_CURRENT_TRAINEE_CRS_STATUS table may be joined to the COURSE_OBJECT table by CO_REC_ID.
      • Note: CO_REC_ID_AS_IS stores the REGISTRATION.CO_REC_ID for the registration record identified by the REG_REC_ID. CO_REC_ID_AS_IS might not accurately reflect the course version to which the course iteration (ITERATION_ID) belongs.
  • The registration status of the record identified by the REG_REC_ID 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 record identified by the REG_REC_ID.
    • 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 status.
    • 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 registration status is Wait-Listed, the number stored in WAIT_LIST_PRIORITY indicates what 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 iteration. Note:
    • Wait lists are kept only for instructor-led courses. (The code identifying the distribution mode is the COURSE_OBJECT.DIST_MODE where LMS_CURRENT_TRAINEE_CRS_STATUS.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.
  • Although an LMS_CURRENT_TRAINEE_CRS_STATUS record includes the DATE_REGISTRATION_CREATED, DATE_REGISTRATION_MODIFIED and DATE_REGISTRATION_DROPPED for the registration record identified by the 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 a course 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 DATE_REGISTRATION_CREATED, DATE_REGISTRATION_MODIFIED and DATE_REGISTRATION_DROPPED might not be reliable. Some records
      • 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.
      • have a value for DATE_REGISTRATION_DROPPED that is later than the value for DATE_REGISTRATION_MODIFIED.
      • have a null value for DATE_REGISTRATION_DROPPED even though their registration status is Dropped.
  • Information is available on two different business units pertaining to the registration:
    • The REGISTRATION_BU_NUMBER, REGISTRATION_BU_ID, and REGISTRATION_BU_NAME provide information on the business unit that included the trainee (USER_REC_ID) and had the course version (CO_REC_ID) assigned to it when the registration record for the REG_REC_ID was created.
    • The COURSE_OWNER_BU_NUMBER, COURSE_OWNER_BU_ID, and COURSE_OWNER_BU_NAME provide information on the business unit that owns the course version (CO_REC_ID).
• The LMS_CURRENT_TRAINEE_CRS_STATUS table stores the following information on the trainee's compliance status with regard to the course:
  • CURRENT_PERIODICITY is the number of months in the period from the time when a trainee completes the course to the time when the trainee is due to complete it again. When CURRENT_PERIODICITY is null or 0, if the course is currently required for the trainee, the trainee is required to complete it only once. If the course is currently optional for the trainee, CURRENT_PERIODICITY is null.
  • DUE_DATE and STATUS_DUEDATE_DESCRIPTION are two of the factors that determine:
    • whether Knowledge Link sends E-mail Notifications about a course to a trainee. For more information, see the data element documentation for COURSE_OBJECT.SD_BOUNDARY.
    • whether the trainee is compliant with regard to a currently required course.
    • DUE_BY_DATE is the date when the trainee is next due to complete the required course. DUE_BY_DATE is null if any of the following is true:
      • No initial due date was specified when the currently required course was assigned, and the trainee has yet to complete an iteration of the course.
      • CURRENT_PERIODICITY is null or 0, and the trainee has completed an iteration of the currently required course.
      • The course is currently optional for the trainee.
    • STATUS_DUEDATE_DESCRIPTION is a phrase describing when the trainee is next due to complete an iteration of the course ('Neither Soon Due nor Past Due', 'Soon Due', or 'Past Due'). STATUS_DUEDATE_DESCRIPTION is null if either of the following is true:
      • No initial due date was specified when the currently required course was assigned, and the trainee has yet to complete an iteration of the course.
      • The course is currently optional for the trainee.
  • COMPLIANT is 'Y' if the trainee is currently required to take the course, and has completed an iteration of the course, and is not overdue to re-take the course.

    		LMS_CURRENT_TRAINEE_CRS_STATUS data element
    Mandatory Assignment	Grandfathered	CURRENTLY_REQUIRED	COURSE_COMPLETED	STATUS_DUEDATE_DESCRIPTION	COMPLIANT
    no	not applicable	'N'	any value	null	null
    yes	yes	'N'	any value	null	null
    yes	no	'Y'	'N'	any value	'N'
    yes	no	'Y'	'Y'	'Neither Soon Due nor Past Due'	'Y'
    yes	no	'Y'	'Y'	'Soon Due'	'Y'
    yes	no	'Y'	'Y'	'Past Due'	'N'

• The list of related tables shown above includes some that may be joined to the LMS_CURRENT_TRAINEE_CRS_STATUS table only with caution, if at all. For example,
  • PKG_CRS_JOIN may be joined to LMS_CURRENT_TRAINEE_CRS_STATUS based on CO_REC_ID or CO_MAS_ID, but the fact that an LMS_CURRENT_TRAINEE_CRS_STATUS record has a corresponding PKG_CRS_JOIN record does not mean that the course was assigned to the trainee as part of a package.
  • Although LMS_CURRENT_TRAINEE_CRS_STATUS may be joined to KNODE_CRS_JOIN
    where the COURSE_OWNER_BU_NUMBER matches the KNODE_ID, you probably do not want to join the tables to each other that way--KNODE_CRS_JOIN.KNODE_ID does not identify the course owner. Joining the tables where the REGISTRATION_BU_NUMBER matches the KNODE_ID makes some sense, but if the only conditions are on the business unit, KNODE_CRS_JOIN and LMS_CURRENT_TRAINEE_CRS_STATUS have a many-to-many relationship; a query on the joined tables would get a Cartesian Product warning. If you add a condition to join based on CO_MAS_ID, some records might not be retrieved because their CO_MAS_IDs do not match. If you add a condition to join based on CO_REC_ID, some records might not be retrieved because the CO_REC_ID in the LMS_CURRENT_TRAINEE_CRS_STATUS record points to an earlier course version than the one in the KNODE_CRS_JOIN record.

Source

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

LMS_CURRENT_TRAINEE_CRS_STATUS Table - Data Element Index    Tables and Data Elements   Learning Management Home   Data Warehouse Home