Most companies have issues with getting their employees approving invoices on time, causing unnecessary costs of late fees and manual overhead.
Using exMon, you can easily monitor received invoices and notify the approver every time an unapproved invoice gets to close to a due date.
For detailed information on how to create a basic Query in exMon, visit Create a Query (Tutorial).
The SQL Query
Below are queries against Navision and Axapta, that monitor all unapproved invoices.
declare @days_from_due_date as int declare @days_min_age_of_invoice as int declare @company_domain as varchar(100) declare @override_email as varchar(100) -- Configuration set @days_from_due_date = -2 -- How close should invoices be to due date before they count as errors (negative number) set @days_min_age_of_invoice = 3 -- How old should invoices be before they count as error set @company_domain = 'example.com' -- Used to create a email addres from approve by column set @override_email = '' -- Override the recipient with another user, for example CFO SELECT 'Unapproved Line' as [Type], v.[Name] + ' ('+ [Vendor No_] + ')' as Vendor, [Registrated date], [Due Date], h.[Posting Date], case when h.[Deadline Date] = '1753-01-01' then h.[Due Date] else h.[Deadline Date] end as [Deadline Date], l.[Document No_], l.[Line No_], l.[Shortcut Dimension 1 Code] as Department, l.[Description], case when @override_email <> '' then @override_email else lower(l.[Approve by]) +'@'+@company_domain end as User_email, l.[Gross Amount (LCY)] as Amount, u.Name as line_approver FROM [dbo].[Company$Approval Line] l INNER JOIN [dbo].[Company$Approval Header] h on l.[Document No_] = h.[Document No_] INNER JOIN [dbo].[Company$Vendor] v on h.[Vendor No_] = v.No_ LEFT OUTER JOIN [dbo].[Company$Approval User] u on l.[Approve by] = u.Code WHERE [Approve or Reject line] <> 1 and datediff(day,case when h.[Deadline Date] = '1753-01-01' then h.[Due Date] else h.[Deadline Date] end, getdate()) >= @days_from_due_date and datediff(day,h.[Registrated date] ,getdate()) >= @days_min_age_of_invoice and l.[Net Amount (LCY)] <> 0 and h.[Document Type] <> 0 --Specific filters and h.[Due Date] >= '2015-01-01' and v.[Vendor Posting Group] not in ('EMPLOYEES','SOMETHINGELSE') and [Vendor No_] NOT IN ('xxxxxxxxx', 'yyyyyyyy') UNION ALL SELECT DISTINCT 'Unapproved Header' as [Type], v.[Name] + ' ('+ [Vendor No_] + ')' as Vendor, [Registrated date], [Due Date], h.[Posting Date], case when h.[Deadline Date] = '1753-01-01' then h.[Due Date] else h.[Deadline Date] end as [Deadline Date], h.[Document No_], 0, h.[Shortcut Dimension 1 Code] as Department, h.[Transaction text] as [Description], case when @override_email <> '' then @override_email else lower(h.[Approve by]) +'@'+@company_domain end as User_email, h.[Amount Including VAT (LCY)] as Amount, h.[Approve by Name] FROM [dbo].[Company$Approval Line] l INNER JOIN [dbo].[Company$Approval Header] h on l.[Document No_] = h.[Document No_] INNER JOIN [dbo].[Company$Vendor] v on h.[Vendor No_] = v.No_ WHERE h.[Document No_] not in (select distinct [Document No_] from [Company$Approval Line] where [Approve or Reject line] <> 1) and datediff(day,case when h.[Deadline Date] = '1753-01-01' then h.[Due Date] else h.[Deadline Date] end ,getdate()) >= @days_from_due_date and datediff(day,h.[Registrated date] ,getdate()) >= @days_min_age_of_invoice and l.[Net Amount (LCY)] <> 0 and h.[Document Type] <> 2 and h.[Document Type] <> 0 --Specific filters and h.[Due Date] >= '2015-01-01' and v.[Vendor Posting Group] not in ('EMPLOYEES','SOMETHINGELSE') and [Vendor No_] NOT IN ('xxxxxxxxx', 'yyyyyyyyyy')
declare @days_from_due_date as int declare @days_min_age_of_invoice as int -- Configuration set @days_from_due_date = -2 -- How close should invoices be to due date before they count as errors (negative number) set @days_min_age_of_invoice = 3 -- How old should invoices be before they count as error SELECT vendor.[NAME] + ' (' + vendor.ACCOUNTNUM + ')' as [Vendor], vendor_trans.TRANSDATE as [Date], approve.LASTPAYMENTDATE as [Due Date], approve.invoiceid as [Invoice Id], approve.TXT as [Description], approve.CURRENCYCODE as [Currency], approve.AMOUNTCUR as [Amount In Currency], emp.EMPLNAME as [Employee] ,approve_dim.DESCRIPTION + ' (' + emp.DIMENSION + ')' as [Department] FROM [dbo].[SOSAPPROVETABLE] approve INNER JOIN [dbo].[VENDTABLE] vendor on approve.VENDACCOUNT = vendor.ACCOUNTNUM and approve.DATAAREAID = vendor.DATAAREAID INNER JOIN [dbo].[VENDTRANS] vendor_trans on approve.VOUCHER = vendor_trans.VOUCHER and approve.DATAAREAID = vendor_trans.DATAAREAID INNER JOIN [dbo].[EMPLTABLE] emp on approve.[APPROVEDBY] = emp.EMPLID and approve.DATAAREAID = emp.DATAAREAID INNER JOIN [dbo].[DIMENSIONS] approve_dim on emp.DIMENSION = approve_dim.NUM and approve.DATAAREAID = approve_dim.DATAAREAID and approve_dim.DIMENSIONCODE = 0 LEFT OUTER JOIN [dbo].[USERINFO] useri on emp.OESEMPLNAME = useri.NAME LEFT OUTER JOIN [dbo].[SYSCOMPANYUSERINFO] companyuser on approve.APPROVEDBY = companyuser.[EMPLID] and approve.dataareaid = companyuser.dataareaid LEFT OUTER JOIN [dbo].[SYSUSERINFO] sysuseri on companyuser.userid = sysuseri.ID WHERE approve.STATUS = 0 and approve.LASTPAYMENTDATE <= dateadd(day,@days_from_due_date,getutcdate()) and datediff(day,vendor_trans.[TRANSDATE],getutcdate()) >= @days_min_age_of_invoice and approve.DATAAREAID = 'exm' and vendor.name not in ('Omitted vendor 1', 'Omitted vendor 2') and approve.invoiceid not in ('0003884','0335577','0003352') order by approve.DUEDATE
Note the highlighted areas in the query.
You need to replace all instances of Company$ with your own prefix.
You can also configure few properties, which are documented in the query text.
In two places you can add your specific filters. For example to omit some departments or vendors, or to filter out any older invoices you are not concerned about.
For detailed information on how to to configure exception manager, visit: Configuring Exception Management (Tutorial).
Primary Key
[Document No_] and [Line No_] serve as a good combined primary key for the query, make sure to configure that in Exception Manager.
Escalation
In many cases it can be useful to escalate the notifications to the CEO or CFO, when the invoices have not been approved and it’s getting closed to the due date.
To make sure they get notified when an unapproved invoice is already due, you can duplicate the query and change the parameters.
— Configuration
set @days_from_due_date = 0 -- How close should invoices be to due date before they count as errors (negative number)
In @override_email, add the email you want to be notified:
set @override_email = 'bill.the.cfo@example.com' -- Override the recipient with another user, for example CFO