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

LM_CURRENT_TRAINING_STATUS Table

DWLMS Schema

Explanation
Tracks Knowledge Link users' (trainees') current status with regard to items and external events. There is one record for each user, for each item or external event that is currently assigned to the user or that is cited in the user's learning history.

An LM_CURRENT_TRAINING_STATUS record stores:

• the user’s name and Penn ID
• specifics about the item or external event: whether it is internal or external, its identifier (TRAINING_ID), its title, and (if it is an item) its item type, item ID, and the date when the item was last revised
• information on whether the item is currently assigned to the user; the dates when it was assigned, when it is required, and when a reminder will be sent (or was sent); whether there is a retraining requirement; and whether the user is compliant (per the user’s learning plan in Knowledge Link)
• current completion data, including a flag indicating whether the user completed the item or external event with credit, and the date and status for the latest completion. Note: see Cautions (below) for caveats regarding completion data.

Common Uses

• Reporting on users' current training status for all of their items and external events, regardless of whether the training is currently assigned
• Monitoring users' compliance status for all currently assigned courses, by user, by TRAINING_ID, or by assignment type (CURRENTLY_REQUIRED)
  

INTERNAL_OR_EXTERNAL
PENN_ID
TRAINING_ID

COMPLETED
COMPLETION_STATUS_DESC
COMPLIANT
CURRENTLY_ASSIGNED
CURRENTLY_REQUIRED
FIRST_NAME
INTERNAL_OR_EXTERNAL
ITEM_ID *
ITEM_REV_DATE *
ITEM_TYPE_ID
LAST_NAME
PENN_ID
TRAINING_ID
TRAINING_TITLE

* see Cautions (below) 
for caveats regarding
these indexes 

LM_COMPLETION_STATUS
LM_CURRICULUM_ITEM
LM_ITEM
LM_LEARNING_HISTORY
LM_REGISTRATION
LM_SCHEDULED_OFFERING
LM_USER
LM_USER_JOB
LM_USER_ORG
LM_USER_SUPERVISOR

Cautions

• As mentioned above, the LM_CURRENT_TRAINING_STATUS table stores information on users' current status with regard to items and external events. For example, if an item was once assigned as training the user was required to complete, but it is not currently required for the user, there is no way to tell that it was ever required for the user.
• ITEM_ID and ITEM_REV_DATE do not have their own indexes. Instead, there is a composite index for the combination of ITEM_TYPE_ID, ITEM_ID, and ITEM_REV_DATE. A query that filters records by ITEM_ID cannot use the composite index unless it also filters records by ITEM_TYPE_ID. A query that filters records by ITEM_REV_DATE cannot use the composite index unless it also filters records by ITEM_TYPE_ID and ITEM_ID.
• The LM_CURRENT_TRAINING_STATUS table stores some, but not all, of the attributes of the training--the item currently assigned to the user, or the item or external event cited in the user's learning history.
  • The combination of INTERNAL_OR_EXTERNAL and TRAINING_ID uniquely identifies the training. If INTERNAL_OR_EXTERNAL is 'Internal', TRAINING_ID identifies the item, and its value is the ITEM_TYPE_ID, a tilde (~), the ITEM_ID, another tilde, and the ITEM_REV_DATE in YYYYMMDD format. (COURSE~UP.91028.ITEM.PROFILER~20121001 is an example of this.) If INTERNAL_OR_EXTERNAL is 'External', TRAINING_ID is the description of the external event. This is usually the name of the event, but the value is whatever was entered when the learning event was recorded. (PNEG conference is an example of this.)
  • If INTERNAL_OR_EXTERNAL is 'Internal', TRAINING_TITLE is the item's title. If INTERNAL_OR_EXTERNAL is 'External', TRAINING_TITLE has the same value as TRAINING_ID.
  • If INTERNAL_OR_EXTERNAL is 'Internal', the other attributes of the item can be retrieved from the LM_ITEM table by joining the LM_CURRENT_TRAINING_STATUS table to it by TRAINING_ID.
• Some basic information about the user is provided.
  • The PENN_ID is an 8-digit identification number assigned to the user 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 LM_CURRENT_TRAINING_STATUS table stores the trainee's FIRST_NAME, MIDDLE_INITIAL, and LAST_NAME. Further information about the user is available in the LM_USER, LM_USER_JOB, LM_USER_ORG, and LM_USER_SUPERVISOR tables. The LM_CURRENT_TRAINING_STATUS table may be joined to these tables by PENN_ID.
• An item may be assigned to users by a Knowledge Link administrator, an Assignment Profile, an external system (such as the Penn Profiler), or by the users themselves when they self-assign an item or self-enroll in a scheduled offering of an item. If the TRAINING_ID refers to an item that is currently assigned to the user (CURRENTLY_ASSIGNED is 'Y'), it may be assigned in Knowledge Link one or more times as an item, or as a component of one or more assigned curricula, or both. If an item is assigned to the user more than once,
  • CURRENTLY_REQUIRED is 'Y' if the assignment type for at least one of the assignments is 'Required'; otherwise, it is 'N'.
  • All assignment-related date values might or might not include the time. They are null if the TRAINING_ID refers to an item that is not currently assigned to the user, or if it refers to an external event.
    • ASSIGNMENT_DATE is the date when the latest (most recent) assignment was made.
    • REMINDER_DATE is the earliest one that falls after the LAST_EXTRACT_DATE; if all of the reminder dates fall on or before the LAST_EXTRACT_DATE, REMINDER_DATE is the latest one.
    • REQUIRED_DATE is the earliest date.
  • RETRAINING_INTERVAL and EXPIRATION_DATE are used only if the item is assigned with retraining requirements; in other cases, they are null.
    • If the RETRAINING_INTERVAL is null or 0, the user is required to complete the TRAINING_ID only once; otherwise, the user has a retraining requirement for the TRAINING_ID. (Because information on the retraining basis and period is not available, the RETRAINING_INTERVAL cannot be used to calculate the EXPIRATION_DATE.)
    • In addition to the cases mentioned above, EXPIRATION_DATE is null if the user has not completed the item with credit. When it is not null, EXPIRATION_DATE might or might not be the same as the REQUIRED_DATE.
    • An item may be assigned more than once as a component of multiple curricula, and these assignments may have different retraining requirements. In such cases,
      • EXPIRATION_DATE is the earliest date. It might or might not be the same as the REQUIRED_DATE.
      • RETRAINING_INTERVAL is the lowest non-zero value. (Because information on the retraining basis and period is not available, the RETRAINING_INTERVAL cannot be used to calculate the EXPIRATION_DATE.)
• The information on the user's completion of the TRAINING_ID depends on whether the trainee has ever completed it with credit. If the user has completed the TRAINING_ID, but never with credit, the completion data reflects the latest completion; otherwise, it reflects the latest completion with credit. For further cautions regarding completion data, see COMPLETED, COMPLETION_DATE, COMPLETION_STATUS_ID, and COMPLETION_STATUS_DESC.
• The value of the COMPLIANT flag reflects the state of the TRAINING_ID per the user’s learning plan in Knowledge Link:
  • if the TRAINING_ID is not currently assigned to the user, COMPLIANT is 'NA' (not applicable). Otherwise,
  • if the TRAINING_ID is currently assigned as an item, and the user has not completed the item with credit since the assignment was made, COMPLIANT is 'N' (the user is not compliant). Otherwise,
  • if the TRAINING_ID is currently assigned as a curriculum component, and the user has never completed the item with credit, COMPLIANT is 'N'. Otherwise,
  • if the user is overdue to complete the TRAINING_ID (the REQUIRED_DATE has passed), COMPLIANT is 'N'. Otherwise,
  • COMPLIANT is 'Y' (the user is compliant).

Note: an item that is currently assigned to a user is not necessarily an item that the user is required to complete with credit. When monitoring training status, you might want to check not only the COMPLIANT flag, but also the CURRENTLY_REQUIRED flag.

• The LAST_EXTRACT_DATE stores the date and time when the LM_CURRENT_TRAINING_STATUS record was last extracted from Knowledge Link and loaded into the Data Warehouse. It is not necessarily the date that the table was last refreshed. To determine when the table was last refreshed, select max(LAST_EXTRACT_DATE).
• The list of related tables shown above includes some that should be joined to the LM_CURRENT_TRAINING_STATUS table only with caution, if at all. For example,
  • The LM_CURRICULUM_ITEM table does not include the TRAINING_ID. LM_CURRENT_TRAINING_STATUS records where INTERNAL_OR_EXTERNAL is 'Internal' ought to be joined to the LM_CURRICULUM_ITEM table based on ITEM_TYPE_ID, ITEM_ID, and ITEM_REV_DATE. However, the join results in a many-to-many relationship. Because more than one user may have an LM_CURRENT_TRAINING_STATUS record for the same item, and an item may be a component of multiple curricula, add other filters on the LM_CURRENT_TRAINING_STATUS table and/or the LM_CURRICULUM_ITEM table to make the query results more useful. Bear in mind that if a user is assigned to an item that is a component of one or more curricula, that does not necessarily mean that any of those curricula have been assigned to the user.
  • Although the LM_CURRENT_TRAINING_STATUS table may be joined to LM_REGISTRATION table based on PENN_ID, that would result in a many-to-many relationship. LM_REGISTRATION records cite the scheduled offering, not the item, so you would probably want to join the LM_REGISTRATION table (r) to the LM_SCHEDULED_OFFERING table (o) based on SCHEDULED_OFFERING_ID, and use a filter where r.PENN_ID || '~' || o.ITEM_TYPE_ID || '~' || o.ITEM_ID || '~' || to_char(o.ITEM_REV_DATE,'YYYYMMDD') is in the list of PENN_ID || '~' || TRAINING_ID per a subquery of LM_CURRENT_TRAINING_STATUS records that have one or more specified TRAINING_IDs and/or meet other criteria. This still will retrieve more than one registration per user/item if the user has registered for more than one offering of the item, so you might want to add other filters on the LM_REGISTRATION table and/or the LM_SCHEDULED_OFFERING table.
  • When the LM_CURRENT_TRAINING_STATUS table (c) is joined to the LM_SCHEDULED_OFFERING table (o) where c.TRAINING_ID = o.ITEM_TYPE_ID || '~' || o.ITEM_ID || '~' || to_char(o.ITEM_REV_DATE,'YYYYMMDD'), the result is a many-to-many relationship. Add other filters on the LM_CURRENT_TRAINING_STATUS table and/or the LM_SCHEDULED_OFFERING table to make the query results more useful.
  • The LM_CURRENT_TRAINING_STATUS table may be joined to the LM_USER_JOB table, the LM_USER_ORG table, and/or the LM_USER_SUPERVISOR table based on PENN_ID, but because the LM_USER_ tables can have more than one record per PENN_ID, be sure to filter the joined LM_USER_ table(s) so that you use just one record per user and avoid a many-to-many relationship.

Source

Knowledge Link, the learning management system used by the University and by the University of Pennsylvania Health System (UPHS).

Knowledge Link is used to track information on training that is required per Federal, State, University, and UPHS regulations and policies. It also tracks information on optional training.  Knowledge Link enables authorized administrators to create online courses, schedule offerings of instructor-led training, and assign training to trainees.  It enables trainees to find out what training has been assigned to them, register for scheduled offerings, take on-line courses, and provide feedback via course evaluations.

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