Alert Example - Invoice Status Change

This example triggers an alert if the status of a sales invoice has been changed in the previous two minutes.

The alert Message Text is:

Sales invoice {InvoiceName} has been changed to {CurrentStatus} at {DateModified} by {User}.

The table below lists the recommended settings for the alert. Other settings may depend on system configuration and configured user groups.

Field	Suggested Value
Name	Sales Invoice New Status Alert
Enabled	True
Frequency Type	Every n Minutes
Query Frequency	2 (This value must match the time indicated in the WHERE statement of the SQL query.)
Relevant Object Field	InvoiceID
Relevant Object Type	SalesInvoice (A similar query can be used for other invoice types. See the information below.)
Key Identity Data Fields	InvoiceID, StatusID (Only these fields should be selected.)

The SQL Query for the alert is:

Copy

SELECT
  I.Name AS InvoiceName,
  S.Name AS CurrentStatus,
  CONVERT(VARCHAR(19), SHE.DateModified, 120) AS DateModified,
  SD.Name AS StatusDefinitionName,
  SD.ApplicableTypeString,
  I.InvoiceID,
  S.StatusID,
  S.StatusDefinitionID,
  COALESCE(U.Alias1, CAST(SHE.UserId as varchar(255))) AS [User]
FROM
  StatusHistoryEntry SHE
  LEFT JOIN SalesInvoiceStatusHist B ON SHE.StatusHistoryEntryID = B.StatusHistoryEntryID
  LEFT JOIN Invoice I ON B.SalesInvoiceID = I.InvoiceID
  LEFT JOIN [Status] S ON S.StatusID = SHE.StatusID
  LEFT JOIN StatusDefinition SD ON SD.StatusDefID = S.StatusDefinitionID
  LEFT JOIN BTUser U ON U.BTUserID = SHE.UserID
WHERE
  SHE.DateModified > DATEADD(minute, -2, GETDATE())
  AND I.InvoiceIDType = 'Mincom.MineMarket.SalesInvoice'
  ORDER BY SHE.DateModified DESC

Note: The time indicated in bold text must match the Query Frequency in the alert configuration.

Testing this SQL query looks for any sales invoice that has new status in the last two minutes. If there has been no status change, then the result displays only the column headers.

To modify this query for other invoice types, substitute values for the bold query text as indicated in the following table.

Relevant Object Type in Invoice	Status History Table in Invoice	Foreign Key Column	InvoiceIDType String
CreditNoteInvoice	CreditNoteInvStatusHist	CreditNoteInvID	Mincom.MineMarket.CreditNoteInvoice
DemurrageInvoice	DemurrageInvoiceStatusHist	DemurrageInvoiceID	Mincom.MineMarket.DemurrageInvoice
FreightCommissionInvoice	FreightInvoiceStatusHist	FreightInvoiceID	Mincom.MineMarket.FreightCommissionInvoice
FreightDemurrageInvoice	FreightInvoiceStatusHist	FreightInvoiceID	Mincom.MineMarket.FreightDemurrageInvoice
FreightInvoice	FreightInvoiceStatusHist	FreightInvoiceID	Mincom.MineMarket.FreightInvoice
PurchaseCreditNote	PrchsCrdtNtInvStatusHist	PrchsCrdtNtInvID	Mincom.MineMarket.PurchaseCreditNoteInvoice
PurchaseInvoice	PurchaseInvoiceStatusHist	PurchaseInvoiceID	Mincom.MineMarket.PurchaseInvoice
SalesInvoice	SalesInvoiceStatusHist	SalesInvoiceID	Mincom.MineMarket.SalesInvoice
ServiceDemurrageInvoice	ServiceInvoiceStatusHist	ServiceInvoiceID	Mincom.MineMarket.ServiceDemurrageInvoice
ServiceInvoice	ServiceInvoiceStatusHist	ServiceInvoiceID	Mincom.MineMarket.ServiceInvoice