Using TimeXtender Orchestration and Data Quality, you can continuously monitor approaching due dates and notify specific employees of upcoming due dates via email.
Most companies find it difficult to get employees to approve invoices on time. This can result in unnecessary late fees and manual overhead. Using TimeXtender Orchestration and Data Quality, you can monitor received invoices and notify the approver every time an unapproved invoice approaches the due date. For detailed information on how to create a basic Query in TimeXtender Orchestration and Data Quality, visit Create a Query (Tutorial).
Worked example
The SQL Queries below are queries against Navision and Axapta, that monitor all unapproved invoices.
Navision
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')Dynamics Axapta
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.DUEDATENote the highlighted areas in the query. You need to replace all instances of Company$ with your own prefix. You can also configure a few properties, which are documented in the query text. In two places you can add your specific filters. For example, omit some departments or vendors, or to filter out any older invoices you are not concerned about.
Tips configuring Exception Manager
For detailed information on how to configure an exception manager, visit: Configuring Exception Management (Tutorial)
Primary Keys
[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 also notify the CEO or CFO of upcoming or overdue invoice deadlines. To make sure they receive notifications, you can duplicate the query and change the parameters.
-- Configuration set @days_from_due_date = 0 --The above query determines how close should invoices be to the due date before they count as errors (negative number).
In @override_email you can 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 or CEO.
