1 | P a g e

Payroll Audit System User Guide

Overview The Payroll Audit System is designed to assist the payroll service bureau in “scrubbing” payroll data and

identifying exceptions before payrolls are submitted to the queue to be processed as well as an ongoing

basis. The system consists of two sections: scheduled audits and pre-process audits. Both are

configurable individually and allow for a great amount of flexibility.

The scheduled audits can be run on any number of schedules, using dynamic date parameters.

Scheduled audit results are sent via HTML formatted e-mails to any number of recipients.

The preprocess audits are configured to show up in the payroll software by using a customized payroll

submit screen. Upon entering the payroll submit screen, the pre-process audits are executed and any

exceptions are displayed on the screen. The pre-process audits can be configured as either mandatory

or approvable. Mandatory audits must be corrected before the payroll is submitted. Approvable audits

may be approved and the approver, along with the time and date, are logged into the system for

reporting on later.

**This user guide is designed for use with version 2.1 and up of the Payroll Audit System. If you are still

on version 2.0, please contact Baker Technology Consulting to obtain an upgrade.

Last Updated: 4/13/2012 4:00 PM

2 | P a g e

Contents Overview ....................................................................................................................................................... 1

Scheduled Audits .......................................................................................................................................... 4

Overview ................................................................................................................................................... 4

Manage ..................................................................................................................................................... 4

General Information ............................................................................................................................. 4

Email Information ................................................................................................................................. 5

Schedules .............................................................................................................................................. 5

SQL Query Code .................................................................................................................................... 6

Run ............................................................................................................................................................ 7

Pre-Process Audits ........................................................................................................................................ 8

Overview ................................................................................................................................................... 8

General Information ................................................................................................................................. 9

Approvals .................................................................................................................................................. 9

Target Databases ...................................................................................................................................... 9

SQL Query Code ...................................................................................................................................... 10

Employee Audits ................................................................................................................................. 10

Company/Payroll Audits ..................................................................................................................... 10

Payroll Submit Screen ................................................................................................................................. 11

Job Requests Screen ................................................................................................................................... 13

Reporting .................................................................................................................................................... 15

Reports List ............................................................................................................................................. 15

Logs ............................................................................................................................................................. 16

Scheduled Audits .................................................................................................................................... 16

Filters ................................................................................................................................................... 16

Pre-Process Audits .................................................................................................................................. 17

Filters ................................................................................................................................................... 17

Archive/Purge ......................................................................................................................................... 18

3 | P a g e

Configuration .............................................................................................................................................. 19

Email Client ............................................................................................................................................. 19

Email Settings ...................................................................................................................................... 19

Test Setup ........................................................................................................................................... 20

Database Connection .............................................................................................................................. 20

SQL Server and Database .................................................................................................................... 21

Test Connection Settings .................................................................................................................... 21

Pick Lists .................................................................................................................................................. 21

4 | P a g e

Scheduled Audits

Overview The Scheduled Audits are SQL queries that are executed against the payroll database and the results

emailed to specified recipients on a customizable schedule. These audits are completely customizable to

allow for an infinite amount of possibilities. (Example: List companies with an incorrect tax setup.) Run

audits daily, weekly, monthly, quarterly, yearly or even all throughout the business day. Any of the

audits in the system can also be run manually (ad-hoc). The audits generate HTML tabular formatted

emails to desired recipients and are easy to read and appealing to the eye. Audits may be set to be

formatted as "alerts", using bold and noticeable colors.

Manage

General Information

Configure the general information for the Scheduled Audit.

5 | P a g e

Enabled - Check the box to enable this audit. If an audit is disabled it will not be run

automatically regardless of the schedules that may be configured.

Name - Give the audit a descriptive name. The name can be changed at any time and should be

unique, although that is not required.

Description - Describe the audit and what it does. This description will be at the top of the email

that is generated. Include any special instructions to the person that will receive the audit email.

Category - Choose a category for the audit. This category is merely for organizational purposes

and can be changed at any time. The category name will appear in the subject line of the

generated email as well as in bold at the top of the email body. The list of categories can be

configured from the "Configuration" tab under "Pick Lists".

Email Information

Configure the email information for the Scheduled Audit.

Format As Alert - Check the box to format the email that is generated with a red background

and bold, yellow text. The subject of the email also contains the word "Alert". This is to make

the email stand out as an issue that should be handled immediately.

Email To - Enter the email address that will appear in the To field of the generated email.

Multiple email addresses may be configured by separating them by semi-colons (";").

Email CC - Enter the email address that will appear in the CC field of the generated email.

Multiple email addresses may be configured by separating them by semi-colons (";").

Email BCC - Enter the email address that will appear in the BCC field of the generated email.

Multiple email addresses may be configured by separating them by semi-colons (";").

Schedules

Configure the schedules for the Scheduled Audit. All audits must be configured with at least one

schedule, even if that schedule is "Ad-hoc" (on-demand only, will not run automatically). Multiple

schedules with different date parameters may be configured for a single audit. For example, if you want

a report on CSR activity daily for the previous business day, weekly for the previous week, monthly for

the previous month and so on. Note: The list of schedules is currently a set list that is configured at the

time of implementation of the Payroll Audit System.

Schedule - Select the schedule name from the list.

Start Date Parameter - Select the dynamic date parameter to be used in the audit. You can

include the parameter "@StartDate" in the audit SQL query and a built-in function will replace

the parameter at runtime with the value that you select here.

End Date Parameter - Select the dynamic date parameter to be used in the audit. You can

include the parameter "@EndDate" in the audit SQL query and a built-in function will replace

the parameter at runtime with the value that you select here.

6 | P a g e

SQL Query Code

Configure the SQL query code for the Scheduled Audit. The code must follow standard SQL query syntax.

No data modification statements are allowed in the code (UPDATE, INSERT, DELETE, etc.). The columns

in the SELECT list determine the columns that appear in the generated email.

Validate - Click the button to validate that the SQL code entered uses proper syntax and will execute

with no errors.

Preview - Click the button to preview the SQL query results in a new window. A list of schedules will be

displayed, allowing you to select which schedule to be used in the preview. This can be helpful in

determining what data will be presented for each schedule. In the preview window, there are 3 tabs:

Query Results (shows the results in a grid format), Query Text (shows the actual query as it is sent to the

server) and HTML Results (shows a preview of the actual email that would be generated and sent).

Database - Select the database that the query will be run against. The list of databases can be

configured on the "Configuration" tab under "Pick Lists".

Import - Click the button the export the contents of the SQL Query Code window to a text file.

Export - Click the button to import query code to the SQL Query Window from a text file.

7 | P a g e

Run

Run a single Scheduled Audit manually or run all configured with a certain schedule. This is handy if a

new audit has been configured and you want to run it immediately instead of waiting on the scheduler

or if an audit has resulted in an error at its scheduled time (i.e. email server is down, SQL server is down,

etc.) and you wish to re-run it.

Run All - Select the schedule from the drop down list and click the Run All button to execute all audits

that are configured with the selected schedule. This mimics the process that is executed automatically

by the scheduler.

Run - Click the Run button next to the single audit you wish to execute. Feedback will be provided at to

the success or failure.

8 | P a g e

Pre-Process Audits

Overview The Pre-Process Audit portion of the system provides a customized “Payroll Submit” screen in the

payroll system which executes special SQL queries against the payroll. When users local to the service

bureau (CSR's, Citrix users, etc.) enter the submit screen, the system automatically audits the payroll

based on parameters that you define. (Example: W2 employees missing taxes, paycheck is over a certain

amount threshold.) Preprocess audits can be configured to be mandatory or approvable. For mandatory

audits, the item must be corrected before they are allowed to submit the payroll. For approvable audits

they are given an “Approve” button which logs the user and time that they have approved it.

Approvable audits can be assigned roles from the payroll system to allow only users in those roles to

approve those audits. For remote clients, you can run the audits on the payroll before it is processed by

using the customized Job Requests screen. If any audits fail, the payroll can be opened, corrected and

re-submitted. This process will greatly reduce your correction payroll runs and tax department issues at

quarter and year end.

9 | P a g e

General Information Configure the general information for the Pre-Process Audit.

Enabled - Check the box to enable this audit. If an audit is disabled it will not be executed for any

company or any database.

Name - Give the audit a descriptive name. The name can be changed at any time and should be unique,

although that is not required. The name will be displayed on the Payroll Submit screen in the payroll

system in the table of failed audits.

Description - Describe the audit and what it is looking for. This description will be displayed to the user

on the Payroll Submit screen in the payroll system when they hover their mouse over the name of the

audit.

Approvals Configure the approval functionality for the Pre-Process Audit.

Approvable - Check the box to allow this audit to be approved. Approvable audits provide a user with an

"Approve" button on the Payroll Submit screen in the payroll system next to the audit in the list of failed

audits. When the user clicks the "Approve" button, the user name, time and date are logged into the

Payroll Audit System database and viewable in the logs. The user will also be presented with an

"Unapprove" button in case they accidentally clicked the "Approve" button. If an audit is not enabled for

approval, the user will not be allowed to close and submit the payroll until the item is corrected. They

will see "Mandatory Correction" next to the audit and the "Close Batch" and "Submit Payroll" buttons

will not be visible.

Approval Roles - Select the security roles from the payroll system that are allowed to approve this audit.

If the list is empty then anyone can approve. If the user is not in a security role that is allowed to

approve the audit then they will see "Authorization Required" instead of an "Approve" button on the

Payroll Submit screen in the payroll system. When the user hovers their mouse over the "Approval

Required" text, a list of authorized roles will appear so the user will know from whom to request

approval. The list of approval roles are configured on the "Configuration" tab under "Approval Roles".

Target Databases Configure the target databases for the Pre-Process Audit. If there are multiple databases being used by

the service bureau and/or clients, this allows for more granular control over which audits are executed

for which database. The list of target databases can be configured on the "Configuration" tab under

"Pick Lists"

10 | P a g e

SQL Query Code Configure the SQL query code for the Pre-Process Audit. The code must follow standard SQL query

syntax. No data modification statements are allowed in the code (UPDATE, INSERT, DELETE, etc.). The

query code must be configured very specifically for the Pre-Process audits. The payroll system passes

variables to the code at runtime form the Payroll Submit screen, namely company code, calendar ID and

batch. In the query code the placeholders for those dynamic values are denoted by @Co, @CalendarId

and @Batch, respectively.

Validate - Click the button to validate that the SQL code entered uses proper syntax and will execute

with no errors.

Import - Click the button the export the contents of the SQL Query Code window to a text file.

Export - Click the button to import query code to the SQL Query Window from a text file.

Employee Audits

If employee details are needed for an audit (example: employees paid over a certain threshold,

employee missing taxes, etc.) then in the SELECT list the column names "EEID", "EEName" and "Reason"

should be used to return data. The user will then see a list of the specific employees that failed the audit

on the Payroll Submit screen in the payroll system. They can then click on the employee's name and be

taken directly into the employee's check to correct the issue.

Company/Payroll Audits

If no employee details are needed for an audit (example: checking for a specific company bank account

setup, validating payroll calendar settings, etc.) then the audit merely needs to return a value to show

up as a failed audit on the Payroll Submit screen in the payroll system.

11 | P a g e

Payroll Submit Screen A customized Payroll Submit screen is presented to the user in the payroll system. Upon entering the

screen the payroll is pre-calculated and all Pre-Process audits that are enabled and configured to run for

that database will be executed. Any audits that return a result will be displayed to the user in a table to

the right of the normal buttons and text on the screen. The “Close Batch” and “Submit Payroll” buttons

will not be displayed until all audits have been handled.

The Audit Results table consists of the following:

Batch – The batch that the result was found in.

Description – The name of the audit is displayed. If the user hovers their mouse over the audit

name a more detailed description is displayed.

Details – If the audit is configured to show details then the list is displayed here within a sub-

table. If individual employees are displayed, the user can click on the employee’s name and be

taken directly into their check to correct the issue.

Action -

o Approve – If the audit is approvable and either there are no approval roles configures or

the user is in a role configured for approval on that audit, then an “Approve” button is

displayed. When the user clicks the button, the user’s name and the time and date are

logged into the Payroll Audit System database. The button is then replaced by the

approval details and an “Unapprove” button (in case they accidentally approved the

audit).

o Mandatory Correction – If the audit is not approvable then the user must correct the

issue before being allowed to close and submit the payroll.

o Authorization Required – If the audit is approvable but the user in not in a role

configured for approval on that audit, then the user must seek approval from another

user that is in the approval roles. If the user hovers their mouse over the “Authorization

Required” text, a list of approval roles are displayed so that the user knows who to go to

seek approval.

12 | P a g e

13 | P a g e

Job Requests Screen A customized Job Requests screen is created in the payroll system. The custom screen no only displays

more data than the standard screen but also allows the mailroom/packout staff to determine if a payroll

has been audited. If the payroll has been audited and has passed all audits then the “Process” button is

enabled. If the payroll has not been audited then the “Process” button is disabled and the “Run Audits”

button is displayed. The user can then click the “Run Audits” button to audit the payroll. If the payroll

passes all audits then the “Process” button is enabled. Is the payroll does not pass all audits then the

user is notified of such and is given a button to take them directly to the Payroll Submit screen for that

company to view the details of the failures.

This screen can be used to audit payrolls that come in remotely from clients that do not have the Payroll

Audit System installed locally or are using the web-based version of the payroll system. In order for the

payrolls to be audited in this manner they must be configured to go to the job queue in the “request”

state rather than being automatically processed.

14 | P a g e

15 | P a g e

Reporting

Select a report from the drop-down list and click the "Run" button. The report will be displayed in the

window and available to print or export. To refresh a report, click the "Run" button again.

Reports List Enabled Scheduled Audits - a list of enabled scheduled audits and their details.

Enabled Preprocess Audits - a list of enabled preprocess audits and their details.

16 | P a g e

Logs

Scheduled Audits

View the logs of all Scheduled Audits that have been run in the system. The logs contain a copy of the

configuration of the audit at the time of its execution as well as the exact audit email output (if any),

email recipients, start and end times and the executing user. If the audit resulted in an error, the error

message is logged and the log row is highlighted in red. Audits that do not produce any results are still

logged so as to show that these items are being audits. Manually run audits are logged just like audits

executed by the scheduler.

Filters

Job End Date - Select the log date range from the list that you want to see displayed.

Show Errors Only - Check the box to show only audits that resulted in an error. Errors may result from

invalid SQL queries, mail server being inaccessible, database being inaccessible, etc.

17 | P a g e

Show Data Sent Only - Check the box to show only audits that had results (generated an email).

Pre-Process Audits

View the logs of all Pre-Process Audits that have been approved by users. The logs contain the entry

time, company code, calendar ID, check date, batch, database, audit name, the approving user and

approval date. If the audit had employee details then the individual employees and the employee

specific reason for audit failure is also listed..

Filters

Entry Date - Select the log date range from the list that you want to see displayed.

Company - Type a company code into the box provided and the list will be dynamically filtered by

companies beginning with the characters that you type.

Approver - Type a user name into the box provided and the list will be dynamically filtered by audits

approved by approvers beginning with the characters that you type.

18 | P a g e

Archive/Purge

Archive or purge logs from the system based on date. Logs can be archived/purged either all at once or

individually (Scheduled vs. Pre-Process). Archived logs are moved from the normal log tables in the

database to secondary archive tables which can later be moved to less expensive storage on the SQL

server. Note: Purged logs are DELETED from the database and cannot be recovered without restoring

the database from backup!

Log Selection - Select "Both" to run the operation on both Scheduled and Pre-Process logs at the same

time. Alternatively, choose either one or the other.

Older Than - Choose a date for which logs will be archived/purged.

Operation - Choose the operation to be performed, either "Archive" or "Purge".

19 | P a g e

Configuration

Email Client

Configure the email client portion of the Payroll Audit System. The email client handles the sending of

the emailed generated by the Scheduled Audits.

Email Settings

Server Name/IP Address - Enter the name or IP address of your email server. There may be specialized

setup required on the email server to allow the database server to send email through it.

Server Port - The port that the SMTP service is running on the server, normally 25.

From Address - The email address that will appear in the From field of the emails generated.

User Name - If your email server requires authentication to send emails, enter the user name here.

20 | P a g e

Password - If your email server requires authentication to send emails, enter the password here. Note:

The password is stored in clear text in the database.

Use SSL - If your email server requires SSL encryption to send emails, check this box.

Test Setup

Send Test Email To - Enter the email address of a valid recipient to which to send the test email.

Test - Click the button to send a test email to the specified recipient. The results of the test will be

displayed in the window.

Database Connection

Configure the database connection for the Payroll Audit System. Normally this will not need to be

changed unless the database has been moved or renamed.

21 | P a g e

SQL Server and Database

SQL Server - Click the "Get Servers" button to scan yoru network for SQL servers. This may take up to 30

seconds. Then select the correct SQL server form the list. If your server does not appear in the list, type

the name directly into the box.

SQL Database - Once you have selected the SQL server, click the "Get Databases" button to get a list of

databases on that server. If you do not see the PayrollAudit database in the list you may not have

appropriate access to it.

Test Connection Settings

Test - Click the button to test the connection to the database. The test also verifies that the database

selected is a Payroll Audit System database. The results of the test will be displayed in the window. Close

the program and re-open it for the settings to take effect.

Pick Lists

22 | P a g e

Configure the pick lists displayed in the Payroll Audit System. To edit an existing item, click on the text

and type in the new text. To delete an item, click on the desired row and press the Delete key on your

keyboard. If an item is in use you will not be allowed to delete it.

Databases - Enter a list of databases that exist on the same SQL server as the Payroll Audit System to

which you want the system to have access.

Categories - Enter a list of categories that will be selectable on the Scheduled Audits management

screen.

Approval Roles - Enter a list of security roles from the payroll system that will be selectable on the Pre-

Process management screen.

23 | P a g e