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.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
/* 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 |