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