Data Integrity Email Background Job Overview

Purpose

The Data Integrity Email Background Job checks various aspects of Accounting and Operational data to identify potential integrity issues, discrepancies, and errors.  It generates an email report that is sent to specified recipients, allowing Accounting and Operations teams to address problems before they impact financial reporting or business operations

Benefits

• Proactive identification of accounting and data integrity issues
• Prevents financial reporting errors
• Helps maintain clean operational data
• Easy-to-follow links to problem records
• Regular automated checks reduce manual review efforts

Job Parameters

Data Integrity Checks

This procedure performs the following integrity checks:

Accounting Integrity

• AR Account Balance Verification – Checks whether Invoice balances match corresponding entries in the AR Journal
• Revenue Account Posting – Detects revenue accounts posted for Invoices on Deferred Revenue Projects
• Deferred Revenue Account Errors – Identifies Account Number errors on Service Contract Invoices
• Deferred Revenue Date Errors – Finds date-related issues with Deferred Revenue (missing dates, invalid date ranges)
• Journal Entry Balance – Verifies that Journal Entries balance (sum to zero)
• Inactive Account Balances – Identifies GL Accounts marked as inactive but still carrying balances
• Unposted Project Revenue – Flags Unposted Revenue from the previous month’s Project WIP

Operational Integrity

• Project Status Validation – Identifies Projects with End Dates in the past that have not been properly closed
• Unposted Time Bills – Highlights Time Bills that remain unposted after 7 days
• Customer Consistency – Identifies Orders (Contracts) linked to Projects with different Customer Numbers
• Inventory Quantity Verification – Checks for mismatches between Asset Counts and Master Branch Quantities

Implementation Considerations

Configuration Settings

This job uses the following Configuration Settings that should be verified:

• WEBROOT – Base URL for application links in the email
• EMAILQDEFAULTREPLYTO – Default Sender Email if not specified
• EmailTestAddress – Email address for testing in non-production environments
• EMAILENGINE – Email sending mechanism configuration
• PRODUCTIONDBNAME – Production database name for environment detection

SQL Queries Considerations

• Account numbers are hard-coded

Setup Instructions

1. Create a scheduled job to run the DataIntegrityEmail_Send stored procedure
2. Configure job parameters:
   • @ps_CompanyNo:  Company number to check, or ‘%’ for all
   • @ps_Recipients:  Email addresses to receive the report
   • @pn_CutOffDays:  Days in the past to check (e.g., 7, 14, 30)
3. Set appropriate schedule (daily or weekly recommended)
4. Run a test execution with @ps_OptArg = 'Verbose' to preview results

Sample Email Output

Below is a sample of what the Data Integrity Email report includes: