Continuous Monitoring
      Back to home
      1. Help Center
      2. Data Governance
      3. Continuous Monitoring

      Monitoring unapproved invoices in Navision or Axapta with exMon

      Created by: Kristinn Magnusson

      Using exMon, 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 exMon, 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 exMon, 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.DUEDATE

       

      Note 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.

      Kristinn is the author of this solution article.