"""Warehouse module — SKU movement & warehouse efficiency, live from stock.picking / stock.move (read-only). Physical operations are consolidated (one network of warehouses, not BU-tagged). What the data supports (probed 2026-07-05): date_done is fully populated on done pickings, the Ditmas Ave outbound runs an explicit Pick -> Pack -> Ship funnel (separate picking types), ~25k pickings and ~221k moves a year, and inventory-adjustment moves (~10k/yr) give a shrinkage / correction signal. Cycle times, on-time rates, throughput, backlog age and adjustment dollars are all derivable; anything scanner-level (walk paths, per-picker rates) is NOT in the data and is not claimed. All metrics are computed from ONE 12-month done-picking pull + small side pulls; validate() reconciles row-built aggregates to independent read_group/search_count paths. """ import sys import datetime as dt from pathlib import Path sys.path.insert(0, str(Path(__file__).resolve().parents[1])) import core.odoo as O import core.periods as P ONTIME_GRACE_DAYS = 1 # shipped within scheduled + 1 day counts as on time # Main-warehouse funnel + Putaway (the dock-to-stock proxy: receipts sit until put away). _STAGE_NAMES = {'Pick': 'Pick', 'Pack': 'Pack', 'Delivery Orders': 'Ship', 'Putaway': 'Putaway'} def _dtp(v): try: return dt.datetime.fromisoformat(str(v)[:19]) except Exception: return None def _types(): """picking_type_id -> {code, wh, name, stage} (stage set for the main-warehouse funnel).""" out = {} for t in O.search_read('stock.picking.type', [], ['name', 'code', 'warehouse_id']): wh = O.m2o_name(t.get('warehouse_id')) or '' stage = _STAGE_NAMES.get(t['name']) if 'Ditmas' in wh else None out[t['id']] = {'code': t['code'], 'wh': wh, 'name': t['name'], 'stage': stage} return out def _done_pickings(y1): return O.search_read('stock.picking', [('state', '=', 'done'), ('date_done', '>=', y1)], ['picking_type_id', 'scheduled_date', 'date_done', 'date', 'backorder_id']) def _open_pickings(): return O.search_read('stock.picking', [('state', 'in', ['confirmed', 'waiting', 'assigned'])], ['picking_type_id', 'scheduled_date', 'date', 'origin', 'partner_id', 'state']) def _median(v): if not v: return None s = sorted(v) n = len(s) return s[n // 2] if n % 2 else (s[n // 2 - 1] + s[n // 2]) / 2 def _p90(v): if not v: return None s = sorted(v) return s[min(len(s) - 1, int(len(s) * 0.9))] def _iso_week(d): y, w, _ = d.isocalendar() return f'{y}-W{w:02d}', w def _xmr(series): """Wheeler XmR (individuals) limits + the THREE detection rules — no more (extra rules = alarm fatigue; see the warehouse research brief). series = [(label, value)] in time order. Ported logic cribbed from xmrit (MIT); ~30 lines, no dependency.""" vals = [v for _, v in series] if len(vals) < 8: return {'center': None, 'unpl': None, 'lnpl': None, 'points': [ {'label': l, 'value': v, 'signal': ''} for l, v in series]} center = sum(vals) / len(vals) mrs = [abs(vals[i] - vals[i - 1]) for i in range(1, len(vals))] mr_bar = sum(mrs) / len(mrs) if mrs else 0.0 unpl = min(100.0, center + 2.66 * mr_bar) lnpl = max(0.0, center - 2.66 * mr_bar) pts = [] for i, (l, v) in enumerate(series): sig = '' if v > unpl or v < lnpl: sig = 'outside limits' # rule 1 if not sig and i >= 7 and all((x - center) > 0 for _, x in series[i - 7:i + 1]): sig = 'sustained shift (8 above center)' # rule 2 if not sig and i >= 7 and all((x - center) < 0 for _, x in series[i - 7:i + 1]): sig = 'sustained shift (8 below center)' if not sig and i >= 3: last4 = [x for _, x in series[i - 3:i + 1]] near = sum(1 for x in last4 if abs(x - center) > 0.5 * ((unpl - center) if x >= center else (center - lnpl))) if near >= 3: sig = 'moderate shift emerging' # rule 3 pts.append({'label': l, 'value': v, 'signal': sig}) return {'center': center, 'unpl': unpl, 'lnpl': lnpl, 'points': pts} def build(t=None): """The full warehouse-efficiency bundle from one done-picking pull + side pulls. Pull window ~14 months so the weekly volume series has a same-week-last-year comparison.""" t = t or P.today() y1 = (t - dt.timedelta(days=430)).isoformat() types = _types() done = _done_pickings(y1) open_ = _open_pickings() _y365 = (t - dt.timedelta(days=365)).isoformat() # ---- monthly throughput by flow (12m) ---------------------------------- monthly = {} for r in done: if str(r['date_done'])[:10] < _y365: continue ti = types.get(O.m2o_id(r['picking_type_id']), {}) code = ti.get('code') or '?' m = str(r['date_done'])[:7] key = {'incoming': 'Receipts', 'outgoing': 'Deliveries', 'internal': 'Internal'}.get(code, code) if ti.get('name') == 'Returns': key = 'Returns' e = monthly.setdefault(m, {'month': m, 'Receipts': 0, 'Deliveries': 0, 'Internal': 0, 'Returns': 0}) e[key] = e.get(key, 0) + 1 monthly_rows = sorted(monthly.values(), key=lambda x: x['month']) # ---- outbound funnel cycle times (main warehouse Pick -> Pack -> Ship, + Putaway) -------- stages, stage_m = {}, {} for r in done: if str(r['date_done'])[:10] < _y365: continue ti = types.get(O.m2o_id(r['picking_type_id']), {}) if not ti.get('stage'): continue cr, dd, sch = _dtp(r['date']), _dtp(r['date_done']), _dtp(r['scheduled_date']) if not (cr and dd): continue cyc = (dd - cr).total_seconds() / 86400 s = stages.setdefault(ti['stage'], {'stage': ti['stage'], 'n': 0, 'cycle': [], 'vs_sched': []}) s['n'] += 1 s['cycle'].append(cyc) if sch: s['vs_sched'].append((dd - sch).total_seconds() / 86400) stage_m.setdefault((ti['stage'], str(r['date_done'])[:7]), []).append(cyc) stage_rows = [] for name in ('Pick', 'Pack', 'Ship', 'Putaway'): s = stages.get(name) if s: stage_rows.append({'stage': name, 'done_12m': s['n'], 'median_days': round(_median(s['cycle']) or 0, 2), 'p90_days': round(_p90(s['cycle']) or 0, 2), 'vs_sched_median': round(_median(s['vs_sched']) or 0, 2)}) stage_monthly = sorted( ({'stage': k[0], 'month': k[1], 'median_days': round(_median(v) or 0, 2), 'p90_days': round(_p90(v) or 0, 2), 'n': len(v)} for k, v in stage_m.items()), key=lambda x: (x['month'], x['stage'])) # ---- on-time ship (outgoing): monthly + WEEKLY (XmR grain) + clean-order -- y365 = (t - dt.timedelta(days=365)).isoformat() d90 = (t - dt.timedelta(days=90)).isoformat() ontime_m, weekly, clean_m, wk_vol = {}, {}, {}, {} ship_cycles = [] clean90 = {'n': 0, 'clean': 0, 'late': 0, 'bo': 0} for r in done: ti = types.get(O.m2o_id(r['picking_type_id']), {}) if ti.get('code') != 'outgoing': continue dd, sch, cr = _dtp(r['date_done']), _dtp(r['scheduled_date']), _dtp(r['date']) if not dd: continue dstr = str(r['date_done'])[:10] wk_key, wk_no = _iso_week(dd.date()) wk_vol.setdefault(wk_key, {'week': wk_key, 'wk_no': wk_no, 'year': dd.year, 'n': 0}) wk_vol[wk_key]['n'] += 1 late = not (sch and (dd - sch).total_seconds() / 86400 <= ONTIME_GRACE_DAYS) bo = bool(r.get('backorder_id')) if dstr < y365: continue # older-than-12m rows only feed the LY weekly compare m = dstr[:7] e = ontime_m.setdefault(m, {'month': m, 'shipped': 0, 'on_time': 0, 'backorders': 0, 'cycle': []}) e['shipped'] += 1 if not late: e['on_time'] += 1 if bo: e['backorders'] += 1 if cr: c = (dd - cr).total_seconds() / 86400 e['cycle'].append(c) ship_cycles.append(c) w = weekly.setdefault(wk_key, {'week': wk_key, 'n': 0, 'on_time': 0}) w['n'] += 1 if not late: w['on_time'] += 1 cm = clean_m.setdefault(m, {'month': m, 'On time & complete': 0, 'Late only': 0, 'Backordered only': 0, 'Late + backordered': 0}) cat = ('On time & complete' if not late and not bo else 'Late only' if late and not bo else 'Backordered only' if bo and not late else 'Late + backordered') cm[cat] += 1 if dstr >= d90: clean90['n'] += 1 clean90['clean'] += (0 if (late or bo) else 1) clean90['late'] += (1 if late else 0) clean90['bo'] += (1 if bo else 0) ontime_rows = [] for m in sorted(ontime_m): e = ontime_m[m] ontime_rows.append({'month': m, 'shipped': e['shipped'], 'on_time_pct': e['on_time'] / e['shipped'] * 100 if e['shipped'] else None, 'median_days': round(_median(e['cycle']) or 0, 2), 'backorder_pct': e['backorders'] / e['shipped'] * 100 if e['shipped'] else None}) n_out = sum(e['shipped'] for e in ontime_m.values()) n_ontime = sum(e['on_time'] for e in ontime_m.values()) # weekly on-time series (complete weeks only — drop the in-progress week) -> XmR cur_wk = _iso_week(t)[0] wk_series = [(k, weekly[k]['on_time'] / weekly[k]['n'] * 100) for k in sorted(weekly) if k != cur_wk and weekly[k]['n'] >= 5] xmr = _xmr(wk_series[-52:]) # weekly volume: last 13 complete weeks vs same ISO week last year vol_rows = [] for k in sorted(wk_vol): v = wk_vol[k] if k == cur_wk or v['year'] < t.year - 1: continue if v['year'] == t.year: ly = next((x for x in wk_vol.values() if x['wk_no'] == v['wk_no'] and x['year'] == v['year'] - 1), None) vol_rows.append({'week': f"W{v['wk_no']:02d}", 'shipped': v['n'], 'shipped_ly': ly['n'] if ly else 0}) vol_rows = vol_rows[-13:] clean_rows = [clean_m[m] for m in sorted(clean_m)] clean_pct_90d = (clean90['clean'] / clean90['n'] * 100) if clean90['n'] else None # ---- backlog (open pickings, oldest first) + expedite list -------------- today = dt.datetime.combine(t, dt.time()) ship_med = _median([s for s in ship_cycles]) or 1.0 bl, expedite = {}, [] for r in open_: ti = types.get(O.m2o_id(r['picking_type_id']), {}) label = f"{ti.get('wh', '?')}: {ti.get('name', '?')}" sch = _dtp(r['scheduled_date']) or _dtp(r['date']) age = (today - sch).days if sch else None e = bl.setdefault(label, {'queue': label, 'open': 0, 'overdue': 0, 'oldest_days': 0}) e['open'] += 1 if age is not None and age > 0: e['overdue'] += 1 e['oldest_days'] = max(e['oldest_days'], age) # Expedite: outgoing, past scheduled+grace, waited longer than the normal ship cycle. # 'waiting' state = unreserved stock -> that's a PROCUREMENT gap, routed separately # (don't tell the warehouse to expedite what it cannot pick). if (ti.get('code') == 'outgoing' and age is not None and age > ONTIME_GRACE_DAYS and age > ship_med): expedite.append({'order': r.get('origin') or '', 'customer': O.m2o_name(r.get('partner_id')), 'queue': label, 'scheduled': str(r.get('scheduled_date') or '')[:10], 'days_late': age, 'blocked_on_stock': r.get('state') == 'waiting'}) backlog_rows = sorted(bl.values(), key=lambda x: (-x['oldest_days'], -x['open'])) expedite.sort(key=lambda x: -x['days_late']) n_done_12m = sum(1 for r in done if str(r['date_done'])[:10] >= _y365) return { 'monthly': monthly_rows, 'stages': stage_rows, 'stage_monthly': stage_monthly, 'ontime': ontime_rows, 'backlog': backlog_rows, 'expedite': expedite, 'xmr': xmr, 'weekly_vol': vol_rows, 'clean': clean_rows, 'clean_pct_90d': clean_pct_90d, 'kpi': { 'done_12m': n_done_12m, 'open_now': len(open_), 'ontime_pct': (n_ontime / n_out * 100) if n_out else None, 'ship_median_days': round(_median(ship_cycles) or 0, 2), 'shipped_12m': n_out, 'backlog_overdue': sum(e['overdue'] for e in bl.values()), }, '_n_out_rowpath': n_out, } def adjustments(t=None, top=15): """Inventory-adjustment moves (to/from an 'inventory'-usage location) = the shrinkage / correction signal. Valued from Odoo's stock.valuation.layer (ACTUAL historical cost) where a layer exists for the move; current standard cost only as fallback — upgraded 2026-07-05 from the all-approximation version. Monthly gains/losses + the most-corrected SKUs.""" t = t or P.today() y1 = (t - dt.timedelta(days=365)).isoformat() gains = O.search_read('stock.move', [('state', '=', 'done'), ('date', '>=', y1), ('location_id.usage', '=', 'inventory')], ['product_id', 'product_uom_qty', 'date']) losses = O.search_read('stock.move', [('state', '=', 'done'), ('date', '>=', y1), ('location_dest_id.usage', '=', 'inventory')], ['product_id', 'product_uom_qty', 'date']) pids = list({O.m2o_id(r['product_id']) for r in gains + losses if r.get('product_id')}) cost = {} for i in range(0, len(pids), 5000): for p in O.search_read('product.product', [('id', 'in', pids[i:i + 5000]), ('active', 'in', [True, False])], ['default_code', 'standard_price']): cost[p['id']] = {'code': (p.get('default_code') or '').strip() or f"#{p['id']}", 'cost': p.get('standard_price') or 0.0} # Actual valuation per move from stock.valuation.layer (Σ |value| per move; a move can carry # several layers). Moves WITH a layer row use it — even a legitimate 0; only layerless moves # fall back to qty × current standard cost. mids = [m['id'] for m in gains + losses] layer_val, layer_seen = {}, set() for i in range(0, len(mids), 5000): for l in O.search_read('stock.valuation.layer', [('stock_move_id', 'in', mids[i:i + 5000])], ['stock_move_id', 'value']): mid = O.m2o_id(l['stock_move_id']) layer_seen.add(mid) layer_val[mid] = layer_val.get(mid, 0.0) + abs(l.get('value') or 0.0) d90 = (t - dt.timedelta(days=90)).isoformat() monthly, per_sku = {}, {} n_layer = n_fallback = 0 for rows, sign, key in ((gains, +1, 'gains'), (losses, -1, 'losses')): for r in rows: pid = O.m2o_id(r['product_id']) c = cost.get(pid, {'code': '?', 'cost': 0.0}) if r['id'] in layer_seen: val = layer_val.get(r['id'], 0.0) n_layer += 1 else: val = (r.get('product_uom_qty') or 0.0) * c['cost'] n_fallback += 1 m = str(r['date'])[:7] e = monthly.setdefault(m, {'month': m, 'gains': 0.0, 'losses': 0.0, 'moves': 0}) e[key] += val e['moves'] += 1 s = per_sku.setdefault(pid, {'sku': c['code'], 'product': O.m2o_name(r['product_id']), 'net_qty': 0.0, 'net_value': 0.0, 'moves': 0, 'moves_90d': 0, 'gross_value': 0.0}) s['net_qty'] += sign * (r.get('product_uom_qty') or 0.0) s['net_value'] += sign * val s['gross_value'] += abs(val) s['moves'] += 1 if str(r['date'])[:10] >= d90: s['moves_90d'] += 1 monthly_rows = sorted(monthly.values(), key=lambda x: x['month']) all_sku = list(per_sku.values()) top_rows = sorted(all_sku, key=lambda x: -abs(x['net_value']))[:top] # Pareto: cumulative share of gross correction value (the "N SKUs = 80% of corrections" view) by_gross = sorted(all_sku, key=lambda x: -x['gross_value']) tot_gross = sum(s['gross_value'] for s in by_gross) or 1.0 run = 0.0 pareto = [] for s in by_gross[:30]: run += s['gross_value'] pareto.append({'sku': s['sku'], 'gross_value': s['gross_value'], 'cum_pct': run / tot_gross * 100}) # Worklists (exception-first: every flag terminates in an actionable list) cc_list = sorted((s for s in all_sku if s['moves_90d'] >= 3), key=lambda x: (-x['moves_90d'], -abs(x['net_value']))) churn_list = sorted((s for s in all_sku if s['gross_value'] > 500 and s['gross_value'] > 2 * abs(s['net_value'])), key=lambda x: -x['gross_value']) return {'monthly': monthly_rows, 'top': top_rows, 'pareto': pareto, # FULL worklists — the UI paginates; a silent [:40] cap made the headline counts # unverifiable in-app (owner rule: every sum drills to its rows) 'cycle_count': cc_list, 'churn': churn_list, 'n_cycle_count': len(cc_list), 'n_churn': len(churn_list), 'total_losses': sum(m['losses'] for m in monthly_rows), 'total_gains': sum(m['gains'] for m in monthly_rows), 'n_moves': sum(m['moves'] for m in monthly_rows), 'n_layer_valued': n_layer, 'n_cost_fallback': n_fallback, '_rowsum_loss_qty': sum((r.get('product_uom_qty') or 0.0) for r in losses), '_rowsum_layer_abs': sum(layer_val.values()), '_y1': y1} def touches(t=None, top=25): """Most-handled SKUs by done stock-move count — where warehouse labor concentrates. Carries default_code so the rows open the canonical SKU drawer (cross-module interop).""" t = t or P.today() y1 = (t - dt.timedelta(days=365)).isoformat() g = O.read_group('stock.move', [('state', '=', 'done'), ('date', '>=', y1)], ['product_uom_qty:sum'], ['product_id'], lazy=False) rows = [] total = 0 for r in g: n = r.get('__count') or 0 total += n if r.get('product_id'): rows.append({'pid': O.m2o_id(r['product_id']), 'product': O.m2o_name(r['product_id']), 'moves': n, 'units': r.get('product_uom_qty') or 0.0}) rows.sort(key=lambda x: -x['moves']) rows = rows[:top] pids = [r['pid'] for r in rows] codes = {p['id']: (p.get('default_code') or '').strip() for p in O.search_read('product.product', [('id', 'in', pids), ('active', 'in', [True, False])], ['default_code'])} for r in rows: r['share_pct'] = r['moves'] / total * 100 if total else None r['code'] = codes.get(r['pid'], '') return {'top': rows, 'total_moves': total} def _sop(diagnose, fix, verify, cadence): """Embedded runbook per flag: versioned IN CODE (like metric definitions — an SOP that drifts in a UI editor breaks the explainability contract). Rendered as a drill-down under the flag.""" return {'diagnose': diagnose, 'fix': fix, 'verify': verify, 'cadence': cadence} def flags(b, adj, ph=None): """The recommendation engine: explainable rules from the warehouse research brief (wiki/research/warehouse.md), each terminating in an action AND carrying its SOP drill-down. Three Wheeler rules on the weekly on-time XmR, stage bottleneck/stalled-subset, clean-order breach, backorder jump, backlog breach, capacity-ahead-of-season, adjustment worklists, counting-program health.""" out = [] if ph and ph.get('ratio_pct') is not None and ph['ratio_pct'] > 1: out.append({'sev': 'high', 'title': f"Corrections churn = {ph['ratio_pct']:.0f}% of inventory value per year " "(the accepted band is 1%; world-class runs under 0.5%)", 'detail': f"Gross adjustments {ph['gross_12m']:,.0f} against ~{ph['inv_value_approx']:,.0f} " "of stock (ratio uses current-standard-cost inventory value - an approximation " "for this ratio only).", 'action': 'This is a counting-PROGRAM problem, not per-SKU noise. Until it heals, ' 'quantity-based recommendations for flagged SKUs carry count-trust ' 'warnings on the Procurement, Inventory and Assortment pages.', 'sop': _sop( ['Confirm the churn is process, not shrink: the gains-vs-losses chart ' 'below - roughly offsetting bars = counting/UoM/receiving errors.', 'The Pareto names where the value concentrates; the churn worklist ' 'names the process suspects.'], ['Stand up the weekly cycle-count ritual (worklist below, 10-20 SKUs/wk, ' 'counter =/= approver, root-cause tag on every correction).', 'Fix the top-5 churn SKUs\' unit-of-measure/receiving practice first - ' 'one UoM fix can kill hundreds of corrections.', 'Track this ratio monthly; it should fall as counts come clean.'], ['Ratio trending toward 1%, then 0.5%; the cycle-count worklist shrinking ' 'month over month.'], 'Monthly program review; weekly count ritual.')}) sigs = [p for p in b['xmr']['points'][-8:] if p['signal']] if sigs: out.append({'sev': 'high', 'title': 'On-time shipping shows a real signal, not noise', 'detail': '; '.join(f"{p['label']}: {p['value']:.0f}% ({p['signal']})" for p in sigs[-3:]), 'action': 'Investigate those weeks specifically - carrier, holiday, stockout or ' 'staffing. XmR limits say this is beyond routine variation.', 'sop': _sop( ['Open the signal week(s) in Odoo: Inventory > Transfers, filter Delivery ' 'Orders, Done, date_done in that week, then sort by scheduled date.', 'Classify the late ones: carrier pickup missed / short-staffed day / ' 'stock not available (backorder created) / holiday cluster.', 'One cause should cover most of the week - a signal week is a COMMON ' 'cause, not many little ones.'], ['Carrier: escalate with the carrier rep, add a second pickup on peak days.', 'Staffing: move the pack-day roster; see the Weekly-volume chart for which ' 'weekdays run hot.', 'Stock: the backorder share in the Clean-orders chart confirms it - work ' 'the Procurement buy list, not the floor.'], ['The next 2 weekly points fall back inside the band.', 'If 8+ points stay above/below center, re-baseline the chart (the process ' 'genuinely changed).'], 'On signal only - do NOT investigate routine (in-band) weeks.')}) if b.get('clean_pct_90d') is not None and b['clean_pct_90d'] < 90: out.append({'sev': 'high', 'title': f"Clean-order rate {b['clean_pct_90d']:.0f}% (90d) - below the 90% bar", 'detail': 'Clean = shipped on time AND complete (no backorder). Top-quartile B2B is 95%+.', 'action': 'Decompose in the clean-order chart: a late-driven gap is a warehouse/carrier ' 'issue; a backorder-driven gap is availability - route to Procurement.', 'sop': _sop( ['Read the Clean-orders chart: which slice eats the gap - Late only (gold), ' 'Backordered only (gray), or both (red)?', 'Late-driven: pull the expedite list + the signal-week SOP above.', 'Backorder-driven: these SKUs stocked out at ship time - cross-reference ' 'the Procurement buy list and the open-PO late chase list.'], ['Late-driven: fix the floor/carrier cause (see the on-time SOP).', 'Backorder-driven: expedite the late POs for the affected SKUs (Procurement ' '> Late deliveries names the supplier and days late); raise reorder points ' 'on repeat offenders.', 'Both: work availability first - a complete late order beats an on-time ' 'partial for most wholesale customers.'], ['Trailing-90d clean rate back over 90% (top-quartile bar: 95%).'], 'Weekly review until >90%, then monthly.')}) pr = b.get('promise') or {} if pr.get('on_promise_pct') is not None and pr['on_promise_pct'] < 85: out.append({'sev': 'high' if pr['on_promise_pct'] < 70 else 'medium', 'title': f"Promise-date OTIF {pr['on_promise_pct']:.0f}% - customers get " f"{len(pr['missed']):,} of {pr['n_measured']:,} promised orders late", 'detail': 'Delivered vs the commitment_date QUOTED to the customer (+1d ' f"grace); p90 lateness +{pr['late_p90']}d. The internal on-time " 'XmR can look healthy while promises are still missed - the ' 'promise is set at order entry, not by the warehouse.', 'action': 'Fix the PROMISE first: quote commitment dates from the real ' 'schedule (order-to-ship p50 + a buffer), then work the missed ' 'list below.', 'sop': _sop( ['Open the Promise vs delivered section: compare the promise-OTIF trend ' 'to the internal on-time XmR - a wide gap = promises are quoted ' 'tighter than the process can ship.', 'Sample 10 missed orders: was the commitment_date realistic at entry ' '(vs the median order-to-ship time), or did the floor slip?'], ['Unrealistic promises -> set commitment dates at entry from the real ' 'p50 ship time + 1 day buffer (sales SOP, not a warehouse fix).', 'Floor slippage -> the expedite list + backlog SOPs above own it.'], ['Promise-OTIF trend back over 90% and converging with the internal ' 'on-time line.'], 'Weekly with the on-time review.')}) om = b['ontime'] if len(om) >= 7: last_bo = om[-1]['backorder_pct'] or 0 base_bo = sum((r['backorder_pct'] or 0) for r in om[-7:-1]) / 6 if last_bo - base_bo > 5: out.append({'sev': 'medium', 'title': f"Backorder rate jumped to {last_bo:.0f}% (was ~{base_bo:.0f}%)", 'detail': 'First-pass fill is slipping - an availability problem, not a floor problem.', 'action': 'Cross-check the Procurement buy list and the open-PO late chase list.', 'sop': _sop( ['List last month\'s backordered deliveries in Odoo (Delivery Orders ' 'with a backorder) and tally the missing SKUs.', 'For each: is it on the Procurement BUY list (never ordered) or on the ' 'open-PO LATE list (ordered, supplier late)?'], ['Never ordered: place the PO - the buy list has qty and supplier.', 'Supplier late: expedite via the late chase list; consider the ' 'alternative vendor shown on the Procurement page.', 'Chronic repeat SKUs: raise the reorder baseline (lead-time cover).'], ['Backorder share back within 5pts of its 6-month norm.'], 'Check at the biweekly procurement ritual.')}) sm = b['stage_monthly'] for stage in ('Pick', 'Pack', 'Ship', 'Putaway'): hist = [r for r in sm if r['stage'] == stage] if len(hist) >= 7: last = hist[-1] base = sorted(r['p90_days'] for r in hist[-7:-1])[3] # median of prior 6 if base > 0 and last['p90_days'] > 2 * base: out.append({'sev': 'medium', 'title': f"{stage} p90 cycle {last['p90_days']:.1f}d - over 2x its 6-month norm ({base:.1f}d)", 'detail': f"Month {last['month']}, n={last['n']}.", 'action': f'Bottleneck forming at {stage} - rebalance labor or clear holds there.', 'sop': _sop( [f'Open the {stage} queue in Odoo, sort oldest first: is the tail a ' 'few ancient transfers (holds) or is the WHOLE queue slower (capacity)?', 'Compare the stage trend chart: did volume also jump that month?'], ['Holds: clear or cancel the stuck transfers (supervisor sign-off).', 'Capacity: shift labor from the fastest stage for 1-2 weeks; the ' 'stage chart shows which stage has slack.'], [f'{stage} p90 back under 2x its 6-month norm next month.'], 'Monthly, at the stage-trend review.')}) for s in b['stages']: if s['median_days'] > 0 and s['p90_days'] / max(s['median_days'], 0.01) > 4 and s['p90_days'] > 1: out.append({'sev': 'info', 'title': f"{s['stage']}: a subset of transfers stalls (p90 {s['p90_days']:.1f}d vs median {s['median_days']:.1f}d)", 'detail': 'A long tail this wide is usually holds or stockouts, not capacity.', 'action': 'Audit the stalled tail - the expedite list below names the current ones.', 'sop': _sop( [f'The median {s["stage"]} transfer takes {s["median_days"]:.1f}d - the slowest ' '10% take 4x+ longer. Sample 10 of the slowest done transfers this month ' '(Odoo: sort by date_done minus create date).', 'Tag each: waiting on stock / waiting on a person or approval / lost paperwork.'], ['Stock waits: those belong to Procurement (see the stock-blocked flag).', 'Approval waits: name the approval and set a same-day rule for it.', 'Lost paperwork: cancel-and-recreate is usually cheaper than archaeology.'], ['p90/median ratio trending toward 2-3x within two months.'], 'Monthly sample of 10.')}) k = b['kpi'] if k['open_now'] and k['backlog_overdue'] / k['open_now'] > 0.10: out.append({'sev': 'high', 'title': f"{k['backlog_overdue']:,} of {k['open_now']:,} open transfers are past schedule " f"({k['backlog_overdue'] / k['open_now'] * 100:.0f}% - the healthy bar is under 10%)", 'detail': 'Old queues (see Backlog) almost always contain dead paperwork as well as real work.', 'action': 'Clear or cancel the ancient queues (Putaway/Move Back to Stock first); ' 'then work the expedite list daily until past-due is under 5%.', 'sop': _sop( ['Backlog table below, oldest queue first: anything older than ~60 days is ' 'almost certainly dead paperwork, not real work.', 'For each ancient queue, sample 10 transfers in Odoo: does the stock ' 'movement it describes still need to happen?'], ['Dead paperwork: CANCEL in Odoo (ops supervisor does it - this app is ' 'read-only by design). One focused afternoon usually clears years.', 'Real work: schedule a catch-up block per queue (Putaway first - it ' 'poisons on-hand accuracy, which poisons the buy list).', 'Then: the expedite list is the daily ritual until past-due < 5%.'], ['Past-due share under 10% within a month, 5% steady-state; oldest-days ' 'column under 30 everywhere.'], 'One-time cleanup, then daily expedite ritual + weekly backlog glance.')}) vol = b['weekly_vol'] if len(vol) >= 4: now4 = sum(r['shipped'] for r in vol[-4:]) ly4 = sum(r['shipped_ly'] for r in vol[-4:]) p90_rising = False ship_hist = [r for r in sm if r['stage'] == 'Ship'] if len(ship_hist) >= 7: p90_rising = ship_hist[-1]['p90_days'] > sorted(r['p90_days'] for r in ship_hist[-7:-1])[3] if ly4 and now4 / ly4 > 1.15 and p90_rising: out.append({'sev': 'medium', 'title': f'Volume running {now4 / ly4 * 100 - 100:.0f}% above last year AND ship times rising', 'detail': f'Last 4 weeks: {now4:,} shipped vs {ly4:,} same weeks last year.', 'action': 'Capacity signal - open the staffing conversation now (practice: hire ' '8-12 weeks before peak).', 'sop': _sop( ['Weekly-volume chart: how many weeks until the seasonal peak (last ' 'year\'s shape shows it)?', 'Estimate the gap: volume % above LY ~= extra hands needed % (floor ' 'labor scales near-linearly at this size).'], ['Under 12 weeks to peak: start temp hiring NOW (8-12 week practice; ' '12-14 in a tight market).', 'Over 12 weeks: schedule the decision, prep onboarding docs.', 'Short-term relief: shift labor to the bottleneck stage (stage trend ' 'chart names it).'], ['Ship p90 flat while volume grows; on-time XmR stays in band through ' 'the peak.'], 'Re-check weekly during the run-up.')}) if adj['n_cycle_count']: out.append({'sev': 'medium', 'title': f"{adj['n_cycle_count']} SKUs adjusted 3+ times in 90 days", 'detail': 'Repeat corrections = the system count for these is not trusted.', 'action': 'Work the cycle-count list below weekly until each SKU has two clean counts.', 'sop': _sop( ['Print the cycle-count worklist (top of the list first - it is ranked by ' 'repeat count then value).', 'For each SKU note WHERE the count went wrong last time (the SKU drawer ' 'shows its movement; the churn list says if it is a process error).'], ['Count 10-20 SKUs per week from the top of the list.', 'Counter and approver are DIFFERENT people (segregation - the practice ' 'that keeps counts honest).', 'Tag every correction with a root cause (miscount / damage / receiving ' 'error / UoM) - the tags are what fix the process.', 'A SKU exits the list after TWO consecutive clean counts.'], ['Worklist shrinking month over month; adjustment gross $ trending down ' 'on the monthly chart.'], 'Weekly, 10-20 SKUs; the list re-ranks itself as data updates.')}) if adj['n_churn']: out.append({'sev': 'medium', 'title': f"{adj['n_churn']} SKUs churn both ways (gross corrections > 2x net)", 'detail': 'Offsetting gains and losses = a counting/receiving/UoM process error, not shrink.', 'action': 'Check unit-of-measure, receiving and put-away practice for the churn list below.', 'sop': _sop( ['Take the top 5 churn SKUs: open each in the SKU drawer and note the pack ' 'size (e.g. "288-Piece per Pack" - candles are classic UoM churn).', 'Ask receiving: are these counted in PIECES or PACKS on arrival? Ask the ' 'floor: which unit does picking use?'], ['UoM mismatch found: fix the unit on the product / retrain the count ' 'habit - one setting usually kills hundreds of corrections.', 'Put-away confusion (same SKU in several spots): consolidate locations.', 'Receiving misses: count at the dock against the PO line, not from the ' 'packing slip.'], ['The SKU stops appearing in the churn list within two cycles; gross ' 'corrections fall while net stays flat.'], 'Top-5 review at the weekly count session.')}) real_exp = [e for e in b['expedite'] if not e['blocked_on_stock']] stock_exp = [e for e in b['expedite'] if e['blocked_on_stock']] if real_exp: out.append({'sev': 'medium', 'title': f'{len(real_exp)} shipments to expedite today', 'detail': 'Past schedule and older than the normal ship cycle, stock available.', 'action': 'Expedite list below, worst first.', 'sop': _sop( ['Expedite list below, top rows first (worst days-late).', 'For each: confirm in Odoo the stock is actually reserved (assigned ' 'state) and the order is not on customer hold.'], ['Pick-pack-ship the top of the list TODAY; anything a customer has ' 'called about jumps the queue.', 'On hold / no longer wanted: cancel the transfer so it stops polluting ' 'the backlog stats.'], ['Expedite list under 10 rows and oldest under 7 days late.'], 'Daily, first thing - it is a 15-minute ritual once the backlog is clean.')}) if stock_exp: out.append({'sev': 'info', 'title': f'{len(stock_exp)} late shipments are blocked on STOCK, not the floor', 'detail': 'Waiting-state transfers cannot be picked - expediting them is pointless.', 'action': 'Routed to Procurement: cross-check the buy list / open-PO ETAs for these.', 'sop': _sop( ['Filter the expedite list to "Blocked on stock = YES" - these SKUs are ' 'sold but not on the shelf.', 'Procurement page: is each SKU on an open PO (check the inbound-matched ' 'table for its ETA) or not ordered at all?'], ['On a PO: tell the customer the ETA; expedite the PO if the customer ' 'matters (late chase list has the supplier contact context).', 'Not ordered: it should be flashing on the buy list - order it or offer ' 'the customer a substitute.'], ['Stock-blocked count trending to near zero outside deep season.'], 'Reviewed at the biweekly procurement ritual.')}) sev_rank = {'high': 0, 'medium': 1, 'info': 2} out.sort(key=lambda f: sev_rank.get(f['sev'], 9)) return out def untrusted_counts(t=None): """The cross-module COUNT-TRUST set: default_code -> adjustment count for SKUs adjusted 3+ times in the last 90 days. For these, the system quantity is unverified until two clean cycle counts — any recommendation built on their on-hand (buy list cover, dead-stock DSI, season readiness) should say so. Deliberately lightweight (grouped reads over 90 days) so any page can join it without running the full adjustments().""" t = t or P.today() d90 = (t - dt.timedelta(days=90)).isoformat() per = {} for loc in ('location_id', 'location_dest_id'): for g in O.read_group('stock.move', [('state', '=', 'done'), ('date', '>=', d90), (loc + '.usage', '=', 'inventory')], ['id'], ['product_id'], lazy=False): if g.get('product_id'): pid = O.m2o_id(g['product_id']) per[pid] = per.get(pid, 0) + (g.get('__count') or 0) pids = [p for p, n in per.items() if n >= 3] codes = {} for i in range(0, len(pids), 5000): for p in O.search_read('product.product', [('id', 'in', pids[i:i + 5000]), ('active', 'in', [True, False])], ['default_code']): c = (p.get('default_code') or '').strip() if c: codes[c] = per[p['id']] return codes def program_health(adj): """Counting-program health: gross corrections (12m, layer-valued) as a share of inventory value. Inventory value here is qty x CURRENT standard cost — an approximation used only for this RATIO (the GL-tied book value lives in Overstock). WERC practice band: <=0.5% target, ~1% acceptable, beyond that = formal program review.""" prods = O.search_read('product.product', [('default_code', '!=', False), ('active', 'in', [True, False])], ['default_code', 'name', 'qty_available', 'standard_price']) valued = [{'code': (p.get('default_code') or '').strip(), 'product': p.get('name') or '', 'qty': p.get('qty_available') or 0.0, 'std_cost': p.get('standard_price') or 0.0, 'value': (p.get('qty_available') or 0.0) * (p.get('standard_price') or 0.0)} for p in prods if (p.get('qty_available') or 0.0) > 0] inv_val = sum(v['value'] for v in valued) gross = adj['total_losses'] + adj['total_gains'] # denominator composition, so the ratio is verifiable in-app down to SKUs (owner rule); # the numerator's rows are the adjustment worklists/Pareto on the same page valued.sort(key=lambda v: -v['value']) return {'inv_value_approx': inv_val, 'gross_12m': gross, 'ratio_pct': (gross / inv_val * 100) if inv_val else None, 'n_skus_valued': len(valued), 'value_rows': valued} def freight(t=None): """Carrier mix + delivery-charge economics: who we ship with, what delivery revenue we charge, and who rides the free-delivery carrier (the coverage question: free delivery for a customer we then pay Uber/local fees to serve).""" t = t or P.today() y1 = (t - dt.timedelta(days=365)).isoformat() mix = [] for g in O.read_group('stock.picking', [('state', '=', 'done'), ('date_done', '>=', y1), ('carrier_id', '!=', False)], ['id'], ['carrier_id'], lazy=False): if g.get('carrier_id'): mix.append({'carrier': O.m2o_name(g['carrier_id']), 'pickings': g.get('__count') or 0}) mix.sort(key=lambda x: -x['pickings']) tot_pk = sum(m['pickings'] for m in mix) or 1 for m in mix: m['share_pct'] = m['pickings'] / tot_pk * 100 ex = O.excluded_partner_ids() dom = [('is_delivery', '=', True), ('order_id.state', 'in', ['sale', 'done']), ('order_id.date_order', '>=', y1)] if ex: dom.append(('order_partner_id', 'not in', list(ex))) lines = O.search_read('sale.order.line', dom, ['price_subtotal', 'order_partner_id', 'create_date']) monthly = {} for l in lines: m = str(l.get('create_date') or '')[:7] e = monthly.setdefault(m, {'month': m, 'revenue': 0.0, 'lines': 0}) e['revenue'] += l.get('price_subtotal') or 0.0 e['lines'] += 1 rev_total = sum(l.get('price_subtotal') or 0.0 for l in lines) free = [] for g in O.read_group('stock.picking', [('state', '=', 'done'), ('date_done', '>=', y1), ('carrier_id.name', 'ilike', 'free')], ['id'], ['partner_id'], lazy=False): if g.get('partner_id'): free.append({'pid': O.m2o_id(g['partner_id']), 'customer': O.m2o_name(g['partner_id']), 'free_deliveries': g.get('__count') or 0}) free.sort(key=lambda x: -x['free_deliveries']) return {'mix': mix, 'monthly': sorted(monthly.values(), key=lambda x: x['month']), 'revenue_12m': rev_total, 'n_charge_lines': len(lines), 'free_top': free[:20], 'n_free_customers': len(free), 'n_free_deliveries': sum(f['free_deliveries'] for f in free), '_rowsum_rev': rev_total, '_dom_is_delivery': dom} def promise_otif(t=None): """Delivered vs PROMISED — the CUSTOMER's on-time, distinct from the internal schedule the on-time XmR measures. 94% of confirmed orders carry a commitment_date; an order is on-promise when its LAST completed outgoing shipment finished by that date +1 day (the same grace the internal measure uses). Measured set = promised orders that HAVE shipped.""" t = t or P.today() d12 = (t - dt.timedelta(days=365)).isoformat() orders = O.search_read('sale.order', [('state', '=', 'sale'), ('date_order', '>=', d12), ('commitment_date', '!=', False)], ['name', 'partner_id', 'commitment_date', 'date_order']) picks = O.search_read('stock.picking', [('picking_type_code', '=', 'outgoing'), ('state', '=', 'done'), ('date_done', '>=', d12)], ['origin', 'date_done']) last_done = {} for p_ in picks: o_, d = p_.get('origin'), str(p_.get('date_done') or '')[:10] if o_ and d and (o_ not in last_done or d > last_done[o_]): last_done[o_] = d rows = [] for so in orders: dd = last_done.get(so['name']) if not dd: continue promised = str(so['commitment_date'])[:10] rows.append({'order': so['name'], 'pid': O.m2o_id(so.get('partner_id')), 'customer': O.m2o_name(so.get('partner_id')), 'promised': promised, 'delivered': dd, 'days_late': (dt.date.fromisoformat(dd) - dt.date.fromisoformat(promised)).days}) n = len(rows) ontime = sum(1 for r in rows if r['days_late'] <= 1) lates = sorted(r['days_late'] for r in rows) per_m = {} for r in rows: e = per_m.setdefault(r['promised'][:7], [0, 0]) e[0] += 1 e[1] += 1 if r['days_late'] <= 1 else 0 return {'n_measured': n, 'n_promised': len(orders), 'n_unshipped': len(orders) - n, 'n_on_promise': ontime, 'on_promise_pct': (ontime / n * 100) if n else None, 'late_p90': lates[int(len(lates) * .9)] if lates else None, 'missed': sorted((r for r in rows if r['days_late'] > 1), key=lambda r: -r['days_late']), # trend only for months with a real sample — orders PROMISED before the pull window # can ship inside it and leave a 1-2 order month that drags the line to 0/100% 'trend': [{'month': k, 'measured': v[0], 'on_promise_pct': v[1] / v[0] * 100} for k, v in sorted(per_m.items()) if v[0] >= 10], '_d12': d12} def summary(t=None): t = t or P.today() b = build(t) b['adjustments'] = adjustments(t) b['touches'] = touches(t) b['freight'] = freight(t) b['program_health'] = program_health(b['adjustments']) b['promise'] = promise_otif(t) b['flags'] = flags(b, b['adjustments'], b['program_health']) b['pulled_at'] = dt.datetime.now().strftime('%Y-%m-%d %H:%M') return b def validate(t=None, team_id=None, pre=None, adj=None, fr=None): """pre/adj/fr: pass the bundle's precomputed build()/adjustments()/freight() to avoid re-running the heavy pulls (the ar.py lesson); validate.py calls bare and computes fresh.""" t = t or P.today() y1 = (t - dt.timedelta(days=365)).isoformat() o = O.get_odoo() b = pre or build(t) checks = [] n_out_direct = o.search_count('stock.picking', [('state', '=', 'done'), ('date_done', '>=', y1), ('picking_type_code', '=', 'outgoing')]) checks.append({'check': 'Warehouse: row-built outgoing done == search_count (12m)', 'a': b['_n_out_rowpath'], 'b': n_out_direct, 'gap': b['_n_out_rowpath'] - n_out_direct, 'ok': abs(b['_n_out_rowpath'] - n_out_direct) <= max(2, n_out_direct * 0.001)}) pr = b.get('promise') or promise_otif(t) if pr: n_srv = o.search_count('sale.order', [('state', '=', 'sale'), ('date_order', '>=', pr['_d12']), ('commitment_date', '!=', False)]) checks.append({'check': 'Promise-OTIF: promised-order universe == server count', 'a': pr['n_promised'], 'b': n_srv, 'gap': pr['n_promised'] - n_srv, 'ok': abs(pr['n_promised'] - n_srv) <= max(2, n_srv * 0.001)}) part = pr['n_on_promise'] + len(pr['missed']) checks.append({'check': 'Promise-OTIF: on-promise + missed == measured (partition)', 'a': part, 'b': pr['n_measured'], 'gap': part - pr['n_measured'], 'ok': part == pr['n_measured']}) mm = sum(sum(v for k, v in m.items() if k != 'month') for m in b['monthly']) n_all = o.search_count('stock.picking', [('state', '=', 'done'), ('date_done', '>=', y1)]) checks.append({'check': 'Warehouse: Σ(monthly throughput) == all done pickings (12m)', 'a': mm, 'b': n_all, 'gap': mm - n_all, 'ok': abs(mm - n_all) <= max(2, n_all * 0.001)}) # Clean-order decomposition is exhaustive: the four categories partition every shipped picking. clean_sum = sum(sum(v for kk, v in m.items() if kk != 'month') for m in b['clean']) ship_sum = sum(r['shipped'] for r in b['ontime']) checks.append({'check': 'Warehouse: Σ(clean-order categories) == Σ shipped (partition test)', 'a': clean_sum, 'b': ship_sum, 'gap': clean_sum - ship_sum, 'ok': clean_sum == ship_sum}) adj = adj or adjustments(t) agg_loss_qty = O.sum_field('stock.move', [('state', '=', 'done'), ('date', '>=', adj['_y1']), ('location_dest_id.usage', '=', 'inventory')], 'product_uom_qty') checks.append({'check': 'Warehouse: adjustment loss qty row-sum == read_group Σ (live-drift tol)', 'a': round(adj['_rowsum_loss_qty'], 2), 'b': round(agg_loss_qty, 2), 'gap': round(adj['_rowsum_loss_qty'] - agg_loss_qty, 2), 'ok': abs(adj['_rowsum_loss_qty'] - agg_loss_qty) <= max(1.0, abs(agg_loss_qty) * 0.001)}) # Valuation layers: our per-move Σ|value| vs the signed read_group sums over the same layer # domains (gains positive, losses negative → |gains| + |losses| should tie). lay_g = O.sum_field('stock.valuation.layer', [('stock_move_id.state', '=', 'done'), ('stock_move_id.date', '>=', adj['_y1']), ('stock_move_id.location_id.usage', '=', 'inventory')], 'value') lay_l = O.sum_field('stock.valuation.layer', [('stock_move_id.state', '=', 'done'), ('stock_move_id.date', '>=', adj['_y1']), ('stock_move_id.location_dest_id.usage', '=', 'inventory')], 'value') lay_agg = abs(lay_g) + abs(lay_l) checks.append({'check': 'Warehouse: Σ|layer value| row-path == |read_group gains|+|losses| (tol)', 'a': round(adj['_rowsum_layer_abs'], 2), 'b': round(lay_agg, 2), 'gap': round(adj['_rowsum_layer_abs'] - lay_agg, 2), 'ok': abs(adj['_rowsum_layer_abs'] - lay_agg) <= max(5.0, lay_agg * 0.005)}) # Freight: delivery-charge revenue row-sum vs an independent read_group sum, same domain. fr = fr or freight(t) agg_rev = O.sum_field('sale.order.line', fr['_dom_is_delivery'], 'price_subtotal') checks.append({'check': 'Warehouse: Σ delivery-charge revenue row-sum == read_group Σ (tol)', 'a': round(fr['_rowsum_rev'], 2), 'b': round(agg_rev, 2), 'gap': round(fr['_rowsum_rev'] - agg_rev, 2), 'ok': abs(fr['_rowsum_rev'] - agg_rev) <= max(1.0, abs(agg_rev) * 0.001)}) return checks