Alert Example - Declaration Date Notification
This example triggers an alert if there is a despatch order with an undeclared quotation pricing (QP) line and the date by which the QP line should have been declared is today or has passed.
This alert is only relevant to despatch orders for contracts where the final QP certainty is unknown or optional. The alert is also only applicable to despatch orders for which the quotation pricing is used in invoice calculations. For example, if an aggregated parent despatch order (PDO) has Invoice Together checked and Aggregation Method set to Average, only the PDO triggers an alert. Quotation pricing is not relevant to the child despatch orders (CDOs). However, if Invoice Together is unchecked on the PDO, or if the Aggregation Method is Sum or None, only the CDOs trigger alerts.
The alert Message Text is:
The final QP needs to be set for a despatch order.
When the user clicks the message, that despatch order displays.
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 |
Declaration Date Notification |
|
Enabled |
True |
|
Frequency Type |
Every n Minutes |
|
Query Frequency |
60 |
|
Relevant Object Field |
DespatchOrderID |
|
Relevant Object Type |
DespatchOrder |
|
Key Identity Data Fields |
DespatchOrderID (Only this field should be selected.) |
The SQL Query for the alert is:
SELECT
DOStateOptQP.DeclarationDate "Declaration Date",
DespatchOrder.DespatchOrderID,
DespatchOrder.Name "Despatch Order",
Contract.ContractID,
Contract.Name "Contract",
ContractProduct.Name "Contract Product",
coalesce(AnalyteDef.Name, MainAnalyte.Name) AS "Payable Analyte"
FROM DOStateOptQP
INNER JOIN DOStateOptionalOPs ON DOStateOptionalOPs.DOStateOptionalQPID = DOStateOptQP.DOStateOptQPID
INNER JOIN DespatchOrderState ON DespatchOrderState.DespatchOrderStateID = DOStateOptionalOPs.DespatchOrderStateID
INNER JOIN DespatchOrder ON DespatchOrder.DespatchOrderID = DespatchOrderState.DespatchOrderID
INNER JOIN Contract ON Contract.ContractID = DespatchOrder.ContractID
INNER JOIN ContractProduct ON ContractProduct.ContractProductID = DespatchOrder.ContractProductID
INNER JOIN SplitDespatchOrderDef ON SplitDespatchOrderDef.SplitDespatchOrderDefID = DespatchOrder.SplitDespatchOrderDefID
LEFT OUTER JOIN SplitDespatchOrderDef ParentSplitDespatchOrderDef ON ParentSplitDespatchOrderDef.MasterDespatchOrderID = DespatchOrder.MasterDespatchOrderID
LEFT OUTER JOIN AnalyteDef ON AnalyteDef.AnalyteDefID = DOStateOptQP.AnalyteDefID
LEFT OUTER JOIN Product ON Product.ProductID = ContractProduct.ProductID
LEFT OUTER JOIN AnalyteDef MainAnalyte ON MainAnalyte.AnalyteDefID = Product.MainAnalyteID
LEFT OUTER JOIN (
SELECT
DODeclaredQPs.DespatchOrderId,
DODeclaredQP.AnalyteDefId,
DODeclaredQP.OrderNumber
FROM DODeclaredQPs, DODeclaredQP
WHERE DODeclaredQPs.DODeclaredQPID = DODeclaredQP.DODeclaredQPId) declaredQP ON
declaredQP.DespatchOrderId = DespatchOrder.DespatchOrderId AND
declaredQP.AnalyteDefId = DOStateOptQP.AnalyteDefID AND
declaredQP.OrderNumber = DOStateOptQP.OrderNumber
WHERE
DespatchOrder.Cancelled = 0 AND
(
(DespatchOrder.IsMasterDespatchOrder = 0 AND DespatchOrder.IsChildDespatchOrder = 0) OR
(DespatchOrder.IsMasterDespatchOrder = 1 AND SplitDespatchOrderDef.InvoicingTogether = 1 AND SplitDespatchOrderDef.AggregationMethod = N'Average') OR
(DespatchOrder.IsChildDespatchOrder = 1 AND
(ParentSplitDespatchOrderDef.InvoicingTogether = 0 OR
(ParentSplitDespatchOrderDef.InvoicingTogether = 1 AND
ParentSplitDespatchOrderDef.AggregationMethod <> N'Average')))
) AND
declaredQP.DespatchOrderID is null AND
DOStateOptQP.DeclarationDate <= GETDATE()Note: This query is specific to Microsoft SQL Server. If the MineMarket database is on an Oracle database server, replace GETDATE() with SYSDATE.
