exact-online - 确定 Exactonlinerest..MailMessagesReceived 的适用划分

Question

Exact Online 的mailmessagesreceived 和mailmessagesent 包含客户所有部门的所有工作。要在我们的 SQL Server 数据库中检索它们，我们使用以下查询:

use select min(code) from systemdivisions group by customercode

通过使用 min，我们确保每个客户代码的部门代码在一段时间内保持不变。

然后加载数据:

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

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

但是，字段 ForDivision 通常是空的。仅填写MT940等银行对账单。

对于在 Exact Online 上有自己的订阅的公司，这不是问题。 min(code) 就是他们自己的划分。

但我们需要评估我们会计订阅下的所有部门每个部门需要完成的工作量。

我们如何将 mailmessagesreceived 和 mailmessagesent 关联到他们的 Exact Online 部门？

Answer 1

这是一个“功能”或“错误”，具体取决于您如何看待 ForDivision 中 MailmessagesSent 的缺失值和 received .

通过以下查询，我确定了每个帐户发送和接收的打开的邮件列表:

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 */

您还可以按客户汇总报告它们:

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

诀窍在于 Mailboxes 中的 ForDivision|与 Exact Online 的 MailMessagesSent/Received 具有不同的语义。

2018 版本提供了一个简化版本:

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

或简称:

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