Alert - GRPOs Older than 10 Days

Alert Overview:

This Alert notifies the user of all Goods Receipt POs that are more than 10 days old.

Purpose of the Alert:

This alert is useful because most GRPOs would be converted into AP Invoices within 10 days. If they are not, it could mean that a user created a spearate AP Invoice without using the Copy To function. This would throw inventory off, because it would bring the inventory in twice.

Parameters:

The alert should be set to run each morning at 8am, so that the user can start the day with a notification of any GRPOs that need to be reviewed.

Example Results / Scenario:

A common use of this report is to monitor users that create AP Invoices, but don't really have visibility into the GRPOs being created by the warehouse staff. It is easy for these users to receive an AP Invoice and after not finding the GRPO (or not looking for it), they just create an AP Invoice. This leaves the GRPO open, and it will then show up on the Alert.

If the GRPO is left open by mistake, it can be closed manually or reversed.

Alert: GRPO Older than 10 Days

 /*
This Alert Displays Goods Receipt POs that are open and are 
Older than 10 Days 
*/
-- Here you can set the number of days prior to today that
-- will display in the alert
-- Replace this value (original value is 10)
Declare @NumDays AS INT = 10
-- These are the columns that will be displayed
Select    T0.[DocNum]
		, T0.[DocDate]
		, DATEDIFF ( DAY ,T0.DocDate,  GETDATE()) AS 'Days Open'
		, 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
Where	T0.[DocStatus] IN ('O')
	AND DATEDIFF ( DAY ,T0.DocDate,  GETDATE()) >= @NumDays	
Order By T0.[DocDate] Asc, T0.[DocNum]
For Browse