Alert Example - Negative Stockpile Balance

This example triggers an alert if the balance of a stockpile goes into a negative amount.

The alert needs to be enabled and executed every minute, checking every WAG stockpile. The relevant object field type, which is one of the fields in the query string, is StockpileID. The object to which this despatch relates is a WAGStockpile object. The alert message needs to be displayed as a warning for 10 seconds to any user within the Administration user group. If the stockpile balance is still less than zero after 30 minutes, the alert needs to be re-issued.

The alert Message Text is:

Stockpile {Stockpile} in location {Location} has a negative balance.

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

The SQL Query for the alert is:

SELECT
  loc.LocationID, loc.Name AS "Location",
  sp.StockpileID, sp.Name AS "Stockpile",
  p.ProductID, p.Name AS "Product", p.Description,
  sbal.Tonnes as "Tonnes"
FROM
  Stockpile sp
  JOIN Location loc ON sp.LocationID = loc.LocationID
  LEFT JOIN Product p ON sp.CurrentProduct = p.ProductID
  JOIN StockpileState spstate ON sp.StockpileID = spstate.StockpileID
  JOIN StockBalance sbal ON spstate.StockBalanceID = sbal.StockBalanceID
  LEFT JOIN StockpileState nextstate ON spstate.StockpileID = nextstate.StockpileID and spstate.AsAtTransID = nextstate.PrevTransID
WHERE
  sp.StockpileIDType in ('Mincom.MineMarket.WAGStockpile')
  AND spstate.AsAtDate <= GetDate()
  AND (nextstate.AsAtDate > GetDate() or nextstate.AsAtDate is null)
  AND sbal.Tonnes < 0.0