• ASSIGNMENT_DATE
• COMPLETED
• COMPLETION_DATE
• COMPLETION_STATUS_DESC
• COMPLETION_STATUS_ID
• COMPLIANT
• CURRENTLY_ASSIGNED
• CURRENTLY_REQUIRED
• EXPIRATION_DATE
• FIRST_NAME
• INTERNAL_OR_EXTERNAL
• ITEM_ID

• ITEM_REV_DATE
• ITEM_TYPE_ID
• LAST_EXTRACT_DATE
• LAST_NAME
• MIDDLE_INITIAL
• PENN_ID
• REMINDER_DATE
• REQUIRED_DATE
• RETRAINING_INTERVAL
• TRAINING_ID
• TRAINING_TITLE

If the TRAINING_ID refers to an item that is currently assigned to the user (PENN_ID), this is the date when the assignment was made. The value might or might not include the time. ASSIGNMENT_DATE is 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.

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.

An item may be assigned to a user more than once. For example, say that Basic Jabberwock Safety was assigned to Alison Wonderland as an item on 11/20/2012, and as a component of the Tulgey Wood Safety curriculum on 1/15/2013. When the item is assigned to the user more than once, ASSIGNMENT_DATE is the date when the latest (most recent) assignment was made. In this example, Alison's ASSIGNMENT_DATE for Basic Jabberwock Safety is 1/15/2013.

Note: an item that is currently assigned to a user is not necessarily an item that the user is required to complete with credit.

See also CURRENTLY_ASSIGNED, CURRENTLY_REQUIRED, COMPLIANT, and INTERNAL_OR_EXTERNAL.

Business Objects hints:

• By default, just the date will appear in your report, but you can change the format of the cell to include the time, if you so desire.
• To select records for a particular date (say, 11/29/2012), set your query conditions to ASSIGNMENT_DATE is greater than or equal to 11/29/2012 and ASSIGNMENT_DATE is less than 11/30/2012. (Business Objects will supply the time--12:00:00 AM--for both dates.)

Examples: 11/30/2012; 12/8/2012 11:50:00 PM

Values:  List of values not available   

A Yes/No flag indicating whether the user (PENN_ID) has ever completed the TRAINING_ID with credit. All records for external events are considered completions with credit.

Note:

• If COMPLETED is 'N', the user will nevertheless have completion data if he or she completed the TRAINING_ID without credit.
• If COMPLETED is 'Y', that does not necessarily mean that the user is compliant with regard to the TRAINING_ID.

See also INTERNAL_OR_EXTERNAL, COMPLETION_DATE, COMPLETION_STATUS_ID, COMPLETION_STATUS_DESC, and COMPLIANT.

Values:
Y - the user completed the TRAINING_ID with credit 
N - the user did not complete the TRAINING_ID with credit

If the user has ever completed the TRAINING_ID with credit, this is the latest (most recent) date when the user completed it with credit. If the user has completed the TRAINING_ID but has never completed it with credit, COMPLETION_DATE is the latest date when the user completed it without credit. The value might or might not include the time. COMPLETION_DATE is null if the user has never completed the TRAINING_ID.

See also COMPLETED, COMPLETION_STATUS_ID, and COMPLETION_STATUS_DESC.

Business Objects hints:

• By default, just the date will appear in your report, but you can change the format of the cell to include the time, if you so desire.
• To select records for a particular date (say, 11/29/2012), set your query conditions to COMPLETION_DATE is greater than or equal to 11/29/2012 and COMPLETION_DATE is less than 11/30/2012. (Business Objects will supply the time--12:00:00 AM--for both dates.)

Examples: 11/30/2012; 12/8/2012 11:50:00 PM

Values:  List of values not available 

A word or phrase describing the status of the completion of the TRAINING_ID by the user (PENN_ID) on the COMPLETION_DATE. The value is stored in mixed case. All records for external events have 'Complete' as the value for COMPLETION_STATUS_DESC.

Note: if the COMPLETION_STATUS_DESC is 'Complete', it does not necessarily mean that the user is compliant with regard to the TRAINING_ID.

See also COMPLETED, COMPLETION_DATE, COMPLETION_STATUS_ID, COMPLIANT, INTERNAL_OR_EXTERNAL.

Examples: Complete, Exempt, Incomplete

Values:
Refer to the LM_COMPLETION_STATUS table for values.

An alphabetic code that identifies the status of the completion of the TRAINING_ID by the user (PENN_ID) on the COMPLETION_DATE. The value is stored in upper case. All records for external events have 'EXTERNAL-COMPLETE' as the value for COMPLETION_STATUS_ID.

Note:

• If the COMPLETION_STATUS_ID is like '%-COMPLETE', that does not necessarily mean that the user is compliant with regard to the TRAINING_ID.
• Most COMPLETION_STATUS_ID values include a hyphen, and have no embedded spaces. There are two exceptions to this rule:
  • Both 'COURSE-INCOMPLETE' (with no embedded spaces) and 'COURSE -INCOMPLETE' (with one space between the E and the hyphen) are valid values for COMPLETION_STATUS_ID. 
  • 'COURSE_SUB' is a valid value for COMPLETION_STATUS_ID, but 'COURSE-SUB' is not.

See also COMPLETED, COMPLETION_DATE, COMPLETION_STATUS_DESC, and COMPLIANT.

Examples: INSERVICE-COMPLETE, COURSE-EXEMPT, COURSE-INCOMPLETE

Values:
Refer to the LM_COMPLETION_STATUS table for values.

An alphabetic code that indicates whether the user (PENN_ID) is compliant with regard to the TRAINING_ID. 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.

See also CURRENTLY_ASSIGNED, COMPLETED, EXPIRATION_DATE, and CURRENTLY_REQUIRED.

Values:
Y - the TRAINING_ID is currently assigned to the user, who
    has completed it with credit and either is not required to 
    do so again or is not overdue to do so again
N - the TRAINING_ID is currently assigned to the user, who
    has never completed it with credit or must complete it with
    credit again
NA - the TRAINING_ID is not currently assigned to the user

A Yes/No flag indicating whether the TRAINING_ID is currently assigned to the user (PENN_ID). CURRENTLY_ASSIGNED is 'N' if the TRAINING_ID refers to an item that is not currently assigned to the user, or if it refers to an external event.

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.

Note: an item that is currently assigned to a user is not necessarily an item that the user is required to complete with credit.

See also ASSIGNMENT_DATE, CURRENTLY_REQUIRED, COMPLIANT, and INTERNAL_OR_EXTERNAL.

Values:
Y - the TRAINING_ID is currently assigned to the user
N - the TRAINING_ID is not currently assigned to the user

A Yes/No flag indicating whether the TRAINING_ID is currently assigned to the user (PENN_ID) as a required ('Y') or optional ('N') item. Also, CURRENTLY_REQUIRED is 'N' if the TRAINING_ID refers to an item that is not currently assigned to the user, or if it refers to an external event.

An item may be assigned to a user more than once, and these assignments may have different assignment types. For example, say that Basic Jabberwock Safety was assigned to Alison Wonderland as an optional item and as a required component of the Tulgey Wood Safety curriculum. When the item is assigned to the user more than once, if the assignment type for at least one of them is 'Required', CURRENTLY_REQUIRED is 'Y'. In this example, the value for Alison's CURRENTLY_REQUIRED flag for Basic Jabberwock Safety is 'Y'.

See also CURRENTLY_ASSIGNED, ASSIGNMENT_DATE, COMPLIANT, and INTERNAL_OR_EXTERNAL.

Values:
Y - the TRAINING_ID is currently assigned to the user
    as a required item
N - the TRAINING_ID is either currently assigned to the 
    user as an optional item, or is not currently assigned to
    the user

If the TRAINING_ID refers to an item that is currently assigned as a curriculum component with a retraining requirement, and the user (PENN_ID) has completed the item with credit, this is the date when the user's credential for the item expires. The value includes the time. EXPIRATION_DATE is null if the user has not completed the item with credit, or has no retraining requirement for the item. Also, EXPIRATION_DATE is null if the TRAINING_ID refers to an external event or to an item that is not currently assigned to the user.

An item may be assigned more than once as a component of multiple curricula, and these assignments may have different retraining requirements. For example, say that, on 2/14/2012, Alison Wonderland completed Basic Jabberwock Safety, which is assigned to her as a component of the Tulgey Wood Safety curriculum that must be completed every 3 years, and as a component of the Carroll Animal Handling curriculum that must be completed every year. When the item is assigned to the user more than once as a component of multiple curricula, EXPIRATION_DATE is the earliest date. In this example, Alison's EXPIRATION_DATE for Basic Jabberwock Safety is 2/14/2013 (one year after she completed it).

The EXPIRATION_DATE might or might not be the same as the REQUIRED_DATE.

See also RETRAINING_INTERVAL and REQUIRED_DATE.

Business Objects hints:

• By default, just the date will appear in your report, but you can change the format of the cell to include the time, if you so desire.
• To select records for a particular date (say, 11/29/2012), set your query conditions to EXPIRATION_DATE is greater than or equal to 11/29/2012 and EXPIRATION_DATE is less than 11/30/2012. (Business Objects will supply the time--12:00:00 AM--for both dates.)

Examples: 11/30/2012; 12/8/2012 11:50:00 PM

Values:  List of values not available

The given name of the user (PENN_ID), stored in upper case to facilitate filtering and sorting records. See also MIDDLE_INITIAL and LAST_NAME.

The FIRST_NAME is taken from LM_USER.FIRST_NAME where LM_CURRENT_TRAINING_STATUS.PENN_ID = LM_USER.PENN_ID. Further information about the user is available in the LM_USER table.

Example: ABIGAIL

Values:  List of values not available.

Text indicating whether the TRAINING_ID refers to an item or to an external event. The value is in mixed case.

An item is an assignable unit whose assignment and completion can be tracked internally (in Knowledge Link).  It is usually a course, but it may be a learning or non-learning activity.  An external event does not have an item record in Knowledge Link, but does have one or more learning history records tracking users' attendance or completion.

An LM_CURRENT_TRAINING_STATUS record is uniquely identified by the combination of PENN_ID, TRAINING_ID, and INTERNAL_OR_EXTERNAL.

See also TRAINING_ID, ITEM_TYPE_ID, ITEM_ID, and ITEM_REV_DATE.

Values:
Internal - the TRAINING_ID refers to an item
External - the TRAINING_ID refers to an external event

Part two of the three part item key, the combination of data elements that uniquely identifies an item in Knowledge Link. Note: ITEM_ID does not have its own index. 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.

The ITEM_ID identifies the topic that the item covers. The value is null if the TRAINING_ID refers to an external event.

An item is an assignable unit whose assignment and completion can be tracked in Knowledge Link.  It is usually a course, but it may be a learning or non-learning activity. 

See also ITEM_TYPE_ID, ITEM_REV_DATE, TRAINING_ID, and INTERNAL_OR_EXTERNAL.

The ITEM_ID may include letters and/or numerals and/or other characters. The letters may be in upper case, lower case, or mixed case. Although Knowledge Link does not validate ITEM_ID values, most values follow a standard format that includes four segments separated by periods. The standard format is EE.#####.ITEM.COURSECODE, where

• EE is the two character abbreviation identifying the entity that offers the item (UP for the University or HS for the Health System).
• ##### is the five digit number that identifies the provider. For UP items, the first two digits are the school/center code (using the same code set as the University's General Ledger), and the last three digits identify the course owner. For example, 40007 is the Perelman School of Medicine (school/center 40) Office of Continuing Medical Education (007).
• ITEM is the value for the third segment of ITEM_IDs that follow the standard format.
• COURSECODE is a code that the provider uses to identify the item. For ITEM_IDs that follow the standard format, the COURSECODE segment is up to 24 characters long. For example, FNS_INSRVTH412 is the code used by the Perelman School of Medicine's Office of Continuing Medical Education to identify the in-service called Use of Thermometers Inservice - PAH.

Examples: UP.91028.ITEM.PROFILER (PennProfiler-Required Training Assessment-VPR); HS.70001.ITEM.NURSED107 (Sim Center Nurse Training - PAH)

Values:
Refer to the LM_ITEM table for values.

Part three of the three part item key, the combination of data elements that uniquely identifies an item in Knowledge Link. Note: ITEM_REV_DATE does not have its own index. 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_REV_DATE cannot use the composite index unless it also filters records by ITEM_TYPE_ID and ITEM_ID.

The ITEM_REV_DATE is the date when the item was last revised. The value might or might not include the time. ITEM_REV_DATE is null if the TRAINING_ID refers to an external event.

An item is an assignable unit whose assignment and completion can be tracked in Knowledge Link.  It is usually a course, but it may be a learning or non-learning activity.

Knowledge Link first ran on the legacy Learning Management System platform (Gen21), then moved to its current platform (SuccessFactors, in use since October 9, 2012). Courses that migrated from the legacy platform to the current platform as items were assigned 10/1/2012 as their ITEM_REV_DATE. This value does not include the time.

ITEM_REV_DATE is intended for use in tracking different versions of an item, so that all of the versions of an item can have the same ITEM_TYPE_ID and ITEM_ID, and so the versions can be distinguished from each other by their different ITEM_REV_DATEs.

See also ITEM_TYPE_ID, ITEM_ID, TRAINING_ID, and INTERNAL_OR_EXTERNAL.

Business Objects hints:

• By default, just the date will appear in your report, but you can change the format of the cell to include the time, if you so desire.
• To select records for a particular date (say, 11/29/2012), set your query conditions to ITEM_REV_DATE is greater than or equal to 11/29/2012 and ITEM_REV_DATE is less than 11/30/2012. (Business Objects will supply the time--12:00:00 AM--for both dates.)

Examples: 10/1/2012; 11/28/2012 1:07:00 PM

Values:
List of values not available.

Part one of the three part item key, the combination of data elements that uniquely identifies an item in Knowledge Link. The ITEM_TYPE_ID is a word that identifies the item type, which is used to categorize items. The value is stored in upper case. ITEM_TYPE_ID is null if the TRAINING_ID refers to an external event.

An item is an assignable unit whose assignment and completion can be tracked in Knowledge Link.  It is usually a course, but it may be a learning or non-learning activity. 

Each ITEM_TYPE_ID has a set of one or more completion status values that can be used to indicate whether and how users have completed the item. The reference table for the completion status values is LM_COMPLETION_STATUS.

See also ITEM_ID, ITEM_REV_DATE, TRAINING_ID, and INTERNAL_OR_EXTERNAL.

Examples: COURSE, INSERVICE

Values:
Refer to the LM_COMPLETION_STATUS table for values.

The latest (most recent) date when the record was extracted from Knowledge Link and imported into the Data Warehouse or refreshed in the Data Warehouse. The value includes the time.

LAST_EXTRACT_DATE indicates when the record was last refreshed, but it does not necessarily indicate when the LM_CURRENT_TRAINING_STATUS table was last refreshed. To determine when the table was last refreshed, select max(LAST_EXTRACT_DATE).

Business Objects hints:

• By default, just the date will appear in your report, but you can change the format of the cell to include the time, if you so desire.
• To select records for a particular date (say, 11/29/2012), set your query conditions to LAST_EXTRACT_DATE is greater than or equal to 11/29/2012 and LAST_EXTRACT_DATE is less than 11/30/2012. (Business Objects will supply the time--12:00:00 AM--for both dates.)

Example: 11/21/2012 3:45:05 AM

Values:
List of values not available.

The surname of the user (PENN_ID), stored in upper case to facilitate filtering and sorting records. See also FIRST_NAME and MIDDLE_INITIAL.

The LAST_NAME is taken from LM_USER.LAST_NAME where LM_CURRENT_TRAINING_STATUS.PENN_ID = LM_USER.PENN_ID. Further information about the user is available in the LM_USER table.

Example: ADAMS

Values:
List of values not available.

The first letter of the user's (PENN_ID's) middle name, stored in upper case. The value is null for some LM_CURRENT_TRAINING_STATUS records.

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 LM_USER.MIDDLE_INITIAL where LM_CURRENT_TRAINING_STATUS.PENN_ID = LM_USER.PENN_ID. Further information about the user is available in the LM_USER table.

Example: S

Values:
List of values not available.

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

An LM_CURRENT_TRAINING_STATUS record is uniquely identified by the combination of PENN_ID, TRAINING_ID, and INTERNAL_OR_EXTERNAL.

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.

Example: 12345678

Values:
Refer to the LM_USER table for values.
Note:  some of the users in that table
have no associated LM_CURRENT_TRAINING_STATUS
records.

The next (or latest) date when Knowledge Link will send (or sent) the user (PENN_ID) an e-mail message warning that the TRAINING_ID is soon due or past due. REMINDER_DATE is null if the REQUIRED_DATE is null. Also, REMINDER_DATE is null if the TRAINING_ID refers to an external event or to an item that is not currently assigned to the user.

An item may be assigned to a user more than once. (For example, Basic Jabberwock Safety may be assigned to Alison Wonderland both as an item and as a component of the Tulgey Wood Safety curriculum.) When the item is assigned to the user more than once, 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.

Business Objects hints:

• By default, just the date will appear in your report, but you can change the format of the cell to include the time, if you so desire.
• To select records for a particular date (say, 11/29/2012), set your query conditions to REMINDER_DATE is greater than or equal to 11/29/2012 and REMINDER_DATE is less than 11/30/2012. (Business Objects will supply the time--12:00:00 AM--for both dates.)

Example: 11/21/2012 3:45:05 AM

Values:
List of values not available.

The date by which the user (PENN_ID) must complete the item (TRAINING_ID), per the user's learning plan in Knowledge Link. The learning plan also uses the REQUIRED_DATE to determine the number of days remaining  for the user to complete the item.

REQUIRED_DATE is 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. Also, REQUIRED_DATE is null if the item was assigned to the user with no required date.

An item may be assigned to a user more than once. For example, say that Basic Jabberwock Safety was assigned to Alison Wonderland as an item with a required date of 12/20/2012, and as a component of the Tulgey Wood Safety curriculum with a required date of 2/15/2013. When the item is assigned to the user more than once, REQUIRED_DATE is the earliest date. In this example, Alison's REQUIRED_DATE for Basic Jabberwock Safety is 12/20/2012.

If the TRAINING_ID refers to an item that is currently assigned as a curriculum component with a retraining requirement, the EXPIRATION_DATE might or might not be the same as the REQUIRED_DATE.

See also INTERNAL_OR_EXTERNAL, COMPLIANT, RETRAINING_INTERVAL and EXPIRATION_DATE.

Business Objects hints:

• By default, just the date will appear in your report, but you can change the format of the cell to include the time, if you so desire.
• To select records for a particular date (say, 11/29/2012), set your query conditions to REQUIRED_DATE is greater than or equal to 11/29/2012 and REQUIRED_DATE is less than 11/30/2012. (Business Objects will supply the time--12:00:00 AM--for both dates.)

Example: 11/21/2012 3:45:05 AM

Values:
List of values not available.

An integer (with no decimal places) indicating whether the user (PENN_ID) has a retraining requirement for the TRAINING_ID. When CURRENTLY_ASSIGNED is 'Y', 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.) RETRAINING_INTERVAL is null when CURRENTLY_ASSIGNED is 'N'.

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, RETRAINING_INTERVAL is the lowest non-zero value. For example, say that the Tumtum Tree Primer is assigned to Alison Wonderland as a component of the Tumtum Tree Management curriculum that must be completed every 3 years (retraining interval 3), as a component of the Bandersnatch Safety curriculum that must be completed every year (retraining interval 1), and as a component of the Jubjub Bird Orientation curriculum that must be completed only once (retraining interval 0). In this example, Alison's RETRAINING_INTERVAL for the Tumtum Tree Primer is 1.

See also EXPIRATION_DATE.

Examples: 0, 1

Values: 
List of values not available.

A character string that identifies the item or external event. A user (PENN_ID) has a LM_CURRENT_TRAINING_STATUS record for a TRAINING_ID if the user has learning history for it, or if it is an item that is currently assigned to the user.

The TRAINING_ID may include letters and/or numerals and/or other characters. The letters may be in upper case, lower case, or mixed case. If the TRAINING_ID refers to an item, the value is the ITEM_TYPE_ID, a tilde (~), the ITEM_ID, another tilde, and the ITEM_REV_DATE in YYYYMMDD format. If the TRAINING_ID refers to an external event, the value 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.

An LM_CURRENT_TRAINING_STATUS record is uniquely identified by the combination of PENN_ID, TRAINING_ID, and INTERNAL_OR_EXTERNAL.

See also ITEM_TYPE_ID, ITEM_ID, ITEM_REV_DATE, INTERNAL_OR_EXTERNAL, and TRAINING_TITLE.

Examples: COURSE~UP.91028.ITEM.PROFILER~20121001 (the item PennProfiler-Required Training Assessment-VPR); PNEG conference (an external event)

Values:
List of values not available.

The name of the item or external event (TRAINING_ID). A user (PENN_ID) has a LM_CURRENT_TRAINING_STATUS record for a TRAINING_ID if the user has learning history for it, or if it is an item that is currently assigned to the user.

The TRAINING_TITLE may include letters and/or numerals and/or other characters. The letters may be in upper case, lower case, or mixed case. If the TRAINING_ID refers to an item, the TRAINING_TITLE is the item's title. If the TRAINING_ID refers to an external event, the TRAINING_TITLE is the same as the TRAINING_ID, which 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.

Examples: PennProfiler-Required Training Assessment-VPR (an item); PNEG conference (an external event)

Values:
List of values not available.