TUTORIAL • OPEN
Automate Your Watchlist: SAP B1 Alerts for Low Stock and Overdue Invoices
Turn saved queries into scheduled alerts that land in users' inboxes. Two ready-to-use alert queries plus scheduling best practices.
SB1
SAP B1 Academy Editorial
Published • 2 min read
Most problems in SAP Business One are visible in the data days before anyone notices them. Alerts Management lets you schedule any saved query and push its results to users through the SAP B1 messaging system and, optionally, email.
How Query Alerts Work
- Save a query in the Query Manager.
- Open Administration → Alerts Management and create a user-defined alert.
- Assign the query, the recipients, and a frequency (for example every Monday at 08:00).
- Users receive the result grid as a message. Clicking a key field drills down to the document.
Alerts only fire when the query returns at least one row, so a well-written alert query returns exceptions only.
Alert 1: Items Below Minimum Stock
SQL · SAP HANA / MS SQL Server
Sign in to copy-- Items whose on-hand quantity is below the minimum inventory level
SELECT T0.ItemCode, T0.ItemName, T0.OnHand, T0.IsCommited, T0.OnOrder,
T0.MinLevel,
T0.OnHand - T0.IsCommited + T0.OnOrder AS Available_After_Orders
FROM OITM T0
WHERE T0.InvntItem = 'Y'
AND T0.frozenFor = 'N'
AND T0.MinLevel > 0
AND T0.OnHand - T0.IsCommited + T0.OnOrder < T0.MinLevel
ORDER BY (T0.OnHand - T0.IsCommited + T0.OnOrder) - T0.MinLevel;Alert 2: A/R Invoices More Than 30 Days Overdue
SQL · SAP HANA / MS SQL Server
Sign in to copy-- Open A/R invoices overdue by more than 30 days (MS SQL Server)
SELECT T0.DocNum, T0.CardCode, T0.CardName, T0.DocDueDate,
DATEDIFF(DAY, T0.DocDueDate, GETDATE()) AS Days_Overdue,
T0.DocTotal - T0.PaidToDate AS Balance_Due
FROM OINV T0
WHERE T0.DocStatus = 'O'
AND DATEDIFF(DAY, T0.DocDueDate, GETDATE()) > 30
ORDER BY Days_Overdue DESC;On SAP HANA, use DAYS_BETWEEN(T0."DocDueDate", CURRENT_DATE) and double-quote the identifiers.
Best Practices
- Keep alerts fast. They run on the server during business hours, so filter early and avoid scanning history tables.
- Send alerts to the people who act. A low-stock alert belongs with purchasing, not with the whole company.
- Review the alert list quarterly and remove alerts nobody reads. Alert fatigue makes people ignore the important ones.
Complete Consultant Toolkit
Get it ($29)The Ultimate SAP B1 SQL & HANA Query Cookbook
Instant-download PDF playbook plus ready-to-import .sql files for Financials, Inventory Audit, Sales Margin, and Approval Alerts.