Overview of Functionality
During implementations of COINS OA, clients often need to load payroll YTD balances from their existing software into COINS OA. This document is a guide to the steps needed to load the data.
The Load Timesheet function allows you to import a comma-separated CSV fiel to create unposted computer checks. It can be used on any payroll type or frequency payroll period.
The import routine allows for validation prior to running the actual import.
Create Load Files
General Guidelines
When loading files, follow the general guidelines outlined below.
Extract the data from existing software for the for YTD amounts. Create the files in Microsoft© Excel.
Use Sorts and Filters to find discrepancies and duplicates.
Verify data accuracy on the Excel spreadsheet before loading.
Ensure YTD amount reconcile with accounts prior to loading.
The fields on the load files can be in any order, as long as kno is the first field and kcheck is the second field.
When loading the file into OA, the file must be in a CSV format. When creating a file using Microsoft Excel, it is recommended to first save the file as an Excel spreadsheet, then save the spreadsheet as a CSV file. If you need to modify the file, you should edit and save the Excel spreadsheet version first, then save it as a CSV file.
NOTE:
Modifying the CSV file directly using Excel, could result in the corruption of data, such as leading zero’s being lost.
An Excel file saved as a CSV file works very well and can be loaded from a local PC. The file can also be loaded from the host server.
Define the CSV import file as follows:
The first row must list the load file fields to be imported.
Column one must be the Employee Number (required).
Column two must be the Check Number (required).
The Check Number is the logical check number, not the check number associated with Net Pay. The Logical Check Number is typically 1 (for first check), 2 could be the second check of period, etc.
Each unique check number produces a separate check for the employee.
2. Create and Load Timesheet Load File
Follow the steps below to create and load a timesheet file.
Create a Load File by using the spreadsheet template shown below.
Row 1 is included for informational purposes only and will need to be deleted before loading the file.
The columns highlighted in green represent the data that will need to be extracted from the current software. COINS assists in populating the other fields prior to loading the data.
NOTE:
Generally, there will be one record per employee per earning category.
2. Create a new payroll period in COINS OA.
Check Date, Period End Date and period start dates should represent the date prior to Go Live Date (Example: go live 19/05/2018, dates will be 18/05/2018).
Monthly Calculation Period = 7 (must be higher than 6 for normal Pay Calcs to be ignored)
Month = Last Month of the Period
3. Once a Load File has been created, use the Payroll Load Timesheets function
Payroll > [Period] > Input > Load Timesheets to check the file for errors.
4. Once Timesheets have been loaded into Payroll, you may use the standard payroll functions to view, report, or inquire about the timesheets.
3. Load Timesheet 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.
Upload Mode: Check mode scans the file for errors and warnings; Load mode imports the file as Payroll Timesheets and reports any warnings. Any records in error are ignored.
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 Period Type is recurring, enter period 999.
Auto Generate: Unselect this box otherwise, timecards will be auto generated during this load
Validate header: Unselect this field
4. Load Timesheet Report Output
A report will be generated which 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.
NOTE: Line Numbers referenced on the report correspond to the rows on the CSV file.
Errors listed on the report must be corrected before you can load the file. Warnings should be reviewed, but are usually used for informational purposes only.
5. Create and Load Override Files
Overrides are all the elements of pay that affect Net Pay; they are entered separately from the Timesheet.
6. Create and Load Tax and Deduction Overrides
Create a Load File for Tax and Deductions Overrides using the template shown below
Row 1 is included for informational purposes only and must be deleted before loading the file.
The columns highlighted in green represent the data that must be extracted from the current software. COINS assists in populating the other fields prior to loading the data.
2. Once a Load File has been created, use the Load Timecard Overrides function (Payroll > [Period] > Input > Load Timecard Overrides). The procedure is exactly the same as the Load Timesheets function outlined above in Section 2.2.
7. Create and Load Super Overrides
Create a Load File for Tax and Deductions Overrides using the template shown below
Row 1 is included for informational purposes only and must be deleted before loading the file.
The columns highlighted in green represent the data that must be extracted from the current software. COINS assists in populating the other fields prior to loading the data.
2. Once a Load File has been created, use the Load Timecard Overrides function (Payroll > [Period] > Input > Load Timecard Overrides). The procedure is exactly the same as the Load Timesheets function outlined above in Section 2.2.
Post Timesheet
Once all data is verified and reconciled, the timesheets can be posted.
Follow the instructions outlined in Section 6 on page 11 to post. Since these records represent beginning balances, you do not want to affect Job Cost or the General Ledger.
8. Other
For easier review of the data, consider creating separate periods for each load file. This allows you to use Timecard Report, Earning Deductions MTD/YTD, and other tools to view the records loaded.
The defaulting of fields such as Rates, States, or Union Code occurs during Load Timesheets in the same way they would default if the timesheet is keyed directly into OA.
The Global Payroll Parameters listed below 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 Timecard Rate Security Types |
Reconciliation when COINS is Performing Beginning Balance Loads
In order to reconcile the beginning balances, the following information needs to be provided to COINS either by email (can password protect the files) or on Workshare for all the various periods that are being loaded:
Total Gross Pay
Net Pay by Employee
Total Tax by Employee and Company
Deduction Totals by Item by employee
Superannuation totals by contribution type by employee
Some payroll systems allow clients to run a Pre-Payment Summary process without generating payment summaries for reconciliation purposes.
There may be small variations between Legacy and COINS systems. COINs will advise of these variations before process is completed.
NOTE:
If there are issues with the reconciliation, detail by employee may be requested.
Instructions to Post Payroll with All Posting Flags Disabled
NOTE:
COINS consultant will change the Workbench to mark payrun as posted. Only completed the below section if instructed to.
**Make sure no one is posting anywhere else in the company**
Set parameters all Parameters to Company Specific PRIOR to making any changes
To Turn off Posting Flags
Payroll Parameters
Set Global Payroll Parameter PR/CASHBOOK to N (turn off posting to Cash Manager)
Set Global Payroll Parameter PR/CPROFILE to N (disable updating Cash Profiles in Job Status)
General Ledger Parameter
Set General Ledger Parameter GL/AUTOPOST to N (autopost general ledger).
Post Payroll
Delete Unposted General Ledger Batch
General Ledger >Journals >Journal Batch
Find unposted batch that was created as a result of posting Payroll. Batch SOURCE will be either P0COST (weekly) P1COST (montly) or P2COST (2-Weekly). Tag the batch, and choose DELETE. The batch will get a status of Cancelled.
11. Delete Unposted Accounts Payable Batch
Accounts Payable > Invoices > Enter Invoices
Find unposted batch in Accounts Payable that was created as a result of posting Payroll.
Description will have P/R Invoice Batch # (# is costing workbench batch in PR)
Tag the batch, and choose DELETE. The batch will get a status of Cancelled.
12. Reset Parameters
13.Reverse steps A and B.