- Created by Gary Godfrey (Unlicensed), last modified on Aug 25, 2014
You are viewing an old version of this page. View the current version.
Compare with Current View Page History
« Previous Version 17 Next »
Alerts:
Alert: GRPO Price and Average Variance Expand source
/* 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
Alert: GRPO Older than 10 Days Expand source
/* 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
Alert: Production Component Variance Expand source
/* 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
Alert: Warehouses without Location assigned Expand source
Alert: Production Header Variance Expand source
/* 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
Alert: A/P Invoices Eligible for Discount Expand source
/* 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
Alert: Purchase Order Price and Average Variance Expand source
/* 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
Alert: Sales Order Price vs Price List variance Expand source
/* 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
Recent space activity
-
-
Record Payroll contributed Jan 12, 2021
-
-
-
Editing Email Body & Subject for SAP B1 Documents contributed Sept 24, 2020
-
-
-
Quick Start: Copy PLD Layout from Company to Company Option 1: (Cut/Paste) contributed Feb 26, 2020
-
Quick Start: Copy Crystal Report (Layout or Report) from Company to Company contributed Feb 26, 2020
-
-
-
OUT OF DATE: Log in to OrchestratedBEER - OnDemand contributed Jun 21, 2017
-
Space contributors
- Gary Godfrey (Unlicensed) (3356 days ago)
- No labels