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

Running the Institutional Planning Spreadsheet to CAT [Edit and Overwrite]

Overview

The IPS → CAT process uploads data from the Institutional Planning Spreadsheet to the Compensation Adjustment Tool for analysis, reporting, editing, and salary line reconciliation.

This process is most useful when mass changes are needed to existing CAT data. If minor changes are need to a few CAT records it is easier and safer to make those changes directly in the CAT.

Process Considerations:

  • The Edit method should be used after the initial upload when editing specific compensation rate change amounts or distribution percentages already in the CAT.
  • The Replace method should be used after the initial upload when replacing data already in the CAT.  The data for the employee records found in the new spreadsheet will replace all data for those employee records in the CAT.
You may click on the links below to navigate directly to a section:


Instructions

The updated IPS can be loaded to the CAT after all mass changes have been made in the IPS. For information on how to use the IPS, please see Institutional Planning Spreadsheet in CAT.
  1. Create a new version of the IPS to edit.

    • “Save As” the current version of the IPS and make changes in the new copy.
  2. Edit and format the IPS with the changes to be uploaded to the CAT.

    • Edit the data in the IPS that is incorrect or incomplete in the CAT and needs to be changed.
    • As a precautionary measure, delete extraneous data. The data to be deleted will depend on the data that is being changed.
      • Delete any employee records that are not being updated or overwritten.
        • Right-click the row containing the employee record and select “Delete.”
      • If only the compensation rate increase data is being updated or overwritten, clear the distribution percentage funding data. 
        • Select all the data from Column 77 (GL Business Unit 1) to Column 106 (Distribution Percentage from 5) starting at row 16 to the bottom of the data. 
        • Right-click the selection and select “Clear contents.” Do not “delete” the cells.
      • If only the distribution percentage funding data is being updated or overwritten, clear the compensation rate increase data.
        • Select all the data in the columns with orange headers from Column 34 to Column 64, beginning at row 16. Multiple sections will need to be selected separately.
        • Right-click the selections and select “Clear contents.” Do not “delete” the cells.
        • Note that the totals in the Updated Annualized Base Salary columns may be incorrect if any pay rate adjustments had been added previously and are then deleted. No action is needed if they are incorrect, as they are not loaded to the CAT.
      • Note that if only compensation data is selected in Step 8, as shown in the image below, funding data will not load regardless of whether it is deleted. The reverse is true for selecting only funding data.
    Image 921
  3. Save the “IPS -> CAT” tab of the IPS as a .CSV.

    • Open the “IPS --> CAT” tab
    • Scroll to the bottom of this tab and delete any rows that appear as so:
    Image of Delete Rows
    • Press the “Click Here to Export File for CAT Page Upload” button.

    Image of Click Here to Export File

    • Save the output file “Name.CSV” under the file menu.

    Image of Name CSV

    • The following box will appear in Excel. Select Okay.

    Image of Make Sure File Looks Correct

    • The “Name.CSV” file will appear on your screen. You may close it out. 
  4. Navigate to the CAT.

    • Log in to HRS and navigate to Workforce Administration > UW External HR Systems > Compensation Admin Tool > CAT Processes > IPS Edit/Replace CAT.
  5. Create a new Run Control ID.

    • The Run Control ID is a unique identifier that saves different values for the run control criteria. Both the update and overwrite methods for the same file will use the same run control.
    • While there is no set format for the Run Control ID, a potential suggested format is Initials + next available number (e.g. “ABC01”).
    • Click either the “Find and Existing Value” or “Add a New Value” tab depending on what you would like to do.
    • Enter the run control.
    • Click either “Search” or “Add.”
    • If you are searching for a run control and it does not exist, you will have the option to create a new run control.
  6. Under “Process Options,” choose between “Report Only” and “Process and Report.”

    • The report mode should be run first.
      • This mode allows the data to be processed without committing data to the CAT. It generates an output .xls file that allows the data and any possible errors to be reviewed.

    Image 923

    • The process mode should be run after.
      • Once the data has been reviewed and any errors in the data have been corrected, the process mode can be run. This commits the data to the CAT and generates an output .xls file.
  7. The Fiscal Year should be automatically populated.

  8. Select the appropriate “Data to Be Loaded."
    • Select “Compensation” if only the compensation rate increase data is being changed (comprate increase $ amount).
    • Select “Funding” if only the funding distribution percentage data is being changed.
    • Select “Both” if both the compensation and funding data are being changed.

    Image 924

  9. If the update method is being used, select “Edit” under “Data Load Option.” If the overwrite method is being used, select “Replace All."
    Image 925
  10. Select the appropriate “Inbound File Upload Criteria.”

    • For File Type, “Compensation Adjustment” should be automatically populated.
    • For Business Unit, click the magnifying glass to search for the appropriate business unit. 
    • For file description, there is no set format. A potential suggested format is “CAT IPS Edit” + date (e.g. “CAT IPS Update 160101”) or “CAT IPS Replace” + date (e.g. “CAT IPS Replace 160601”)
    • Click the “Upload File” link next to Attached File to browse for, select, and upload the CSV file from Step 1.
    • Once all fields have been filled out or selected, click the “Save” button at the bottom of the window.
      • The window will not change after clicking “Save.”

    Image 926

  11. Upload the IPS to the CAT.

    • Click “Run” at the top right of the window.
    • Click “OK” at the bottom of the new window.
    Image of 927
    • You will have returned to your previous window. Click “Process Monitor” at the top right of the window.
    • The new window will indicate the status of your file upload. When the file is finished uploading, Run Status will be “Success.” 
      • If Run Status is “Queued,” it indicates that the file is waiting to be uploaded.
      • Click Refresh at the top right of the window to refresh the status of the process.
  12. Once the Run Status says “Success” and Distribution Status says "Posted," download the output.
    • Click “Details.”
    • Note: This page may appear differently depending on the browser you are using.
    process list
    • Click “View Log/Trace” at the bottom right of the window.
    Image of 929
    • Under File List, click the file with the .xls extension to download the output.
    Image of 9210
    • When opening the file, if an error message appears indicating that “The file you are trying to open…is in a different format than specified by the file extension,” click “Yes.”
    Image of Click Yes for File Extension
  13. Review the output file.
    • Open the downloaded Excel file.   
      • A depiction of the output fill is displayed below.
      • A description of the different header fields of the output is below.
    • The file will contain Error, Warning, and Successful sections. 
      • Review the Error and Warning section of the output. Use the ERRORMSG column to diagnose the errors and correct if necessary.
      • A table with error descriptions is below.
    IPS to CAT upload process
    • Correct the IPS based on the errors in the output. Once all errors have been corrected, the upload can be run in Process mode.

Output Header Fields

Report Parameters

 Field Name  Description
 Run Control ID  The run control used to create the report.
 Run Date  The date the report was created.
 Mode     The mode used for the upload. Either report or process.
 Process Instance  The number assigned by PeopleSoft for each process.
 Ran By  The user ID of the user who ran the report.

Report Statistics

 Field Name  Description
Total CAT Job Records Updated  The sum of CAT records updated successfully and updated with warnings.
Updated  The total number of CAT job records processed successfully.
 Updated with Warning  The total number of CAT job records processed successfully but with a warning.
 Rejected  The total number of CAT job records rejected because of errors.

Error Warning & Successful Messages

IPS Edit Errors

 #  Row Type  Description  Comments  Message
 1  Error  Empl Id/Empl Rcd Not Active in HRS  Entire row errors out  Did not load
 2  Error  Position Number does not match emplid/empl rcd    Did not load
 3  Error  Invalid Effective Date, Emplid, emplrcd, position number, business unit, deptid, action reason, comprate, funding string    Did not load
 4  Warning  Department (UDDS), Empl Class, Job Code, Pay Basis, EJED mismatch between loaded row and HRS.   Load, but only load planned FTE, Action Reasons, Change amounts and funding strings.  Take all other information from HRS>  Loaded w/Warning
 5  Warning  Identical emplid/empl rcd/action reason/change amount exists  Funding section still loads  Loaded w/Warning
 6  Warning  Identical funding string/distribution % exists  Compensation section still loads  Loaded w/Warning
 7  Warning  Change amount replaced for existing action reason    Replacement
 8  Warning  Distribution % replaced for existing funding string    Replacement
 9  Successful  New Emplid/empl rcd combos  Create New CAT record  Loaded Successfully
 10  Successful  Existing record, but unique emplid/empl rcd/actionreason/change amount exists  Add to existing CAT record  Loaded Successfully
 11  Successful  Existing record, but unique funding string/distribution% exists  Add to existing CAT record  Loaded Successfully

IPS Replace Errors

 #  Row Type  Description  Comments  Message
 1  Error Empl Id/Empl Rcd Not Active in HRS  Entire row errors out  Did not load
 2  Error Position Number does not match emplid/empl rcd    Did not load
 3  Error Invalid Effective Date, Emplid, emplrcd, position number, business unit, deptid, action reason, comprate, funding string    Did not load
 4  Warning Department (UDDS), Empl Class, Job Code, Pay Basis, EJED mismatch between loaded row and HRS.   Load, but only load planned FTE, Action Reasons, Change amounts and funding strings.  Take all other information from HRS.  Loaded w/Warning
 5  Warning    Loaded w/Warning
 6 Successful  New Emplid/empl rcd combos  Create New CAT record  Loaded w/Warning
 7  Error  Empl Id/Empl Rcd Not Active in HRS  Entire row errors out  Replacement

Additional Resources

Related KBs:

Related Links:



KeywordsComp compensation tool administration initialization campus salary unique output csv report compensation adjustment error warning cbs flag ejed compensation pay changes fund compensation admin tool fiscal year excel position job aap ipsCAT HR, Human Resources HR, Human Resources   Doc ID54557
OwnerJessica R.GroupUW–Shared Services
Created2015-07-31 14:41:29Updated2023-08-15 10:23:45
SitesUW–Shared Services
Feedback  0   0