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

LMS_COURSE_MASTER Table

DWLMS Schema

Explanation
Reference table with current data on Knowledge Link courses. A course is a formal educational unit that deals with a particular subject. A course provides training to members of the Penn Community in support of their academic activities and job responsibilities at the University and/or at the University of Pennsylvania Health System. You can identify a course by its CO_MAS_ID or COURSE_MASTER_ID. The LMS_COURSE_MASTER table has one record per course, and includes descriptive information (such as the latest owner, title, and description of the course) as well as information on the status of the course.

The LMS_COURSE_MASTER table is an enhanced version of the COURSE_MASTER table, and was developed to support ad hoc reporting.

Common Uses

• Displaying the COURSE_MASTER_ID or the latest title of the course, rather than the numeric ID number that appears in a related table.
• Selecting courses to be included in a report based on their status.
  

CO_MAS_ID

CO_MAS_ID
COURSE_MASTER_ID
COURSE_STATUS
LATEST_CO_REC_ID
LATEST_COURSE_OWNER_BU_ID
LATEST_COURSE_OWNER_BU_NAME
LATEST_COURSE_OWNER_BU_NUMBER
LATEST_COURSE_TITLE

COURSE_ITERATION
COURSE_MASTER
COURSE_OBJECT
KNODE_CRS_JOIN
LMS_BUSINESS_UNIT_GROUP
LMS_CURR_TRNEE_CRS_STAT_MV
LMS_CURRENT_CRS_ITER_REG_INSTR
LMS_CURRENT_TRAINEE_CRS_STATUS
LMS_REGISTRATION
LMS_STAFF_QUAL
LMS_TRAINEE_BU_COURSE_ASSIGN
LMS_TRAINEE_COURSE_ASSIGN
PKG_CRS_JOIN
PROFILER_ASSIGNMENTS
REGISTRATION
UPHS_COST_CENTER
UPHS_RAW_DATA
UPHS_USER_ENTITY
USER_COURSES

Cautions

• The LMS_COURSE_MASTER table stores only current information on Knowledge Link courses.
  • If a record once existed for course X, but that record was deleted, there is no way to tell from the LMS_COURSE_MASTER table that the course ever existed.
  • The LMS_COURSE_MASTER table stores information on the latest course version's owner, title, and description. Information on earlier versions is available in the COURSE_OBJECT table where LMS_COURSE_MASTER.CO_MAS_ID = COURSE_OBJECT.CO_MAS_ID.
• There are several data elements that provide information about the course's identity:
  • The COURSE_MASTER_ID is a string that uniquely identifies the course to Knowledge Link users. One example is 'UNIV_ALL_EHRS_BPYIP'. (The value is stored without the quotes.) See the documentation on the COURSE_MASTER_ID data element for more information on how the value of the identifying string is determined.
  • The CO_MAS_ID is a number (such as 11883) used to uniquely identify the course within the Knowledge Link system.
  • A course usually (but not always) has at least one course version. Because Knowledge Link does not track a title, description, or owner for the course itself, the LMS_COURSE_MASTER table stores the information for the latest version of the course.
    • Actually, the information is for the version of the course that has the highest version number. This is usually, but not always, the course version whose iterations are scheduled in the future, or were scheduled most recently in the past.
      • A course may have more than one version. For example, if the course covers Software Package X, version 1 of the course might cover Software Package X release 2.0, and version 2 of the course might cover Software Package X release 3.5. Information on the latest course version is available in the COURSE_OBJECT table where LMS_COURSE_MASTER.LATEST_CO_REC_ID = COURSE_OBJECT.CO_REC_ID.
    • There are some records in LMS_COURSE_MASTER for courses with no course versions. For such courses, LATEST_CO_REC_ID, LATEST_COURSE_TITLE, LATEST_COURSE_DESCRIPTION, LATEST_COURSE_OWNER_BU_NUMBER, LATEST_COURSE_OWNER_BU_ID, and LATEST_COURSE_OWNER_BU_NAME will be null.
    • Note: it is common for more than one course to have the same LATEST_COURSE_TITLE.
    • For information on the versions of a given course, see the COURSE_OBJECT table where LMS_COURSE_MASTER.CO_MAS_ID = COURSE_OBJECT.CO_MAS_ID.
• COURSE_STATUS indicates whether the course is active ('A') or inactive ('I'). (The value is stored without the quotes.) A course must be active when a record is created to:
  • establish a version of the course. (See the COURSE_OBJECT table.)
  • set up an iteration of the course. (See the COURSE_ITERATION table.) An iteration is an offering of a particular version of the course.
  • name the course as part of a package. (See the PKG_CRS_JOIN table.) A package is a set of courses that may be taken in any order, and that can be assigned to a business unit using one assignment specification.
  • assign the course to a business unit. (See the KNODE_CRS_JOIN table.) A business unit (also known as a BU, role, Knowledge Node, or K Node) is a set of one or more Knowledge Link users.
  • have a Knowledge Link user register as a trainee for the course. (See the LMS_REGISTRATION and REGISTRATION tables.)
• The GRANDFATHERING_VALUE in the LMS_COURSE_MASTER table is not the one that indicates whether a trainee is grandfathered for a particular mandatory course assignment. The trainee's grandfathered status for the mandatory course assignment is available in LMS_TRAINEE_BU_COURSE_ASSIGN.GRANDFATHERED.
  • Sometimes, when a mandatory course is assigned to a business unit, not all of the trainees included in the business unit are required to take the course; some are "grandfathered". (For information on the inclusion of trainees in business units, see KNODE_USER_JOIN.KNODE_ID.)
  • A mandatory course is assigned to a business unit, which may include one or more trainees. The trainees' TRANSFER_TYPE indicates how they became included in the business unit--as a new employee, or via some kind of job transfer. (See KNODE_USER_JOIN.TRANSFER_TYPE.) Their TRANSFER_TYPE is one of the things that may qualify trainees to be grandfathered for the mandatory course is assignment. The course's GRANDFATHERING_VALUE (available in the COURSE_MASTER table) indicates what transfer types are grandfathered for the course. When a course is created, if the GRANDFATHERING_VALUE is not otherwise specified, it is set by default to 0 (no transfer types are grandfathered).
    • The GRANDFATHERING_VALUE in the LMS_COURSE_MASTER table is a scrubbed version of COURSE_MASTER.GRANDFATHERING_VALUE, and simply indicates (with a ‘Y’ or an ‘N’) whether the course does or does not allow for grandfathering based on transfer type.
  • When a course version is created, its GRANDFATHERING_VALUE is stored in the COURSE_OBJECT table. If its value is not otherwise specified, it is set by default to the value for the course per the COURSE_MASTER table.
  • When a mandatory course is assigned to a business unit, an EFFECTIVE_DATE can be specified for the assignment.
  • When a trainee's record is set up in Knowledge Link, the DEPT_START_DATE is stored. When the trainee is assigned to a business unit, the trainee's TRANSFER_TYPE is stored. (Note: currently, this information is stored for trainees who are University of Pennsylvania Health System (UPHS) employees, but not for other trainees.)
  • In the Data Warehouse, LMS_TRAINEE_BU_COURSE_ASSIGN.GRANDFATHERED tracks whether trainees are grandfathered for their mandatory course assignments.
    • A trainee is included in a mandatory course assignment if the trainee is included in the business unit to which the mandatory course is assigned. (See KNODE_USER_JOIN.KNODE_ID.) For each mandatory course assignment, Knowledge Link identifies the trainees that are included, and determines each trainee's grandfathered status for the mandatory course assignment. (See LMS_TRAINEE_BU_COURSE_ASSIGN.GRANDFATHERED.) A trainee is grandfathered for a given mandatory course assignment if at least one of the following is true:
      • the trainee's DEPT_START_DATE falls before the mandatory course assignment's EFFECTIVE_DATE
      • the trainee's TRANSFER_TYPE is grandfathered according to the GRANDFATHERING_VALUE for the course version named in the mandatory course assignment
    • If the trainee is included in only one mandatory course assignment for the course, the trainee's grandfathered status for that assignment (LMS_TRAINEE_BU_COURSE_ASSIGN.GRANDFATHERED) is the trainee's grandfathered status for the course.
    • If the trainee is included in more than one mandatory course assignment for the course, the trainee is grandfathered for the course only if the trainee is grandfathered for all of the assignments. Otherwise, the trainee is not grandfathered for the course.
    • If the trainee is not grandfathered for the course, the trainee's grandfathered status for the course is available in the LMS_TRAINEE_COURSE_COMPLIANCE table, which also stores information used by Knowledge Link when sending E-mail Notifications to the trainee about the currently required course. The course is currently required for the 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.
• The E-mail Notifications parameters in the LMS_COURSE_MASTER table are not the ones that control whether Knowledge Link sends E-mail Notifications about a currently required course to a particular trainee.
  • When a course is created, if the values for the E-mail Notifications parameters are not otherwise specified, they are set by default as follows:
    • COURSE_NOTIFICATION: 'Y' (E-mail Notifications are turned on for this course)
    • COURSE_ASSIGNMENT: 'N' (E-mail Notifications are not sent to trainees when a course assignment is created that indicates that this course is currently required for them)
    • COURSE_STATUS_SD: 'Y' (E-mail Notifications are sent to trainees when their status for this course becomes 'Soon Due')
    • COURSE_STATUS_PD: 'Y' (E-mail Notifications are sent to trainees when their status for this course becomes 'Past Due')
    • COURSE_SD_BOUNDARY: 30 (a trainee's status for this course becomes Soon Due 30 days before the trainee's Due Date for this course)
    • COURSE_SD_RECURR: 'Once' (E-mail Notifications are sent only once to trainees when their status for this course becomes 'Soon Due')
    • COURSE_PD_RECURR: 'Once' (E-mail Notifications are sent only once to trainees when their status for this course becomes 'Past Due')
  • When a course version is created, its E-mail Notifications parameters are stored in the COURSE_OBJECT table. (The names of the parameters for the course version are the names of the parameters for the course, with 'COURSE_' removed--for example, NOTIFICATION.) If the values for these parameters are not otherwise specified, they are set by default to the values for the course.
  • When a mandatory course is assigned to a business unit, if the E-mail Notifications parameters for the mandatory course assignment are not otherwise specified, they are set by default to the values for the course version (per the COURSE_OBJECT table). The E-mail Notifications parameters for the mandatory course assignment are stored in the LMS_GOVERNING table.
  • Whether Knowledge Link sends E-mail Notifications about a course to a particular trainee depends not only on the parameters for the mandatory course assignment, but also on the trainee's GRANDFATHERED status for the course, the date when the trainee is next due to complete the course (DUE_DATE), and the status of that date relative to today's date (STATUS_DUEDATE_DESCRIPTION). The LMS_TRAINEE_BU_COURSE_ASSIGN table stores these three data elements for the trainees and their mandatory course assignments. These three data elements are also available in LMS_TRAINEE_COURSE_COMPLIANCE for trainees and their currently required courses (that is, mandatory courses for which the trainees are not grandfathered).

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_COURSE_MASTER Table - Data Element Index    Tables and Data Elements   Learning Management Home   Data Warehouse Home