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.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
/* 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 |