Skip to main content
EMS Software, LLC

HR Toolkit - Group/Group Type Model

Obtaining the HR Toolkit Components

Please contact your Professional Services Consultant to obtain the following components:

  • HRTK Staging DB Install G_GT_WU.sql – SQL file required to create the staging database - EMS_Staging.
  • HRTK Update Script G_GT_WU.sql – SQL file required to create the stored procedure HRTK_ Update_Group within your EMS database.

HR Toolkit Overview

 

The EMS_Staging Database

The EMS_Staging database (created by HRTK Staging DB Install G_GT_WU.sql) is used to stage Group and Web User records before they are imported into EMS. 

Note: your EMS database may have the “Groups” field renamed to another term.

Once you have identified the data source within your organization that will be used to create and maintain your EMS Group and Web User records, you will be required to define/create a process to import ACTIVE Group records into the tblPeople table within EMS_Staging.

Maintaining Group Records in EMS

The HRTK_Update_Group stored procedure (created by HRTK Update Script G_GT_WU.sql) contains all of the logic to create your EMS Group and Web User data. Records are imported into EMS from the EMS_Staging database according to the specifications outlined in the following table:

EMS Staging Field (varchar)

Writes Data to EMS Database Fields

Requirements/Notes

PersonnelNumber (20)

Group.ExternalReference

Required Unique identifier (e.g. Employee/Student ID, Personnel Number, etc.) for each web user.  Used for purposes of creating /updating EMS.

FirstName  (50)

Group.GroupName

WebUser.UserName

Must have at least 1 of 3. Built in EMS as LastName, FirstName Middle Initial

LastName  (50)

Group.GroupName

WebUser.UserName

Must have at least 1 of 3. Built in EMS as LastName, FirstName Middle Initial

Title (50)

Contact.Title

can be null

MiddleInitial (10)

Group.Group Name

WebUser.UserName

Must have at least 1 of 3. Built in EMS as LastName, FirstName Middle Initial

EMailAddress (75)

Group.EmailAddress

WebUser.EmailAddress

Required for Web User creation

Phone (50)

Group.Phone

WebUser.Phone

can be null

Fax (50)

Group.Fax

WebUser.Fax

can be null

Address1 (50)

Group.Address1

can be null

Address2 (50)

Group.Address2

can be null

City (50)

Group.City

can be null

State (50)

Group.State

can be null

ZipCode (10)

Group.ZipCode

can be null

Country (50)

Group.Country

can be null

NetworkID (50)

Group.NetworkID

WebUser.NetworkID

Required for Integrated Authentication

GroupType (50)

GroupType.Description if unique Group.GrouptypeID

Required for assignment of Group Type to groups. Will assign GroupType of (None) when left blank or null.

EMS uses the PersonnelNumber field in EMS_Staging.tblPeople and the tblGroup.ExternalReference and tblWebUser.ExternalReference fields in the EMS database to determine whether a record needs to be added, updated, or inactivated. 

Group and/or Web User records within EMS with an empty External Reference field are excluded from processing. Therefore, Groups that are not included in your data feed (e.g. contractors, temporary employees, etc.) can be added and maintained manually within EMS and will not impacted by the HR Toolkit.

Important Note:  Existing EMS clients must ‘synchronize’ existing EMS Group and/or Web User records before activating the HR Toolkit by adding a valid PersonnelNumber value in tblGroup.ExternalReference and tblWebUser.ExternalReferenceIgnoring this step will result in duplicate records

Group Type Records

Unique GroupTypes in EMS_Staging.tblPeople are added to the Group Types table in EMS (EMS.tblGroupType). Group Types are not updated or inactivated.

New Group Records

If PersonnelNumber in EMS_Staging.tblPeople is not found to be associated with a Group record in EMS, a Group and Web User account will be created for that record.

In addition to creating a new Web User record, this process also automatically assigns the Group to the Web User record. This allows a Web User to make a reservation using their Group record within Virtual EMS. 

Various configuration settings are available to automatically assign the appropriate Web Process Template(s) when a user hits your Virtual EMS site for the first time.  Within the Virtual EMS Parameters area of EMS (System Administration > Settings > Parameters (Virtual tab)), the following parameters must be set accordingly to activate this feature:

Note:  Ignore the “Auto Creates…” and “Security Status…” parameters if your HR Toolkit was customized to automatically assign Web Process Templates to Web Users.

Area

Description

Value

Account Management

 

Auto Creates Web Users During Integrated Authentication

Yes

 

Account Management

 

Default Security Template for User

 

Must be specified

 

Account Management

 

Security Status for User

 

Active

 

To automatically assign a Web Process Template to new Web Users, select the ‘Available to New Web Users’ option within your Web Process Template(s) (Configuration > Web > Web Process Templates).

Updates to Existing Group Records

If PersonnelNumber in EMS_Staging.tblPeople is found in EMS, existing Group, Web User and Contact records will updated with any changes.

Inactive Group Records

If PersonnelNumber in EMS is not found on a record in EMS_Staging.tblPeople, the Group and Web User record in EMS will be inactivated.

Installing and Executing the HR Toolkit

Installation

The followings steps should be performed on the MS SQL Server that hosts your EMS database.

  1. Obtain the SQL files outlined in the section above.
  2. Using Microsoft SQL Server Management Studio execute the HRTK Staging DB Install G_GT_WU.sql script against your master database.  This will create the EMS_Staging database containing one table named tblPeople.
  3. Using Microsoft SQL Server Management Studio execute the HRTK Update Script G_GT_WU.sql script against your EMS database.  This will create a stored procedure in your EMS database named HRTK_Update_Group

Execution

  1. Import ACTIVE records from your organization’s data source into the tblPeople table within the EMS_Staging database.
  2. For current EMS clients only:  Ensure that existing Group and/or Web User records have been ‘synchronized’ as outlined above.
  3. Using Microsoft SQL Server Management Studio execute the HRTK_Update_Group stored procedure.

Note:  Step 3 (and Step 1 with assistance from your IT Department) can be scheduled to execute automatically using a SQL Job.  Please refer to your MS SQL Server documentation for more information on creating and scheduling a SQL Job. This is NOT an EMS managed process.