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.
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%.
SQL Query
/*
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
Comments