The Payroll Load Timesheets function in COINS OA allows you to import a comma-separated CSV file to create unposted computer checks. Timesheets may be loaded into Regular, Adjusting, or Future Periods.
The CSV file can contain basic information about the transaction and use standard payroll defaulting to create the remaining values needed on a payroll timesheet. Additionally, you can include fields in the load file that will hold values such as state, rate, or union.
You cannot adjust or void checks using this function. Additionally, it does not update existing timesheets.
Create Timesheet Load File
You can use any field that is used in the Payroll timesheet file in the Load file. However, it is important to note that every file must have kno (Employee Number) as the first element of the file, and kcheck (Check #, for example, 1, 2, 3) as the second element. You can refer to the sample Load files in Figure 1 and Figure 2 for reference. The remaining fields included in the file are listed below and will be determined based on the type of information you are loading.
Row 1 of Load Timesheets must contain the database field name. The list of fields that can be used with Load Timesheets and the field name can be found in Table 1.
Figure 1: Sample Timesheet Load File – Microsoft Excel Format
Figure 2: Sample Timesheet Load File – Notepad Format
When you load the file into Coins ERP+, it must be in CSV format. If you create the file using Microsoft Excel, it is recommended that you first save it as an Excel spreadsheet and then save the spreadsheet as a CSV file. If you need to make changes to the file, you should edit and save the Excel spreadsheet version first and then save it as a CSV file.
Modifying the CSV file directly using Excel could result in the corruption of data, such as leading zeroes being lost.
Payroll Timecard Fields
Any field used on Payroll timesheets can be included on the Timesheet Load file. The list of fields available for use is contained in Table 1.
Fields can be listed in any order in the Timesheet Load file. The exception is KNO, which must be the first field, and KCHECK, which must be the second field.
Table 1: Database Fields Used in Timecard Entry
Field | Database Field Name | Format | Notes |
Employee Number | kno | Text | MANDATORY first field |
Check # | kcheck | Integer (1-9) | MANDATORY second field |
Transaction # | ktran | Integer (1-99) | If omitted, transaction numbers will be assigned chronologically, based upon the order of the transactions in the file for each employee/check. |
Timecard Date | tce_date | Date (MM/DD/YYYY) | MANDATORY field, except when loading Recurring Timesheets |
Day of Period | tce_dayofper | Integer (1-7 for Weekly, 1-14 for 2-Weekly, 1-16 for Twice-Monthly, 1-28 for 4-Weekly, 1-31 for Monthly) | MANDATORY field for Recurring Timesheets only. The Timecard Date is auto-created based on the Period End Date of the active payroll, Day of Period on the timesheet, and the Period Frequency used. |
Earnings Category | pec_earncat | Text | If omitted, employee’s primary earnings category are used. If this value cannot be determined, an error will be returned. |
Pay Shift | tce_payshift | Text | Use code, not description. If omitted, the Pay Shift value will be blank. |
Costing Method | cpg-method | Integer -
| If omitted, value is determined by employee’s earnings category, then by employee’s default costing method. If this value cannot be determined, an error is returned. |
Analysis | pec_analysis | Text | If omitted, value is determined by employee’s earnings category, then by employee’s default costing method. If this value cannot be determined, an error is returned. |
Cost Transaction Analysis | jct_anal__1 jct_anal__2 jct_anal__3 jct_anal__4 jct_anal__5 jct_anal__6 jct_anal__7 jct_anal__8 | Text | Set up Job Status. When field is omitted from file, value = blank. |
Regular Hours | tce_rhrs | Decimal 0.00 | A blank value will be interpreted as 0.00. |
Overtime Hours | tce_ohrs | Decimal 0.00 | A blank value is interpreted as 0.00. |
Double Time Hours | tce_dhrs | Decimal 0.00 | A blank value is interpreted as 0.00. |
Regular Rate | tce_rrate | Decimal 0.0000 | When blank, value defaults from the earnings category. If the value is not to default from the earnings category, enter a decimal value. If zero is entered, this will be the value for the field. |
Overtime Rate Only available if configured to input overtime and double time rates | tce_orate | Decimal 0.0000 | When blank, value defaults from the earnings category. If the value is not to default from the earnings category, enter a decimal value. If zero is entered, this will be the value for the field. |
Double Time Rate Only available if configured to input overtime and double time rates | tce_drate | Decimal 0.0000 | When blank, value defaults from the earnings category. If the value is not to default from the earnings category, enter a decimal value. If zero is entered, this will be the value for the field. |
Regular Amount | tce_ramt | Decimal 0.00 | If Earnings Category’s basis is Hourly, this value will be set using rate x hours. For Amount Based Earnings Categories, when blank, value will default from the earnings category. If the value is not to default from the earnings category, enter a decimal value. If zero is entered, this will be the value for the field. |
Overtime Amount | tce_oamt | Decimal 0.00 | If Earnings Category’s basis is Hourly, this value will be set using rate x hours. For Amount Based Earnings Categories, a blank value will be interpreted as 0.00. |
Double Time Amount | tce_damt | Decimal 0.00 | If Earnings Category’s basis is Hourly, this value will be set using rate x hours. For Amount Based Earnings Categories, a blank value will be interpreted as 0.00. |
Reimbursable | tce_reimb | Decimal 0.00 | A blank value will be interpreted as 0.00. |
Union | pru-code | Text | Use code, not description. When blank, value will default from the Employee file. Use / to indicate value should be set to blank on the timesheet. |
Trade | tsv-code | Text | Use code, not description. When blank, value will default from the Employee file. Use / to indicate value should be set to blank on the timesheet. |
Skill Level | emp_skill | Text | Use code, not description. When blank, value will default from the Employee file. Use / to indicate value should be set to blank on the timesheet. |
EEO-1 Code | por_eeo | Text | Use code, not description. When blank, value will default from the Employee file. Use / to indicate value should be set to blank on the timesheet. |
Work Classification | emp_wrkcls | Text | Use code, not description. When blank, value will default from the Employee file. Use / to indicate value should be set to blank on the timesheet. |
Utilization Trade | emp_utiltrade | Text | Use code, not description. When blank, value will default from the Employee file. Use / to indicate value should be set to blank on the timesheet. |
Utilization Classification | emp_utilclass | Text | Use code, not description. When blank, value will default from the Employee file. Use / to indicate value should be set to blank on the timesheet. |
Certified | emp_certif | Y/N | Use code, not description. When blank, value will default from the Employee file. Use / to indicate value should be set to blank on the timesheet. |
Allocation Group | pag_code |
| Use code, not description. When blank, value will default from the Job or Section, for job transactions, or from Payroll Tailoring for GL, Asset, or Workshop Transactions. Use / to indicate value should be set to blank on the timesheet. |
State | tce_state | Text | When field is omitted from file, value will follow defaulting rules set in Employee file. If field is included in file, it may not be left blank. |
SUTA State | tce_sutastate | Text | When field is omitted from file, value will follow defaulting rules set in Employee file. If field is included in file, it may not be left blank. |
DBL State | tce_dblstate | Text | When field is omitted from file, value will follow defaulting rules set in Employee file. If field is included in file, it may not be left blank. |
WC State | tce_wcstate | Text | When field is omitted from file, value will follow defaulting rules set in Employee file. If field is included in file, it may not be left blank. |
Comp Code | tce_comp | Text | When field is omitted from file, value will follow defaulting rules set in Employee file. If field is included in file, it may not be left blank. |
City Locality | tce_local | Text | When field is omitted from file, value will follow defaulting rules set in Employee file. If field is included in file, use / to indicate value should be set to blank on the timesheet. |
County Locality | tce_local2 | Text | When field is omitted from file, value will follow defaulting rules set in Employee file. If field is included in file, use / to indicate value should be set to blank on the timesheet. |
School Locality | tce_local2 | Text | When field is omitted from file, value will follow defaulting rules set in Employee file. If field is included in file, use / to indicate value should be set to blank on the timesheet. |
Regular Daily Hours* | tce_rday__1 tce_rday__2 tce_rday__3 tce_rday__4 tce_rday__5 tce_rday__6 tce_rday__7 | Decimal 0.00 | If fields are omitted from the Timesheet Load, the regular, overtime, and double time hours automatically default into the appropriate day of the week if Default Daily Hours is checked in PR Payroll > Company Setup > Company Configuration. If fields are included in the Timesheet Load, a blank value will mean 0.00. |
Overtime Daily Hours* | tce_oday__1 tce_oday__2 tce_oday__3 tce_oday__4 tce_oday__5 tce_oday__6 tce_oday__7 | Decimal 0.00 | If fields are omitted from the Timesheet Load, the regular, overtime, and double time hours automatically default into the appropriate day of the week if Default Daily Hours is checked in PR Payroll > Company Setup > Company Configuration. If fields are included in the Timesheet Load, a blank value will mean 0.00. |
Double Time Daily Hours* | tce_dday__1 tce_dday__2 tce_dday__3 tce_dday__4 tce_dday__5 tce_dday__6 tce_dday__7 | Decimal 0.00 | If fields are omitted from the Timesheet Load, the regular, overtime, and double time hours automatically default into the appropriate day of the week if Default Daily Hours is checked in PR Payroll > Company Setup > Company Configuration. If fields are included in the Timesheet Load, a blank value will mean 0.00. |
*Daily Hours are stored in the payroll timesheet file as follows, where x will mean Regular (r), Overtime (o) or Double time (d).
Sunday | tce_xday__1 |
Monday | tce_xday__2 |
Tuesday | tce_xday__3 |
Wednesday | tce_xday__4 |
Thursday | tce_xday__5 |
Friday | tce_xday__6 |
Load Timesheets Function
Once a Load file has been created, use the Load Timesheets function to check the file for errors (Figure 3). A report will be generated (Figure 4) that will display how many records were included in the file (Row 1, which contains the field names counts as a record) and how many could be read successfully. Immediately following the number of records will be a list that contains Records in Error followed by Warnings.
Selection Criteria
File Location: The file could be located on a PC or network drive, or the OA server.
PC File Name: If the Load Timesheets file is located on the PC, use the Browse option to locate the file.
Server File Location: If the Load Timesheets file is located on the server, use the lookup option to locate the file.
Upload Mode: Check mode will scan the file for errors and warnings; Load mode
will import the file as Payroll Timesheets and report any warnings.
Period Type: Choose from Regular, Future, Adjusting, or Recurring.
Period: If Period Type is Regular, Future, or Adjusting, enter the period number to receive the timesheets. If the Period Type is Recurring, enter period 999.
Validate Header: Whether the header on the file should be validated.
Create Manual Checks: Whether the timesheets that are loaded will be created as Manual Checks.
This field is used to load checks that have been previously paid out, such as historical checks or year-end adjustments. If you load with this box checked, you will not be able to print the checks.
Figure 4: Load Timesheets Report
Errors must be corrected before you can load the file. Warnings should be reviewed but are usually used for informational purposes only.
Once Timesheets have been loaded into Payroll, you may use the standard Payroll functions to view, report, or inquire about the timesheets.
Other
For easier review of the data, consider creating separate future periods for each Load file. This allows you to use Timesheet Report, Timecard Detail Inquiry, and other tools to view the records loaded.
The defaulting of fields such as rates, states, or union code will occur during Load Timesheets exactly as they would default if the timesheet was keyed directly into Coins ERP+.
The following Global Payroll Parameters will affect a user’s ability to load timesheets:
Parameter | Description |
FUTUSER | Users who can only update a timesheet in a future period |
TERMUSER | Users who can enter a timesheet for a terminated employee |
TSSECURE | Use Timesheet Security Types and Timesheet Rate Security Types |