Determine applicable division of Exactonlinerest..MailMessagesReceived

The mailmessagesreceived and mailmessagessent of Exact Online contain all work for all divisions of a customer. To retrieve them in our SQL Server database we use the following query:

use select min(code) from systemdivisions group by customercode

By using the min we ensure that the division code stays constant over time per customer code.

And then load the data:

select /*+ ods(true, interval '20 hours') */ * from mailmessagessent

select /*+ ods(true, interval '20 hours') */ * from mailmessagesreceived

However, the field ForDivision is in general empty. It is only filled for bank statements such as MT940.

For companies that have their own subscription on Exact Online, that is not a problem. The min(code) then is their own division.

But we need to assess for all divisions under our accountancy subscription what the amount of work to be done is per division.

How can we associate the mailmessagesreceived and the mailmessagessent to their Exact Online division?

Solution

This is a "feature" or a "bug" depending on how you look on it for the missing values in ForDivision for MailmessagesSent and received.

With the following query I determine the list of open mail messages sent and received per account:

use select min(code) from systemdivisions where status = 1 /* Active. */ group by customercode

select mbx.accountname
,      mbx.ForDivisionDescription 
,      mbx.ForDivision
,      mrd.*
from   MailMessagesReceived mrd
join   mailboxes mbx
on     mbx.id = mrd.recipientmailboxid
--and    mbx.ForDivision = 886678
where  mrd.recipientstatus in (10, 20, 25, 30) /* 10: Draft, 20: Open, 25: Prepared, 30: Approved */

You can also report them aggregated per customer:

select mbx.accountname
,      mbx.ForDivisionDescription 
,      mbx.ForDivision
,      count(*)
from   MailMessagesReceived mrd
join   mailboxes mbx
on     mbx.id = mrd.recipientmailboxid
where  mrd.recipientstatus in (10, 20, 25, 30) /* 10: Draft, 20: Open, 25: Prepared, 30: Approved */
group
by     mbx.accountname
,      mbx.ForDivisionDescription 
,      mbx.ForDivision

The trick is that ForDivision in Mailboxes has different semantics than in MailMessagesSent/Received of Exact Online.

A simplified version is available with 2018 releases:

select mrd.RecipientMailboxCustomerName
,      mrd.RecipientMailboxForDivisionDescription
,      mrd.RecipientMailboxForDivision
,      count(*)
from   MailMessagesReceived mrd
where  mrd.recipientstatus in (10, 20, 25, 30) /* 10: Draft, 20: Open, 25: Prepared, 30: Approved */
and    mrd.division in ( select /*+ low_cost */ min(sdn.code) from systemdivisions sdn group by sdn.customer )
group
by     mrd.RecipientMailboxCustomerName
,      mrd.RecipientMailboxForDivisionDescription
,      mrd.RecipientMailboxForDivision

or for short:

select mrd.RecipientMailboxCustomerName
,      mrd.RecipientMailboxForDivisionDescription
,      mrd.RecipientMailboxForDivision
,      count(*)
from   MailMessagesReceivedUnhandled mrd
group
by     mrd.RecipientMailboxCustomerName
,      mrd.RecipientMailboxForDivisionDescription
,      mrd.RecipientMailboxForDivision