EPAF Upload *HAPSS Additional Pay for Salary Services Overview Each semester 100’s of EPAFs are entered to hire employees into jobs with very similar attributes. For example, each semester each campus enters hundreds of EPAFs to hire Additional Pay for Salary Services. For many campuses the position number is the same (a shared pooled position by area – PSU, GSC, CEPS, COLA, COLSA, etc.), the contract dates are the same, the annual or daily rate is the same and quite often the employees are rehires from the prior semester or previous years. In response to this need, STHRs and HR IT Team have developed a Banner process: NZPELOD ‐ EPAF Upload, which will facilitate batch processing of large volumes of these types of EPAF transactions. Creating EPAFs via the EPAF Upload process will require users to populate a specialized MS Excel template supplied by STHRs for use with a specific EPAF Approval Category (*HAJFY, *HAPSS, *HGRAD, etc). Once the spreadsheet is completed, a restricted set of users (2 per Campus/BSC) will have the ability to save the MS Excel template as a .csv file to their dropoff folder on the FTS File server. These same users will then run the Banner process NZPELOD – EPAF Upload for their specific area. The process can be run in AUDIT mode to identify any errors in the file and finally in COMMIT mode to create EPAFs. The EPAFs will be subjected to the same Banner validations and EPAF Trappings as EPAFs entered directly into NOAEPAF. The process when run in AUDIT or COMMIT will generate output reports to assist users with auditing their transactions. The EPAFs produced by the process will have either a status of W‐Waiting or P‐ Pending. Users will then be able to access the EPAFs from within Banner to correct errors and apply the appropriate approvals. Tasks The instructions contained in this document focus on creating Hire Additional Pay for Salary Services EPAF transactions using the MS Excel HAPSS EPAF UPLOAD TEMPLATE, FileZilla and the NZPELOD – EPAF Upload process. 1. Accessing, Saving and Updating the MS Excel HAPSS EPAF UPLOAD TEMPLATE 2. Saving the MS Excel Worksheet with a .csv File Format 3. Using FileZilla to copy the .csv file to the FTS production file server 4. Using NZPELOD to Create EPAFs from the .csv file 5. Report samples.
Process flow
Open Excel HAPSS EPAF Upload Template And Save it with new name
HAJFY EPAF Upload Template
EPAF_DD_MMM_YYYY _XXXXXX
Enter or Correct Appointment Information in Excel Template
Save Template File in Department Directory
Save Template File in BANNER csv NOAEPAF Format
Department Directory
Department Directory
Audit
Exit Excel
Move files for upload using FileZilla
FTS Directory
NZPELOD AUDIT And / Or COMMIT
Commit
BANNER NOAEPAF
12/3/08
Page 2
I:\System_Replacement\Hr\Training\HR Training\Steady State\Training\Hire Additional Pay for Salary Services EPAF Flat File
Upload.docx
1.
Accessing, Saving and Updating the MS Excel HAPSS EPAF UPLOAD TEMPLATE 1.1
Accessing and Saving the HAPSS EPAF UPLOAD TEMPLATE
1.1.1 OPEN the HAPSS EPAF UPLOAD TEMPLATE located at: http://usnh.edu/banner/training/active/HAPSS%20EPAF%20UPLOAD%20TEMPLATE.xls 1.1.2 SAVE the MS Excel file in a directory used by your department. 1.1.2.1 File Name The suggested file naming convention is: epaf_mm_dd_yy_xxxxxxxx where xxxxxxxx could be your user name, BSC name, or anything meaningful to your department. 1.2
Updating the MS Excel HAPSS EPAF UPLOAD file
1.2.1 Columns and Rows 1.2.1.1 The first row of the worksheet must only contain column names. 1.2.1.2 Column names are case sensitive. 1.2.1.3 Optional columns have column names highlighted in White. 1.2.1.4 Required columns have column names highlighted in Yellow. If a required column is missing the file will not be processed. 1.2.1.5 You can add your own columns to the worksheet. Your column names should be unique and have the word USER somewhere in it (e.g. USER Totals). Blank column names are not allowed. 1.2.1.6 Column names defined in the EPAF UPLOAD TEMPLATE must not be changed. If a predefined column name is changed NZPELOD will not know how to process it. 1.2.1.7 Columns can be arranged in any order. 1.2.1.8 A new row is needed for each job record. 1.2.1.9 All rows, EXCEPT the first row containing column names, can be sorted in any order. 1.2.1.10 All data entered into date columns must be entered in the format: DD‐MMM‐YYYY [ex: 19‐JUN‐2008] 12/3/08
Page 3
I:\System_Replacement\Hr\Training\HR Training\Steady State\Training\Hire Additional Pay for Salary Services EPAF Flat File
Upload.docx
12/3/08
Page 4
I:\System_Replacement\Hr\Training\HR Training\Steady State\Training\Hire Additional Pay for Salary Services EPAF Flat File
Upload.docx
1.2.2 HAPSS Data 1.2.2.1 Required Data The following columns contain key data and are therefore required. If any key data is missing or is invalid the job record(s) will not be processed. a. ID b. Query Date c. Approval Category (Upper case) d. Position (Upper case) e. Suff f. Originator (Upper case) g. Contract End Date h. Timesheet Orgn i. Regular Rate j. Pays k. Factor l. FTE m. Sub‐Classification n. Effective Date [Term] 1.2.2.2 Suff Using the suffix entered in the Suff column, the NZPELOD program will create an EPAF with the next available one‐up suffix as determined by reviewing existing jobs and outstanding EPAFs. a. Suffix Numbering in the worksheet Any suffix number can be entered (1, 2, 3, a,b,c, etc.) in Suff b. Additional Pay for Salary Services with Multiple Jobs Referencing the Same Position. If an Additional Pay for Salary Services member has multiple jobs referencing the same position number the suffix entered for each job record in the file must be unique. c. Multiple Job Labor Distribution Records For those jobs entered with multiple job labor distribution lines, ID, Position and Suff are used to ensure the rows are grouped correctly. 1.2.2.3 Originator, Must be the Banner user id and must be in upper case 1.2.2.4 Regular Rate, allows for 4 decimal places.
1.2.2.4 Pays and Factors, allows for 1 decimal place. 1.2.2.5 FTE, allows for 3 decimal places. 1.2.2.6 Contract Number, highlighted in blue, is required for Granite State College and UNH. 1.2.2.7 Complem Pos Info, highlighted in blue, is required for Granite State College and Keene. This filed can be in upper and lower case or mixed case. 12/3/08
Page 5
I:\System_Replacement\Hr\Training\HR Training\Steady State\Training\Hire Additional Pay for Salary Services EPAF Flat File
Upload.docx
1.2.2.8 Salary Grade, highlighted in blue, is a required column for Keene.
12/3/08
Page 6
I:\System_Replacement\Hr\Training\HR Training\Steady State\Training\Hire Additional Pay for Salary Services EPAF Flat File
Upload.docx
1.2.2.10 Fund Code, Organization Code, Account Code and Percent, highlighted in green, are for entering job labor distribution information. a. Adding one job labor distribution record to the worksheet Enter the job record with all of the required and user specific information for the job including the job labor distribution information. b. Adding multiple job labor distribution records to the worksheet i. Enter the first job record with all of the required and user specific information for the job including the first set of job labor distribution information. ii. Insert a blank row below the job record you just created. iii. Copy the first row and paste it into the blank row. iv. Update only the job labor distribution information on the newly created row. v. Retro pay fields can exist on only 1 row for each position. If multiple rows are needed for distribution the retro pay fields on the additional lines need to be emptied. Repeat steps i) through iv) above until you have created all of the job labor distribution records. c. Processing Job Labor Distribution Information The job labor distribution information entered on the spreadsheet will replace the position labor distribution if that is your campus policy, otherwise it will be ignored. d. Percent The distribution Percent column allows for 4 decimal places. e. Missing Data If one of the job labor distribution columns is blank on a job record when the others are not an EPAF will not be created for the job. f. Percent Total must equal 100% All of the job labor distribution percentages entered on the worksheet for a job must add up to 100%. If they do not add up to 100% an EPAF will not be created for the job. g. 0% Labor Distribution Records on the Position If your position is defined with 0% Labor Distribution records, the EPAF created will have a status of Waiting and you will have to adjust or remove the labor distribution records on the EPAF. h. Multiple Labor Distribution Records on the Position If your position is defined with multiple labor distribution records which contain percentages other than what you would like on the job, you can enter the correct job labor distribution information in the spreadsheet. You can also decide to allow the position labor distribution information to default to the EPAF. If the EPAF has a status of Waiting because it has errors you can correct the percentages when you correct the errors. If the EPAF has a status of Pending because it has no errors you 12/3/08
Page 7
I:\System_Replacement\Hr\Training\HR Training\Steady State\Training\Hire Additional Pay for Salary Services EPAF Flat File
Upload.docx
will need to have the EPAF returned for correction so you can adjust the percentages accordingly. i. Once all records have been entered in Excel save the document in the designated department directory. 1.2.2.11 Earnings Code, Earnings Hrs/Units, Special Rate, and Cancel Date are for entering Retro Pay information. a) Earning information is only entered if retro pay is needed. b) The retro pay Earn Code should be coded with 155. c) Missing Data If one of the 4 earnings columns are left blank on a job record when the others are not an EPAF will not be created for the job. d) An Earn code of 130 and Earnings Hrs/Units of 10 will automatically be generated for every position being added. Fields needed for Retro pay e) Earnings Code f) Earnings Hrs/Units g) Special Rate column allows for 2 decimal places. h) Cancel Date i) Personnel Date
j) Personnel Date [Term]
12/3/08
Page 8
I:\System_Replacement\Hr\Training\HR Training\Steady State\Training\Hire Additional Pay for Salary Services EPAF Flat File
Upload.docx
2.
Saving the MS Excel Worksheet with a .csv File Format
2.1
Saving the MS Excel file in .csv File format Save the MS Excel file as a .csv file only after all jobs have been entered and the file is complete.
2.1.1 File Directory Save the .csv in the same department directory as the MS Excel file. 2.1.2 File Naming Conventions The .csv file name must adhere to the following naming conventions: a. Begin with epaf_ b. All lowercase. c. Never contain blank spaces. d. Never contain special characters such as *, %, or #,or ‐. e. Can contain the following characters (a‐z, 1‐9, _, . ) f. A suggested file name: epaf_mm_dd_yy_xxxxxxxx where xxxxxxxx could be your user name, BSC name, or anything meaningful to your department. 2.1.3 Saving the file using MS Office 2003 a. Click FILE on the menu bar. b. Click Save as. c. Select the desired directory in the Save in: field. d. TYPE the file name In the File name: field. e. Use the dropdown selection arrow
to select the Save as type: CSV (Comma delimited) (*.csv). f.
Click Save
g. Exit Excel and do not save changes to csv file when prompted.
2.1.4 Saving the file using MS Office 2007 a. Click the Microsoft Office Button
.
b. Click Save as. c. Select the desired directory in the Save in: field. 12/3/08
Page 9
I:\System_Replacement\Hr\Training\HR Training\Steady State\Training\Hire Additional Pay for Salary Services EPAF Flat File
Upload.docx
d. TYPE the file name In the File name: field. e. Use the dropdown selection arrow
to select the Save as type: CSV (Comma delimited) (*.csv). f.
Click Save
g. Exit Excel and do not save changes to csv file when prompted.
2.2
Managing the .csv File 2.2.1 NEVER open the .csv file. Once you save the .csv file NEVER open it.
2.2.2 Make corrections to your original MS Excel file Always make corrections to your original MS Excel file and then re‐save it as a .csv file. 2.2.3 Replacing an existing .csv file If you are replacing an existing .csv file, when you go to save the .csv file make sure to overwrite it by double clicking the existing .csv file name in the Save in: window. This will insure that you are using the same file name. You will receive the following warning:
Click Yes to replace the existing file which will save it with the same name. 2.2.4 Creating a new .csv file If you are saving a different .csv file, make sure to save it with a different name than the first file you saved. 2.2.5 csv Icons and xls icons have a different look. csv icon
12/3/08
xls icon
Page 10
I:\System_Replacement\Hr\Training\HR Training\Steady State\Training\Hire Additional Pay for Salary Services EPAF Flat File
Upload.docx
3.
Using FileZilla to copy the .csv file to the FTS production file server 3.1
Access the FTS File Sharing Server These instruction assume the application FileZilla [SSH SECURE File Transfer Client] has already been installed on your computer and the FTS connection has already been established. The FileZilla application and instructions for Using FileZilla with FTS are available at: http://www.usnhgateway.org/soft.html
3.1.1 3.1.2 3.1.3 3.1.4 3.1.5 3.1.6 3.1.7 3.1.8
Start FileZilla. Select File from the menu bar. Select Site Manager. Click on FTS. Enter your User: and Account: info if it isn’t already displayed. Enter your password. Click Connect. Access your FTS folder: In the Remote Site: window pane [MIDDLE RIGHT] you will see /fts/ already entered. Double click on the folders listed in the Filename column until you reach the last folder as defined below for your group: FTS folders list Campus BSC (NZPELOD parameter) Users dropoff folder on FTS /fts/gsc/fis/user_dropoff/usnhfs GSC Granite State College /fts/ksc/hrs/user_dropoff/hr KSC Keene /fts/psc/hrs/user_dropoff/hr PSU Plymouth /fts/fis/user_dropoff/colsabsc UNH UNH COLSA /fts/fis/user_dropoff/colabsc UNH UNH COLA /fts/fis/user_dropoff/cepsbsc UNH UNH CEPS /fts/fis/user_dropoff/wsbebsc UNH UNH WSBE /fts/fis/user_dropoff/chhsbsc UNH UNH HHS /fts/fis/user_dropoff/aabsc UNH UNH Academic Affairs /fis/unhm/fis/user_dropoff/bsc UNHM Manchester /fts/usnh/user_dropoff/hr USNH USNH
3.2
Copy the .csv file to the FTS dropoff folder 3.2.1 In the FileZilla Local Site: window pane [MIDDLE LEFT], double click on the folders listed in the Filename column until you reach the departmental directory containing the .csv file you want to copy to your FTS dropoff folder. 3.2.2 If you are replacing an existing file make sure you use the same name when copying the .csv file to FTS. If you use a different file name two separate files will exist in your dropoff folder and the NZPELOD process will process both files.
12/3/08
Page 11
I:\System_Replacement\Hr\Training\HR Training\Steady State\Training\Hire Additional Pay for Salary Services EPAF Flat File
Upload.docx
3.2.3 Right Click and drag the .csv file in the Local Site: window pane [MIDDLE LEFT] to drop it into the white space below the Remote Site window pane [MIDDLE RIGHT].
After the file is copied the screen should look like the following:
3.2.4 It may take 15 minutes for the file to be available for use with the NZPELOD process in BPRD. 3.3
12/3/08
Exit FileZilla 3.3.1 Select File from the menu bar. 3.3.2 Select Disconnect.
Page 12
I:\System_Replacement\Hr\Training\HR Training\Steady State\Training\Hire Additional Pay for Salary Services EPAF Flat File
Upload.docx
4.
Using NZPELOD to Create EPAFs from the .csv file 4.1 Lon into VPN 4.2 Log into Banner. 4.3 In the Go To... field on the Banner main menu type NZPELOD and press Enter. 4.4 Click on Next Block icon to get to the Printer Control block. Confirm in the Printer Control block in the Printer: field DATABASE is displayed. 4.5 Access the Parameter Values block 4.4.1 Click on Next Block icon to get to the Parameter Values block 4.4.2 Define Parameter 01 – Audit or Commit Indicator 4.4.2.1 Parameter 01 has two modes: ‘A’ Audit When the process is run in this mode it will generate error reports but WILL NOT create EPAFs for user approval and completion. ‘C’ Commit When the process is run in this mode it will generate error reports and WILL create EPAFs for user approval and completion. 4.4.2.2 ALWAYS run the process in A – Audit mode first. You will always want to run the process in Audit mode first to check for any errors. 4.4.2.3 The mode can be typed in the field or you can use the Value selection button to select it. Double click the mode to select it from the list. 4.4.3 Define Parameter 02 – Campus/BSC name 4.4.3.1 Click the Next Record button to navigate to the next parameter value. 4.4.3.2 Parameter 02 is used to select the Campus/BSC name. The value should always be selected by using the Value selection button Campus or BSC name to select it from the list.
12/3/08
to select it. Double click the
Page 13
I:\System_Replacement\Hr\Training\HR Training\Steady State\Training\Hire Additional Pay for Salary Services EPAF Flat File
Upload.docx
4.5
Submit/Run NZPELOD 4.5.1 Click on Next Block icon
to get to the Submission block.
4.5.2 Click the save Icon to submit/run the NZPELOD process. The process may take a few minutes to run. 4.5.3 Write down the process number displayed in the BOTTOM LEFT corner of the Banner window. You will use this number to select the appropriate output for review. Review Output Reports
4.6
4.6.1 Select the Options menu. 4.6.2 Select the Review Output [GJIREVO]. 4.6.3 Click on the Selection button to the right of the File Name box to view the outputs. You should see the following three Output File names associated with nnnnnn being your process number: a. nzpelod_nnnnnn_errs.lis This report lists all the errors that will stop a row from being converted into an EPAF. b. nzpelod_nnnnnn_eoaf.lis This report lists EPAFs created with Warnings and/or Errors. c. nzpelod_nnnnnn.log This report is a log of the processing and provides files selected and record counts. 4.6.4 Double click on the report you want to view or select the report you want to view on the list and click the OK button. 4.6.5 To print the report: a. Click on the Options menu. b. Click on the Show Document (Save and Print File) option. c. Click the Yes button on the following message.
d. Click on the File menu in the browser window. e. Click on the Print option
12/3/08
Page 14
I:\System_Replacement\Hr\Training\HR Training\Steady State\Training\Hire Additional Pay for Salary Services EPAF Flat File
Upload.docx
5.
Report samples. 5.1
nzpelod_999999_errs.lis
Note:
5.1.1 The report lists all the errors that will stop a row from being converted into an EPAF. If the report was created while running it in audit mod the EPAF upload file can be corrected and reprocessed. 5.1.2 On the report you can find: FILE: The csv file name being processed. Rec: The record/row number being processed in the csv file. Error Messages Error messages explain why the record was not Accepted. 5.1.3 If NZPELOD was run in Audit mode the corrections should be mode in the Excel document. 5.1.4 If NZPELOD was run in Commit mode the EPAF will need to be entered through the BANNER process NOAEPAF.
12/3/08
Page 15
I:\System_Replacement\Hr\Training\HR Training\Steady State\Training\Hire Additional Pay for Salary Services EPAF Flat File
Upload.docx
5.2
nzpelod_999999_epaf.lis
Note:
12/3/08
5.2.1 The report lists the EPAF transactions that where created and any EPAF trapping errors. 5.2.2 The record will have a status of “P” (pending) or “W” (waiting if errors found) 5.2.3 On the report you can find: FILE: The csv file name being processed. Rec: The record/row number being processed in the csv file. Error Messages Error or Warning messages that may need to be corrected. Transaction # The Banner transaction number which can be used in NOAEPAF to make any corrections. 5.1.5 If NZPELOD was run in Audit mode the corrections should be mode in the Excel document. 5.1.6 If NZPELOD was run in Commit mode the corrections will need to be made through the BANNER process NOAEPAF.
Page 16
I:\System_Replacement\Hr\Training\HR Training\Steady State\Training\Hire Additional Pay for Salary Services EPAF Flat File
Upload.docx
5.2
nzpelod_999999.log
Note: 5.2.1 The log file has several pieces of valuable information BANUID User ID who ran NZPELOD. bsc The BSC the EPAF csv file was uploaded from. Load file The file name and path the file was loaded from. 14 lines loaded The number of line loaded including the heading line.
12/3/08
Page 17
I:\System_Replacement\Hr\Training\HR Training\Steady State\Training\Hire Additional Pay for Salary Services EPAF Flat File
Upload.docx