Alert - Purchase Price vs Cost Variance


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.


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.


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
ON T3.ItemCode = T1.ItemCode
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)      
            T1.[Price] = '0'   
    AND DATEDIFF ( DAY ,T0.UpdateDate,  GETDATE()) <= @NumDays   
For Browse
Was this article helpful?
0 out of 0 found this helpful