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 ComponentProduced Items IssuedReceived 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' , T0.[ItemCode] AS 'Component ItemCode' , T1.[ItemName] AS 'Component ItemName' , T0T2.[PlannedQty] AS 'Component Planned Qty' , T0T2.[IssuedQtyCmpltQty] AS 'Component IssuedCompleted Qty' , (T0T2.[IssuedQtyCmpltQty] - T0T2.[PlannedQty]) AS 'Difference' , ((T0T2.[IssuedQtyCmpltQty] - T0T2.[PlannedQty]) / T0T2.[PlannedQty] * 100) As 'Variance %' -- Here are the tables the data comes from from WOR1 T0 Inner Join OITM T1 ON T1.[ItemCode] = T0.[ItemCode] Inner Join OWOR T2 ON T2.[DocEntry] = T0.[DocEntry] Inner Join OITM T3 ON T3.[ItemCode] = T2.[ItemCode] Where T0T2.[IssuedQtyCmpltQty] > '0' AND ABS((T0T2.[IssuedQtyCmpltQty] - T0T2.[PlannedQty]) / T0T2.[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 |