Daily Odoo low stock brief that skips what is already on order

Every weekday at 7am, work out which products in my Odoo inventory are genuinely about to run out, and brief my operations team on them.

Start with current stock. Use Odoo Search & Read Records (search_read) on stock.quant to get on hand quantities per product, restricted to internal stock locations so you are not counting stock sitting in customer, supplier or virtual locations. Then use Search & Read Records on product.product to pull the details needed for the brief: name, internal reference or SKU, list price, and whether the product is still active. Skip anything archived (active is false) or marked discontinued, and ignore service type products that do not hold stock at all.

Next work out how fast each product is selling. Use Read Grouped Aggregates (read_group) on sale.order.line grouped by product, filtered to confirmed orders from the last 30 days, summing both quantity sold and line revenue. Divide the 30 day quantity by 30 to get a daily velocity per product. If a product sold nothing in that window it has no meaningful velocity, so leave it out of the brief rather than reporting infinite days of cover.

Then find out what is already on the way. Use Search & Read Records on purchase.order.line to pull open purchase order lines per product, with quantity ordered, quantity already received, and the expected receipt date. Only count lines belonging to confirmed purchase orders, not drafts or cancelled ones.

Now apply the judgement, which is the whole point of this workflow. For each product compute days of cover as quantity on hand divided by daily velocity, then estimate a stockout date as today plus days of cover. Exclude any product where a confirmed inbound purchase order is expected to arrive before that stockout date, because that gap is already handled and flagging it just creates noise. What remains is the genuine risk list. Odoo's native reordering rules fire on a static minimum quantity with no awareness of sales velocity or of inbound stock already covering the gap, which is exactly what generates alert fatigue, so do not simply reproduce them.

Rank the risk list by days of cover ascending first, then by revenue at risk descending as the tiebreaker, where revenue at risk is roughly daily velocity multiplied by list price across the expected replenishment window. For each product suggest a reorder quantity that covers the supplier lead time plus a safety buffer, using the product's configured lead time in Odoo where one exists and a sensible default where it does not.

Post the brief to Slack using Send a Message to my operations channel. Keep it to the top items, around ten, rather than dumping the whole catalogue. For each product show the name and SKU, days of cover, recent daily velocity, quantity on hand, suggested reorder quantity and revenue at risk, ordered worst first. Close with a single line saying how many further products were low but excluded because stock is already inbound, so the team can see they were considered.

Finally, append one row per at risk product to my Google Sheets tracking sheet using Append Values, so we build a history of stockout near misses over time. Each row should carry the run date, product name, SKU, quantity on hand, daily velocity, days of cover, revenue at risk, suggested reorder quantity, and whether an inbound purchase order exists. If nothing is at risk on a given morning, post a short all clear to Slack and append nothing to the sheet.