System Control Pack Home

/*
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

 /* Production Orders
 Alert for Produced Items Received vs Planned Qty 
 If the variance is greater than the percent specified 
*/
-- 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 25)
Declare @Percentage AS INT = 25
-- 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 T2.[DocNum]	AS 'Production #'
		, T2.[U_ORC_BE_ProdDate] AS 'Production Date'
		, Case (T2.[Type])
			When 'S' Then 'Standard'
			When 'D' Then 'Disassembly'
			Else T2.[Type]
			End AS 'Production Type'
		, T2.[ItemCode] AS 'Parent ItemCode'
		, T3.[ItemName] AS 'Parent ItemName'
		, T2.[PlannedQty] AS 'Planned Qty'
		, T2.[CmpltQty] AS 'Completed Qty'
		, (T2.[CmpltQty] - T2.[PlannedQty]) AS 'Difference'
		, ((T2.[CmpltQty] - T2.[PlannedQty]) / T2.[PlannedQty] * 100) As 'Variance %'
-- Here are the tables the data comes from
from OWOR T2
	Inner Join OITM T3
	ON T3.[ItemCode] = T2.[ItemCode]
Where T2.[CmpltQty] > '0'
	AND ABS((T2.[CmpltQty] - T2.[PlannedQty]) / T2.[PlannedQty] * 100) > @Percentage   
	AND DATEDIFF ( DAY , T2.[U_ORC_BE_ProdDate],  GETDATE()) <= @NumDays	
Order By T2.[U_ORC_BE_ProdDate] Desc, T2.[DocNum]
For Browse

/*
This Alert Displays Open A/P Invoices that have payment terms
with a discount that can be taken within the next number of days 
It will show documents that have a discount that can be taken in the next 2 days.
*/
-- Here you can set the number of days prior to today that
-- will display in the alert
-- Replace this value (original value is 2)
Declare @DaysBefore AS INT = 2
-- These are the columns that will be displayed
SELECT T0.DocEntry 
	, T0.DocNum
	, T0.DocStatus
	, T0.DocDate
	, T0.DocDueDate
	, T0.CardCode
	, T0.CardName
	, T0.DocTotal
	, T0.PaidToDate
	, T1.PymntGroup AS 'Payment Terms'
	, T2.TableDesc As 'Discount Name'
	, T3.NumOfDays As 'Discount Days'
	, T3.Discount AS 'Discount Percent'
	, DateDiff(Day, GETDATE(), (T0.[DocDate] + T3.[NumOfDays])) AS 'Days left'
	, DateAdd(Day, T3.[numofdays], T0.[DocDate]) AS 'Discount Date'
	
FROM opch T0
Inner Join OCTG T1
ON T1.GroupNum = T0.GroupNum
Inner Join OCDC T2
ON T2.Code = T1.DiscCode
Inner Join CDC1 T3
ON T3.CDCCode = T2.Code
WHERE DateDiff(Day, GETDATE(), (T0.[DocDate] + T3.[NumOfDays])) > 0
	AND DateDiff(Day, GETDATE(), (T0.[DocDate] + T3.[NumOfDays])) <= @DaysBefore
	AND T0.DocStatus = 'O'
	
Order By DateDiff(Day, GETDATE(), (T0.[DocDate] + T3.[NumOfDays])) Asc
For Browse

/*
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

/*
This Alert Displays Sales Orders that have a Document Price that is different 
Than the Price list for the Business Partner
It will show documents that have been updated in the last 7 days.
*/
-- 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 'Sales Order' AS 'Doc Type'
		, T0.[DocNum] AS 'Document #'
		, T0.[DocDate] AS 'Doc Date'
		, T0.[CardCode] AS 'Customer Code'
		, T0.[CardName] AS 'Customer Name'
		, T1.[ItemCode] AS 'Item Code'
		, T1.[Price] AS 'Document Price'
		, T3.[Price] as 'Price List Price'
		, (T1.[Price] - T3.[Price]) AS 'Difference $'
		, T4.[U_Name] AS 'Created By'
		, T5.[U_Name] AS 'Updated By'
		, T0.[UpdateDate] AS 'Update Date'
-- Here are the tables the data comes from
from ORDR T0
Inner Join RDR1 T1
ON T1.[DocEntry] = T0.[DocEntry]
Inner JOIN OCRD T2
ON T2.[CardCode] = T0.[CardCode]
Inner JOIN ITM1 T3
ON T3.[ItemCode] = T1.[ItemCode]
AND T3.[PriceList] = T2.[ListNum]
Inner Join OUSR T4
ON T0.[UserSign] = T4.[USERID]
Inner Join OUSR T5
ON T0.[UserSign2] = T5.[USERID]
Where T1.[Price] <> T3.[Price]
	AND DATEDIFF ( DAY ,T0.[UpdateDate] ,  GETDATE()) <= @NumDays	
For Browse