Follow

Alert - Purchase Price vs Cost Variance

Summary

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.

SQL QUERY

/*
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
Version 4.5.1.0
 
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments