Skip to end of metadata
Go to start of metadata
You are viewing an old version of this page. View the current version.
Compare with Current
View Page History
« Previous
Version 2
Next »
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 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