This query displays all production orders where there is a variance greater than 25% on one of the issued components.
Purpose of alert:
This alert is valuable tool in monitoring variances that are happening within a brewery or distillery.
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 Orchestrated Support.
Example Results / Scenario:
As an example, if a user processing a brewing production order that calls for 1200 Lbs of Bulk Malt, but accidentally mistypes and issues 120 Lbs, it would trigger the alert and notify the recipient of the alert.
/* Production Orders Alert for Component Items Issued 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' , T0.[PlannedQty] AS 'Component Planned Qty' , T0.[IssuedQty] AS 'Component Issued Qty' , (T0.[IssuedQty] - T0.[PlannedQty]) AS 'Difference' , ((T0.[IssuedQty] - T0.[PlannedQty]) / T0.[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 T0.[IssuedQty] > '0' AND ABS((T0.[IssuedQty] - T0.[PlannedQty]) / T0.[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