Reconcile Payroll Batches

Description: In this topic, the user will learn how to reconcile payroll batches after the employee has been paid. This helps track which entries have been paid. Additionally, it is necessary to reconcile dollar-based entries in a payroll batch in order to give a billable amount to the entry.

Role Required: Super User, Payroll Team

Permission Required: N/A

Download Raw Payroll Dump File

After a payroll batch has been processed and approved, the system will generate the raw payroll dump file. This file contains data that will be required to reconcile the batch. But first it's necessary to create the text file that will be used for reconciliation.

If on the final step of processing a batch and have the batch details screen open:

1. When a batch has been approved, the green Reconcile button displays and the batch details status is transmitted.

2. Scroll down the page to click the Attachments tab

3. Locate the file that begins with RawPayrollDump

4. Click the Download drop-down and select CSV as the file type

5. Click the blue Download Arrow Icon to start the download

6. Click the downloaded file to open the RawPayrollDump in Excel

If the batch was transmitted and the raw payroll file was not downloaded during the batch creation process:

1. Select Payroll from the main menu

2. Select Batches from the submenu

3. Use the filters to locate the batch and click Search

4. Click anywhere in the batch row to open the batch details page

5. When a batch has been approved, the green Reconcile button displays and the batch details status is transmitted.

6. Scroll down the page to click the Attachments tab

7. Locate the file that begins with RawPayrollDump

8. Click the Download drop-down and select CSV as the file type

9. Click the blue Download Arrow Icon to start the download

10. Click the downloaded file to open the RawPayrollDump in Excel

Create Reconcile Text File

1. Open Excel and create a file with the following column headers:

2.  

   1. PayrollEntryID

   2.  

      1. Listed as Entry Id on the RawPayrollDump file

      2. Create one row for each payroll entry in the batch

   3. PayrollSystemReferenceID

   4.  

      1. ID generated from the organization's payroll system/engine, external to DCI.

   5. PayrollRefCheckId (optional)

   6.  

      1. Check number on which the entry was paid, generated from the organization's payroll system/engine.

   7. UnitAmount

   8.  

      1. Unit amount as entered on the punch entry. This must match the punch exactly.

      2. Listed as Pay Units on the RawPayrollDump file

   9. NetPay

   10.  

       1. Net amount paid to employee, generated from the organization's payroll system/engine.

   11. PayDate

   12.  

       1. Date the employee was paid, generated from the organization's payroll system/engine.

   13. PayrollBurden

   14.  

       1. Total dollar amount expected to be billed for service; usually equal to what was paid out by the payroll system plus any taxes, fees, and other overhead expected to be included in the billed amount.

       2. Required for dollar-based entries, optional for all other units.

3. Save as a Text (TXT) file to import

*Please note: The headers must be spelled exactly as listed above and in the same sequence as listed above or there will be an error when trying to import the text file.

Reconcile a Single Batch

If on the final step of processing a batch and have the batch details screen open:

1. On an approved batch, click the green Reconcile button.

2. Complete the Reconcile from Payroll form wizard

   1. Enter a Ref Reconcile Id - Choose any reference number

   2. File Name - The name that will appear in the Attachments tab describing the reconcile file

   3. Click Add Attachment to attach the reconcile text file discussed in the previous steps

   4. Click Save

3. Click on the Attachments tab

4. Click anywhere on the row in the attachments table to open the results file

5. In the Attachment Details widget, next to the name field, click the blue download arrow icon to start the download.

6. Open the downloaded TXT file

7. Review the results file

   1. First row is headers

   2. Subsequent rows are results for each line on the payroll reconcile file

   3. Each row will state Failure or Success at the end of the row (see ReconcileStatus header)

      1. Failures will include a failure reason (see Reconcile Reason header)

Review Reconciled Batches

1. Select Payroll from the main menu

2. Select Reconcile from the submenu

3. Search for the file by typing the reconcile text file name into the search filters and clicking Search

4. Click anywhere on the row in the attachments table to open the results file

5. In the Attachment Details widget, next to the name field, click the blue download arrow icon to start the download.

6. Open the downloaded TXT file

7. Review the results file

   1. First row is headers

   2. Subsequent rows are results for each line on the payroll reconcile file

   3. Each row will state Failure or Success at the end of the row (see ReconcileStatus header)

      1. Failures will include a failure reason (see Reconcile Reason header)

Reconcile Multiple Payroll Batches

Reconciling from the Reconcile submenu allows users to reconcile rollups across multiple batches.

1. Create the Reconcile Text File using the steps above

2. Select Payroll from the main menu

3. Select Reconcile from the submenu

4. From the attachments table, select one or more files to reconcile.

5. Click Actions 

6. Select Upload File 

7.  

   1. The reconcile text file will include the entries from the selected batches

8. Complete the Reconcile File form wizard

9.  

   1. Enter a Ref Reconcile Id - Choose any reference number

   2. File Name - The name that will appear in the Attachments tab describing the reconcile file

   3. Click Add Attachment to attach the reconcile text file discussed in the previous steps

   4. Click Save

The system will start the reconciliation process. The batch status will update to ReconciliationInProcess. When complete, the user can see results under the Attachments tab on that batch page.  

• If all entries were successfully reconciled, the batch status will update to reconciled.

• If any entries failed reconciliation, the results file will display the reconciliation status and the reason for failure.

• If entries were successfully reconciled, the results file will list success as the reconciliation status and the entry status will update to paid.

*Please note: The reconciliation process can be automated if desired. When enabled, it will automatically move punches to paid status after the specified number of days after a batch is approved. This process simply changes the status; it does not input any data and will not update the billable amount on dollar-based punches. However, auto reconciliation will not work if dollars-based authorizations are enabled in the instance.

Related articles