Duplicate Invoice Audit Tool Documentation

mightymacros Dat Add-in

Minimum requirements for both 32 or 64-bit systems :

  1. Microsoft Windows 7
  2. Microsoft Office Excel 2007
  3. Microsoft SQL Server Express Edition 2012
  4. SQL Server Express LocalDB version 11.0
  5. Dot Net 4.5

mightymacros Dat Add-in requires an installation of Microsoft SQL Server Express LocalDB version 11 or above. Full SQL Server Express installation is optional.

SQL Server Express LocalDB, is a lightweight version of Express, that runs in user mode and does not require any configuration or administration.

LocalDB is an addition that you select during the install process of the Advanced Express package. LocalDB and Express can also be installed from separate packages.

Recommend using the Express edition 2012 and LocalDB version 11.0 on Windows 7 and any of the newer Express editions 2014, 2016, 2017 along with their respective LocalDB versions 12.0 or 13.0 on Windows 8 or 10

For further information on LocalDB:

Download Links for SQL Server Express:

.Net Framework Guide:

Note: For this application .Net 4.5 is the minimum requirement

 Refer to Help with Install for more information.

Before commencing open the setup form:

  1. Confirm that both SQL Express and LocalDB versions are displayed on the top of the setup form
  2. SQL Express as well as the LocalDB version must be 11.0 or later
  3. Confirm that .Net Version is displayed as 4.5 or later
  4. Set a minimum amount for highlighting on the output report
  5. Set a minimum amount on the duplicate report
  6. Choose a colour for the highlight option
  7. Select the option that best fits your business (different logic is applied when a supplier provided multiple invoices with the same date)
  8. Upload a vendor listing using the 'Upload' button under the vendor ribbon group of the DAT Addin menu.

The Setup Form:

General settings:

Setup-1

Upload settings:

Setup-2

Upload settings with custom headers

Setup-3

The option is available to set header names, but the column order and data type requirements remain fixed.

Setting custom row headers:

  1. Select 'Upload' tab on the setup form
  2. Check option box: 'Use custom headers'
  3. Fill in all the custom header name fields
  4. Click button 'Set Custom Headers'

Note: unchecking the option box will clear custom headers and reset headers to default names

Conformance to column order and header naming on the top row is required for data uploads, also refer to button help text for default requirements

Except for the optional columns (Payment Date and Payment Status), all other columns must contain a value for each record.

SQL data types and character length accepted for upload from Excel into the SQL database:

  1. [CompanyCode] NVARCHAR (10)
  2. [VendorNo] NVARCHAR (50)
  3. [VendorName] NVARCHAR (50)
  4. [DocType] NVARCHAR (10)
  5. [DocNo] NVARCHAR (50)
  6. [Reference] NVARCHAR (50)
  7. [DocDate] DATE
  8. [PmtDate] DATE
  9. [Status] NVARCHAR (50)
  10. [Amount] MONEY
  11. [Curr] NVARCHAR (10)

NVARCHAR datatype accepts both letters and numbers within the field and stores them in unicode format

Optional columns can only be uploaded when using 'Upload Invoice Items',

Vendor number and vendor name uploads:

Note: Vendor uploads require the same adherence to order and naming on the header row.


UploadVndrVndrHelp

Requirements for uploading data

DAT works by analyzing and comparing invoice history against current bills to be paid.

The invoice item table should contain at least 2 years of invoice history as well as current unpaid bills.

The open item table should comprise soley of unpaid bills.

UploadAllAllTextUploadOpenOpenText

Header Naming

DAT matches column naming and header order prior to upload

Additional validation of upload data:

  • Checks for null values. All fields must contain a value
  • Identifies any additional columns which may be due to import errors separating the data.
  • Checks for non-dates in the date field
  • Text or date values in the amount column

Sample upload sheet

Sample_Upload

Note: The above sample data is fictitious and not intended to represent any known business or organisation

Important considerations:

  • keep data uploads to a minimum (recommend 100k rows at a time)for optimum performance and to prevent issues with Excel
  • Do not close Excel while the tool is in operation, wait for the operation to complete, the database connection will timeout if there is an issue
  • If the database times out on upload, some of the data may have uploaded. You can either try re-running the upload or upload in smaller lots.
  • Excel may interpret an abrupt closure as a crash and unload the Add-in. This means the DAT tool may not automatically load next time you start Excel.
  • If Excel unloads or disables the add-in:
  1. Re-load using Excel's File menu: Select Options, then add-ins. Go to and select from the Manage dropdown, Com Add-ins or Disabled items and re-enable the tool.
  2. May require an update to windows registry. Better avoid this issue by not abruptly ending the program while it is running.
  3. Contact:  This email address is being protected from spambots. You need JavaScript enabled to view it.

Creating the Dat report

The DAT report needs to be prepared or generated before it can be downloaded into Excel, and split by category/rule type onto worksheet tabs

ReportOptions

Analysing the report output:

  1. Work through selecting one rule tab at a time
  2. Check results against ERP and invoice records
  3. Determine the cause if any, then select the comment from the dropdown in the comment 'column J' that best fits
  4. Click the 'Update' button of the 'Comments' group on the DAT add-in ribbon menu to update or add new comments to the database

Any changes to highlight or minimum values must be done before the report is generated

DATReport

Note: The above sample data is fictitious and is not intended to represent any known business or organisation

Comments

Are broadly based on the rules used to identify the potential duplicate invoices, such as having a different reference or date

The standard list of comments:

  1. Invoice Reversed And Reprocessed
  2. Duplicate Transaction-Incorrect Amount
  3. Duplicate Transaction-Incorrect Company
  4. Duplicate Transaction-Incorrect Currency
  5. Duplicate Transaction-Incorrect Doc Date
  6. Duplicate Transaction-Incorrect Doc Type
  7. Duplicate Transaction-Incorrect Reference
  8. Duplicate Transaction-Incorrect Vendor
  9. Not A Duplicate Transaction

Reasons for commenting:

  1. To keep a record of items that have been reviewed and to prevent rework
  2. To provide a history of stats based on the duplicates found
  3. To highlight potential fraudulent practices of suppliers

Comment menu

Comments

Use the update button to add or update comments made on the report output

After reviewing the first tab run the update button, this will update any item that has been extracted by a different rule and regenerates the report, thus saving rework.

Backup and restore options are available for your comments,

Stats:

Stats

There are three basic outputs provided:

  1. Group by comment - displays comment stats by year and by quarter

    Stats

  2. Group by Amount - displays the amount saved by year and by quarter

    Saved

  3. Group by Vendor - displays comment stats by vendor, where the headers Reference, Amount, Company, etc... below refer to incorrect outcomes or duplications

    VndrDuplicates

The show vendor option:

Enter a vendor to investigate from the 'Group by Vendor' output

VndrCheck

Select the 'Show Detail' button to view records for that vendor

VndrReport

Note: The above sample data is fictitious and not intended to represent any known business or organization

Archive Manager

The archive manager provides a backup and restore function to remove and store old items from the database

The LocalDB SQL database with a 10-gigabyte size limit, over time, will require maintenance to maintain performance, so archiving is recommended

The archive option removes and saves records prior to the date selected in the date picker