9.2 Employee HR Status System-wide

Purpose:  Use this document as a reference to ensure that an offboarding employee is not active at another institution. The query QHC_SEC_HR_STATUS_SYSTEM_LEVEL is featured prominently.

Audience:  Local Security Administrators

You must have at least one of these local college managed security roles:

  • ZD_DS_QUERY_VIEWER

You must also have each of these roles to run the QHC_SEC_HR_STATUS_SYSTEM_LEVEL query:

  • ZD_DS_QRY_SECURITY_TABLES

If you need assistance with the above security roles, please contact your local college supervisor or IT Admin to request role access.

HR Status System-wide Query

When an employee offboards from an institution, it is critical to clean up their security access within the system.  However, sometimes it is difficult to ensure that the employee is not active at another institution.  Because there is only one User ID in the system for an active employee, it is critical to coordinate with other institutions if the employee transferred and is active.  This query is meant to serve as a secondary tool to help identify inactive employees, vs employees that are still active somewhere in the system.  As a security administrator it is important that you also check with your HR counterpart and the HR counterpart at the other institution to confirm employment.  

Also you can look at:  9.2 Termination (Offboarding) Checklist Details | 9.2 HCM - HR Core | ctcLink Reference Center for details as well.

Navigation:  Menu > Reporting Tools > Query > Query Viewer

  1. The Query Viewer search page displays.
  2. Enter Query Name begins with = "QHC_SEC_HR_STATUS_SYSTEM_LEVEL".
  3. Select the Search button.
  4. The Search Results section populates.
  5. Select the Run to HTML link.
  6. The QHC_SEC_HR_STATUS_SYSTEM_LEVEL page opens in a new window.
  7. The Company field is required. Employee ID is optional.  Entering only the Company code will return information on all employees associated with your company.  This can be useful tool for auditing security. The *From Effdt and *To Effdt fields look at the job record and only shows results if an update was made within the given range.
  8. Select the View Results button.
  9. The data that is returned is shown below.  The last two columns have drilldown into CS/FS pillars. For those to work, you must run the query in HTML mode.
Query screenshot 1
Column Descriptions
  1. Company Query Prompt will display the employee’s status for the college entered in the Company field above. *Before onboarding or offboarding a user, confirm they are not active at another college by checking the system status column".
  2. Employee Associated Companies column will list all companies that the user has ever been associated with.  The next column will indicate whether the user is an ACTIVE employee in the system.
  3. Apply Data Protection is a new requirement for any HCM queries that contain employee names (identifying information).
  4. System Status will display INACTIVE IN SYSTEM if the person is no longer ACTIVE at ANY institution. If it displays ACTIVE, then they are active at one or more institutions across the system.
  5. HR Active Companies:   Shows institutions where the employee is Active.
  6. Max Active Effd: the most recent active date in HCM
  7. HR Inactive Companies:  Shows any institution where the employee is inactive/terminated. 
  8. Max Inactive Effd:  the most recent inactive date in HCM.

The next set of columns is designed to help understand which are the minimum required roles, which roles are missing, and which roles need to be added.

  1. HCM Pillar Minimum Required roles is a list of minimum roles required to use the system:
    1. CTC_UN_HCM
    2. EOPP_USER
    3. PAPP_USER
    4. NA Payroll WH Form User
    5. ZZ PeopleSoft User - for active employees
    6. ZZ Former Employee - for inactive employees (role assigned dynamically)
    7. CTC_XXX_DISTR - role assigned dynamically
  2. The Required roles Assigned User column shows which one of the required roles is actually assigned to the user.
  3. The Missing CTC_%_DISTR role shows if the user is missing the tile role in HCM.
  4. The Former Empl Err for Sys Status column shows if the user is inactive and if they do NOT have the ZZ Former Employee role.  Role is assigned dynamically when job data is INACTIVE in HCM.
  5. The PSUser Error For Sys Status column will show if they have a role that they should not if they are inactive.
  6. The Missing Required role column shows any required role that the user is missing in HCM.

The Next Set of columns is for Portal Access.  Roles Assigned to User in Portal will show you which roles the user has in the portal environment.  Keep in mind most of these sync from either HCM or CS across to Portal.

LSA tip: If a user is missing a role in portal, remove the role from the pillar. Save. Add the role in the pillar and save once more. The Integration Broker will assign the role in Portal.  The reverse also works to remove a role that is assigned in portal, add it in the pillar and save. Then remove from the Pillar and save. The role will remove from Portal.

  1. The Missing Portal CTC_%_DISTR will show if the user is missing the tile role in portal also.  If they are, you would need to add the role in the HCM environment so it will sync to portal.
  2. The Portal Former Error for Status column will show if they are missing the ZZ Former Employee role in Portal.  The ZZ Former Employee role is dynamically assigned.
  3. Portal PSUser Error for Status: If a user has the ZZ PeopleSoft User role in Portal and should not. In most of these instances, if the role is removed in HCM it will remove itself in portal.
  4. The Portal SS Role Err for Status column will show other roles assigned to the user in Portal outside the base roles that should not be.  For instance, Faculty or Advisor type access remaining on the user in Portal.  This access should then be reviewed in CS and removed as appropriate.
  5. The Missing Required Portal Role column will tell you if the user is missing a required role in the portal.
  6. The Link to CS Roles for Inactive column is a drilldown that will show current roles assigned to the inactive user in the CS pillar.  Must run query in HTML mode to use the drilldown.  
  7. The Link to FS Roles for Inactive column is a drilldown that will show current roles assigned to the inactive user in the FS pillar.  Must run query in HTML mode to use the drilldown.

Below are a few examples of results that can be returned.  

The first one is active at WA140 and has all required base roles.  

The second example is inactive at WA140, and not active at any other institution.  In HCM, the ZZ PeopleSoft User role should be removed and replaced with the ZZ Former Employee role (dynamically assigned) and the CTC_%_DISTR role for WA140 added in HCM.  Then you can click the links in the Roles assigned in CS and FS to drill down to the respective pillars.

Drill Down Examples 1 and 2

Employee name and employee id are left out of all the screen shots but those are columns in the query.

The next one is inactive at two institutions.  These two institutions should probably coordinate the effort but the security again needs to be cleaned up.  The ZZ Former Employee role in HCM will be added dynamically.  They are missing their CTC_%_DISTR role in HCM.  Their access in CS should be evaluated as they appear to have been a student.

Drilldown example 3
  1. After the ZZ Former Employee role is added dynamically in HCM it will flow to FS;  The ZZ PeopleSoft User role needs to be removed.
  2. For the Employee Associated column:  
    1. If it displays WA140, WA220 but the person is inactive in system, you will see WA140, WA220 in the HR Inactive Companies column;  if they were active at WA140, you would see WA140 in the HR Active column.

Process complete.

Video Tutorial

The video below demonstrates the process actions described in steps listed above. There is no audio included with this video. Select the play button to start the video.

Video Tutorial via Panopto

View the external link to Employee HR Status System-wide. This link will open in a new tab/window.

0 Comments

Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.