Implementation Guide: Auto-trigger parts reorder when inventory drops below min/max thresholds

# Python script for ABC analysis and min/max calculation
# Save as calculate_thresholds.py

import pandas as pd
import numpy as np

# Load exported parts usage data
usage = pd.read_csv('parts_usage_12mo.csv')  # Columns: PartNumber, Description, QtySold, UnitCost, UnitRetail
inventory = pd.read_csv('current_inventory.csv')  # Columns: PartNumber, QtyOnHand, BinLocation, PrimarySupplier, LeadTimeDays

# Calculate annual revenue per SKU
usage['AnnualRevenue'] = usage['QtySold'] * usage['UnitRetail']
usage['AnnualCost'] = usage['QtySold'] * usage['UnitCost']

# Sort by annual revenue descending
usage = usage.sort_values('AnnualRevenue', ascending=False)

# ABC classification
usage['CumulativeRevenue'] = usage['AnnualRevenue'].cumsum()
total_revenue = usage['AnnualRevenue'].sum()
usage['CumulativePct'] = usage['CumulativeRevenue'] / total_revenue * 100

def classify_abc(pct):
    if pct <= 80:
        return 'A'
    elif pct <= 95:
        return 'B'
    else:
        return 'C'

usage['ABC_Class'] = usage['CumulativePct'].apply(classify_abc)

# Merge with inventory data
merged = usage.merge(inventory, on='PartNumber', how='left')

# Calculate daily usage (based on 300 working days/year for a shop)
WORKING_DAYS = 300
merged['AvgDailyUsage'] = merged['QtySold'] / WORKING_DAYS

# Safety factor by ABC class
safety_factors = {'A': 0.10, 'B': 0.15, 'C': 0.25}
merged['SafetyFactor'] = merged['ABC_Class'].map(safety_factors)

# Min/Max calculation
merged['SafetyStock'] = np.ceil(merged['AvgDailyUsage'] * merged['LeadTimeDays'] * merged['SafetyFactor'])
merged['MinStock'] = np.ceil((merged['AvgDailyUsage'] * merged['LeadTimeDays']) + merged['SafetyStock'])
merged['EOQ'] = np.ceil(merged['QtySold'] / 12)  # Simplified: 1 month supply
merged['MaxStock'] = merged['MinStock'] + merged['EOQ']

# Ensure minimums of 1 for active parts
merged.loc[merged['MinStock'] < 1, 'MinStock'] = 1
merged.loc[merged['MaxStock'] < 2, 'MaxStock'] = 2

# Export for DMS import
output = merged[['PartNumber','Description','ABC_Class','QtyOnHand','BinLocation',
                  'PrimarySupplier','LeadTimeDays','AvgDailyUsage','SafetyStock',
                  'MinStock','MaxStock']]
output.to_csv('min_max_thresholds.csv', index=False)

print(f'Total active SKUs: {len(output)}')
print(f'A-items: {len(output[output.ABC_Class=="A"])}')
print(f'B-items: {len(output[output.ABC_Class=="B"])}')
print(f'C-items: {len(output[output.ABC_Class=="C"])}')
print('\nThreshold file exported to min_max_thresholds.csv')

docker pull n8nio/n8n
docker run -d --name n8n -p 5678:5678 -v n8n_data:/home/node/.n8n n8nio/n8n

POST /webhook/inventory-alert
Content-Type: application/json

{
  "event": "inventory_below_minimum",
  "part_number": "15400-PLM-A02",
  "description": "Oil Filter - Honda",
  "current_qty": 2,
  "min_threshold": 5,
  "max_threshold": 15,
  "order_qty": 13,
  "preferred_supplier": "NAPA",
  "supplier_account": "ACCT-12345",
  "timestamp": "2025-01-15T14:30:00Z"
}

#!/usr/bin/env python3
"""
Min/Max Threshold Calculator for Automotive Parts Inventory
Usage: python3 threshold_calculator.py --usage parts_usage_12mo.csv --inventory current_inventory.csv --output thresholds.csv

Input CSV schemas:
  parts_usage_12mo.csv: PartNumber, Description, QtySold, UnitCost, UnitRetail, Category
  current_inventory.csv: PartNumber, QtyOnHand, BinLocation, PrimarySupplier, LeadTimeDays, PackSize

Output CSV schema:
  PartNumber, Description, ABC_Class, QtyOnHand, BinLocation, PrimarySupplier,
  LeadTimeDays, AvgDailyUsage, SafetyStock, MinStock, MaxStock, OrderQty, EstAnnualCost
"""
import argparse
import pandas as pd
import numpy as np
import sys
from datetime import datetime

def load_and_validate(usage_file, inventory_file):
    usage = pd.read_csv(usage_file)
    inventory = pd.read_csv(inventory_file)
    
    required_usage_cols = ['PartNumber', 'QtySold', 'UnitCost', 'UnitRetail']
    required_inv_cols = ['PartNumber', 'QtyOnHand', 'PrimarySupplier', 'LeadTimeDays']
    
    for col in required_usage_cols:
        if col not in usage.columns:
            raise ValueError(f'Missing required column in usage file: {col}')
    for col in required_inv_cols:
        if col not in inventory.columns:
            raise ValueError(f'Missing required column in inventory file: {col}')
    
    return usage, inventory

def classify_abc(usage_df):
    usage_df = usage_df.copy()
    usage_df['AnnualRevenue'] = usage_df['QtySold'] * usage_df['UnitRetail']
    usage_df = usage_df.sort_values('AnnualRevenue', ascending=False).reset_index(drop=True)
    usage_df['CumulativeRevenue'] = usage_df['AnnualRevenue'].cumsum()
    total = usage_df['AnnualRevenue'].sum()
    if total == 0:
        usage_df['ABC_Class'] = 'C'
        return usage_df
    usage_df['CumulativePct'] = (usage_df['CumulativeRevenue'] / total) * 100
    usage_df['ABC_Class'] = usage_df['CumulativePct'].apply(
        lambda p: 'A' if p <= 80 else ('B' if p <= 95 else 'C')
    )
    return usage_df

def calculate_thresholds(merged_df, working_days=300):
    df = merged_df.copy()
    safety_factors = {'A': 0.10, 'B': 0.15, 'C': 0.25}
    df['SafetyFactor'] = df['ABC_Class'].map(safety_factors)
    df['AvgDailyUsage'] = df['QtySold'] / working_days
    df['LeadTimeDays'] = df['LeadTimeDays'].fillna(5)  # default 5 days
    df['SafetyStock'] = np.ceil(df['AvgDailyUsage'] * df['LeadTimeDays'] * df['SafetyFactor'])
    df['MinStock'] = np.ceil((df['AvgDailyUsage'] * df['LeadTimeDays']) + df['SafetyStock'])
    df['EOQ'] = np.ceil(df['QtySold'] / 12)  # ~1 month supply
    
    # Round up to pack size if available
    if 'PackSize' in df.columns:
        df['PackSize'] = df['PackSize'].fillna(1).astype(int)
        df['EOQ'] = np.ceil(df['EOQ'] / df['PackSize']) * df['PackSize']
    
    df['MaxStock'] = df['MinStock'] + df['EOQ']
    
    # Enforce minimums for active parts
    active = df['QtySold'] > 0
    df.loc[active & (df['MinStock'] < 1), 'MinStock'] = 1
    df.loc[active & (df['MaxStock'] < 2), 'MaxStock'] = 2
    
    # Zero out inactive parts
    inactive = df['QtySold'] == 0
    df.loc[inactive, 'MinStock'] = 0
    df.loc[inactive, 'MaxStock'] = 0
    
    df['OrderQty'] = df['MaxStock'] - df['MinStock']
    df['EstAnnualCost'] = df['QtySold'] * df['UnitCost']
    
    return df

def generate_report(df, output_file):
    output_cols = ['PartNumber', 'Description', 'ABC_Class', 'QtyOnHand', 'BinLocation',
                   'PrimarySupplier', 'LeadTimeDays', 'AvgDailyUsage', 'SafetyStock',
                   'MinStock', 'MaxStock', 'OrderQty', 'EstAnnualCost']
    available_cols = [c for c in output_cols if c in df.columns]
    output = df[available_cols].copy()
    output['MinStock'] = output['MinStock'].astype(int)
    output['MaxStock'] = output['MaxStock'].astype(int)
    output['OrderQty'] = output['OrderQty'].astype(int)
    output.to_csv(output_file, index=False)
    
    print(f'\n=== Threshold Calculation Report ===')
    print(f'Generated: {datetime.now().strftime("%Y-%m-%d %H:%M")}')
    print(f'Total SKUs processed: {len(output)}')
    print(f'  A-items: {len(output[output.ABC_Class=="A"])} ({len(output[output.ABC_Class=="A"])/len(output)*100:.1f}%)')
    print(f'  B-items: {len(output[output.ABC_Class=="B"])} ({len(output[output.ABC_Class=="B"])/len(output)*100:.1f}%)')
    print(f'  C-items: {len(output[output.ABC_Class=="C"])} ({len(output[output.ABC_Class=="C"])/len(output)*100:.1f}%)')
    active = output[output.MaxStock > 0]
    print(f'  Active (with thresholds): {len(active)}')
    inactive = output[output.MaxStock == 0]
    print(f'  Inactive (zero threshold): {len(inactive)} — review for removal')
    print(f'\nEstimated annual parts spend: ${output["EstAnnualCost"].sum():,.2f}')
    below_min = output[output.QtyOnHand < output.MinStock]
    print(f'Parts currently below minimum: {len(below_min)} — immediate POs needed')
    print(f'\nOutput written to: {output_file}')

def main():
    parser = argparse.ArgumentParser(description='Calculate min/max inventory thresholds')
    parser.add_argument('--usage', required=True, help='CSV of 12-month parts usage data')
    parser.add_argument('--inventory', required=True, help='CSV of current inventory data')
    parser.add_argument('--output', default='thresholds.csv', help='Output CSV file')
    parser.add_argument('--working-days', type=int, default=300, help='Working days per year')
    args = parser.parse_args()
    
    usage, inventory = load_and_validate(args.usage, args.inventory)
    usage = classify_abc(usage)
    merged = usage.merge(inventory, on='PartNumber', how='left')
    result = calculate_thresholds(merged, args.working_days)
    generate_report(result, args.output)

if __name__ == '__main__':
    main()

*/15 7-18 * * 1-6

GET https://api.{{SMS_PLATFORM}}.com/v1/inventory/parts
Authorization: Bearer {{SMS_API_KEY}}
Content-Type: application/json

Query Parameters:
  include_thresholds: true
  status: active

const items = $input.all();
const belowMin = items.filter(item => {
  const data = item.json;
  return data.qty_on_hand < data.min_threshold && data.min_threshold > 0;
});

return belowMin.map(item => {
  const data = item.json;
  return {
    json: {
      part_number: data.part_number,
      description: data.description,
      qty_on_hand: data.qty_on_hand,
      min_threshold: data.min_threshold,
      max_threshold: data.max_threshold,
      order_qty: data.max_threshold - data.qty_on_hand,
      preferred_supplier: data.primary_supplier,
      unit_cost: data.unit_cost,
      order_total: (data.max_threshold - data.qty_on_hand) * data.unit_cost
    }
  };
});

POST https://api.{{SMS_PLATFORM}}.com/v1/purchase-orders
Authorization: Bearer {{SMS_API_KEY}}
Content-Type: application/json

{
  "supplier": "{{$json.preferred_supplier}}",
  "status": "pending_approval",
  "auto_generated": true,
  "line_items": [
    {
      "part_number": "{{$json.part_number}}",
      "description": "{{$json.description}}",
      "quantity": {{$json.order_qty}},
      "unit_cost": {{$json.unit_cost}}
    }
  ],
  "notes": "Auto-generated: stock fell below minimum threshold"
}

<h2>Automatic Parts Reorder Notification</h2>
<p>The following parts have fallen below their minimum stock threshold.
Purchase orders have been created and are pending your approval.</p>
<table border='1' cellpadding='5'>
  <tr><th>Part #</th><th>Description</th><th>On Hand</th><th>Min</th><th>Order Qty</th><th>Supplier</th><th>Est. Cost</th></tr>
  {{#each items}}
  <tr><td>{{part_number}}</td><td>{{description}}</td><td>{{qty_on_hand}}</td>
      <td>{{min_threshold}}</td><td>{{order_qty}}</td><td>{{preferred_supplier}}</td>
      <td>${{order_total}}</td></tr>
  {{/each}}
</table>
<p><a href='https://{{SMS_PLATFORM}}.com/purchase-orders'>Review and Approve POs</a></p>

# Run quarterly to recommend min/max threshold adjustments based on seasonal
# demand patterns

#!/usr/bin/env python3
"""
Seasonal Threshold Adjuster
Run quarterly to recommend min/max adjustments based on seasonal demand patterns.
Usage: python3 seasonal_adjuster.py --usage monthly_usage.csv --current-thresholds thresholds.csv --quarter Q1 --output seasonal_adjustments.csv

Input: monthly_usage.csv with columns: PartNumber, Description, Month (1-12), QtySold
       thresholds.csv: current min/max thresholds from threshold_calculator.py
"""
import argparse
import pandas as pd
import numpy as np

QUARTER_MONTHS = {
    'Q1': [1, 2, 3],
    'Q2': [4, 5, 6],
    'Q3': [7, 8, 9],
    'Q4': [10, 11, 12]
}

def calculate_seasonal_index(monthly_usage, target_quarter):
    """Calculate seasonal index: ratio of quarter's avg monthly demand to annual avg."""
    df = monthly_usage.copy()
    
    # Annual average monthly demand per part
    annual_avg = df.groupby('PartNumber')['QtySold'].mean().reset_index()
    annual_avg.columns = ['PartNumber', 'AvgMonthlyDemand']
    
    # Target quarter average monthly demand per part
    target_months = QUARTER_MONTHS[target_quarter]
    quarter_data = df[df['Month'].isin(target_months)]
    quarter_avg = quarter_data.groupby('PartNumber')['QtySold'].mean().reset_index()
    quarter_avg.columns = ['PartNumber', 'QuarterAvgDemand']
    
    merged = annual_avg.merge(quarter_avg, on='PartNumber', how='left')
    merged['QuarterAvgDemand'] = merged['QuarterAvgDemand'].fillna(0)
    merged['SeasonalIndex'] = np.where(
        merged['AvgMonthlyDemand'] > 0,
        merged['QuarterAvgDemand'] / merged['AvgMonthlyDemand'],
        1.0
    )
    # Cap extreme values
    merged['SeasonalIndex'] = merged['SeasonalIndex'].clip(0.5, 3.0)
    
    return merged

def adjust_thresholds(current_thresholds, seasonal_data):
    """Apply seasonal index to current thresholds."""
    df = current_thresholds.merge(seasonal_data[['PartNumber', 'SeasonalIndex']], on='PartNumber', how='left')
    df['SeasonalIndex'] = df['SeasonalIndex'].fillna(1.0)
    
    df['AdjustedMin'] = np.ceil(df['MinStock'] * df['SeasonalIndex']).astype(int)
    df['AdjustedMax'] = np.ceil(df['MaxStock'] * df['SeasonalIndex']).astype(int)
    
    # Enforce minimums
    active = df['MinStock'] > 0
    df.loc[active & (df['AdjustedMin'] < 1), 'AdjustedMin'] = 1
    df.loc[active & (df['AdjustedMax'] < 2), 'AdjustedMax'] = 2
    
    df['MinChange'] = df['AdjustedMin'] - df['MinStock']
    df['MaxChange'] = df['AdjustedMax'] - df['MaxStock']
    
    # Only flag parts with meaningful changes (>= 1 unit change)
    significant = (df['MinChange'].abs() >= 1) | (df['MaxChange'].abs() >= 1)
    
    return df[significant].sort_values('SeasonalIndex', ascending=False)

def main():
    parser = argparse.ArgumentParser()
    parser.add_argument('--usage', required=True, help='Monthly usage CSV')
    parser.add_argument('--current-thresholds', required=True, help='Current thresholds CSV')
    parser.add_argument('--quarter', required=True, choices=['Q1','Q2','Q3','Q4'])
    parser.add_argument('--output', default='seasonal_adjustments.csv')
    args = parser.parse_args()
    
    monthly_usage = pd.read_csv(args.usage)
    current = pd.read_csv(args.current_thresholds)
    
    seasonal = calculate_seasonal_index(monthly_usage, args.quarter)
    adjustments = adjust_thresholds(current, seasonal)
    
    output_cols = ['PartNumber', 'Description', 'ABC_Class', 'MinStock', 'AdjustedMin',
                   'MinChange', 'MaxStock', 'AdjustedMax', 'MaxChange', 'SeasonalIndex']
    available = [c for c in output_cols if c in adjustments.columns]
    adjustments[available].to_csv(args.output, index=False)
    
    increases = adjustments[adjustments['MinChange'] > 0]
    decreases = adjustments[adjustments['MinChange'] < 0]
    print(f'\n=== Seasonal Adjustment Report for {args.quarter} ===')
    print(f'Parts with recommended increases: {len(increases)}')
    print(f'Parts with recommended decreases: {len(decreases)}')
    print(f'Parts unchanged: {len(current) - len(adjustments)}')
    if len(increases) > 0:
        print(f'\nTop 10 parts to INCREASE (highest seasonal demand):')
        top = increases.head(10)
        for _, row in top.iterrows():
            print(f"  {row.get('PartNumber','?')}: Min {int(row.get('MinStock',0))} -> {int(row.get('AdjustedMin',0))} (index: {row.get('SeasonalIndex',1):.2f})")
    print(f'\nOutput: {args.output}')
    print('IMPORTANT: Have Parts Manager review before importing into DMS/SMS.')

if __name__ == '__main__':
    main()

# deploy on MSP management server, run via cron every 30 minutes during
# business hours

#!/usr/bin/env python3
"""
PO Exception Alert Monitor
Deployed on MSP management server. Checks for anomalies in auto-reorder activity.
Run via cron every 30 minutes during business hours:
  */30 7-18 * * 1-6 /usr/bin/python3 /opt/msp/automotive/po_exception_monitor.py

Configuration via environment variables or config file.
"""
import os
import json
import smtplib
import requests
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from datetime import datetime, timedelta

# Configuration — set via environment variables or config.json
CONFIG = {
    'sms_api_url': os.getenv('SMS_API_URL', 'https://api.example.com/v1'),
    'sms_api_key': os.getenv('SMS_API_KEY', ''),
    'smtp_server': os.getenv('SMTP_SERVER', 'smtp.office365.com'),
    'smtp_port': int(os.getenv('SMTP_PORT', '587')),
    'smtp_user': os.getenv('SMTP_USER', 'alerts@msp-domain.com'),
    'smtp_pass': os.getenv('SMTP_PASS', ''),
    'alert_recipients': os.getenv('ALERT_RECIPIENTS', 'tech@msp.com,parts.mgr@client.com').split(','),
    'large_po_threshold': float(os.getenv('LARGE_PO_THRESHOLD', '1000')),
    'reorder_frequency_threshold': int(os.getenv('REORDER_FREQ_THRESHOLD', '3')),
    'adjustment_qty_threshold': int(os.getenv('ADJUSTMENT_QTY_THRESHOLD', '10')),
    'lookback_hours': int(os.getenv('LOOKBACK_HOURS', '24')),
}

def get_recent_pos(since_hours=24):
    """Fetch POs created in the last N hours."""
    since = (datetime.utcnow() - timedelta(hours=since_hours)).isoformat()
    headers = {'Authorization': f'Bearer {CONFIG["sms_api_key"]}'}
    try:
        resp = requests.get(
            f'{CONFIG["sms_api_url"]}/purchase-orders',
            params={'created_after': since, 'auto_generated': True},
            headers=headers, timeout=30
        )
        resp.raise_for_status()
        return resp.json().get('purchase_orders', [])
    except Exception as e:
        return [{'error': str(e)}]

def check_exceptions(pos):
    """Analyze POs for exception conditions."""
    alerts = []
    
    # Check 1: Unusually large POs
    for po in pos:
        if po.get('total', 0) > CONFIG['large_po_threshold']:
            alerts.append({
                'type': 'LARGE_PO',
                'severity': 'WARNING',
                'message': f"Auto-PO #{po.get('id','')} total ${po.get('total',0):.2f} exceeds ${CONFIG['large_po_threshold']:.2f} threshold",
                'details': po
            })
    
    # Check 2: Rejected/backordered POs
    for po in pos:
        if po.get('status') in ('rejected', 'backordered', 'failed'):
            alerts.append({
                'type': 'PO_REJECTED',
                'severity': 'CRITICAL',
                'message': f"Auto-PO #{po.get('id','')} was {po.get('status','')} by {po.get('supplier','unknown')}",
                'details': po
            })
    
    # Check 3: Same part reordered too frequently
    part_reorder_count = {}
    for po in pos:
        for line in po.get('line_items', []):
            pn = line.get('part_number', '')
            part_reorder_count[pn] = part_reorder_count.get(pn, 0) + 1
    
    for pn, count in part_reorder_count.items():
        if count >= CONFIG['reorder_frequency_threshold']:
            alerts.append({
                'type': 'FREQUENT_REORDER',
                'severity': 'WARNING',
                'message': f"Part {pn} has been reordered {count} times in {CONFIG['lookback_hours']} hours — possible threshold miscalculation or data quality issue",
                'details': {'part_number': pn, 'reorder_count': count}
            })
    
    return alerts

def send_alert_email(alerts):
    """Send exception alerts via email."""
    if not alerts:
        return
    
    subject = f"[Auto-Reorder Alert] {len(alerts)} exception(s) detected — {datetime.now().strftime('%Y-%m-%d %H:%M')}"
    
    body_html = '<h2>Auto-Reorder Exception Alerts</h2>'
    body_html += f'<p>Detected at: {datetime.now().strftime("%Y-%m-%d %H:%M")}</p>'
    body_html += '<table border="1" cellpadding="5">'
    body_html += '<tr><th>Severity</th><th>Type</th><th>Details</th></tr>'
    for alert in alerts:
        color = 'red' if alert['severity'] == 'CRITICAL' else 'orange'
        body_html += f'<tr><td style="color:{color}">{alert["severity"]}</td>'
        body_html += f'<td>{alert["type"]}</td><td>{alert["message"]}</td></tr>'
    body_html += '</table>'
    body_html += '<p>Please review in the shop management system.</p>'
    
    msg = MIMEMultipart('alternative')
    msg['Subject'] = subject
    msg['From'] = CONFIG['smtp_user']
    msg['To'] = ', '.join(CONFIG['alert_recipients'])
    msg.attach(MIMEText(body_html, 'html'))
    
    try:
        with smtplib.SMTP(CONFIG['smtp_server'], CONFIG['smtp_port']) as server:
            server.starttls()
            server.login(CONFIG['smtp_user'], CONFIG['smtp_pass'])
            server.sendmail(CONFIG['smtp_user'], CONFIG['alert_recipients'], msg.as_string())
        print(f'Alert email sent to {CONFIG["alert_recipients"]}')
    except Exception as e:
        print(f'ERROR sending email: {e}')

def main():
    print(f'[{datetime.now()}] Running PO exception monitor...')
    pos = get_recent_pos(CONFIG['lookback_hours'])
    if pos and 'error' in pos[0]:
        print(f'API error: {pos[0]["error"]}')
        return
    
    alerts = check_exceptions(pos)
    if alerts:
        print(f'Found {len(alerts)} exception(s):')
        for a in alerts:
            print(f'  [{a["severity"]}] {a["message"]}')
        send_alert_email(alerts)
    else:
        print('No exceptions found.')

if __name__ == '__main__':
    main()