eLearning Data Extract

							eLearning Data Extract

        SkillSoft
    Overview
     Part of the 2011 CSU Synergy Project
     Collaborative team effort across various groups:
         Environmental Health & Safety (EHS)
         Systemwide Professional Development (SPD)
         Human Resources Management (HRM)
         Risk Management
         San Diego State University – provided the initial file format
         Identity Access Management
         CMS-HR
         Third Party Vendor: SkillSoft
     Expected to be delivered in two phases
       Phase I: deliver project/objects to load initial file with vendor, SkillSoft
       Phase II: automate updates using the SkillSoft API tool


2                                                                               4/19/2012
    Implementation contacts
     Contact information located on page 19 of the posted
      BPG/Implementation guide – Section 5

     Systemwide Professional Development
       Robin Innes
     HRM Technology
       Tammy Hines
     Identity Access Management
       Michael Trullinger



3                                                            4/19/2012
    CMS delivered objects: MP 4.01
     New field on CSU Installation table to track a unique
      “campus code” by business unit
     Run Control – select Business Unit
     SQR – CSUHR014
     New Records:
       CSU_ELX_HISTORY
       CSU_ELX_EXTRACT
     Above records delivered on the custom query tree for audit
      purposes


4                                                             4/19/2012
    General Business Process
     Campus enters “campus code” for business unit on the CSU
        Installation table record
         Campus code to use is located in Appendix A of the BPG
     Review how your business email address is tracked and
        update as appropriate
       Generate the initial load by running the CSUHR014.sqr
       Save the file in the csv format (comma-delimited)
       Contact Tammy Hines for help with loading this to SkillSoft
       Schedule the CSUHR014 to run monthly or as directed by
        the Systemwide Professional Development group

5                                                                  4/19/2012
    Mandatory One-Time Campus Setup
     Appendix A of the BPG lists the codes used in this process:
       Campus Code
       SkillSoft Group Code
     Add your Campus Code by
      Business Unit to the
      CSU Installation Table
     Navigation:
        Main Menu  Set Up HRMS 
        CSU Product Related Setup 
        CSU General Setup 
        CSU General Setup
6                                                            4/19/2012
    General Selection Criteria
     Broad selection for the data extract
     Must be an Active Employee
       PER_ORG = ‘EMP’
       HR_STATUS = ‘A’
     Not working in Jobcodes assigned to Union Codes:
       E99
       R11




7                                                        4/19/2012
    Validations
     Campus Code:
       Cannot be blank
       Cannot be set up for multiple business units
       Must be a code listed in Appendix A
         SkillSoft Group Code is hard coded and required

     CSUHR014.sqr can only run ONCE per day




8                                                           4/19/2012
    Extract Fields
     There are 14 fields sent in the data extract to SkillSoft.
     Some are sent as null values (indicated below)
     One row of data is sent per employee

    1.   User Name                      8.    Role - null
    2.   Password - null                9.    Empl ID
    3.   Force Password Change - null   10.   Department ID
    4.   Status                         11.   Job Code
    5.   First Name                     12.   Campus
    6.   Last Name                      13.   Group Membership
    7.   Email Address                  14.   Group Operation - null
9                                                                  4/19/2012
     User Name
      The run control will ask for the business unit to process
      Based on the business unit, the campus code from the CSU
       Installation table will be used to create the User Name
      This structure will be consistent with the CSU Identity
       system
      Structure:
        2 digit campus code
         +
        EMPLID
           Assumed a 9 digit numeric EMPLID
           If less than 9 digits, will zero fill with leading zeros to equal 9
           If more than 9 or contains alpha characters, please contact HRM
10                                                                                4/19/2012
     Email Address
      This is the baseline delivery of how the eMail Address will be
       reported:
        Will first look and report the email address if seen in
         PSOPRDEFN.EMAILID
        If there is nothing found, then we will look at
         EMAIL_ADDRESSES.EMAIL_ADDR in this E_ADDR_TYPE
         order:
               BUSN
               OCMP
               CAMP
        In the event where after reviewing all of the above, no email
         address is found, a null value will be reported.
11                                                                 4/19/2012
     Department ID and Job Code
      For each active eligible multiple job, one row of data is reported
       in the extract
      The Department and/or Job Codes could be the same or could
       be different for multiple jobs
      The program reports in one field the eligible active
       Departments and Job Codes separated with a backslash (\) in
       the ERN order.
      Example:
        ERN Dept ID Job Code        Department ID:
       0    1234    2333               1234\5627\4512
       1    5627    3214
                                   Job Code:
       2    4512    1224
12                                     2333\3214\1224          4/19/2012
     Run Control
      Navigation:
       Main Menu Workforce Admnistration  CSUWorkforce Admin Process  CSU Regulatory
       Reporting  Run eLearning Extract




      Schedule the CSUHR014 SQR to run monthly or as directed by
       HRM
13                                                                                4/19/2012
     View Log/Trace
                       After the process runs to
                        SUCCESS, link to the
                        Details to View
                        Log/Trace files
                       Select “View Log/Trace”
                        link




14                                         4/19/2012
     View Log/Trace
      User should see three links in the File List:
        %.csv
        %.log
        %.out
      If the CSV link is missing, link on the OUT file to view
       messages
      Error messages will be displayed




15                                                            4/19/2012
     View Log/Trace

                       Open the OUT file
                        even if all three links
                        are available
                       Other data issues are
                        listed
                       Total counts included
                        in file displayed at the
                        bottom of the list


16                                      4/19/2012
     View Log/Trace
      Sample of the top of the file:




      Sample of end of the file:




17                                      4/19/2012
     The CSV File
      As part of the CSUHR014 SQR, a file in the CSV format is
       generated each time this is run
      For the initial file load, Campus is required to save this in the
       CSV format and send to HRM for assistance in loading the
       initial file with SkillSoft
      There is no need to open the file
      If you do plan to open it, recommend that you open it with
       Notepad to avoid Excel changing the structure of some of the
       fields when you resave it.



18                                                               4/19/2012
     The CSV File
      Click on the CSV link
      When prompted,
       select SAVE
      Change the extension
       from .XLS to .CSV
      Change “Save as type”
       to All Files




19                             4/19/2012
     CSU_ELX_HISTORY
      The CSUHR014 SQR reports all active employees to the
       CSU_ELX_HISTORY record by system date of the run
      We have set this to run only ONCE a day
      The program simply compares the list of active employees
       from the current day’s run to the history or previous list of
       active employees in the CSU_ELX_HISTORY record
      The CSV resulting values is tracked in the record:
       CSU_ELX_EXTRACT. This record will be used in Phase II
       for the automation of updates to SkillSoft



20                                                              4/19/2012
     CSU_ELX_HISTORY
      The program only retains the current list of active employees
       and the previously runned list of active employees for the
       comparison
      Activate (status = 1)
         Employee exists in the current list of active employees, but did not
          exist in the previous list of active employees
         Employee has a change in the text string between the current list and
          previous list that would warrant an update to data: First or Last Name,
          Email Address, Department ID, or Job Code
      De-activate (status = 0)
        Employee no longer exists in the list of current active employees, but
         did exist in the previous list of active employees
21                                                                        4/19/2012
     CSU_ELX_EXTRACT
      In Phase II - this record will be used to send the Activate/De-
       activate data to update SkillSoft using the SkillSoft API
      The table is updated with the changes included in the CSV
       file by the run date
      The campus will still need to schedule the SQR to run
       periodically to generate the changes




22                                                             4/19/2012
23   4/19/2012