File size: 8,724 Bytes
c14ceee | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 | """Backorders β what we owe customers, aged (the mirror image of AR aging).
Every confirmed-but-undelivered sale line, aged against the order's promise date
(commitment_date, else the earliest open outgoing picking's scheduled date), valued at the
line's discounted unit price, joined to supply (on-hand + earliest inbound PO ETA) so each
row carries ONE verb action: Ship (stock is on hand) / Expedite the PO (inbound exists) /
Call the customer (no supply in sight). Line-level fill canon: β₯95% industry bar.
Scope: wholesale teams (5/6), GIFTWARE/Amazon excluded β a backorder board is about promises
to dealers. BU-filterable.
"""
import datetime as dt
import core.odoo as O
import core.periods as P
import modules.procurement as proc
BUCKETS = ['not due', '0-7', '8-21', '22-45', '45+', 'no promise']
_OPEN_PICK_DOMAIN = [('state', 'in', ['confirmed', 'assigned', 'waiting']),
('picking_type_id.code', '=', 'outgoing')]
def _chunk(ids, n=2000):
ids = list(ids)
for i in range(0, len(ids), n):
yield ids[i:i + n]
def _open_pickings():
"""Open outgoing pickings β {origin(SO name): earliest scheduled date}."""
picks = O.search_read('stock.picking', list(_OPEN_PICK_DOMAIN),
['origin', 'scheduled_date'])
sched = {}
for p in picks:
o = (p.get('origin') or '').strip()
if not o:
continue
d = str(p.get('scheduled_date') or '')[:10]
if d and (o not in sched or d < sched[o]):
sched[o] = d
return picks, sched
def board(team_id=None, t=None):
"""The full open-line board + bucket rollup. Returns dict(rows, buckets, orders, coverage)."""
t = t or P.today()
today = t
picks, sched = _open_pickings()
# the sale orders behind those pickings, in wholesale scope
orders = []
names = list(sched)
ex = O.excluded_partner_ids()
for ch in _chunk(names, 500):
dom = [('name', 'in', ch), ('state', 'in', ['sale', 'done'])]
dom.append(('team_id', '=', team_id) if team_id is not None
else ('team_id', 'in', O.TEAM_IDS))
if ex:
dom.append(('partner_id', 'not in', list(ex)))
orders += O.search_read('sale.order', dom,
['name', 'partner_id', 'team_id', 'date_order',
'commitment_date'])
by_oid = {o['id']: o for o in orders}
# their lines, open qty computed locally (a domain cannot compare two fields)
lines = []
for ch in _chunk(list(by_oid), 500):
lines += O.search_read('sale.order.line',
[('order_id', 'in', ch), ('display_type', '=', False),
('product_id', '!=', False)],
['order_id', 'order_partner_id', 'product_id',
'product_uom_qty', 'qty_delivered', 'price_unit',
'price_subtotal', 'name'])
open_lines = []
for r in lines:
openq = (r.get('product_uom_qty') or 0.0) - (r.get('qty_delivered') or 0.0)
if openq <= 1e-3:
continue
qty = r.get('product_uom_qty') or 0.0
unit = (r.get('price_subtotal') or 0.0) / qty if qty else (r.get('price_unit') or 0.0)
r['open_qty'] = openq
r['open_value'] = openq * unit
open_lines.append(r)
# supply joins: on-hand + earliest inbound ETA per product
pids = list({O.m2o_id(r['product_id']) for r in open_lines})
onhand, codes = {}, {}
for ch in _chunk(pids):
for p in O.search_read('product.product',
[('id', 'in', ch), ('active', 'in', [True, False])],
['qty_available', 'default_code']):
onhand[p['id']] = p.get('qty_available') or 0.0
codes[p['id']] = (p.get('default_code') or '').strip()
_all_pol, pol = proc._open_po_lines()
inbound = {}
for l in pol:
pid = O.m2o_id(l.get('product_id'))
if not pid:
continue
cur = inbound.setdefault(pid, {'qty': 0.0, 'eta': None})
cur['qty'] += l.get('open_qty') or 0.0
eta = l.get('expected') or ''
if eta and (cur['eta'] is None or eta < cur['eta']):
cur['eta'] = eta
rows = []
n_promised = 0
for r in open_lines:
oid = O.m2o_id(r['order_id'])
o = by_oid.get(oid) or {}
promise = str(o.get('commitment_date') or '')[:10] or sched.get(o.get('name', ''), '')
if str(o.get('commitment_date') or '')[:10]:
n_promised += 1
if promise:
days = (today - dt.date.fromisoformat(promise)).days
bucket = ('not due' if days < 0 else '0-7' if days <= 7
else '8-21' if days <= 21 else '22-45' if days <= 45 else '45+')
else:
days, bucket = None, 'no promise'
pid_prod = O.m2o_id(r['product_id'])
oh = onhand.get(pid_prod, 0.0)
inb = inbound.get(pid_prod)
if oh >= r['open_qty']:
action = 'Ship β stock on hand'
elif inb and inb.get('eta'):
action = f"Expedite PO β ETA {inb['eta']}"
else:
action = 'Call customer β no supply inbound'
rows.append({
'pid': O.m2o_id(r['order_partner_id']),
'customer': O.m2o_name(r['order_partner_id']),
'order': o.get('name', ''),
'bu': O.TEAM_NAMES.get(O.m2o_id(o.get('team_id')), '?'),
'sku': codes.get(pid_prod, ''), 'product': O.m2o_name(r['product_id']),
'open_qty': r['open_qty'], 'open_value': r['open_value'],
'promise': promise or 'β', 'days_past': days if days is not None else '',
'bucket': bucket, 'on_hand': oh,
'inbound_eta': (inb or {}).get('eta') or '',
'action': action,
})
rows.sort(key=lambda x: -x['open_value'])
bucket_rollup = []
for bu in sorted({r['bu'] for r in rows}):
for b in BUCKETS:
v = sum(r['open_value'] for r in rows if r['bu'] == bu and r['bucket'] == b)
if v:
bucket_rollup.append({'bu': bu, 'bucket': b, 'value': v})
return {
'rows': rows,
'buckets': bucket_rollup,
'n_orders': len({r['order'] for r in rows}),
'open_value': sum(r['open_value'] for r in rows),
'past_value': sum(r['open_value'] for r in rows
if r['bucket'] in ('0-7', '8-21', '22-45', '45+')),
'promise_coverage': (n_promised / len(open_lines) * 100.0) if open_lines else 0.0,
'n_pickings': len(picks),
}
def validate(t=None, team_id=None):
"""(1) the picking pull is complete (search_count ties the row pull β truncation guard);
(2) the local open-qty arithmetic ties Odoo's own aggregates: for the pulled order set,
Ξ£(product_uom_qty) β Ξ£(qty_delivered) via server-side read_group == Ξ£ of our per-row
open_qty (same universe, independent arithmetic path)."""
b = board(team_id, t)
n_count = O.get_odoo().search_count('stock.picking', list(_OPEN_PICK_DOMAIN))
checks = [{'check': 'open outgoing pickings β pull complete',
'a': b['n_pickings'], 'b': n_count,
'gap': b['n_pickings'] - n_count, 'ok': b['n_pickings'] == n_count}]
oids = sorted({r['order'] for r in b['rows']})
if oids:
dom = [('order_id.name', 'in', oids[:500]), ('display_type', '=', False),
('product_id', '!=', False)]
try:
g = O.read_group('sale.order.line', dom,
['product_uom_qty:sum', 'qty_delivered:sum'], [], lazy=False)
srv_open = ((g[0].get('product_uom_qty') or 0.0)
- (g[0].get('qty_delivered') or 0.0)) if g else 0.0
# NET open recomputed row-level (includes over-shipped negatives, which the board
# excludes) so both sides measure the same universe
rl = O.search_read('sale.order.line', dom, ['product_uom_qty', 'qty_delivered'])
ours = sum((l.get('product_uom_qty') or 0.0) - (l.get('qty_delivered') or 0.0)
for l in rl)
checks.append({'check': 'open qty β server aggregate vs row arithmetic',
'a': round(ours, 2), 'b': round(srv_open, 2),
'gap': round(ours - srv_open, 2),
'ok': abs(ours - srv_open) < 1.0})
except Exception as e:
checks.append({'check': 'open qty aggregate (read_group)', 'a': 'β',
'b': str(e)[:60], 'gap': '', 'ok': False})
return checks
|