Alert - Production Completed Variance > 25%

Alert Overview:

This query displays all production orders where there is a variance greater than 25% on one of the completed quantity.

Purpose of the Alert:

This alert is valuable tool in monitoring variances that are happening in the brewery.

Parameters:

The Alert should be set to run each morning at 8am, to notify the user of any production from the previous day that had the variance over 25%.

It is possible to have the Alert more often, but this requires some in depth knowledge of Alerts and Queries. If you would like help on this topic, please contact your Account Manager or log a support ticket with OBeer Support.

Alerts are useful tools within OBEER. They allow you to "Manage by Exception", rather than constantly running reports to monitor what is happening in the business.

Alerts can be configured to run daily or more often, depending on your needs.

For advanced help with Alerts and their corresponding queries, please contact your OBeer Account Manager.

Example Results / Scenario:

As an example, if a user processing a brewing production order that is planned for 51.5 BBLs of Wort, but when doing the receipt qty they accidentally type in 515, it will cause a variance and the alert will be triggered.

Alert: Production Completed Variance Query

 /* Production Orders
 Alert for Produced Items Received vs Planned Qty 
 If the variance is greater than the percent specified 
*/
-- Here you can set the Variance Percentage to report 
-- Any Variance Greater than this will show up in the alert
-- Replace this value with your desired percentage (original value is 25)
Declare @Percentage AS INT = 25
-- Here you can set the number of days prior to today that 
-- will display in the alert
-- Replace this value (original value is 7)
Declare @NumDays AS INT = 7		
-- These are the columns that will be displayed
Select T2.[DocNum]	AS 'Production #'
		, T2.[U_ORC_BE_ProdDate] AS 'Production Date'
		, Case (T2.[Type])
			When 'S' Then 'Standard'
			When 'D' Then 'Disassembly'
			Else T2.[Type]
			End AS 'Production Type'
		, T2.[ItemCode] AS 'Parent ItemCode'
		, T3.[ItemName] AS 'Parent ItemName'
		, T2.[PlannedQty] AS 'Planned Qty'
		, T2.[CmpltQty] AS 'Completed Qty'
		, (T2.[CmpltQty] - T2.[PlannedQty]) AS 'Difference'
		, ((T2.[CmpltQty] - T2.[PlannedQty]) / T2.[PlannedQty] * 100) As 'Variance %'
-- Here are the tables the data comes from
from OWOR T2
	Inner Join OITM T3
	ON T3.[ItemCode] = T2.[ItemCode]
Where T2.[CmpltQty] > '0'
	AND ABS((T2.[CmpltQty] - T2.[PlannedQty]) / T2.[PlannedQty] * 100) > @Percentage   
	AND DATEDIFF ( DAY , T2.[U_ORC_BE_ProdDate],  GETDATE()) <= @NumDays	
Order By T2.[U_ORC_BE_ProdDate] Desc, T2.[DocNum]
For Browse