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

LMS_TRAINEE_BU_COURSE_ASSIGN Table

DWLMS Schema

Explanation
Tracks the currently active mandatory course assignments for trainees. There is one record for each course version, for each business unit to which it was assigned as a mandatory course, and for each trainee included in the business unit. If a trainee belongs to multiple business units that have the same course or course version assigned to them as a mandatory course, there is more than one record for the trainee and course. TRAINEE_ASSIGNMENT_BU_NUMBER is the number that uniquely identifies the record. An LMS_TRAINEE_BU_COURSE_ASSIGN record stores:

• attributes of the course and course version assigned (including the course version's owner and access level)
• identifying information for the business units pertinent to the assignment (the one cited in the course assignment and the trainee's Knowledge Position that falls under the cited business unit)
• the trainee’s name, Penn ID, and USER_REC_ID
• the trainee's status with regard to the mandatory course assignment and some of the factors affecting that status

A trainee registers for a course iteration, which is an offering of a particular version of a course. A trainee cannot register for an iteration of a course version if that course version does not 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. Business 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 LMS_TRAINEE_BU_COURSE_ASSIGN table is an enhanced version of the USER_COURSES table, and was developed to support ad hoc reporting. The primary source of information on which courses are currently required for trainees is the KNODE_CRS_JOIN table, which stores information on mandatory and optional courses currently
assigned to business units that may represent individual trainees or groups of trainees. The USER_COURSES and LMS_TRAINEE_BU_COURSE_ASSIGN tables store information on mandatory courses currently assigned to individual trainees. If a trainee belongs to multiple business units that have the same mandatory course assigned to them, these tables store more than one record for the trainee and course. The LMS_TRAINEE_COURSE_ASSIGN table stores one record per trainee and currently required course. (If the course is actively assigned as a mandatory course to a business unit that currently includes the trainee, and the trainee is not grandfathered for the course, the course is currently required for the trainee.) Historical information on mandatory course assignments is not available. 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.

Common Uses

• Listing the trainees that are affected by the assignment of a particular mandatory course to a particular business unit
• Identifying all of the mandatory course assignments for a trainee that factor into whether a particular course is or is not currently required for the trainee

TRAINEE_BU_CRS_ASSIGNMENT_ID

BU_CRS_ASSIGNMNT_STATUS
COURSE_ASSIGNMENT_BU_ID
COURSE_ASSIGNMENT_BU_NAME
COURSE_ASSIGNMENT_BU_NUMBER
COURSE_MASTER_ID
COURSE_OWNER_BU_ID
COURSE_OWNER_BU_NAME
COURSE_OWNER_BU_NUMBER
CO_MAS_ID
CO_REC_ID
CURRENTLY_REQUIRED
FIRST_NAME
LAST_NAME
MANDATORY
PENN_ID
TRAINEE_ASSIGNMENT_BU_ID
TRAINEE_ASSIGNMENT_BU_NAME
TRAINEE_ASSIGNMENT_BU_NUMBER
TRAINEE_BU_CRS_ASSIGNMENT_ID
USER_REC_ID

COURSE_ITERATION
COURSE_MASTER
COURSE_OBJECT
CUSTOM_DATA
GEN21_USER
KNODE
KNODE_CRS_JOIN
KNODE_USER_JOIN
LMS_BUSINESS_UNIT_GROUP
LMS_COMPLIANCE
LMS_COURSE_ITER_INSTRUCTOR
LMS_COURSE_MASTER
LMS_CURRENT_CRS_ITER_REG_INSTR
LMS_CURRENT_TRAINEE_CRS_STATUS
LMS_DEFINITION
LMS_GOVERNING
LMS_PERSON
LMS_REGISTRATION
LMS_STAFF_QUAL
LMS_TRAINEE_COURSE_ASSIGN
LMS_TRAINEE_COURSE_COMPLIANCE
PKG
PKG_CRS_JOIN
PROFILER_ASSIGNMENTS
PROFILER_USER_ANSWER
PROFILER_USER_QUESTIONNAIRE
REGISTRATION
TEST
UPHS_COST_CENTER
UPHS_RAW_DATA
UPHS_USER_ENTITY
USER_COURSES

Cautions

• As mentioned above,
  • if a trainee belongs to multiple business units that have the same mandatory course assigned to them, LMS_TRAINEE_BU_COURSE_ASSIGN stores more than one record for the trainee and course.
  • LMS_TRAINEE_BU_COURSE_ASSIGN stores information on mandatory courses that are currently assigned to individual trainees. Historical information on mandatory course assignments is not available. 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.
• Information on the business unit to which the mandatory course was assigned per the assignment tracked in the LMS_TRAINEE_BU_COURSE_ASSIGN record is available in COURSE_ASSIGNMENT_BU_NUMBER, COURSE_ASSIGNMENT_BU_ID, and COURSE_ASSIGNMENT_BU_NAME. This may or may not be the same as a business unit to which the trainee is assigned—a Knowledge Position (KP). Information on the trainee's KP that falls under the COURSE_ASSIGNMENT_BU_ is available in TRAINEE_ASSIGNMENT_BU_NUMBER, TRAINEE_ASSIGNMENT_BU_ID, and TRAINEE_ASSIGNMENT_BU_NAME.
• There are several data elements providing information on the trainee's status with regard to the mandatory course assignment tracked in the LMS_TRAINEE_BU_COURSE_ASSIGN record.

  	LMS_TRAINEE_BU_COURSE_ASSIGN data element
  COMPLETE	Mandatory Assignment	Grandfathered	CURRENTLY_REQUIRED	STATUS_DUEDATE_DESCRIPTION	COMPLIANT
  any value	'Y'	'Y'	'N'	'Neither Soon Due nor Past Due'	'Y'
  no	'Y'	'N'	'Y'	any value	'N'
  yes	'Y'	'N'	'Y'	'Neither Soon Due nor Past Due'	'Y'
  yes	'Y'	'N'	'Y'	'Soon Due'	'Y'
  yes	'Y'	'N'	'Y'	'Past Due'	'N'

• EFFECTIVE_DATE is the date when the mandatory course assignment takes effect. The value may be null.
• GRANDFATHERED is 'Y' if the trainee is grandfathered for the mandatory course assignment.
  • A trainee is grandfathered for a particular mandatory course assignment if at least one of the following is true:
    • the trainee's DEPT_START_DATE (per the LMS_PERSON table) falls before the EFFECTIVE_DATE of the mandatory course assignment
      • The trainee's DEPT_START_DATE is stored in LMS_PERSON.DEPT_START_DATE where LMS_TRAINEE_BU_COURSE_ASSIGN.USER_REC_ID = LMS_PERSON.USER_REC_ID. Further information about the trainee is available in the LMS_PERSON table.
    • the trainee's TRANSFER_TYPE is grandfathered according to the GRANDFATHERING_VALUE for the course version named in the mandatory course assignment
      • the trainee's TRANSFER_TYPE is stored in KNODE_USER_JOIN.TRANSFER_TYPE where LMS_TRAINEE_BU_COURSE_ASSIGN.USER_REC_ID = KNODE_USER_JOIN.USER_REC_ID.
  • If the trainee has only one mandatory assignment for the course (CO_MAS_ID), the trainee's grandfathered status for that assignment is the trainee's grandfathered status for the course.
  • If the trainee has more than one mandatory 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 trainee's grandfathered status for the course is one 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.
• The LMS_TRAINEE_BU_COURSE_ASSIGN record for the mandatory course assignment has CURRENTLY_REQUIRED set to 'Y' if GRANDFATHERED is 'N'.
  • If the trainee has at least one currently required assignment for the course (CO_MAS_ID), the course is currently required for the trainee. For the trainee's CURRENTLY_REQUIRED status for the course, see the LMS_TRAINEE_COURSE_ASSIGN table or the LMS_TRAINEE_COURSE_COMPLIANCE table. Note: if there is no record for the trainee and course in either of those tables, the course is not currently required for the trainee.
  • The trainee's CURRENTLY_REQUIRED status for the course is one 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.
• 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, per the mandatory course assignment. When PERIODICITY is null or 0, the trainee is required to complete the course only once.
  • When CURRENTLY_REQUIRED is 'N', PERIODICITY is irrelevant, even if its value is not null.
  • If the trainee has only one currently required assignment for the course (CO_MAS_ID), the trainee's PERIODICITY for that assignment is the same as the trainee's PERIODICITY for the course.
  • If the trainee has multiple currently required assignments for the course, the assignment with the lowest non-null value of DUE_DATE is the one with the trainee's PERIODICITY for the course. (When the trainee has completed the course at least once, the assignment with the lowest non-null value of DUE_DATE is also the assignment with the lowest non-null, non-zero value for PERIODICITY.) For the trainee's PERIODICITY for the course, see LMS_TRAINEE_COURSE_ASSIGN.PERIODICITY or LMS_TRAINEE_COURSE_COMPLIANCE.CURRENT_PERIODICITY.
• DUE_DATE is the date when the trainee is next due to complete an iteration of the currently required course, per the mandatory course assignment..
  • DUE_DATE is null if any of the following is true:
    • GRANDFATHERED is 'Y'
    • No initial due date was specified for the mandatory course assignment, and the trainee has yet to complete an iteration of the course.
      • The assignment's initial due date is stored in LMS_GOVERNING.INITIAL_DUE_DATE where LMS_TRAINEE_BU_COURSE_ASSIGN.KCJ_REC_ID = LMS_GOVERNING.KCJ_REC_ID.
      • For information on the trainee's latest completion of the course (if any), see the record in the LMS_REGISTRATION table where LMS_TRAINEE_BU_COURSE_ASSIGN.CO_MAS_ID = LMS_REGISTRATION.CO_MAS_ID and LMS_TRAINEE_BU_COURSE_ASSIGN.USER_REC_ID = LMS_REGISTRATION.USER_REC_ID and LMS_REGISTRATION.LAST_COURSE_COMPLETION_FLAG = 'Y'
    • PERIODICITY is null or 0, and the trainee has completed an iteration of the course.
• STATUS_DUEDATE_DESCRIPTION is a phrase describing when the trainee is next due to complete an iteration of the course, per the mandatory course assignment. The value may be 'Neither Soon Due nor Past Due', 'Soon Due', or 'Past Due'. If DUE_DATE is null, STATUS_DUEDATE_DESCRIPTION is 'Neither Soon Due nor Past Due'.
• The DUE_DATE and STATUS_DUEDATE_DESCRIPTION for the course are two of the factors that determine whether Knowledge Link sends E-mail Notifications about a course to a trainee. For more information on E-mail Notifications, see the data element documentation for COURSE_OBJECT.SD_BOUNDARY.
  • If the trainee has only one currently required assignment for the course (CO_MAS_ID), the trainee's DUE_DATE and STATUS_DUEDATE_DESCRIPTION for that assignment are the same as the trainee's DUE_DATE and STATUS_DUEDATE_DESCRIPTION for the course.
• If the trainee has multiple currently required assignments for the course, the assignment with the lowest non-null value of DUE_DATE is the one with the trainee's DUE_DATE and STATUS_DUEDATE_DESCRIPTION for the course. For the trainee's DUE_DATE and STATUS_DUEDATE_DESCRIPTION for the course, see the LMS_TRAINEE_COURSE_COMPLIANCE table, the LMS_TRAINEE_COURSE_ASSIGN table, or the LMS_TRAINEE_COURSE_COMPLIANCE table. (In the LMS_TRAINEE_COURSE_COMPLIANCE table, the due date is called DUE_BY_DATE.
• The LMS_TRAINEE_BU_COURSE_ASSIGN record for the mandatory course assignment has COMPLIANT set to 'Y' if CURRENTLY_REQUIRED set to 'Y', and STATUS_DUEDATE_DESCRIPTION is 'Neither Soon Due nor Past Due', and the trainee has completed an iteration of the course.
  • For information on the trainee's latest completion of the course (if any), see the record in the LMS_REGISTRATION table where LMS_TRAINEE_BU_COURSE_ASSIGN.CO_MAS_ID = LMS_REGISTRATION.CO_MAS_ID and LMS_TRAINEE_BU_COURSE_ASSIGN.USER_REC_ID = LMS_REGISTRATION.USER_REC_ID and LMS_REGISTRATION.LAST_COURSE_COMPLETION_FLAG = 'Y'
  • If the trainee has only one currently required course assignment for the course (CO_MAS_ID), the trainee's COMPLIANT status for that assignment is the same as the trainee's COMPLIANT status for the course.
  • If the trainee has multiple currently required assignments for the course, the assignment with the lowest non-null value of DUE_DATE is the one with the trainee's COMPLIANT status for the course. For the trainee's COMPLIANT status for the course, see the LMS_TRAINEE_COURSE_COMPLIANCE table, the LMS_TRAINEE_COURSE_ASSIGN table, or the LMS_TRAINEE_COURSE_COMPLIANCE table.

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