In order to use Attendance Import, the user will need to have the default USPS_STANDARD or USPS_STANDARD_ATTENDANCE_IMPORT role. (can be added under ADMIN/ROLE)
The Utilities/Attendance Import option allows data obtained from third party software to be imported into USPS. The information is imported into Attendance and may be used to automatically load information into Current or Future.
The import file supplied by the third party software must conform to a specific format. It must be a comma delimited or a comma separated values (CSV) file. There are certain fields that are required and the data must be in a certain format/order.
The Attendance Import option has the ability to import records into both Attendance and CURRENT/FUTURE. A specific Expenditure Account can be specified to be charged for the CURRENT/FUTURE record.
The header line on the csv file must be removed before importing the file.
- Attendance Import File- can import file supplied by a third party software. Must be a comma delimited or a comma separated values (CSV).
- Location Code - prompt is used to determine the job number to post the transaction to if a job number is not supplied. The location code may contain either a Building IRN or Building/Department code. If you are using the location code field, enter one of the following codes:
- Building IRN
- Building/Department Code
- Post to Payroll Processing Options offers the following options:
- Post to Payroll Processing Future
- Post to Payroll Processing Current
- None - No Payroll Processing Current/Future posting will take place. The "Calc gross" parameter from the CSV file is ignored as well
- Combine Attendance Entries
- Check if AT entries charged to CURRENT/FUTURE will be combined for an employee. In order to combine entries in CURRENT/FUTURE, the EMPID, job number, pay type, unit, unit amount, tax option, retirement flag, expenditure account, leave projection flag, and the BRDDIS flag must match
Leave unchecked if AT entries will not be combined when posted to CURRENT/FUTURE
Allow Negative Leave Balance prompt offers the following options:
Check if AB entries for Sick, Vacation and Personal Leave will be allowed to go negative. It will appear as a WARNING on the report.
Leave uncheck if AB entries for Sick, Vacation and Personal Leave will NOT go negative. It will appear as a FATAL Error on the report
- Payroll Account to Charge prompt offers the following options:
- Defined Payroll Account for Position -Pay account defined in Payroll Account for the job will be use
- Sub for SSN -the regular employee's pay account(s) will be charged.
USPS Attendance Import SPECS
Attendance/Absence Specific Record Formal
This record is used to import attendance and absence information into USPS.
|Field Name||Spreadsheet Column||CSV Position||Description||Format||Max. Size|
|Record Indicator||A||1||Literal record indicator must contain "AA" to indicate Absence/AttendanceLiteral||XXXX||4|
|Employee ID or SSN||B||2||NumberEmploy||XXXXXXXXX||9|
Optional: Compensation number. Either the job number or a location code must be provided to permit posting into CURRENT or FUTURE. This job number would be matched directly to the COMPENSATION record for the employee.Option
Optional: Buidling IRN or Building/Department code. If a job number is not provided, the location code may be used to determine which job the record is to be posted to.
|5||Transaction posting date||YYYYMMDD||8|
Valid values are: AB = absence AT = attendance
Valid values for type AT: AT = Attendance SB = Substituting Valid values for type AB: CA = calamity DO = dock HO = holiday JD = jury duty MI = military OT = other PL = personal leave PR = professional leave SI = sick UN = unknown VA = vacation
Length of absence or attendance
|Tracking Unit||I||9||Valid values for unit of attendance or absence: H = hourly D = dailyValid||XX||1|
|Unit amount||J||10||Optional: If supplied, this rate will be used for paying the job. If not supplied, the job will be paid using the unit amount from the POSITION record same location code.Optional|
Optional: Valid values for appointment type: Space = not defined 1 = Certified 2 = Classified
|Sub for ID||L||12|
Optional: Employee ID Number for absent employee. ID of employee whose position the sub is filling in for. May be used to determine pay account charging.
Optional: User defined. May be used to further define the transaction category.
Budget account or XREF code
Optional: Budget account to charge during payroll. An XREF code may be used rather than the full budget account code.
|Special Cost Center||XXXX|
|Calculate gross||O||15||Optional: Valid values for gross flag are: Y = yes N = no space = user defined at run time The user will be asked at run time, "Post to Current, Future, or None?" If none, this flag is ignored and no transactions will be posted to Payroll Payments - Current or Future. If the user answers Current or Future, the space and "Y" will provide the same result. The transaction will be posted to Payroll Payments - Current or Future. If "N", the transaction will not be posted to Payroll Payments - Current or Future.||X||1|
|Retire hours||P||16||Optional: Retirement hours will be imported to CURRENT or FUTURE, depending on what the user chooses. If retirement hours are not specified for a REG or OT pay type, the COMPENSATION Hours In Day fields (if populated) will be used to automatically calculate a value.||(+/-)999.999||8|
|Pay Type||Q||17||Optional: REG, MIS, OT, DCK, SHP, IRR, BCK, TRM, POF, NC1, NC2, NC3||XXX||3|
|Tax Option||R||18||Optional: 1, 2, 3 or space|
If left blank in csv file:
• If POSITION Supplemental Type = SUP1, then 1 is automatically assigned
• If POSITION Supplemental Type = SUP3, then 3 is automatically asssigned
• If POSITION Supplemental Type not SUP1 or SUP3, then 2 is automatically assigned
|Retirement option||S||19||Optional: Y, N or space |
Y or Space = Yes
N = No
|Pay Amount Description||T||20||Optional||Alphanumeric||25|
|Attendance posting Flag||U||21||Optional: Y, N or space|
Y or Space = Yes
N = No
|Board Distribution Flag||V||22||Optional: Y, N or space|
Y or Space = Yes
N = NO
|Leave Projection Flag||W||23||Optional: Y, N or space|
Y or Space = Yes
N = No
|Effective Date||X||24||Optional: The effective date only applies to FUTURE entries. This date must be a vaild date in the format of YYYYMMDD and should be in the future. CURRENT entries will always contain zeroes since it is for the current payroll.||YYYYMMDD||8|
|Label||Y||25||Required for Posting a Payroll Payment: Must match the label field on the compensation you want the payroll payment to be posted for. This field is optional if you are only posting attendance.||Alphanumeric|
All errors will be displayed in the error report file (ATTERR.CSV) that is generated when running the Attendance Import. When a record is not valid it will be added to the error report as a CSV line with a ' I ' character at the end. Everything after this is the error message:
AA,XXXXXXXXX,01,,20160520,AB,VA,1,D,320.000,1 | "Error message here"
The following fatal errors may be encountered during the import and are displayed on the ATTERR report.
- Attendance for the day cannot total more than 1 day.
- If the tracking unit is "D" (daily) and the transaction length is greater than 1 or less than -1.
- Attendance for the day cannot total more than 24 hours
- If the tracking unit is "H" (hourly) and the transaction length is greater than 24 or less than -24.
- An Attendance entry cannot be posted to a position for which the job status is deceased.
- An ATTENDANCE entry cannot be posted to a position for which the Job Status is deceased in POSITIONS
- An Attendance entry cannot be posted to a position for which the job status is terminated.
- An ATTENDANCE entry cannot be posted to a position for which the Job Status is terminated in POSITIONS
- Appropriation account does not exist for budget account
- Budget account to be charged does not have a corresponding appropriation account. ATDSCN record and UPDCAL records are not written.
- Board distribution flag must be Y, N or blank. If not specified the default is Y
- Can not determine compensation for posting transaction to payroll. More than 1 active non-contract compensation.
- Date of transaction is not in YYYYMMDD format or is invalid based on the month and the number of days in the month (ie. 19990231 would be invalid since February does not have 31 days.)
- Correct date formatting on the csv file would be YYYYMMDD.
- Days worked = work days in contract.
- The days worked cannot equal work days in the contract
- Days worked exceeds work days in contract.
- The days worked cannot exceed the work days in the contract.
- District not set up to use XREF codes
- XREF code has been supplied in the CSV record but the district's USAS configuration information indicates the district does not utilize XREF codes. The record is not written to ATDSCN or UPDCAL.
- Invalid Activity Date.
- Date must be in YYYYMMDD format. Date on the csv file is blank.
- Invalid (insert budget account related item here)
- For example, invalid function per the current Blue Book standards. Budget account to be charged is invalid. Record is not written to ATDSCN or UPDCAL.
- Invalid Effective Date
- Effective Date must be in YYYYMMDD format.
- Invalid job number
- If posting to UPDCAL and the job number is zero or not found, or a job number was supplied in the CSV file, but there is no corresponding JOBSCN record on file for the SSN and job number.
- Invalid pay type
- Posting to UPDCAL and an invalid pay type (other than spaces) is found.
- Invalid special cost center
- Special cost center in budget account to be charged is not valid. Record not written to ATDSCN or UPDCAL.
- Invalid transaction category
- If an AT transaction type, the category must be either AT or SB. If an AB transaction type, the category must be one of the following: CA, DO, HO, JD, MI, OT, PL, PR, SI, UN, VA.
- Invalid transaction date
- Transaction date is prior to 1970/01/01. Date of transaction is not in YYYYMMDD format or is invalid based on the month and the number of days in the month (ie. 19990231 would be invalid since February does not have 31 days.)
- Invalid transaction type
- Transaction type must be either AB or AT.
- Invalid record indicator on record:
- The record number specified does not contain a record indicator of AA. Verify the information listed on the CSV file.
- No Employee found for Employee #: XXXXXXXX
- There was no match found in the employee master file for the SSN/Employee ID supplied in the CSV file.
- No position number provided or determined for payroll posting
- A position number is not defined on the csv file for the related record.
- The Appointment Type in the CSV file must match the Appointment Type on Position View. Employee #: XXXXXXXX
- The appointment type in the csv file must match the appointment type in POSITION screen.
- The value supplied in the CSV file for the calculate gross option was invalid. This field is optional. If supplied it must be either Y, N OR Space.
- This Position / Employee is not eligible for personal leave
- According to CORE/POSITION, the job/employee is not eligible for personal leave.
- This Position / Employee is not eligible for sick leave
- According to CORE/POSITION, the job/employee is not eligible for sick leave.
- This Position / Employee is not eligible for vacation leave
- According to CORE/POSITION, the job/employee is not eligible for vacation leave.
- Leave Projection flag must be Y, N or blank. If not specified the default is Y.
- Leaves records do not exist for Employee #: XXXXXXXXX
- There is no CORE/LEAVES record on file for the SSN. This record must be present to post an absence for sick, vacation, or personal leave.
- No acct code, but has BRDDIS/LEVPRO.
- Account code is needed if BRDDIS or LEVPRO flags are specified.
- Pays paid = number of pays.
- Pays paid cannot equal number of pays.
- Position number could not be determined for payroll posting.
- Position number must be specified for this type of transaction.
- String index out of range: XX
- Account code needs to equal 28 digits
- Too large. The unit amount on the csv file cannot be greater than 9999.999.
- Tracking unit not equal to benefit unit
- The tracking unit supplied in the CSV file (hours/days) does not match the tracking unit defined on the employee's USPSCN/BENSCN record.
- Transaction length cannot equal zero
- Unable to write ABSDET record
- An error occurred while trying to write the ABSDET record. This generally means there is already a record on file with the same key values.
- XREF code not found
- XREF code supplied on CSV record could not be found on the district's XREF file. Record not written to ATDSCN or UPDCAL.