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

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

 /* Production Orders
 Alert for Component Items Issued 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'
		, T0.[ItemCode] AS 'Component ItemCode'
		, T1.[ItemName] AS 'Component ItemName'
		, T0.[PlannedQty] AS 'Component Planned Qty'
		, T0.[IssuedQty] AS 'Component Issued Qty'
		, (T0.[IssuedQty] - T0.[PlannedQty]) AS 'Difference'
		, ((T0.[IssuedQty] - T0.[PlannedQty]) / T0.[PlannedQty] * 100) As 'Variance %'
-- Here are the tables the data comes from
from WOR1 T0
	Inner Join OITM T1
	ON T1.[ItemCode] = T0.[ItemCode]
	Inner Join OWOR T2
	ON T2.[DocEntry] = T0.[DocEntry]
	Inner Join OITM T3
	ON T3.[ItemCode] = T2.[ItemCode]
Where T0.[IssuedQty] > '0'
	AND ABS((T0.[IssuedQty] - T0.[PlannedQty]) / T0.[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

 /* Warehouses Without a location
 Alert for Warehouses with a Tank Type that do not have a location
 set.*/
 Select T0.WhsCode
	, T0.WhsName
	, T1.[Location] AS 'Location '
	, Case (T0.U_ORC_BE_WhseType)
		When 'BH' Then 'Brew House'
		When 'FG' Then 'Finished Goods'
		When 'BT' Then 'Bright Tank'
		When 'FV' Then 'Fermenter'
		When 'U' Then 'Uni Tank'
		Else 'NA'
		End AS 'Warehouse Type'
	
From OWHS T0
Left Join OLCT T1
ON T1.[Code] = T0.[Location]
Where T0.U_ORC_BE_WhseType IN ('BH', 'FG', 'BT', 'FV', 'U')
	AND (T0.Location IS NULL OR T0.[Location] = '-1')
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