HR - CAT - IPS Upload to CAT (Append) and Additional Adjustments Page

GENERAL DESCRIPTION:

The Institution Planning Spreadsheet (IPS) Load to the Compensation Admin Tool (CAT) and Additional Adjustments Page (AAP) (Append) process uploads data from the IPS to the CAT and AAP for analysis, reporting, and editing.  The IPS Load to CAP/AAP process appends existing data in the CAT/AAP by only adding new, unique employee records or rows to existing employees.

PROCESS CONSIDERATIONS:
  • The process will ignore and not replace data that is identical on all fields that make a unique record.  A record is considered 'unique' if any of the following fields are different when comparing one row to another row within the file and/or CAT:  Effective Date, Employee ID (Empl_ID), Employee Record (Empl_Rcd), Action, Action/Reason, and Change Amount.


Procedure Steps: IPS UPLOAD TO CAT (Append)

The IPS should be loaded to the CAT after all mass changes have been made in the Institution Planning Spreadsheet.  For information on how to use the IPS, please reference HR - CAT - Compensation Administration Tool Initialization Report .

1.  Save the "IPS → CAT" tab of the Institution Planning Spreadsheet as a .CSV file.

  • Open the "IPS → CAT" tab
  • Scroll to the bottom of this tab and delete any rows that appear like this:


CAT1.png

  • Press the "Click Here to Export File for CAT Page Upload" button


CAT2.png

  • Save the output file "Name.CSV" under the file menu


CAT3.png


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


CAT4.png


  • The "Name.CSV" file will appear on your screen.  You may close it out.

2.  Navigate to Workforce Administration > UW External HR Systems > Compensation Admin Tool > CAT Processes > Upload to CAT/AAP


3.  Create a new Run Control ID or use an existing Run Control ID.

  • The Run Control ID is a unique identifier that saves values for the Run Control Criteria.  There is no set format for creating a Run Control ID.  A suggested format is your initials.
  • Click either the "Find an Existing Value" or "Add a New Value" tab
  • Create a Run Control ID or enter the Run Control ID you would like to use.
  • Click either "Search" or "Add"
The Run Control screen will appear.


921.jpg

4.  Under "Process Options," choose between "Report" and "Process and Report."  The report mode should be run first.
  • The Report mode should be run first.  This mode allows the data to be processed without committing the data to the CAT.  It generates an output Excel (.xls) file that allows the data and any possible errors to be reviewed.

922.jpg
  • Once the data has been reviewed and any errors in the data have been corrected, the Process and Report mode can be run.  This commits the data to the CAT and generates an output Excel (.xls) file for reference.
5.  The Fiscal Year should be automatically populated.
6.  Select the appropriate "Inbound File Upload Criteria"


923.jpg

  • For File Type, select "Compensation Adjustment"
  • For Business Unit, you can type in your Business Unit, or click the magnifying glass to search for the appropriate Business Unit.
  • For File Description, enter "CAT_IPS plus the date.  (example:  CAT_IPS_20160101)

7.  Click the "Upload File" hyperlink next to the Attached File to browse for, select, and upload the CSV file from Step 1.

8.  Once the CSV file has uploaded, press the "Save" button at the bottom of the window.  The screen will appear the same and your Run Control parameters will be saved.

9.  Upload the IPS to the Compensation Admin Tool.
  • Press "Run" at the top right of the window.

924.jpg

10.  The Process Scheduler Request screen will be displayed.  Press OK to continue.


925.jpg

11.  You will have returned to your previous window.  Press "Process Monitor" at the top right of the window.

926.jpg

12.  The new window will indicate the status of your file upload.  When the file is finished uploading, the Run Status will equal "Success" and the Distribution Status will be equal to "Posted."
  • If the Run Status is "queued," it indicates that the file is waiting to be uploaded.
  • Click the Refresh button at the top right of the window to refresh the status of the document.

927.jpg

13.  Once the Run Status is equal to "Success," you can download the output.
  • Click the "Details" hyperlink

928.jpg

  • Click on the "View Log/Trace" hyperlink at the bottom right of the window.

929.jpg

  • Under File List, click the file with the Excel extension (.xls) to download the output.


9210.jpg

  • 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."

CAT15.png


14.  Review the output file.
  • Open the downloaded Excel file.
    • An example of the output file is displayed below.
    • A description of the different Header Fields is displayed in the "Output Header Fields" table 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 description of the different types of error messages is displayed in the "Error, Warning, and Successful Messages" table below.

CAT16.png

  • Correct the IPS based on the errors in the output file.  Once all errors have been corrected, the upload can be run in Process and Report mode.
    • If the output file contains errors after the upload is run in Process and Report mode, failed rows can also be manually added or edited directly within the CAT program.
  • If saving the output file, be sure to save with the file type of Excel Workbook (.xlsx).

OUTPUT HEADER FIELDS

CAT17.png

CAT18.png
 Number  Row Type
 Description  Comments  IPS Upload Error Message
 1

 Error

 Empl ID/Empl Rcd Not Active in HRS

 Entire row errors out

 Emplid/Empl_Rcd not in
Active HR STATUS
 2

 Error

 Position Number does not match
emplid/empl rcd
   Position_Nbr does not
match emplid/empl_rcd
 3



 Error



 Invalid Effective Date, Fiscal Year
Emplid, emplrcd, position number,
business unit, deptid, action reason
comprate, funding string, planned FTE
 Field errors.  Does this
need to include all
fields in the funding
string?

 INVALID_Field Name



 3.1  Error
 Invalid Account Code
   Account Code is invalid
 3.2

 Error

 Negative Distribution %

   Negative Distribution % is
invalid
 3.3

 Error

 Business Unit did not match Run
Control
   Business Unit: XXXXX did
not match Run Control
 3.4  Error  Invalid CSB Flag
 CSB Flag is not 'Y' or 'N'
 CBS Flag value is invalid
 3.5

 Error

 Planned FTE < 0

   Planned FTE cannot be
negative
 4.1


 Error


 Business unit did not match
between loaded row and HRS

   Business Unit: XXXXX
mismatch between loaded
row and HRS
 4.2


 Warning


 Deptid did not match between
loaded row and HRS

   Deptid: XXXX mismatch
between loaded row and
HRS
 4.3


 Warning


 FTE did not match between loaded
row and HRS

 For L Basis employees
HRS FTE .000250 = File
FTE 0
 FTE: XXX mismatch
between loaded row and
HRS
 4.4


 Warning


 Pay Basis did not match between
loaded row and HRS

   Pay Basis:  X mismatch
between loaded row and
HRS
 4.5


 Warning


 Comprate did not match between
loaded row and HRS

 Comprate pulled from
PS_COMPENSATION

 Comprate: XXXX mismatch
between loaded row and
HRS
 4.6

 Warning

 EJED did not match between
loaded row and HRS
   EJED mismatch between
loaded row and HRS
 5



 Warning



 Identical emplid/empl rcd/action
reason/change amount exists


 Funding section still loads



 Identical
emplid/empl_rcd/action
reason/change amount
exists in the file
 5.1


 Warning


 Invalid Action Reason for PAY


 Rate row will error out
but all other rate/fund
row still loads
 Action Reason is not valid
for PAY

 6


 Warning


 Identical funding
string/distribution % exists

 Compensation section
still loads

 Identical
emplid/empl_rcd/acct_cd/uw
_cat_dist_pct exists in the file
 6.1


 Warning


 Invalid Fund Code


 Errors out fund row but
all other rate/fund rows
still load
 Account Code:  Fund Code is
not valid

 6.2


 Warning


 Invalid Deptid


 Errors out fund row but
all other rate/fund rows
still load
 Account Code:  DeptID is
not valid

 6.3


 Warning


 Invalid Program Code


 Errors out fund row but
all other rate/fund rows
still load
 Account Code: Program
Code is not valid

 6.4


 Warning


 Invalid Project ID


 Errors out fund row but
all other rate/fund rows
still load
 Account Code:  Project ID
is not valid

 6.5


 Warning


 Invalid Business Unit


 Errors out fund row but
all other rate/fund rows
still load
 Account Code:  Business
Unit is not valid

 7  Successful  New Emplid/empl rcd combos
 Create New CAT record
 N/A
 8  Successful  Existing record, but unique
emplid/empl_rcd/action reason/
change amount exists
 Add to existing CAT record
 N/A
 9  Successful  Existing record, but unique funding
string/distribution % exists
 Add to existing CAT record
 N/A



Procedure Steps:  IPS UPLOAD TO AAP


The Additional Adjustments tab can be loaded to the AAP after all mass changes have been made in the IPS.  For information on how to use the IPS, please reference:  XXXXXX

1.  Save the "IPS →AAP" tab of the IPS as a CSV file.
  • Open the "IPS → CAT" tab
  • Scroll to the bottom of this tab and delete any rows that appear as so:
CAT1.png

  • Press the "Click Here to Export File for AAP Upload" button

CAT19.png
  • Save the output file "Name.CSV" under the file menu
CAT20.png
  • The following box will appear in Excel.  Select OK to continue.
CAT21.png
  • The "Name.CSV" file will appear on your screen.  You may close it out.
2.  Navigate to the CAT:  Workforce Administration > UW External HR Systems > Compensation Admin Tool > Upload to CAT and AAP Process

3.  Enter an Existing Run Control or create a new Run Control.
CAT23.png
4.  The following screen will appear:
924.jpg
5.  Under "Process Options," choose between "Report" and "Process and Report."
  • The Report mode should be run first.  This mode allows the data to be processed without committing the data to the AAP.  It generates an output .xls file that allows the data and any possible errors to be reviewed.

922.jpg

  • The Process and Report 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 AAP and generates an output .xls file.
6.  The Fiscal Year should be automatically populated.

7.  Select the appropriate "Inbound File Upload Criteria"
  • For File Type, select "Additional Adjustment"
  • For Business Unit, click the magnifying glass to search for the appropriate Business Unit.  Click the blue hyperlink for the Business Unit to select it.
  • For File Description, enter "AAPCAT IPS + Date (Example:  CAT IPS 20160101)

  • Click the "Upload File" link next to the Attached File to browse for, select, and upload the CSV file from Step 1.
CAT27
  • Once the CSV file has uploaded, press the "SAVE" button at the bottom of the window.
    • The screen will appear the same, and your Run Control will be saved.
8.  Upload the AAP to the CAT.
  • Press "RUN" at the top right of the window.
924.jpg
  • Click "OK" at the bottom of the new window.
925.jpg
  • You will have returned to the previous window.  Click the Process Monitor hyperlink at the top right of the window.
926.jpg
  • The new window will indicate the status of your file upload.  When the file is finished uploading, the Run Status will be equal to "Success" and the Distribution Status will be equal to "Posted."
    • If Run Status is "queued," it indicates that the file is waiting to be uploaded.
    • Click the Refresh button at the top right of the window to refresh the status of the document.
927.jpg
9.  Once the Run Status is "Success" and the Distribution Status is "Posted," download the output:
  • Click the Details hyperlink to open the Process Detail page.
928.jpg
  • Click the View Log/Trace hyperlink at the bottom right of the window.
929.jpg
  • Under the File List, click the hyperlink for the file with the .xls extension to download the output.
9210.jpg
  • 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" to accept.
CAT35
10.  Review the output file.
  • Open the downloaded file.  (A depiction of the Excel file output is shown below)
    • A description of the different Header Fields of the output is displayed below in the "Output Header Fields" table.
  • The file will contain Error, Warning, and Successful sections.
    • Review the Error and Warning section of the output.  Use the Error Message column to diagnose the errors and correct if necessary.
    • A description of the different error messages is shown below.
CAT36
  • Correct the AAP based on the errors in the output.  Once all errors have been corrected, the upload can be run in Process mode.
CAT37

 Number  Row Type
 Description  Comments  Message
 1

 Error

 Funding Department not
active in HRS
 Entire row errors out

 Invalid Funding
Department
 2

 Error

 Funding Department does
not match Business Unit
 
 Invalid Funding
Department
 3  Error  Fiscal Year not Active in CAT
   Fiscal Year not Active in CAT
 3.1

 Error

 Fiscal Year did not match
Run Control
   Fiscal Year did not
match Run Control
 4





 Error





 Invalid Fiscal Year, Funding
Department, Job Code, Pay
Basis, Position Number,
Business Unit, FTE, Adj Type
Fund, Deptid, Program,
Budgeted Amount
 Field Errors





 Did not load





 4.1  Error  Invalid Jobcode
   Invalid Jobcode
 4.2  Error  Invalid Pay Basis
 Pay Basis not 'H' 'A' or 'C'
 Invalid Pay Basis
 4.3  Error
 Invalid Position Type
 Position Type not 'Vacant' or
'Bud. Adj.'
 Invalid Position Type,
needs to be either Vacant or Bud. Adj.
 4.4  Error  Invalid Position Number
   Invalid Position Number
 4.5

 Error

 Invalid Adjustment Type

 Adjustment Type not 'SH' 'Grad'
'LTE' 'Univ Staff' or 'LI/AS/FA'
 Invalid Adjustment Type

 4.6

 Error

 Invalid Business Unit GL in
Account Code
   Invalid Business Unit GL

 4.7

 Error

 Invalid Fund Code in
Account Code
   Invalid Fund Code
 4.8

 Error

 Invalid Dept ID in Account
Code
   Invalid Department ID
 4.9

 Error

 Invalid Program Code in
Account Code
   Invalid Program Code

 4.10

 Error

 Invalid Project ID in
Account Code
   Invalid Project ID

 5

 Error

 Identical AAP Row Exists
(entire row is key)
 AAP Row Exists entire row is key
not 'H' 'A' or 'C'
 Identical AAP Row Exists in File/Table

 6


 Error


 Incomplete funding string
(missing fund, deptid, or
program)
 Funding string missing fund
deptid or program not 'H' 'A' or 'C'

 Account Code is invalid


 7


 Error


 Row Errors out, no
budgeted amount entered
for positive FTE or no FTE
 Errors out no budgeted amount
entered for positive FTE or no
FTE not 'Vacant' or 'Bud. Err.'
 
 7.1  Error  No FTE entered
 No FTE for Empl Type 'LTE' or 'SH'
 No FTE entered
 7.2

 Error

 No budget amount for
position FTE
   No budget amount
entered for position FTE
 8



 Error



 Does not pass budget edits



 Does not pass budget edits error
'Vacant' or 'Bud. not.'


 No special messages.
The error messages from
Section 4 is used
for Budget Error Edits
 9  Warning  Adj Type SH, FTE Entered
 Everything will load except the
FTE, SH cannot have FTE entered
 Adjustment Type SH,
FTE Entered
 10

 Warning

 Adj Type LTE, FTE Entered

 Everything will load except the
FTE, LTE cannot have FTE entered
Adjustment Type LTE
FTE Entered
 11

 Successful

New Funding Department
Record
 Create New AAP record

 Loaded Successfully

 12

 Successful

 Existing record, but unique
row (entire row is Key)
 Add to existing AAP record

 Loaded Successfully

 13  Successful  Row loads, Negative FTE,
$0 budgeted amount
 Loads Negative FTE budgeted
amount Loaded 'Vacant' or 'Bud. loa.'
 Loaded Successfully


ADDITIONAL RESOURCES:

Related KBs

Related Links




Keywords:unique output CSV report compensation adjustment error warning CBS flag EJED compensation pay changes fund compensation admin tool fiscal year excel position job AAP IPSInstitutional Planning Spreadsheet   Doc ID:55923
Owner:Chris L.Group:Human Resource System (HRS)
Created:2015-09-04 09:25 CSTUpdated:2017-10-10 09:27 CST
Sites:Human Resource System (HRS)
Feedback:  0   0