Creating a right alert without duplicated lines
SELECT T2.WhsCode,
T1.AcctCode,
T1.CurrTotal,
SUM(T2.OnHand) AS 'On Hand'
FROM dbo.OWHS T0
INNER JOIN dbo.OACT T1 ON T0.BalInvntAc = T1.AcctCode
INNER JOIN dbo.OITW T2 ON T0.WhsCode = T2.WhsCode
WHERE T1.CurrTotal !=0
GROUP BY T2.WhsCode, T1.AcctCode, T1.CurrTotal
Having SUM(T2.OnHand)=0
To create an alert query to catch anyone adding anything to an invoice without a base document
SELECT T1.DocNum
FROM dbo.INV1 T0
INNER JOIN dbo.OINV T1 ON T1.DocEntry = T0.DocENtry
WHERE T1.DocNum = $[$8.0.0] AND
T0.ItemCode = $[$38.1.0] AND
T0.BaseType = -1
A/R Invoice past due alert
SELECT T0.CardCode,
T0.CardName,
T0.DocNum,
T0.DocDate,
T0.DocDueDate,
(T0.DocTotal-T0.PAIDTODATE) 'Due Amount'
FROM dbo.OINV T0
WHERE DateDiff(DD,T0.DocDueDATE,GETDATE()) > 0 AND T0.DocTotal>T0.
PaidTODate
ORDER BY T0.CardCode
Special ship to alert for Sales Order
SELECT T0.DocDate,
T0.DocNum,
T0.Cardname as 'Customer Name',
T0.NumAtCard as 'Customer Ref No.'
FROM dbo.ORDR T0
INNER JOIN dbo.OCRD T2 ON T2.CardCode = T0.CardCode
WHERE
T0.Address2 like '%n%' AND T0.DoCStatus = 'O'
Open Sales Opportunity alert
SELECT T0.OpprId,
LEFT(T0.Name,20) As 'Sales Op',
T0.CardCode,
Left(T0.CardName,20) AS Customer,
T0.MaxSumLoc AS 'Potential Amt',
T0.WtSumLoc AS 'Weighed Amt',
T0.PredDate AS 'Predicted Closing Date',
Max(T1.Step_Id) 'Stage Key',
T2.SlpName AS 'Ac. Mgr.'
FROM dbo.OOPR T0
INNER JOIN dbo.OPR1 T1 ON T0.OpprId = T1.OpprId
INNER JOIN dbo.OSLP T2 ON T0.SlpCode = T2.SlpCode
WHERE T0.Status ='O'
Group By T0.OpprId, T0.Name, T0.CardCode, T0.CardName, T0.MaxSumLoc, T0.WtSumLoc, T0.PredDate, T2.SlpName
