• CO_MAS_ID
• COMPLIANT
• COURSE_MASTER_ID
• COURSE_OWNER_BU_ID
• COURSE_OWNER_BU_NAME
• COURSE_OWNER_BU_NUMBER
• CURRENTLY_REQUIRED
• COURSE_TITLE
• DUE DATE
• FIRST_NAME

• LAST_EXTRACT_DATE
• LAST_NAME
• MANDATORY
• MIDDLE_INITIAL
• PENN_ID
• PERIODICITY
• STATUS_DUEDATE_DESCRIPTION
• TRAINEE_CRS_ASSIGNMNT_STATUS
• USER_REC_ID

The number used within the Knowledge Link system as the unique identifier for the course, which is currently required for the trainee. See also COURSE_MASTER_ID and COURSE_TITLE.

To retrieve the attributes of the course, query the LMS_COURSE_MASTER table where LMS_TRAINEE_COURSE_ASSIGN.CO_MAS_ID = LMS_COURSE_MASTER.CO_MAS_ID.

Examples: 12437 (UNIV_ALL_ALL_PROFILER ); 10252 (UPHS_HUP_NURS_ORT131)

Values:
Refer to the LMS_COURSE_MASTER
or COURSE_MASTER table for values.
Note:  some of the courses in those tables
have no associated LMS_TRAINEE_COURSE_ASSIGN
records.

A Yes/No flag indicating whether the trainee (USER_REC_ID) is
compliant regarding the course (CO_MAS_ID), which is currently required for the trainee. See also PENN_ID, FIRST_NAME, MIDDLE_INITIAL, LAST_NAME, COURSE_MASTER_ID, COURSE_TITLE, CURRENTLY_REQUIRED, and STATUS_DUEDATE_DESCRIPTION.

If the trainee has multiple mandatory course assignments for the course that make it currently required for the trainee, the assignment with the lowest non-null value of DUE_DATE is the one whose COMPLIANT value is stored in the LMS_TRAINEE_COURSE_ASSIGN table. (For all of the mandatory course assignments for the trainee and course, see the LMS_TRAINEE_BU_COURSE_ASSIGN table.)

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, the trainee is compliant with regard to the course. (The term "compliant" has no meaning for optional courses.)

Note: In some cases, the trainees may have a null value for the STATUS_DUEDATE_DESCRIPTION for the currently required course. Although the trainee is not considered Past Due for the course, if the trainee has never completed an iteration of the course, COMPLIANT is 'N'.

Values:
Y (the trainee is compliant with the course requirement)
N (the trainee is not compliant with the course requirement)

A string that uniquely identifies the course, which is currently required for the trainee. See also CO_MAS_ID and COURSE_TITLE.

To retrieve the attributes of the course, query the LMS_COURSE_MASTER table where LMS_TRAINEE_COURSE_ASSIGN.COURSE_MASTER_ID = LMS_COURSE_MASTER.COURSE_MASTER_ID.

The COURSE_MASTER_ID may include letters and/or numerals and/or other characters. The letters in COURSE_MASTER_ID may be in upper case, lower case, or mixed case. Most COURSE_MASTER_ID values consist of four segments, in the following format:

1. the enterprise that developed the course, followed by an underscore. Examples: UNIV (the University); UPHS (the University of Pennsylvania Health System); SOM (the School of Medicine)
2. the intended audience for the course at the entity, school or center level, followed by an underscore. Examples: MED (Medicine); CPP (Clinical Practices); ALL (all entities)
3. the intended audience for the course at the level of the division within the enterprise, followed by an underscore. Examples: EHRS (Environmental Health and Radiation Safety); NURS (Nursing); ALL (all schools or divisions)
4. the course identifier code (determined by the course developer)

Examples: UNIV_ALL_ALL_PROFILER; UPHS_HUP_NURS_ORT131

Values:
Refer to the LMS_COURSE_MASTER
or COURSE_MASTER table for values.
Note:  some of the courses in those tables
have no associated LMS_TRAINEE_COURSE_ASSIGN
records.

A string that uniquely identifies the business unit that owns the course version cited in the mandatory course assignment that makes the course currently required for the trainee. See also COURSE_OWNER_BU_ID and COURSE_OWNER_BU_NUMBER.

If the trainee has multiple mandatory course assignments for the course that make it currently required for the trainee, the assignment with the lowest non-null value of DUE_DATE is the one whose COURSE_OWNER_BU_ID is stored in the LMS_TRAINEE_COURSE_ASSIGN table. (For all of the currently active mandatory course assignments for the trainee and course, see the LMS_TRAINEE_BU_COURSE_ASSIGN table.)

It is possible for more than one business unit to have the same name. See also COURSE_OWNER_BU_NAME and COURSE_OWNER_BU_NUMBER.

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

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.

The COURSE_OWNER_BU_NAME is the KNODE.NAME where LMS_TRAINEE_BU_COURSE_ASSIGN.COURSE_OWNER_BU_NUMBER = KNODE.KNODE_ID. For information on how the COURSE_OWNER_BU_NAME value is formatted, see the data element documentation for KNODE.NAME.

Examples: 'Environmental Health and Radiation Safety Parent'; 'Patient Registration' The value is stored without the surrounding quotes.

Values:
Refer to the KNODE table for values.
Note:  some of the business units in that table
have no associated LMS_TRAINEE_COURSE_ASSIGN records.

A phrase or string that describes the business unit that owns the course version cited in the mandatory course assignment that makes the course currently required for the trainee. It is possible for more than one business unit to have the same name. See also COURSE_OWNER_BU_ID and COURSE_OWNER_BU_NUMBER.

If the trainee has multiple mandatory course assignments for the course that make it currently required for the trainee, the assignment with the lowest non-null value of DUE_DATE is the one whose COURSE_OWNER_BU_NAME is stored in the LMS_TRAINEE_COURSE_ASSIGN table. (For all of the currently active mandatory course assignments for the trainee and course, see the LMS_TRAINEE_BU_COURSE_ASSIGN table.)

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

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.

The COURSE_OWNER_BU_NAME is the KNODE.NAME where LMS_TRAINEE_COURSE_ASSIGN.COURSE_OWNER_BU_NUMBER = KNODE.KNODE_ID. For information on how the COURSE_OWNER_BU_NAME value is formatted, see the data element documentation for KNODE.NAME.

Examples: 'Environmental Health and Radiation Safety Parent'; 'Patient Registration' The value is stored without the surrounding quotes.

Values:
Refer to the KNODE table for values.
Note:  some of the business units in that table
have no associated LMS_TRAINEE_COURSE_ASSIGN records.

The number used within the Knowledge Link system as the unique identifier for the business unit that owns the course version cited in the mandatory course assignment that makes the course currently required for the trainee. The COURSE_OWNER_BU_NUMBER value corresponds to a KNODE.KNODE_ID. See also COURSE_OWNER_BU_ID and COURSE_OWNER_BU_NAME.

If the trainee has multiple mandatory course assignments for the course that make it currently required for the trainee, the assignment with the lowest non-null value of DUE_DATE is the one whose COURSE_OWNER_BU_NUMBER is stored in the LMS_TRAINEE_COURSE_ASSIGN table. (For all of the currently active mandatory course assignments for the trainee and course, see the LMS_TRAINEE_BU_COURSE_ASSIGN table.)

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

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.

Example: 44922 (Environmental Health and Radiation Safety Parent [UP.EHRS])

Values:
Refer to the KNODE table for values.
Note:  some of the business units in that table
have no associated LMS_TRAINEE_COURSE_ASSIGN records.

COURSE_TITLE

Indexed - no
Format - varchar2 (256)
May be null? yes

The latest title of the course, which is currently required for the trainee. See also CO_MAS_ID and COURSE_MASTER_ID.

The latest title of the course is title of 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. It is not necessarily the same as the version to which the trainee has been assigned.) The course per se has no title specified in Knowledge Link, but the title of the course's latest version may be considered to be the current title of the course.

Most course version titles consist of three parts, in the following format: segments identify both the content and provider of the course version, and follow the following format:

1. text briefly identifying the course content
2. a dash ('-') with spaces on either side, to serve as a separator
3. the enterprise that developed the course. Examples: UNIV (the University); UPHS (the University of Pennsylvania Health System); SOM (the School of Medicine)

COURSE_TITLE is taken from the LMS_COURSE_MASTER table where LMS_TRAINEE_COURSE_ASSIGN.CO_MAS_ID = LMS_COURSE_MASTER.CO_MAS_ID. Further information about the course is available in the LMS_COURSE_MASTER table.

Example: 'Bloodborne Pathogens for Childcare Center Employees - EHRS' The value is stored without the surrounding quotes.

Values:
Refer to the LMS_COURSE_MASTER table for values.
Note:  some of the courses in that table
have no associated LMS_TRAINEE_COURSE_ASSIGN
records.

A Yes/No flag indicating whether or not the course is currently required for the trainee. 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, CURRENTLY_REQUIRED is 'Y'.

If the trainee has multiple mandatory course assignments for the course, the trainee is grandfathered for the course only if the trainee is grandfathered for all of the assignments. The process that reviews the multiple mandatory course assignments and loads the data for the trainee and course into the LMS_TRAINEE_COURSE_ASSIGN table ensures that the table includes only records for courses that are currently required for the trainees.

In other words, for all LMS_TRAINEE_COURSE_ASSIGN records, CURRENTLY_REQUIRED is 'Y'.

See also PENN_ID, FIRST_NAME, MIDDLE_INITIAL, LAST_NAME, COURSE_MASTER_ID, COURSE_TITLE, STATUS_DUEDATE_DESCRIPTION, and COMPLIANT.

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

One of the factors that determine whether Knowledge Link sends E-mail Notifications about a course to a trainee is whether the course is currently required for the trainee. For more information, see the data element documentation for COURSE_OBJECT.SD_BOUNDARY.

Values:
Y (the course is currently a required course for the trainee)
N (the course is currently an optional course for the trainee)

The date when the trainee is next due to complete an iteration of the currently required course. See also PERIODICITY.

If the trainee has multiple mandatory course assignments for the course that make it currently required for the trainee, the assignment with the lowest non-null value of DUE_DATE is the one whose DUE_DATE is stored in the LMS_TRAINEE_COURSE_ASSIGN table. (For all of the mandatory course assignments for the trainee and course, see the LMS_TRAINEE_BU_COURSE_ASSIGN table.)

DUE_DATE 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.
  • For information on the initial due dates for all of the mandatory course assignments for the course that make it currently required for the trainee, see LMS_GOVERNING.INITIAL_DUE_DATE where LMS_TRAINEE_BU_COURSE_ASSIGN.CURRENTLY_REQUIRED = 'Y' and 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.

DUE_DATE is one of the factors that determine:

• whether the trainee is compliant with regard to a currently required course. See STATUS_DUEDATE_DESCRIPTION and COMPLIANT.
• 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.

Example: 10/27/2009

Values:
List of values not available

The given name of the trainee (USER_REC_ID), stored in upper case to facilitate record selection conditions and sorting. See also MIDDLE_INITIAL and LAST_NAME.

The FIRST_NAME is taken from LMS_PERSON.FIRST_NAME where LMS_TRAINEE_COURSE_ASSIGN.USER_REC_ID = LMS_PERSON.USER_REC_ID. Further information about the trainee is available in the LMS_PERSON table.

Example: ABIGAIL

Values:
List of values not available.

The date and time when this record was extracted from Knowledge Link and loaded into the Data Warehouse.

Business Objects hint: by default, just the date will appear in your report, but you can change the format of the cell to show just the time, if you so desire.

Example: 8/29/2009 3:09:00 AM

Values:
List of values not available.

The surname of the trainee (USER_REC_ID), stored in upper case to facilitate record selection conditions and sorting. See also FIRST_NAME and MIDDLE_INITIAL.

The LAST_NAME is taken from LMS_PERSON.LAST_NAME where LMS_TRAINEE_COURSE_ASSIGN.USER_REC_ID = LMS_PERSON.USER_REC_ID. Further information about the trainee is available in the LMS_PERSON table.

Example: ADAMS

Values:
List of values not available

A Yes/No flag indicating whether or not there is a course assignment that assigns a version of the course as a mandatory course to a business unit that includes the trainee.

For all LMS_TRAINEE_COURSE_ASSIGN records, MANDATORY is 'Y'.

MANDATORY is one of the factors that determine whether the course is currently required for the trainee. See CURRENTLY_REQUIRED.

Values:
Y (the course is currently assigned to the trainee as 
   a mandatory course)
N (there currently is no mandatory assignment that 
   assigns the course to trainee)

The first letter of the middle name of the trainee (USER_REC_ID), stored in upper case. The value is null for some trainees. See also FIRST_NAME and LAST_NAME.

Note: although the value of MIDDLE_INITIAL ought to be an upper case letter (if it is not null), it can be a character of any type--for example, '-'.

The MIDDLE_INITIAL is taken from LMS_PERSON.MIDDLE_INITIAL where LMS_TRAINEE_COURSE_ASSIGN.USER_REC_ID = LMS_PERSON.USER_REC_ID. Further information about the trainee is available in the LMS_PERSON table.

Example: S

Values:
List of values not available

An 8-digit identification number assigned by the Penn Community system to the trainee. No two persons have the same Penn ID. See also USER_REC_ID, FIRST_NAME, MIDDLE_INITIAL, and LAST_NAME.

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 PENN_ID is taken from LMS_PERSON.PENN_ID where LMS_TRAINEE_COURSE_ASSIGN.USER_REC_ID = LMS_PERSON.USER_REC_ID. Further information about the trainee is available in the LMS_PERSON table.

Example: 12345678

Values:
Refer to the LMS_PERSON table for values.
Note:  some of the trainees in that table have
no associated LMS_TRAINEE_COURSE_ASSIGN records.

An integer (with no decimal places) indicating 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 PERIODICITY is null or 0, the trainee is required to complete the course only once.

If the trainee has multiple mandatory course assignments for the course that make it currently required for the trainee, the assignment with the lowest non-null value of DUE_DATE is the one whose PERIODICITY value is stored in the LMS_TRAINEE_COURSE_ASSIGN table. 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 all of the mandatory course assignments for the trainee and course, see the LMS_TRAINEE_BU_COURSE_ASSIGN table.)

Examples: 0, 12

Values:
List of values not available

A phrase describing when the trainee is next due to complete the course. See also DUE_DATE and PERIODICITY.

If the trainee has multiple mandatory course assignments for the course that make it currently required for the trainee, the assignment with the lowest non-null value of DUE_DATE is the one whose STATUS_DUEDATE_DESCRIPTION is stored in the LMS_TRAINEE_COURSE_ASSIGN table. (For all of the mandatory course assignments for the trainee and course, see the LMS_TRAINEE_BU_COURSE_ASSIGN table.)

STATUS_DUEDATE_DESCRIPTION is null if no initial due date was specified when the mandatory course was assigned, and the trainee has yet to complete an iteration of the course. To check the initial due date for all of the mandatory course assignments for the trainee and course, see LMS_GOVERNING.INITIAL_DUE_DATE where LMS_TRAINEE_BU_COURSE_ASSIGN.KCJ_REC_ID = LMS_GOVERNING.KCJ_REC_ID and LMS_TRAINEE_BU_COURSE_ASSIGN.CO_MAS_ID = LMS_TRAINEE_COURSE_ASSIGN.CO_MAS_ID and LMS_TRAINEE_BU_COURSE_ASSIGN.USER_REC_ID = LMS_TRAINEE_COURSE_ASSIGN.USER_RECS_ID

STATUS_DUEDATE_DESCRIPTION is one of the factors that determine:

• whether the trainee is compliant with regard to a currently required course. See COMPLIANT.
• 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.

Values:
[null] (there is no due date for this assignment) 
Neither Soon Due nor Past Due
Past Due
Soon Due

An upper case letter indicating whether or not there is an active course assignment that assigns a version of the course as a mandatory course to a business unit that includes the trainee.

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.

For all LMS_TRAINEE_COURSE_ASSIGN records, TRAINEE_CRS_ASSIGNMNT_STATUS is 'A' (active).

TRAINEE_CRS_ASSIGNMNT_STATUS is one of the factors that determine whether the course is currently required for the trainee. See CURRENTLY_REQUIRED.

Values:
A (there currently is an active mandatory assignment 
   that assigns the course to trainee)
I (there currently is no active mandatory assignment 
   that assigns the course to trainee)

The number used within the Knowledge Link system as the unique identifier for the trainee. See also PENN_ID, FIRST_NAME, MIDDLE_INITIAL, and LAST_NAME.

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_TRAINEE_COURSE_ASSIGN table to be joined to those tables.

Further information about the trainee is available in the LMS_PERSON table where LMS_TRAINEE_COURSE_ASSIGN.USER_REC_ID = LMS_PERSON.USER_REC_ID.

Example: 10159

Values:
Refer to the LMS_PERSON table or the 
  GEN21_USER table for values.
Note:  some of the trainees in those tables have 
no associated LMS_TRAINEE_BU_COURSE_ASSIGN records.