Attendance/Absence Import

The Utilities>Attendance/Absence Import option allows data (often obtained from third party software) contained in a load file to be imported into USPS. When the load file is imported, information contained in the load file creates Core>Attendance records as well as payment information into Payroll Payments - Future or Payroll Payment - Current. For payment entries, payroll account(s) can be included in the import file for charging purposes. There is no limit to the number of records that can be included in the import file.

The import file must conform the following:

• Column placement must match the file specifications listed below.

• All required fields must be formatted the file specifications listed below.

• The load must be formatted as a comma delimited or a comma separated values (CSV) file.

Some districts utilize the deferred posting absence option when posting sick, vacation, and personal leave. The deferred absence posting option allows absence entries to be created in Attendance that affect a future pay period to be entered at any time. These leave entries will not touch the employee’s current leave balance until the Activity Date on the leave record falls within the payroll’s Start Date and Stop Date. If the System>Configuration>Deferred Absence Posting checkbox is marked prior to the file being loaded in Attendance/Absence Import, the deferred absences rules will apply. For more information regarding deferred absence posting, click here.

Open

• Attendance Import File - Must be formatted as a comma delimited or a comma separated (CSV) file.

• Location Code - Used to determine the Position to post the transaction to if one is not included in the load file. The Location Code may contain either a Position>Building IRN or Position>Building Code/Department Code. If using the Location Code field, select one of the following options: 

  •  Building IRN

  •  Building/Department Code

  •  None

• Post to Payroll Processing Options offers the following options:

  •  None - No Payroll Payments - Future or Payroll Payments - Current payment posting will occur. Column O (Gross) of the CSV file is ignored as well.

  • Post to Payroll Processing Future - Payment information will be posted to Payroll Payments - Future.

  • Post to Payroll Processing Current - Payment information will be posted to Payroll Payments - Current.

• Combine Attendance Entries

  • Mark the checkbox - If AT entries that include payment information, when imported will create Payroll Payment - Future or Payroll Payments - Current entries, for employees should be combined. In order for Payroll Payments - Future and Payroll Payments Current entries to be combined, Column B, Column C, Column Q, Column I, Column J, Column R, Column S, Column V, Column N, Column W must match. These entries will be combined on the employees pay slip - not on the Pay Report.

  • Unmark the checkbox - If AT entries that include payment information, when imported will create Payroll Payment - Future or Payroll Payments - Current entries, for employees should not be combined.

• Allow Negative Leave Balance prompt offers the following options: 

  • Mark the checkbox - If AB entries, when imported will create Attendance records for sick, vacation and personal leave, should allow employees leave balances to become negative. These situations will appears as warning errors on the error report.

  • Unmark the checkbox - If AB entries, when imported will create Attendance records for sick, vacation and personal leave, should not allow employees leave balances to become negative. These situations will appears as fatal errors on the error report.

    • Even if the Rules>Rule to prevent Sick Leave balance from going negative is false, the checkbox needs to be checked to allow negative balances.

• Payroll Account to Charge prompt offers the following options:

  • Defined Payroll Account for Position - Charge the Position>Pay Account(s).

  • Sub for SSN - Charge the regular employee’s Position>Pay Account(s). The Employee ID entered must have a Compensation with remaining pays (Pays Paid must be less than Pays In Contract).

    • Specific object codes can be entered when the Sub for SSN option is selected.

      • Certified Object - Enter the appropriate certified object code.

      • Classified Object - Enter the appropriate classified object code.

Open

File Specifications

 This file is used to import attendance and/or absences into Attendance and/or payment information into Payroll Payment - Future or Payroll Payment - Current. The field names that are bolded are required in the load file. Column headings are not required in the load file; however, column placement must be match the spreadsheet column listed below.

USPIMPORT.CSV Sample

Open

ATTERR Error Report

Once the import file has been loaded, any errors will be displayed on the Attendance/Absence Import grid and listed in the ATTERR.CSV report. To view the error(s), open the ATTERR.CSV file. Below is an example of an error message (the error is in bold):

• AA,XXXXXXXXX,01,,20160520,AB,VA,1,D,320.000,1 | "Error message here"

Any necessary corrections can be made directly in the ATTERR.CSV file. Removing the error messages is not required. The corrected ATTERR.CSV file can then be saved in CSV format and then loaded using the Attendance/Absence Import option.

Common Fatal Errors:

• Attendance for the day cannot total more than 1 day.

  •  If the Leaves>Leave Unit equals Daily and the Length is greater than 1 or less than -1.

• Attendance for the day cannot total more than 24 hours.

  •  If the Leaves>Leave Unit equals Hourly and the Length is greater than 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 with a Job Status that equals Deceased.

• 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 with a Job Status that equals Terminated.

• Appropriation account does not exist for budget account.

  • Budget account to be charged does not have a corresponding appropriation account. Verify the Expenditure Account is in the CSV file (Column N).

• Balance for Sick (Employee#: XXXXXXXXX) is negative.  Negative leave balances are not allowed.

  • Verify the employee’s Leaves>Balance.

  • Verify System>Rules:

    • Is the Rule to prevent Sick Leave Balance from going negative enabled (true)?

  • If advance sick leave is permitted (Leaves>Max Advance Leave has a value and Advanced Units Used along with System>Configuration>Advance Sick Leave Configuration and System>Rules>Rule to prevent Advanced Sick Leave balance from going past Max Advanced allowed is set, mark the Allow Negative Leave Balances checkbox in Attendance/Absence Import.

• Board distribution flag must be Y, N or blank. 

  • Verify the Board Distribution Flag field in the CSV file is set to a Y, N, or a space (Column V).

• Cannot determine compensation for posting transaction to payroll.  More than 1 active non-contract compensation.

  • Employee has more than one Compensation created for the same Position. Verify the correct Position Number (Column C) and a Compensation>Label is entered (Column L) in the CSV file.

• Compensation is advanced.  New earnings (REG) may not be added to the compensation.

  • When the Compensation>Strs Advance checkbox is marked a payment using the Regular Pay Type can not be created. Verify the Pay Type in the CSV file (Column Q).

• Date of transaction is not in YYYYMMDD format or is invalid based on the month and the number of days in the month.

  • Date must be in YYYMMDD format and/or be a valid date within the month. Verify the Activity Date in the CSV file (Column E).

• Days worked = workdays in contract.

  • The Compensation>Contract Days Worked cannot equal Contract Work Days.

• Days worked exceeds workdays in contract.

  • The Compensation>Contract Days Worked can not exceed the Contract Work Days.

• District not set up to use XREF codes.

  • XREF code has been supplied in the CSV record, but the district does not have XREF codes created in USAS>Accounts. Verify the Expenditure Account is in the CSV file (Column N).

•  Invalid Activity Date. 

  • Date must be in YYYYMMDD format and can not be blank. Verify the Activity date in the CSV file (Column E).

• Invalid XXXXXX account.

  • Account in the CSV file is invalid. The pay account must meet AOS Blue Book standards. Verify the Expenditure Account is in the CSV file (Column N).

• Invalid Effective Date.

  • Date must be in YYYYMMDD format. Verify the Effective Date in the CSV file (Column X).

• Invalid job number.

  • If posting to Payroll Payments - Future or Payroll Payments - Current and the Position Number (Column C) is zero or a Position Number was supplied in the CSV file and there is no corresponding Position record on file for the Employee ID.

• Invalid pay type.

  • Posting to Payroll Payments - Future or Payroll Payments - Current and the CSV file contains an invalid Pay Type. Verify the Pay Type in the CSV file (Column Q).

• Invalid special cost center.

  • Special Cost Center of the pay account in the CSV file is not valid. Verify the Expenditure Account is in the CSV file (Column N).

• Invalid transaction category.

  • For the AT Transaction Type, the Category must be either AT or SB. For the AB Transaction Type, the category must be one either CA, DO, HO, JD, MI, OT, PL, PR, SI, UN, or VA. Verify the Transaction Category in the CSV file (Column G).

• Invalid transaction date.

  • Activity Date is before 1970/01/01, the date is not in YYYYMMDD format, or the date is invalid based on the month and the number of days in the month. For example: 19990231 would be invalid since February does not include 31 days. Verify the Activity date in the CSV file (Column E).

• Invalid transaction type.

  • Transaction Type must be either AB or AT. Verify the Transaction Type in the CSV file (Column F).

• Invalid record indicator on record:

  • The record number specified does not contain a record indicator of AA.  Verify the Record Indicator in the CSV file (Column A).

• Irregular pay type affects contract amount paid for advanced compensation, cannot be used.

  • When the Compensation>Strs Advance checkbox is marked a payment using the Irregular Pay Type can not be created. Verify the Pay Type in the CSV file (Column Q).

• No Active compensations found for posting transaction to payroll

  • Employee's Compensation has the Archived checkbox marked, the Compensation Start Date and Compensation Stop Date values are not within the current payroll date range. Verify the correct Compensation>Label is entered (Column L) and the Position Number (Column C) in the CSV file.

• No Employee found for Employee #: XXXXXXXX

  • There was no match found in the system for the Employee ID supplied in the CSV file. Verify the Employee record exists in USPS and the Employer Number in the CSV file (Column B). 

• No position number provided or determined for payroll posting

  • A Position Number was not supplied in the CSV file. Verify the Position Number in the CSV file (Column C).

• 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 Position>Appointment Type. Verify the Appointment Type in the CSV file matches USPS (Column K).

• The value supplied in the CSV file for the calculate gross option was invalid.   

  • Verify the Gross field in the CSV file is set to a Y, N, or a space (Column O).

• This Position / Employee is not eligible for personal leave.

  • The Core>Position>Eligible For Personal Leave checkbox is not marked.

• This Position / Employee is not eligible for sick leave.

  • The Core>Position>Eligible For Sick Leave checkbox is not marked..

• This Position / Employee is not eligible for vacation leave.

  • The Core>Position>Eligible For Vacation Leave checkbox is not marked.

• Leave Projection flag must be Y, N or blank. 

  • Verify the Leave Projection field is set to a Y, N, or a space (Column W).

• Leaves records do not exist for Employee #: XXXXXXXXX.

  • There is no Core>Leaves record in USPS for the Employee ID in the CSV file.

• No acct code but has BRDDIS/LEVPRO.

  • A Pay Account is required if the Board Distribution Flag (Column V) and Leave Projection Flag (Column W) are set to Y. Verify an Expenditure Account (Column N) is in the CSV file.

• Pays paid = number of pays.

  • The Compensation>Pays Paid cannot equal the Pays In Contract. Verify the Position Number (Column C) and/or Label (Column Y) in the CSV file.

• Position number could not be determined for payroll posting.

  •  Verify a Position Number is included in the CSV file (Column C).

• String index out of range: XX.

  • The Pay Account must be 28 digits. Verify the Expenditure Account in the CSV file (Column N).

• Too large. The unit amount on the csv file cannot be greater than 9999.999.

  • The unit amount is greater than the acceptable amount of 9999.999. Verify the Unit in the CSV file (Column I).

• Tracking unit not equal to benefit unit.

  • The Unit supplied in the CSV file does not match the employee’s Leaves>Leaves Unit. Verify the Unit in the CSV file (Column I).

• Transaction length cannot equal zero.

  •  Verify the Transaction Length in the CSV file is greater than zero (Column H).

• Unable to write ABSDET record.

  • When the system tried creating the record, a record with the same key values already exists.

• XREF code not found.

  • XREF code has been supplied in the CSV record, but the USAS>Accounts does not include the XREF code. Verify the Expenditure Account is in the CSV file (Column N).