Alert Overview:
This Alert notifies a user when a price on an item on a GRPO has a a price that is more than 10% different than the current cost of that item.
Purpose of the Alert:
This report helps monitor purchases where the price on the purchase document will change the cost of the item.
Parameters:
The alert should be set to run each morning, so that the user catches all the purchase orders placed the previous day.
Alerts are useful tools within OBEER. They allow you to "Manage by Exception", rather than constantly running reports to monitor what is happening in the business. Alerts can be configured to run daily or more often, depending on your needs. For advanced help with Alerts and their corresponding queries, please contact your OBeer Account Manager. |
Example Results / Scenario:
This alert is very similar to the Purchase Order alert that shows the same thing. The difference is that after the Purchase oRder is entered, you will have ample time to go and make the change to the PO. But once a GRPO is input, it has already updated the cost of the item. So, this alert is very useful in notifying the user of items whose cost has been updated by a GRPO by more than 10%.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
/* This Alert Displays Goods Receipt POs that have a variance in the purchase price vs. the Average price of the goods It looks for Open GRPOs updated in the last week */ -- 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 10) Declare @Percentage AS INT = 10 -- 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 T0.[DocNum] , T0.[DocDate] , T0.[CardCode] , T0.[CardName] , T0.[NumAtCard] As 'Vend Ref#' , T1.[ItemCode] , T1.[Dscription] As 'Item Name' , T1.[InvQty] , (T1.[LineTotal]/T1.[InvQty]) As Price , T4.[AvgPrice] , (T1.[LineTotal]/T1.[InvQty]) - T4.[AvgPrice] AS 'Difference $' , Case When T4.[AvgPrice] = 0 Then 0 Else ((((T1.[LineTotal]/T1.[InvQty]) - T4.[AvgPrice]) / T4.[AvgPrice] * 100)) End AS 'Variance %' -- Here are the tables the data comes from From OPDN T0 Inner Join PDN1 T1 ON T1.DocEntry = T0.DocEntry Inner Join OCRD T2 ON T2.CardCode = T0.CardCode Inner JOIN OITM T3 ON T3.ItemCode = T1.ItemCode INNER JOIN OITW T4 ON T4.ItemCode = T1.ItemCode AND T4.WhsCode = T1.WhsCode Where T0.[DocStatus] IN ('O') AND T3.[InvntItem] = 'Y' AND ((CASE When T4.[AvgPrice] = 0 Then ABS(((T1.[LineTotal]/T1.[InvQty]) - T4.[AvgPrice]) / 1 * 100) Else ABS(((T1.[LineTotal]/T1.[InvQty]) - T4.[AvgPrice]) / ISNULL(T4.[AvgPrice],1) * 100) End >= @Percentage) OR T1.[Price] = '0' ) AND DATEDIFF ( DAY ,T0.UpdateDate, GETDATE()) <= @NumDays For Browse |