Spaces:
Paused
Paused
Download platform/modules/warehouse.py from aios-loopable/cfo-os: direct link, hf CLI and curl.
- Browser
- Download file 54.6 kB
-
https://huggingface.co/spaces/aios-loopable/cfo-os/resolve/main/platform/modules/warehouse.py
- Command line
-
hf download hf://spaces/aios-loopable/cfo-os/platform/modules/warehouse.py
-
curl -L -o warehouse.py https://huggingface.co/spaces/aios-loopable/cfo-os/resolve/main/platform/modules/warehouse.py
54.6 kB
| """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 | |