Alert - Purchase Price vs Cost Variance

Alert Overview:

This Alert notifies a user when a price on a Purchase Order is more than 10% different than the current cost of the 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.

Example Results / Scenario:

This report is very helpful in preventing GRPOs that throw off the cost of an item and then effect production costs. With this Alert, if a user enters a price on a Purchase Order that is going to change the cost of an item by more than 10%, the alert will notify the recipient. This will allow you to go in and change the purchase order with the correct price.

Alert: Purchase Order Price and Average Variance

/*
This Alert Displays Purchase Orders that have a variance in the 
purchase price vs. the Average price of the goods 
It looks for Open Purchase orders 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 OPOR T0
Inner Join POR1 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