Penn Computing

University of Pennsylvania
Penn Computing << go backback
COURSE_OBJECT Table - Data Element Index    Tables and Data Elements   Learning Management Home   Data Warehouse Home

COURSE_OBJECT Table

DWLMS Schema

Explanation
Tracks current information on course versions. You can identify a course version by its CO_REC_ID or CO_ID. The COURSE_OBJECT table has one record per course version, and includes descriptive information on the course version, as well as information related to its status, its content and the means of delivering that content, and the course assignment process.

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. 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. A course iteration is an offering of a particular version of the course. A course version may have more than one course iteration. For example, version 2 of the instructor-led course on Software Package X will have two course iterations if it is offered in two different places on the same date and at the same time. Web-based courses commonly have one iteration per version.

Common Uses

  • Displaying the course version's title or identifier, rather than the numeric ID number that appears in a related table.
  • Providing the descriptions for selected course versions.
  • Checking administrative information related to the course assignment process: the course version's status, owner, access level, default periodicity, and grandfathering value.
  • Retrieving information related to a course version's content and the means for delivering that content: the author, objectives, distribution mode, and launch type.
  • Getting records on one or more versions of a particular course.
Primary Key Indexed Data Elements Related Tables 
CO_REC_ID
 
CO_ID
CO_MAS_ID
CO_REC_ID
KNODE_ID

COURSE_ITERATION
COURSE_MASTER
KNODE
KNODE_CRS_JOIN
LMS_BUSINESS_UNIT_GROUP
LMS_COURSE_MASTER
LMS_CURRENT_CRS_ITER_REG_INSTR
LMS_CURRENT_TRAINEE_CRS_STATUS
LMS_DEFINITION
LMS_STAFF_QUAL
LMS_TRAINEE_BU_COURSE_ASSIGN
PKG_CRS_JOIN
REGISTRATION
TEST
UPHS_COST_CENTER
UPHS_USER_ENTITY
USER_COURSES


Cautions

  • The COURSE_OBJECT table stores only the current information for a course version. Historical information for a course version is not available. For example, if a course version originally had a PASSING_SCORE of 80, but had its PASSING_SCORE changed to 100, there is no way to tell from the COURSE_OBJECT table that the course version ever had a PASSING_SCORE of 80.
  • There are a few data elements that provide information about the course version's identity:
    • The CO_REC_ID is a number (such as 20264) used to uniquely identify the course version within the Knowledge Link system.
    • The CO_ID is a string (such as UNIV_ALL_ALL_PRIV-SEC_v1) that uniquely identifies the course version to Knowledge Link users.
      • Its value is usually the Course Master ID, followed by an underscore, a lower case 'v', and the version number (CRS_VERSION). For more information on the Course Master ID, see the documentation on LMS_COURSE_MASTER.COURSE_MASTER_ID.
    • The CO_TITLE is the title of the course version. One example is 'Information Privacy and Security at Penn - UNIV'. (The value is stored without the quotes.)
      • Note: It is common for more than one course version to have the same value for CO_TITLE.
      • The CO_TITLE may be stored in upper case, lower case, or mixed case.
    • The DESCRIPTION often stores a few sentences about the content of the course version and the target audience.
      • Note: It is common for more than one course version to have the same value for DESCRIPTION.
      • For some course versions, the DESCRIPTION is the same as the CO_TITLE.
      • The DESCRIPTION may be stored in upper case, lower case, or mixed case. Some course versions have no description--their value for DESCRIPTION is null. Some course versions have a DESCRIPTION that includes no words (for example, '111').
  • In the COURSE_OBJECT table, a course (CO_MAS_ID) has at least one course version (CO_REC_ID), and may have more than one. For a given course, the version with the highest CRS_VERSION number is usually the latest version--the one whose iterations are scheduled in the future, or were scheduled most recently in the past. However, when a course has at least one COURSE_OBJECT record, and another record is created so that Knowledge Link can store data on trainees who completed an earlier version of the course in the past, the historical version of the course has the highest CRS_VERSION number, until another COURSE_OBJECT record is created for another version of the course.
  • STATUS indicates whether the course version is active (1) or inactive (0). A course version must be active when a record is created to:
    • set up an iteration of the course version. (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 an iteration of the course version. (See the LMS_REGISTRATION and REGISTRATION tables.)
  • Instead of text, some data elements in the COURSE_OBJECT table store a numeric code. The decoded value is stored in the LMS_DEFINITION table.
    • To display the description of the course version's distribution mode (such as 'Instructor Led' or 'Web Based') rather than the numeric code (such as 13 or 12), use the LMS_DEFINITION.NAME where COURSE_OBJECT.DIST_MODE = LMS_DEFINITION.DEF_ID.
    • To display the description of the language used to present the course version's content (such as 'US English') rather than the numeric code (such as 29), use the LMS_DEFINITION.NAME where COURSE_OBJECT.LANGUAGE = LMS_DEFINITION.DEF_ID. As of this writing, all course versions use US English.
    • To display the description of the course version's course type (such as 'Course') rather than the numeric code (such as 3321), use the LMS_DEFINITION.NAME where COURSE_OBJECT.COURSE_TYPE = LMS_DEFINITION.DEF_ID. As of this writing, the course type for all course versions is Course.
    • To display the description of the course version's launch type (such as 'GEN21' or 'SCORM 1.2') rather than its numeric code (such as 372 or 388), use the LMS_DEFINITION.NAME where COURSE_OBJECT.LAUNCH_TYPE = LMS_DEFINITION.DEF_ID.
    • To display the description of the course version's access level (such as 'Inheritable') rather than its numeric code (such as 166), use the LMS_DEFINITION.NAME where COURSE_OBJECT.XACCESS = LMS_DEFINITION.DEF_ID.
  • The COURSE_OBJECT table stores the SUPPLIER_ID, the unique identifier for the group that is responsible for maintaining the course version in Knowledge Link. In the Knowledge Link system, the SUPPLIER table stores the name and contact information for the supplier, but that information is not available in the Data Warehouse.
  • The GRANDFATHERING_VALUE in the COURSE_OBJECT 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 course is assigned to a business unit as a mandatory course, 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 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.)
    • When the Knowledge Link data is exported to the Warehouse early every morning, the GRANDFATHERED flag in the Data Warehouse's LMS_TRAINEE_BU_COURSE_ASSIGN table is updated to reflect whether a particular trainee is grandfathered for a particular mandatory course assignment.
      • 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 mandatory course.
  • The E-mail Notifications parameters in the COURSE_OBJECT table are not the ones that control whether Knowledge Link sends E-mail Notifications about a currently required course to a trainee.
    • When a course version is created, its E-mail Notifications parameters are stored in the COURSE_OBJECT table. If the values for these parameters are not otherwise specified, they are set by default to the values for the course. (See the COURSE_MASTER and LMS_COURSE_MASTER tables. The COURSE_MASTER and COURSE_OBJECT tables store the codes for the parameters; the LMS_COURSE_MASTER table stores the decoded values.) The parameters for the course version are:
      • NOTIFICATION
      • ASSIGNMENT
      • STATUS_SD
      • STATUS_PD
      • SD_BOUNDARY
      • SD_RECURR
      • PD_RECURR
    • 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).
  • The COURSE_OBJECT table is unlike most other tables in the Data Warehouse in that not all of its records have the same date stored in LAST_EXTRACT_DATE.
    • The Data Warehouse’s COURSE_OBJECT table (the “target”) is a copy of Knowledge Link’s COURSE_OBJECT table (the “source”). When most Data Warehouse tables are refreshed, all of the records in the target are deleted, and then the source records are loaded into the target. However, when the COURSE_OBJECT target is refreshed, it’s compared to the source. Target records that are not in the source are deleted from the target. Source records that are not in the target are added to the target, and have their LAST_EXTRACT_DATE set to the date and time when they were added to the target. If a course version’s source record has data that doesn’t match what is in its target record, the target record is updated, and its LAST_EXTRACT_DATE is changed to the date and time when the target record was updated. If a course version’s source record has data that matches what is in its target record, the target record is left untouched, and its LAST_EXTRACT_DATE remains unchanged.
    • To see when the COURSE_OBJECT table was last refreshed in the Data Warehouse, see the record with the highest value for LAST_EXTRACT_DATE.
  • The COURSE_OBJECT table includes several data elements that currently are not used. Those data elements are:
    • BLENDED
    • ECOMMERCE
    • FINISH_BY
    • G21_LOOK_AND_FEEL
    • ITG_ID
    • MAX_PASSES
    • MGR_APPROVAL
    • PRICE
    • THRESHOLD_SCORE
  • It might appear that the list of related tables shown above incorrectly excludes the KNODE_USER_JOIN table. Although COURSE_OBJECT and KNODE_USER_JOIN can be joined based on KNODE_ID, you probably do not want to join the tables to each other directly--KNODE_ID does not have the same meaning in both tables.
    • In a COURSE_OBJECT record, the KNODE_ID identifies the business unit that owns the course version. However, in a KNODE_USER_JOIN record, the KNODE_ID denotes a business unit to which the Knowledge Link user has been assigned--a Knowledge Position, or KP.
    • An example of a scenario in which you might need to use both the KNODE_USER_JOIN table and the COURSE_OBJECT table is a situation where you want to determine whether a given Knowledge Link user can assign a particular course to a business unit. If you do use both tables, you'll need to use more than one query.
      • First, check the user (USER_REC_ID). Select the active KNODE_USER_JOIN record(s) for the user, checking the user's KP(s) to see whether any of them grants the user the necessary privileges. If the user has administrator privileges, the user can create course assignments involving any course, and you can stop at this point. You can also stop at this point if the user does not have privileges that include the ability to create course assignments.
      • Next, check the course (CO_MAS_ID).
        • If the course is inactive (LMS_COURSE_MASTER.COURSE_STATUS='I'), the user cannot create course assignments for the course, and you can stop at this point.
        • Get the relevant course version (CO_REC_ID)--the one from the COURSE_OBJECT record that has the highest CRS_VERSION number for the course. If that course version is inactive (STATUS=0), the user cannot create course assignments for the course, and you can stop at this point.
      • If the user does not have administrator privileges, but does have privileges that include the ability to create course assignments, and the course and its relevant version are active, the next step is to run one or more queries to check the combination of the user and the course. You might need to check up to four possibilities. For each possibility, given the active KNODE_USER_JOIN record(s) for the user and the privileged KP(s), and the active COURSE_OBJECT record for the relevant course version,
        1. select records where KNODE_USER_JOIN.KNODE_ID = KNODE.KNODE_ID(+) and KNODE.PARENT_ID = LMS_BUSINESS_UNIT_GROUP.BUSINESS_UNIT_GROUP_NUMBER(+) and LMS_BUSINESS_UNIT_GROUP.BUSINESS_UNIT_MEMBER_NUMBER = COURSE_OBJECT.KNODE_ID (+). The user can assign the course if:
          • LMS_BUSINESS_UNIT_GROUP.BUSINESS_UNIT_GROUP_TYPE_ABBR = 'KMC' (the parent of the user's privileged KP is a Knowledge Microcommunity), and
          • KNODE.PARENT_ID = COURSE_OBJECT.KNODE_ID (the parent of the user's privileged KP is the same as the course version's owner).
        2. for the user, select records where KNODE_USER_JOIN.KNODE_ID = KNODE.KNODE_ID(+) and KNODE.PARENT_ID = LMS_BUSINESS_UNIT_GROUP.BUSINESS_UNIT_GROUP_NUMBER(+). In a separate query, check the COURSE_OBJECT.XACCESS for the relevant course version. The user can assign the course if:
          • LMS_BUSINESS_UNIT_GROUP.BUSINESS_UNIT_GROUP_TYPE_ABBR = 'KC' (the parent of the user's privileged KP is a Knowledge Community), and
          • COURSE_OBJECT.XACCESS = 165 (the course version's access level is Global).
        3. select records where KNODE_USER_JOIN.KNODE_ID = KNODE.KNODE_ID(+) and KNODE.PARENT_ID = LMS_BUSINESS_UNIT_GROUP.BUSINESS_UNIT_GROUP_NUMBER(+) and LMS_BUSINESS_UNIT_GROUP.BUSINESS_UNIT_MEMBER_NUMBER = COURSE_OBJECT.KNODE_ID (+) (the parent of the user's privileged KP is the same as the course version's owner or falls above it in the business unit hierarchy). The user can assign the course if:
          • LMS_BUSINESS_UNIT_GROUP.BUSINESS_UNIT_GROUP_TYPE_ABBR = 'KC' (the parent of the user's privileged KP is a Knowledge Community), and
          • COURSE_OBJECT.XACCESS =166 (the course version's access level is Inheritable).
        4. select records where KNODE_USER_JOIN.KNODE_ID = KNODE.KNODE_ID(+) and KNODE.PARENT_ID = LMS_BUSINESS_UNIT_GROUP.BUSINESS_UNIT_GROUP_NUMBER(+) and LMS_BUSINESS_UNIT_GROUP.BUSINESS_UNIT_MEMBER_NUMBER = COURSE_OBJECT.KNODE_ID (+). The user can assign the course if:
          • LMS_BUSINESS_UNIT_GROUP.BUSINESS_UNIT_GROUP_TYPE_ABBR = 'KC' (the parent of the user's privileged KP is a Knowledge Community), and
          • KNODE.PARENT_ID = COURSE_OBJECT.KNODE_ID (the parent of the user's privileged KP is the same as the course version's owner), and
          • COURSE_OBJECT.XACCESS =167 (the course version's access level is Local).

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

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

Questions about this page? Email us at da-staff@isc.upenn.edu

Information Systems and Computing
University of Pennsylvania
Information Systems and Computing, University of Pennsylvania