Payroll: Reconciliation Import

SuccessWare payroll reconciliation import file specification

This document describes the information required and the general layout of information required for automatic import of payroll information from an external payroll software program or payroll service.

Overview

For typical payroll reconciliation, SuccessWare requires the following general information:

·   check amounts by employee

·   deduction amounts, either by employee or summarized

·   employer expense (contribution) amounts, either by employee or summarized.

All of this information can be included in a single import file as long as each item can be uniquely identified.  SuccessWare has a user customizable import setup utility that will allow virtually any identifier or description to uniquely identify a particular entry.  Required information must be supplied in Tab delimited format, one row per entry.

Filename

Currently, SuccessWare requires that the import file (or files) be named in the format: “yymmdd X YYY.txt”, where yymmdd is the pay period ending date in year, month, day order, X is the pay period type – ‘W’ for weekly, ‘M’ for monthly, ‘S’ for semi-monthly, or ‘B’ for bi-weekly, and YYY identifies the type of information contained in the file.  For example, ‘REC’ might be used when all reconciliation information is included in the file.  Or, ‘CHK’, ‘DED’, and ‘EXP’ might be used when three separate files are provided for checks, deductions, and employer expenses.  A space character is required between each part of the filename.

Information required

Employee checks

Minimum information required:

·   Check Number

·   Check Date

·   Employee Name (Last, First, M format)

·   Description, such as ‘Paycheck’, that identifies the row as a check

·   Check Amount (normally negative amount)

Deductions

Minimum information required:

·   Description, such as ‘FICA - Employee’ that uniquely identifies the deduction

·   Deduction Amount (normally negative amount)

Employer expenses

Minimum information required:

·   Description, such as ‘FICA - Employer’ that uniquely identifies the company expense

·   Amount

Examples

Sample A: Single file example, by employee

Following is a sample single import file that contains all of the necessary information.  This example shows all deductions and employer expenses broken down by employee, but alternatively, these entries could be submitted as company totals.

Import filename: 030110 W REC.txt (Tab delimited)

16425         Paycheck         12/27/2002     201475      Smith, John A.                                                                                                                                                -346.69

                                                                                                               Smith, John A.            Dental Insurance                                    2134 Dental Insurance    -4.92

                                                                                                               Smith, John A.            Health plan                                                Payroll Liabilities                 -19.98

                                                                                                               Smith, John A.            Federal Withholding                           Payroll Liabilities                 -269.00

                                                                                                               Smith, John A.            Social Security Company                  5910 FICA                                  117.47

                                                                                                               Smith, John A.            Social Security Company                  Payroll Liabilities                 -117.47

                                                                                                               Smith, John A.            Social Security Employee                 Payroll Liabilities                 -117.47

                                                                                                               Smith, John A.            Medicare Company                              5920 Medicare                      27.48

                                                                                                               Smith, John A.            Medicare Company                              Payroll Liabilities                 -27.48

                                                                                                               Smith, John A.            Medicare Employee                             Payroll Liabilities                 -27.48

                                                                                                               Smith, John A.            Federal Unemployment                    5930 FUTA                                0.00

                                                                                                               Smith, John A.            Federal Unemployment                    Payroll Liabilities                 0.00

                                                                                                               Smith, John A.            FL - Unemployment Company      5952 SUIFL                               0.00

                                                                                                               Smith, John A.            FL - Unemployment Company      Payroll Liabilities                 0.00

16426         Paycheck         12/27/2002     201476      Jones, James E.                                                                                                                                              -268.87

                                                                                                               Jones, James E.          Health plan                                                Payroll Liabilities                 -133.40

                                                                                                               Jones, James E.          National Linen                                         5582 Uniform/Mat              -4.53

                                                                                                               Jones, James E.          Federal Withholding                           Payroll Liabilities                 -31.00

                                                                                                               Jones, James E.          Social Security Company                  5910 FICA                                  25.16

                                                                                                               Jones, James E.          Social Security Company                  Payroll Liabilities                 -25.16

                                                                                                               Jones, James E.          Social Security Employee                 Payroll Liabilities                 -25.16

                                                                                                               Jones, James E.          Medicare Company                              5920 Medicare                      5.88

                                                                                                               Jones, James E.          Medicare Company                              Payroll Liabilities                 -5.88

                                                                                                               Jones, James E.          Medicare Employee                             Payroll Liabilities                 -5.88

                                                                                                               Jones, James E.          Federal Unemployment                    5930 FUTA                                0.00

                                                                                                               Jones, James E.          Federal Unemployment                    Payroll Liabilities                 0.00

                                                                                                               Jones, James E.          FL - Unemployment Company      5952 SUIFL                               0.00

                                                                                                               Jones, James E.          FL - Unemployment Company      Payroll Liabilities                 0.00

16427         Paycheck         12/27/2002     201477      Smith, Mary N.                                                                                                                                               -605.61

                                                                                                               Smith, Mary N.           Dental Insurance                                    2134 Dental Insurance    -17.97

                                                                                                               Smith, Mary N.           Health plan                                                Payroll Liabilities                 -133.40

                                                                                                               Smith, Mary N.           Federal Withholding                           Payroll Liabilities                 -81.00

                                                                                                               Smith, Mary N.           Social Security Company                  5910 FICA                                  46.09

                                                                                                               Smith, Mary N.           Social Security Company                  Payroll Liabilities                 -46.09

                                                                                                               Smith, Mary N.           Social Security Employee                 Payroll Liabilities                 -46.09

                                                                                                               Smith, Mary N.           Medicare Company                              5920 Medicare                      10.78

                                                                                                               Smith, Mary N.           Medicare Company                              Payroll Liabilities                 -10.78

                                                                                                               Smith, Mary N.           Medicare Employee                             Payroll Liabilities                 -10.78

                                                                                                               Smith, Mary N.           Federal Unemployment                    5930 FUTA                                0.00

                                                                                                               Smith, Mary N.           Federal Unemployment                    Payroll Liabilities                 0.00

                                                                                                               Smith, Mary N.           FL - Unemployment Company      5952 SUIFL                               0.00

                                                                                                               Smith, Mary N.           FL - Unemployment Company      Payroll Liabilities                 0.00

Sample B: Single file with Company Totals

This example shows all deductions, expenses, and deductions submitted as company totals.

Import filename: 030110 W REC.txt (Tab delimited)

Paycheck                              Jones, James E.                            -110                                            11111                                 1/1/2000

Paycheck                              Smith, John A.                              -220                                            22222                                 1/1/2000

Paycheck                              Smith, Mary N.                             -330                                            33333                                 1/1/2000

Deduction                           401K                                                   -6543.21                                                                                          

Deduction                           Federal                                              -2456.78                                                                                          

Deduction                           FICA EMPE                                       -1423.32                                                                                          

Deduction                           FICA EMPR                                       -1333.43                                                                                          

Deduction                           FUTA                                                    -14.54                                                                                                 

Deduction                           Local State                                      -458.34                                                                                             

Deduction                           Medicare EMPE                           -354.21                                                                                             

Deduction                           Medicare EMPR                           -387.12                                                                                             

Deduction                           Misc Deduction                           -75                                                                                                        

Deduction                           Post Dent                                        -12.34                                                                                                

Deduction                           PostMed                                           -45.39                                                                                                

Deduction                           Pre Dental                                      -122.98                                                                                             

Deduction                           SUI EE                                                 -19.32                                                                                                

Deduction                           Town A                                              -15.89                                                                                                

Deduction                           Town B                                              -234                                                                                                     

Deduction                           Uniforms                                          -25                                                                                                        

Deduction                           Wage Garnishments                 -145.84                                                                                             

Expense                                401K Match                                   -356.23                                                                                             

Expense                                Employer Taxes                           1578.83                                                                                            

Disbursment                      401K Match                                   356.23                                                                                               

Disbursment                      Earnings                                            -14326.71                                                             

Sample C: Multiple files example

Here is how the data might look if provided in separate files:

Import filename: 030527 W CHK.txt (Tab delimited)

16425         Paycheck               12/27/2002        201475         Smith, John A.                                                                                                   -346.69

16426         Paycheck               12/27/2002        201476         Jones, James E.                                                                                                 -268.87

16427         Paycheck               12/27/2002        201477         Smith, Mary N.                                                                                                  -605.61

Import filename: 030527 W DED.txt (Tab delimited)

Smith, John A.                           Dental Insurance                                       2134 Dental Insurance                      -4.92

Smith, John A.                           Health plan                                                   Payroll Liabilities                                   -19.98

Smith, John A.                           Federal Withholding                              Payroll Liabilities                                   -269.00

Smith, John A.                           Social Security Employee                    Payroll Liabilities                                   -117.47

Smith, John A.                           Medicare Employee                                Payroll Liabilities                                   -27.48

Jones, James E.                         Health plan                                                   Payroll Liabilities                                   -133.40

Jones, James E.                         National Linen                                            5582 Uniform/Mat                                -4.53

Jones, James E.                         Federal Withholding                              Payroll Liabilities                                   -31.00

Jones, James E.                         Social Security Employee                    Payroll Liabilities                                   -25.16

Jones, James E.                         Medicare Employee                                Payroll Liabilities                                   -5.88

Smith, Mary N.                          Dental Insurance                                       2134 Dental Insurance                      -17.97

Smith, Mary N.                          Health plan                                                   Payroll Liabilities                                   -133.40

Smith, Mary N.                          Federal Withholding                              Payroll Liabilities                                   -81.00

Smith, Mary N.                          Social Security Employee                    Payroll Liabilities                                   -46.09

Smith, Mary N.                          Medicare Employee                                Payroll Liabilities                                   -10.78

Import filename: 030527 W EXP.txt (Tab delimited)

Smith, John A.                           Social Security Company                                 5910 FICA                               117.47

Smith, John A.                           Social Security Company                                 Payroll Liabilities              -117.47

Smith, John A.                           Medicare Company                                             5920 Medicare                   27.48

Smith, John A.                           Medicare Company                                             Payroll Liabilities              -27.48

Smith, John A.                           Federal Unemployment                                   5930 FUTA                             0.00

Smith, John A.                           Federal Unemployment                                   Payroll Liabilities              0.00

Smith, John A.                           FL - Unemployment Company                     5952 SUIFL                            0.00

Smith, John A.                           FL - Unemployment Company                     Payroll Liabilities              0.00

Jones, James E.                         Social Security Company                                 5910 FICA                               25.16

Jones, James E.                         Social Security Company                                 Payroll Liabilities              -25.16

Jones, James E.                         Medicare Company                                             5920 Medicare                   5.88

Jones, James E.                         Medicare Company                                             Payroll Liabilities              -5.88

Jones, James E.                         Federal Unemployment                                   5930 FUTA                             0.00

Jones, James E.                         Federal Unemployment                                   Payroll Liabilities              0.00

Jones, James E.                         FL - Unemployment Company                     5952 SUIFL                            0.00

Jones, James E.                         FL - Unemployment Company                     Payroll Liabilities              0.00

Smith, Mary N.                          Social Security Company                                 5910 FICA                               46.09

Smith, Mary N.                          Social Security Company                                 Payroll Liabilities              -46.09

Smith, Mary N.                          Medicare Company                                             5920 Medicare                   10.78

Smith, Mary N.                          Medicare Company                                             Payroll Liabilities              -10.78

Smith, Mary N.                          Federal Unemployment                                   5930 FUTA                             0.00

Smith, Mary N.                          Federal Unemployment                                   Payroll Liabilities              0.00

Smith, Mary N.                          FL - Unemployment Company                     5952 SUIFL                            0.00

Smith, Mary N.                          FL - Unemployment Company                     Payroll Liabilities              0.00