Complying with new EEOC Requirements

Just when you thought you had some time to breathe with Open Enrollment, Year-End, Salary Administration, and the User Conference behind you… the EEOC intervenes!

Beginning in 2007, the existing EEO-1 report calls for workforce data to be broken down by nine job categories, using five race and ethnic categories. The revised EEO-1 report contains changes to the race and ethnic categories. A new category titled "two or more races" has been added, and the category "Asian or Pacific Islander" has been divided into two separate categories, “Asian" and "Native Hawaiian or other Pacific Islanders." In addition, the approved revisions include an increase in the number of job categories as a result of dividing the Officials and Managers category into two subgroups, Executives/ Senior Level and First/Mid Level Officials. For detailed information regarding these changes, go to http://www.eeoc.gov/eeo1/index.html.

The revision to job categories is not difficult to manage, but it does require analysis of your job (position) groups. Start by running a list of all of your current job groups (active and inactive) and review the current categories with the revised categories provided by the EEOC. After deciding which revisions to commit, you can have your DBA run a simple SQL statement to update the job (position) groups.

Race and ethnic categories are a little more involved, though. While PDS has made the necessary code changes, it might leave you wondering, “How do I collect the revised information from the existing employee population without having to send out paper forms?” and “How do I collect the information from new employees in the future?” I knew this was a dilemma that all PDS clients would face so I did what any wise woman would do…I called PDS!

The Solution

The ideal solution is to have all employees review their own EEO information and make the appropriate changes themselves. This minimizes the load on the HR department. However, it might also open a can of worms if not done correctly. Employees might not have update access to their demographic self-service pages and you might not want to give them that access. (In fact, you might not have rolled out self-service at all, but this might give you the incentive to do so.) Next, the self-service demographics page in Vista has a lot more information than the basic fields that need to be updated for EEO reporting. You might not want to clutter users' screens with information not essential to the task at hand. Then, once you get through the initial issues and figure out how to get an update form to everyone, how can you know who reviewed and who did not review their information? Finally, after the process is completed, how can you make sure that new employees review their information so that you don't start drifting out of compliance?

Marco will cover the “technical side” (or as I like to call it, “The Dark Side”). However, I know that all of you HR people out there are thinking, “We need to communicate something to our associates before this workflow e-mail arrives in their in-box.” That is correct. It would be wise to have the head of your HR department send some type of communication to all associates regarding the changes to the race/ethnic codes as defined by the EEOC. The last thing you want to do is have your employee population thinking that your company is doing some type of profiling. The communication can be simple and to the point, as in the below example.

If you will be asking for updates to other pieces of data in your workflow, you will want to expand on this sample communication. Also, if your employees are not familiar with the concept of workflow, you might want to include a screen print of what the workflow will look like.

Vista Workflow and Enterprise Server (“The Dark Side”)

This situation is perfect for countless hours of distributing, tracking, collecting, and manually inputting hundreds of forms since you have nothing better to do. Alternately, the situation is also perfect for Vista Workflow, Enterprise Server, and database stored procedures. Let's look at how we can use these to construct a solution that satisfies all of our needs.

First we create a solution to handle a single person. This will have the following components.
- A workflow template —This will, if sent to a person, allow him to review and update a predefined list of demographic information, without any additional clutter. Once that person acknowledges or updates the information, the flow goes to Enterprise Server to update the database, and then the flow completes. We won't bother with approvals or completed notifications because we don't want an HR manager inundated with hundreds or thousands of emails. (Or do we? Hmmm…)
- An Enterprise Server job —This will kick off a workflow instance for the above template for any specified individual.
- A stored procedure —This accepts an employee's person_id as an input parameter and then schedules the above Enterprise Server job to kick off the workflow.
Next we create a solution to execute the above stored procedure for every employee.
- A mixed-mode Enterprise Server job —This will SELECT every employee's person_id from the Vista database and then repetitively pass each of those person_ids to the above stored procedure. (Hence, this will kick off one workflow for every employee.)
- (Execute this job when you are ready to launch the company-wide EEO update.)
Finally, we create a solution to execute the same stored procedure for all future new hires, as they are hired.

Stored procedure user exit code —Add code to the new-hire stored procedure's user exit to call the above custom stored procedure for the passed person_id.
(This will be called automatically by the system as every new hire completes, kicking off a single workflow for each new employee.

Since we are constructing our solution using Vista Workflow, we will have the added benefit of audit trails, progress tracking, and administration capabilities.

The Vista Workflow engine and Enterprise Server have options to automatically send notices when flows are not completed in a timely manner.
The existing library of reports and/or Enterprise Server's simple reporting capability can be used to review outstanding flows at any point in time.
Likewise, an audit trail of completed flows is maintained in Vista, so those can also be reported on using the same tools.
For those individuals unable to complete their own flows, the Vista HR administrator can assume ownership of their flows to complete them.

Workflow Template

• Note the above description associated with the START step—the process that creates the flow instance (Enterprise Server) will pass the referenced person's initial demographic information. This immediately raises the question, “What information needs to be displayed and updated?” For EEO purposes, obviously race code is required, but we might also want to take this opportunity to have people review other information such as marital status and maiden name. Additionally, so people know that they are editing their own information, we should display (but not necessarily open up for editing) first and last name.

• The next step is an FYIA step, which means that after a person completes it, the flow always moves to a single next step, the EXEC step. This implies that if a person's information is initially correct, the database update will still occur. We could have changed this flow to replace the FYIA step with an Approval or Decision step, but why add complexity for the user? There is no negative effect of an execution step that updates a table with the same information that is currently in it. So, by using an FYIA step, the users' interface is simpler—they just have to click one button.

• We have a couple of options for implementing the EXEC step. We could tell Enterprise Server to explicitly handle just the three fields that we selected above—i.e., UPDATE person SET race_code = ?, marital_status = ?, maiden_name = ? WHERE person_id = ?. This would simplify the coding of the flow template, but it's not the best coding practice. There is a Vista stored procedure whose job is to maintain the demographic data in the person table— PER_Demographics(). Whenever possible, it is better to use the predefined stored procedures to update tables rather than to write direct code. This ensures that all validation and secondary processing incorporated into the procedure is handled. It also allows for the template to be expanded later with minimal effort. However, there is one drawback to using this approach. The PER_Demographics() stored procedure expects many more fields than just race_code, marital_status, and maiden_name. This can be handled in Vista workflow, though. Rather than opening up all of the extra fields for reviewing and editing, we can still load these fields in the START step above, but set them all as hidden so that users never see them.

• Nothing special needs to be done about the DONE step. When a flow completes, the workflow engine automatically moves it to the workflow history tables from which further reports can be generated.

Just for fun, and because we want to make this the longest UGA Newsletter article ever, let's expand the above flow template definition to handle a couple of more issues.

• Although it does no damage to display race, marital status, and maiden name for everyone, only women would generally need to see the maiden name field. (I say “generally” because I do know a couple where the man took the woman's last name, but we'll ignore that for now.) So, to keep things as simple and minimal for everyone, let's change the flow so that it only shows maiden name for women.

• One of the pieces of information that is passed to the PER_Demographics() stored procedure is Social Security number. Even though this field is hidden so there really should be no security risk, we can waste a little more time by removing it completely from the flow until after it passes the individual review and acknowledgement. Then, just before going to the Execution step, we can load that field so PER_Demographics() can still function correctly. Moreover, we'll be able to show off more Vista workflow features.

Let's look at our expanded flow design. The changes are highlighted in red. (Note: this expanded design requires features—the AUTOD and AUTOU steps—that are only available in the 3.2 version of Vista.)

Note: at the conclusion of this article will be hyperlinks for you to download the actual workflow template as well as the other components of this solution.

The second component of our solution is an Enterprise Server job to kick off the above workflow template for a given individual. So, the two things we need to know how to do here are launching workflows from within Enterprise Server and passing information (e.g., the person id of the person's whose information should be updated) to the flow. These are both basic actions of the Enterprise Server “workflow” job type (item_type_code=”F”).

A workflow Enterprise Server command block should contain a few critical pieces of information, passed as XML tags.

The workflow template created earlier will have a unique id in the database (wflow_id in the wflow table). By replacing the above xxxx with that ID, we tell Enterprise Server which workflow template to use when instantiating the new flow. (It is also possible to have Enterprise Server infer the workflow template based on the other information passed and the different templates' linkage sections, but there's no need for ambiguity here. We know the specific template that we want to use, so we might as well explicitly pass it.

The Enterprise Server workflow job type also expects the user id and password of an originating user. However, if the originator is Enterprise Server itself (PDSADMIN), then PDSESVISTA can be used as a placeholder for the password.
Next, for subsequent tracking and reporting purposes, we'll want the system to record the Vista web page/security option being processed in the flow. Even though we are constructing our own special workflow, technically, we're basically still just processing the Vista Demographics update page (20) in self-service (ESS) mode.
Likewise for tracking, but also for the bulk of the actual flow processing and execution, the flow will need information about the person whose demographic information is being updated. The [person_id] field is the most important piece of information to be passed, but other information (e.g., [ReferencedPerson] ) can also be passed at the outset.
And, finally, we want to have a meaningful description (EEO Self-Update for …) of the flow instance for monitoring and later reporting.

Note: as with the original flow template, at the conclusion of this article will be hyperlinks for you to download the actual Enterprise Server job as well as the other components of this solution.

Stored Procedure to Launch Enterprise Server Job

The third component of our solution is a stored procedure to launch the above Enterprise Server job. Basically, this procedure should receive as its input the person_id of the person to be updated, gather whatever information is needed about that person, and then launch the above Enterprise Server job, passing the gathered information.

The key to the above procedure is the call to the ES_SchedJob stored procedure. That procedure schedules an Enterprise Server job as identified by a job name (in this case, we're assuming that our previous job template was named “EEOCWFLOW”) or a job esprgm_id, passing along any job-specific information in a constructed command resolution block.

Note: as with the above solution components, at the conclusion of this article will be hyperlinks for you to download the actual stored procedure definition.

Mixed-Mode ES Job to Launch for All Employees

The fourth component of our solution is a mixed-mode Enterprise Server job that will call the above stored procedure for every active employee in the company.

Note: as with the above solution components, at the conclusion of this article will be hyperlinks for you to download the actual Enterprise Server job definition.

Stored Procedure User Exit Code for New Hires

The fifth and final component of our solution is code inside of the user exit procedure for the new-hire action. Remember that all Vista actions are executed in the database using stored procedures, and every one of these stored procedures has a corresponding “user exit” procedure available for you to add your own processing to those events. In our situation, at the conclusion of the new-hire processing, we want to kick off this same workflow, but just for the newly hired person.

So, we will add code such as the below (highlighted in blue) to the new-hire user exit procedure.

Note: as with the above solution components, at the conclusion of this article will be hyperlinks for you to download the actual user exit code.

Final Note

When you are ready to launch the job for everyone to update their EEOC information, consider modifying the earlier mixed-mode Enterprise Server job that starts the workflow for everyone. Make the WHERE clause of the SELECT statement limit the number of people to a manageable portion of your organization (e.g., to one department). Then, each day modify the job to include a different subgroup and resubmit it. This way, your HR department won't be inundated with calls if people have questions or problems.

Downloads

Following are links to download scripts to create all of the above components in your Vista database. There are separate links for Oracle and SQL Server.

Editors Note: In August 2007 PDS will be releasing an update to the EEO-1 report format to accomodate the race and ethnic category changes.