Weekly stock replenishment brief from ERPNext to Slack

Every Monday at 7am, build me a stock replenishment brief from ERPNext and post it to my procurement channel in Slack.

Start with ERPNext List Documents on the Item doctype to pull every item that carries a reorder level and safety stock. For each of those items also read the fields the rest of this workflow depends on: the default supplier, the supplier lead time on the Item record, the item group, the default warehouse, and the stock unit of measure.

For each item and warehouse pair, call Get Stock Balance (erpnext.stock.utils.get_stock_balance) to get the real on hand quantity. Do not trust a cached quantity on the Item record, use the actual balance.

Next, cross reference open supply so we never double order something that is already inbound. Use List Documents on Purchase Order and on Material Request to find documents that are submitted (docstatus 1) but not yet fully received, and total the outstanding quantity already on its way in for each item and warehouse. Subtract nothing that has already been received.

For every item where on hand plus inbound falls below the reorder level, judge the urgency yourself rather than sorting by shortfall size. Compare how long the remaining stock will last against the supplier lead time on the Item record. A reorder point that does not account for lead time still produces stockouts even when the rule triggers correctly, so an item with a 21 day lead time and 9 days of cover is far more urgent than an item with a 3 day lead time and a bigger raw shortfall. If an item has no lead time recorded, still report it and flag the missing lead time instead of guessing.

For each shortfall, create a draft Material Request in ERPNext with Create Document for the shortfall quantity, targeting the right warehouse and using a required by date that reflects the supplier lead time. Leave every one of them as a draft with docstatus 0 so a human reviews and submits it. Do not submit them, and do not auto commit spend.

Finally, post a Send a Message digest to the procurement channel in Slack. Rank the shortfalls by urgency, and for each one name the item, the warehouse, the on hand quantity, the inbound quantity, the reorder level, the shortfall, the supplier, the supplier lead time, and the draft Material Request that was created for it. Explicitly call out any item that will stock out before its lead time elapses, since those are the ones that need a phone call and not just an approval. If nothing is below its reorder level, post a short all clear message instead of an empty table.