Skip to end of metadata
Go to start of metadata
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.
/* 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