Topics Map > Human Resource System (HRS) > HRS Human Resources Administration

Running the Mass Job Update (Location) in HRS

Overview

This document outlines the process for performing ad hoc Location change mass updates in HRS.
Public query UW_HR_MASS_UPDATE_LOCATION is for populating the HR Mass Job Update Template when an institution seeks to utilize the HR Mass Job Update process for updating an employee's job and position data location. The Excel template is specific to location changes.

The UW_HR_MASS_UPDATE_LOCATION query does the following:
1. Pulls Active population for a particular department from PS_JOB
a. Empl ID
b. Empl Rcd
c. Position Nbr
d. Job Code
e. Empl Class
f. Pay Group
g. EJED
h. Dept ID
i.  Location
2. Pulls full Name (last,first) for each employee from PS_PERSONAL_DATA
3. Pulls Department Description name from PS_DEPT_TBL
4. Pulls Location Description name from PS_LOCATION_TBL

5. Pulls Job Code Description from PS_JOBCODE_TBL

Process Considerations:
  • Especially for larger populations, other teams (Benefits, Payroll) should be given notice; see step E
  • Avoid processing mass updates on final calc days and during payroll post confirm
  • Encourage an Effective Date equal to the first day of the pay period
  • There should only be one instance of the Mass Job Update program (UW_HR_LD_PGM) running at a time
  • UWSC HR team is gathering statistics to capture how long the Mass Update process takes

    • The Mass Update Statistics.xlsx file is found at S: \ ServiceCenterNew \ UW HR TAM \ Mass Updates \ Employee Job Mass Updates \ _Process Documents and Templates \
    • There are three sections being monitored:

      1. UWSC Prep hours (procedure steps A through F)
      2. HRS Run Time hours (procedure steps G through J.6)
      3. UWSC Post-Process hours (procedure steps J.7 through K)



Instructions

*Note the time you started this work for tracking purposes.

A. Query Mass Update Population

HRS Navigation: Reporting Tools > Query > Query Viewer/Manager

Public Query Name: UW_HR_MASS_UPDATE_LOCATION

1.  Run to Excel

2.  Enter the Dept ID; use % if searching for entire institution or division

3.  Click View Results

4.  After query runs, click the Excel document to open

5.  Instead of running directly to Excel, you can click Edit to modify the query criteria as needed

6. (Optional) Run public query UW_HR_ACTIVE_LOCATIONS to Excel

  • Use the campus alpha character (e.g. A%) for the Location search criteria
  • Provide this query in addition to the populated mass update template (step B) as a help resource for the customer to determine the appropriate location to use for the mass update
To the top

B. Populate the Mass Job Update Template - Location

1.  Open the Mass Job Update Template - Location.xlsx from the shared drive at:

S: \ ServiceCenterNew \ UW HR TAM \ Mass Updates \ Employee Job Mass Updates \ _Process Documents and Templates \ Templates \

2.  Create a new sub-folder using folder name HRS-xxxxx (Business Unit) Location Mass Update following file path:

S: \ ServiceCenterNew \ UW HR TAM  \ Mass Updates \ Employee Job Mass Updates \ Location \

Example: HRS-xxxxx Mass Job Update - Location (UWEAU)

3Save the Excel template file within the newly created folder, using file name Business Unit Mass Job Update Template - Location

Example: UWEAU Mass Job Update Template - Location

4.  Note how many rows of data were on the query results (step A) and extend/reduce the template formatting and formulas as needed

  • For example, the text string (column S) on the template are pre-filled through row 500

5. Copy results from step A and Paste Special Values into the Mass Update Template

6.  If the requestor provided a list of employees in their request, you will need to take extra steps to update the template to match their provided data. 

  • Regardless, always start from a fresh export from HRS (Step A)
  • To minimize mass update errors, do not rely on employee data, e.g., Position Number, from the requestor's spreadsheet

7.  Populate the Action column with POS or DTA, and Action Reason column with:

  • POS and 084 - Change Location (if the employee has a Position Number)
  • DTA and 001 - Change Location (if the employee does not have a Position Number)
Note: If a Position Number has multiple incumbents, the mass update will not work properly. The template has conditional formatting to highlight multiple instances of a Position Number; this population will need to be manually updated.
To the top

C. Send Populated Template to Customer

1.  Upload the Excel template file (and location list, if applicable) to the WiscIT and email it to the customer from within the WiscIT  (attach file)

a.  In the email, note that it is encouraged to use the first day of the payroll for the mass update Effective Date

b.  Note that instead of filtering out the population that will not be updated, delete the rows (this helps maintain the text string integrity)

c.  Values needed from institution:

  • Effective Date
  • New Location

2. Update the Mass Update Statistics.xlsx file with the estimated Prep Hours to this point (steps A and B); spreadsheet found at:

S: \ ServiceCenterNew \ UW HR TAM \ Mass Updates \ Employee Job Mass Updates \ _Process Documents and Templates \


D. Create Jira

After the campus has returned the updated Location template:

1.  Create a Jira by populating fields:

Project: UW Human Resource System
Issue Type: System Issue
Summary: Mass Job Update Location - Business Unit
Problem Type: 6 - Data Related
Reporter:
Person Creating Jira
Assignee: Person Creating Jira
Environment: HRS
Team: HRS-HR
Description: "Per (customer name and/or Business Unit) request, HR Team will be utilizing HR Mass Job Update process to update the Location for (x) number of employees"
Reporting Campus: the Campus making the request
HRS Change Management Impact:
# of Employees Impacted: choose appropriate range
# of Campuses Impacted: 1-5
WiscIT #: The ticket number in Cherwell

Click Create

2.  Click the link for the newly created Jira. Alternatively, click Issues from the top menu and select My open issues

3.  Add the following to the Jira:

Label: MassJobUpdateSC
Watchers: Team: HRS_HR_TAM _and_Finance

4.  Update the sub-folder name to reflect the Jira number; file path:

S: \ ServiceCenterNew \ UW HR TAM \ Mass Updates \ Employee Job Mass Updates \ Location \

Example: HRS-53534 UWPLT Location Mass Update

5.  Update the WiscIT by adding the Jira number.


E. Review Completed Spreadsheet and Convert to CSV File

* Remember to note the time you begin work for tracking purposes.
1.  After the institution returns the updated Location spreadsheet, save it using name [Business Unit/Division] Returned Template From Institution to the appropriate folder at:

S: \ ServiceCenterNew \ UW HR TAM \ Mass Updates \ Employee Job Mass Updates \ Location \ HRS-xxxxx [Business Unit] Location Mass Update

Example: A80 Returned Template From Campus

2.  Review the data as additional follow-up with requestor may be necessary

What to look for:

  • Applied filters; are the filtered results the only population being updated?
  • Are all values for the text string populated?
    • Empl ID: 8 numbers with leading zeros
    • Empl Rcd: 1 number
    • Position # (can be null); 8 numbers with leading zeros
    • Effective Date: mm/dd/yyyy format
    • Action: 3 letters - be sure POS is used for employees with Position Numbers, and DTA is used for employees without Position Numbers
    • Action Reason: 3 numbers with leading zero - be sure 084 is used with POS and 001 is used with DTA
    • New Location: typically 1 letter followed by 4 numbers (5 characters); possibility of 6 characters
  • Does Text String column data look consistent (size and formatting) and formula referencing correct row?

If unsure about any of these, reach out to the campus contact for clarification.

3.  Attach spreadsheet to Jira

4.  Open TextPad and copy data found in Text String column of the returned spreadsheet and paste to Text Pad

5.  Save the Text Pad file to the folder referenced above, naming the file [Jira#] [Business Unit] Location.csv  (Be sure to use CSV as the file extension.)

Example: HRS-53534 UWPLT Location.csv

6.  Attach CSV file to Jira

7.  Update the Mass Update Statistics.xlsx file with the additional estimated Prep Hours to this point (add the time for steps D and E to time already recorded for steps A, B, and C); spreadsheet found at:

S: \ ServiceCenterNew \ UW HR TAM \ Mass Updates \ Employee Job Mass Updates \ _Process Documents and Templates \


F. Notify Other Teams

1. Finance: No matter the population size, notify Finance Analysts about the mass update so they can be prepared for unnecessary encumbrance triggers

  • They will need to create a JIRA requiring a CR to reset the encumbrance trigger for the updated population; how to supply them with the information they need will be explained in a later step

2. Benefits: If the population size is large (over 1,000 rows), notify the Benefits Analysts about the mass update, so they can be prepared in case it impacts their daily/nightly processes; if they determine it could affect their processes, add as Watcher.

3. Payroll: Payroll should not be impacted and does not need to be informed. Generally, large scale mass updates are scheduled around 2am when payroll is not processing. If processing were to collide (mass update and payroll calc), a Component Interface Error is generated within the output file and the employee is captured as an error within the mass update output file.


G. Load HRS Staging Table in Report Mode

1.  Log into HRS with your user credentials

HRS Navigation: Inbound file Upload > Inbound File Uploads/Errors

2.  Add a Row

a.  Click the '+' sign on the last record within the Inbound File Uploads Page.

3.  Within the new row:

a.  File Type: Mass Job Update-Change Loc
b.  File Description: same as CSV file from above
c.  Business Unit: leave blank
d.  Check the Approved for Processing box
e.  Click the paperclip to upload CSV file created above
f.  Click Upload
g.  Watch for Saved in top-right corner

Inbound File Upload
4.  Log out of HRS

5.  Log into HRS with HR Batch ID (UW_BAT_HR) at:

https://www.hrs.wisconsin.edu/psp/hrs-bd/?cmd=login&languageCd=ENG

HRS Navigation: Set Up HCM > UW HR Mass Update > UW Processes > Load Staging Table

6.  Enter Run Control ID: UW_HR_LD_STG

7.  Populate the following parameters:

  • Process Options: Report Only
  • File Type: Mass Job Update-Change Loc
  • Program ID: Populates based off selected file type (LOCATION)
  • Batch ID: Same as CSV filename

8.  Click Save

9.  Click Run

Staging Table Report Only

10. The Process Scheduler Request appears

a. If running immediately, click OK
b. If scheduling (rare for staging table load), set Run Date and Run Time accordingly and click OK

Staging Table Process Scheduler

11.  Click the Process Monitor link.  Click Refresh until the process runs to a status of Success and Posted.

a. Click Details > View Log/Trace
b. Open and review the Excel file (.xls) to ensure the results appear appropriate.  If you have any concerns about the results, consult another team member for assistance.
c. Save with name 1. [Business Unit] Report Mode Staging Table Results YYYYMMDD.xlsx to the appropriate folder at:

S: \ ServiceCenterNew \ UW HR TAM \ Mass Updates \ Employee Job Mass Updates \ Location \

Staging Table Process Monitor


H. Load HRS Staging Table in Process Mode

The steps in G are are identical to H, with the exception of running the UW_HR_LD_STG in Process Mode.

All steps and screen shots will be identical with the exception of the Processing Options on the UW HR Mass Load Staging Table screen shot.

1.  Log into HRS with your user credentials

HRS Navigation: Inbound file Upload > Inbound File Uploads/Errors

2.  Add a Row

a.  Click the '+' sign on the last record within the Inbound File Uploads Page.

3.  Within the new row:

a.  File Type: Mass Job Update-Change Loc
b.  File Description: same as CSV file from above
c.  Business Unit: leave blank
d.  Check the Approved for Processing box
e.  Click the paperclip to upload CSV file created above
f.  Click Upload
g.  Watch for Saved in top-right corner


Inbound File Upload
4.  Log out of HRS

5.  Log into HRS with HR Batch ID (UW_BAT_HR) at:

https://www.hrs.wisconsin.edu/psp/hrs-bd/?cmd=login&languageCd=ENG

HRS Navigation: Set Up HCM > UW HR Mass Update > UW Processes > Load Staging Table

6.  Enter Run Control ID: UW_HR_LD_STG

7.  Populate the following parameters:

  • Process Options: Process and Report
  • File Type: Mass Job Update-Change Loc
  • Program ID: Populates based off selected file type (LOCATION)
  • Batch ID: Same as CSV filename

8.  Click Save

9.  Click Run

Staging Table Process and Report

10. The Process Scheduler Request appears

a. If running immediately, click OK

b. If scheduling (rare for staging table load), set Run Date and Run Time accordingly and click OK

Staging Table Process Scheduler

11.  Click the Process Monitor link.  Click Refresh until the process runs to a status of Success and Posted.

a. Click Details > View Log/Trace

b. Open and review the Excel file (.xls) to ensure the results appear appropriate.  If you have any concerns about the results, consult another team member for assistance.


c. Save with name 2. [Business Unit] Process and Report Mode Staging Table Results  YYYYMMDD.xlsx to the appropriate folder at:

S: \ ServiceCenterNew \ UW HR TAM \ Mass Updates \ Employee Job Mass Updates \ Location \

12.  Add comment to Jira describing results; example:

In HRS:

Ran UW_HR_LD_STG to Success in Process Mode
Process Instance: 2890026
Total Records: 114
Processed: 114
Rejected: 0

13.  If there are rejected rows, keep spreadsheet open, as you will need to copy and paste these reject rows into the final Process Mode output file

Staging Table Process Monitor


I. Complete Mass Update Process in Report Mode

1.  Log into HRS with HR Batch ID (UW_BAT_HR) at:

https://www.hrs.wisconsin.edu/psp/hrs-bd/?cmd=login&languageCd=ENG

HRS Navigation: Set Up HCM > UW HR Mass Update > UW Processes > Mass Update Process
2.  Enter Run Control ID: UW_HR_LD_PGM

3.  Populate the following parameters:

  • Process Options: Report Only
  • Program ID: LOCATION
  • Batch ID: Same as CSV filename
  • Advanced Options: Campus Reports (double check - resets after Program ID, if selected prior to Mass Update Run Parameters)
4.  Click Run
Mass Update Report Only
5. The Process Scheduler Request appears

a. If running immediately, click OK

b. If scheduling, set Run Date and Run Time accordingly and click OK

Mass Update Process Scheduler

6.  Click the Process Monitor link.  Click Refresh until the process runs to a status of Success and Posted.

a. Click Details > View Log/Trace

b. Open and review the Summary_Report Excel file (.xls) to ensure the results appear appropriate.  If you have any concerns about the results, consult another team member for assistance.


c. Save with name 3. [Business Unit] UW_HR_LD_PGM Report Mode Output FileYYYYMMDD.xlsx to the appropriate folder at:
S: \ ServiceCenterNew \ UW HR TAM \ Mass Updates \ Employee Job Mass Updates \ Location \
Mass Update Process Monitor
7.  Warning means a future dated Job Data row exists; process will still insert row we are uploading, but it will not update the future dated row that exists; review the employee's who came up with Warning
  • If the future dated Job Data row is a termination, you can remove the employee from the mass update by continuing with step 8
  • If the future dated Job Data row is not a termination, skip to step J
8.  To remove the employee from mass update, navigate to Set Up HCM > UW HR Mass Update > UW Configuration > UW Mass Load Page
9.  Enter the Empl ID/Rcd combination, change Processed Flag to blank, and click Search

10. If multiple mass load options exist, select the Batch ID that matches the CSV filename

11. Uncheck the OK To Process checkbox and Save

J. Complete Mass Update Process in Process Mode

1.  Log into HRS with HR Batch ID (UW_BAT_HR) at:

https://www.hrs.wisconsin.edu/psp/hrs-bd/?cmd=login&languageCd=ENG

HRS Navigation: Set Up HCM > UW HR Mass Update > UW Processes > Mass Update Process
2.  Enter Run Control ID: UW_HR_LD_PGM

3.  Populate the following parameters:

  • Process Options: Process and Report
  • Program ID: LOCATION
  • Batch ID: Same as CSV filename
  • Advanced Options: Campus Reports (double check - resets after Program ID, if selected prior to Mass Update Run Parameters)
4.  Click Run
Mass Update Process and Report
5. The Process Scheduler Request appears

a. If running immediately, click OK

b. If scheduling, set Run Date and Run Time accordingly and click OK

Mass Update Process Scheduler

6.  Click the Process Monitor link.  Click Refresh until the process runs to a status of Success and Posted.

a. Click Details > View Log/Trace

b. Open and review the UW_HR_LD_PGM_Summary_Report Excel file (.xls) to ensure the results appear appropriate.  If you have any concerns about the results, consult another team member for assistance.


Useful Information Regarding Summary Report:
Successful: Record loaded successfully with no issues

Warnings: Record inserted successfully, but there are future rows that need to be reviewed by the institution. The HR Mass Job Update process does not update future rows (with the exception of PAY).

Errors: Record did not load; institution review is necessary
Mass Update Process Monitor
7.  Update the Mass Update Statistics.xlsx file with the estimated HRS Run Time Hours (step F through current step, J.7); spreadsheet found at:
S: \ ServiceCenterNew \ UW HR TAM \ Mass Updates \ Employee Job Mass Updates \ _Process Documents and Templates \

8.  Save the output file as 4. [Business Unit] UW_HR_LD_PGM Process Mode Output FileYYYYMMDD.xlsx to the appropriate folder at:
S: \ ServiceCenterNew \ UW HR TAM \ Mass Updates \ Employee Job Mass Updates \ Location \
Example: 4. UWEAU UW_HR_LD_PGM Process Mode Output File 20200501
9.  Copy the rejected lines from step H.13 and paste with ERRORS section of the output file (create if none)
  • Adjust the Report Statistics numbers as needed; Total Records Processed and Rejected totals will need to be increased
10. Spot check a few of the Successful and Warnings in Job Data to ensure they did in fact update

11. Add Excel output file to Jira and add comment describing results; example:

In HRS:

Ran UW_HR_LD_PGM to Success in Process Mode
Process Instance: 2890026
Start Date/Time:
End Date/Time:
Total Records Processed: 114
Total Records Updated: 113
Updated: 112
Updated With Warning: 1
Rejected: 1 (note reject reason)


K. Close WiscIT and Jira

1.  Update the Jira field in the WiscIT

2.  Upload the Process Mode Output File to the WiscIT and email it to the customer from within the WiscIT

3.  Close WiscIT

4.  Add final comment on Jira and close; example:
Emailed customer the Process Mode Output file via Cherwell.
Closed WiscIT.
Closing Jira.
5.  Update the Mass Update Statistics.xlsx file with an estimate of Post-Process Hours (steps J.8 through current step K.5); spreadsheet found at:
S: \ ServiceCenterNew \ UW HR TAM \ Mass Updates \ Employee Job Mass Updates \ _Process Documents and Templates \

L. Provide Employee and Position Detail to Finance Analyst for CR Jira

As mentioned earlier, a Finance Analyst will need to create a CR Jira (see HRS-49431 for example) to reset the false encumbrance trigger due to the mass update. The following employee information will be needed:
1. On the Process Mode Output file, create another tab and name it Finance

2. Provide the Empl ID and Empl Rcd of employees that were Updated and Updated with Warning
a.  In cell A1, type: Empl ID & Empl Rcd Concatenate Formula
b.  Enter the following formula in cell A2 and copy down:
=CONCATENATE("'",’Output File’!C1,"'","||","'",’Output File’!D1,"',")
The column reference (C,D) may change; these should be EMPLID (C) and EMPL RCD (D)
The row reference (1,1) will change; update accordingly to capture Updated and Updated with Warning population
3.  Provide the Position Number of employees that were Updated and Updated with Warning
a.  In cell C1, type: Position Concatenate Formula
b.  Enter the following formula in cell C2 and copy down:
=CONCATENATE("'",'Output File'!F1,"',")
The column reference (F) may change; this should be POS NBR
The row reference (1) will change; update accordingly to capture Updated and Updated with Warning population
4.  Save

Additional Resources

Related KBs:

  • [Link for document 77879 is unavailable at this time.]
  • [Link for document 81799 is unavailable at this time.]
  • [Link for document 82416 is unavailable at this time.]
  • [Link for document 78188 is unavailable at this time.]
  • [Link for document 78427 is unavailable at this time.]
  • [Link for document 77759 is unavailable at this time.]
  • [Link for document 77596 is unavailable at this time.]
  • [Link for document 80775 is unavailable at this time.]
Related Links:

    Get Help

    • Click HERE to contact UW Shared Services - Service Operations Department or HERE for the UWSS IT HelpDesk if you have any issues with these instructions.

    To the top




    Keywords:HR mass update job location   Doc ID:102208
    Owner:tami e.Group:UW–Shared Services
    Created:2020-05-19 06:18 CDTUpdated:2020-07-24 12:14 CDT
    Sites:Human Resource System (HRS), UW-La Crosse, UW–Shared Services
    Feedback:  0   0