Skip to content
TheChatArchiveThe Chat Archive
DeepSeekdeepseek-v3-0324· DeepSeekHugging Face LMArena Human Preference 140K

What are the tables in my ERP to refer for diagnosing Out of stocks

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Geminigemini-2.5-flash’s answer.
  1. User

    What are the tables in my ERP to refer for diagnosing Out of stocks

  2. DeepSeek Assistant

    Here are the key ERP tables to analyze for diagnosing out-of-stock (OOS) situations, categorized by functional area:

    Core Inventory Tables

    1. INVENTORY_MASTER

      • SKU details, lead times, MOQs, safety stock levels
      • Key fields: product_id, min_stock_level, reorder_point, lead_time_days
    2. INVENTORY_ON_HAND

      • Current stock levels by location
      • Key fields: product_id, location_id, qty_available, qty_allocated, qty_on_order
    3. INVENTORY_TRANSACTIONS

      • Movement history (receipts, issues, adjustments)
      • Key fields: transaction_date, transaction_type, qty, from_location, to_location

    Demand & Planning Tables

    1. DEMAND_FORECAST

      • Projected demand by SKU/location
      • Key fields: product_id, location_id, forecast_date, forecast_qty, confidence_level
    2. SALES_ORDERS

      • Customer demand (booked orders)
      • Key fields: order_date, requested_ship_date, product_id, qty, fulfillment_status
    3. PURCHASE_REQUISITIONS

      • Generated replenishment requests
      • Key fields: product_id, request_date, required_date, qty, approval_status

    Supply Chain Tables

    1. PURCHASE_ORDERS

      • Supplier commitments
      • Key fields: po_date, expected_delivery_date, product_id, qty_ordered, qty_received
    2. RECEIVING_LOG

      • Actual supplier deliveries
      • Key fields: receipt_date, product_id, qty_accepted, qty_rejected, carrier_delay_hours
    3. SUPPLIER_PERFORMANCE

      • Vendor reliability metrics
      • Key fields: supplier_id, avg_delay_days, otif_score, quality_reject_rate

    Operational Tables

    1. ALLOCATION_RULES

      • Stock distribution logic
      • Key fields: product_id, priority_group, min_alloc_qty, location_priority
    2. PICKING_EXCEPTIONS

      • Warehouse fulfillment issues
      • Key fields: product_id, location_id, exception_type ("not found", "damaged", etc.)
    3. STOCKOUT_HISTORY

      • OOS incident log (if available)
      • Key fields: product_id, location_id, start_time, end_time, impacted_sales

    Diagnostic SQL Queries

    1. Current OOS Analysis

    SELECT 
        p.product_id, 
        p.description,
        i.location_id,
        i.qty_available,
        i.qty_allocated,
        f.forecast_qty,
        (f.forecast_qty - i.qty_available) AS deficit
    FROM INVENTORY_ON_HAND i
    JOIN INVENTORY_MASTER p ON i.product_id = p.product_id
    JOIN DEMAND_FORECAST f ON i.product_id = f.product_id 
        AND i.location_id = f.location_id
    WHERE i.qty_available <= 0
        AND f.forecast_date = CURRENT_DATE
    

    2. Root Cause Identification

    SELECT 
        p.product_id,
        CASE 
            WHEN po.qty_received < po.qty_ordered THEN 'Supplier Shortage'
            WHEN i.qty_available < so.qty THEN 'Demand Spike'
            WHEN i.qty_available > 0 AND p.pick_location IS NULL THEN 'Warehouse Slotting'
            WHEN i.qty_available < p.min_stock_level THEN 'Replenishment Failure'
            ELSE 'Unknown'
        END AS root_cause,
        COUNT(*) AS occurrences
    FROM STOCKOUT_HISTORY s
    JOIN INVENTORY_MASTER p ON s.product_id = p.product_id
    LEFT JOIN PURCHASE_ORDERS po ON s.product_id = po.product_id
        AND po.expected_delivery_date BETWEEN s.start_time-7 AND s.start_time
    LEFT JOIN SALES_ORDERS so ON s.product_id = so.product_id
        AND so.requested_ship_date BETWEEN s.start_time-3 AND s.start_time
    LEFT JOIN INVENTORY_ON_HAND i ON s.product_id = i.product_id
        AND s.location_id = i.location_id
    GROUP BY p.product_id, root_cause
    

    3. Preventive Analysis

    SELECT 
        p.product_id,
        p.description,
        AVG(DATEDIFF(day, pr.request_date, r.receipt_date)) AS actual_lead_time,
        p.lead_time_days AS planned_lead_time,
        (SELECT COUNT(*) 
         FROM STOCKOUT_HISTORY 
         WHERE product_id = p.product_id) AS stockout_count
    FROM PURCHASE_REQUISITIONS pr
    JOIN RECEIVING_LOG r ON pr.requisition_id = r.requisition_id
    JOIN INVENTORY_MASTER p ON pr.product_id = p.product_id
    WHERE pr.request_date > CURRENT_DATE - 90
    GROUP BY p.product_id, p.description, p.lead_time_days
    HAVING actual_lead_time > planned_lead_time * 1.2
    ORDER BY stockout_count DESC
    

    Key Relationships to Analyze:

    Table 1Table 2Join ConditionInsight
    INVENTORY_ON_HANDSALES_ORDERSproduct_id + location_idDemand vs. availability gap
    PURCHASE_ORDERSRECEIVING_LOGpo_idSupplier delivery reliability
    DEMAND_FORECASTINVENTORY_TRANSACTIONSproduct_id + dateForecast accuracy impact

    Pro Tip: Cross-reference with PICKING_EXCEPTIONS to identify "phantom stock" issues where system shows availability but physical inventory is missing.

  3. User

    Give me 5 questions on inventory management for diagnosing problems

  4. DeepSeek Assistant

    Here are 5 critical diagnostic questions for inventory management problems, along with the data sources and analysis approaches for each:

    1. Where are we experiencing the highest out-of-stock rates?

    • Data Sources: INVENTORY_ON_HAND, SALES_ORDERS, STOCKOUT_HISTORY
    • Analysis:
      SELECT 
          product_id, 
          location_id,
          COUNT(CASE WHEN qty_available <= 0 THEN 1 END) * 100.0 / COUNT(*) AS oos_rate
      FROM INVENTORY_SNAPSHOTS
      WHERE snapshot_date BETWEEN [start_date] AND [end_date]
      GROUP BY product_id, location_id
      HAVING oos_rate > 5  -- Threshold for investigation
      ORDER BY oos_rate DESC;
      
    • Diagnostic Value: Identifies SKU-location combinations needing immediate attention.

    2. What percentage of our inventory is stagnant (no sales in X days)?

    • Data Sources: INVENTORY_ON_HAND, SALES_TRANSACTIONS
    • Analysis:
      SELECT 
          i.product_id,
          DATEDIFF(day, MAX(s.sale_date), CURRENT_DATE) AS days_since_last_sale,
          i.qty_on_hand,
          i.qty_on_hand * p.unit_cost AS stagnant_value
      FROM INVENTORY_ON_HAND i
      LEFT JOIN SALES_TRANSACTIONS s ON i.product_id = s.product_id
      JOIN PRODUCTS p ON i.product_id = p.product_id
      GROUP BY i.product_id, i.qty_on_hand, p.unit_cost
      HAVING days_since_last_sale > 90  -- Adjust threshold
      ORDER BY stagnant_value DESC;
      
    • Diagnostic Value: Reveals dead stock tying up working capital.

    3. How accurate are our inventory records vs physical counts?

    • Data Sources: INVENTORY_ON_HAND, CYCLE_COUNT_RESULTS
    • Analysis:
      SELECT 
          location_id,
          COUNT(*) AS total_counts,
          AVG(ABS(system_qty - counted_qty)) AS avg_discrepancy,
          SUM(CASE WHEN ABS(system_qty - counted_qty) > 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS error_rate
      FROM CYCLE_COUNT_RESULTS
      WHERE count_date BETWEEN [start_date] AND [end_date]
      GROUP BY location_id
      HAVING error_rate > 2  -- Industry benchmark
      ORDER BY avg_discrepancy DESC;
      
    • Diagnostic Value: Measures process control in warehouse operations.

    4. Which suppliers consistently deliver late, causing stockouts?

    • Data Sources: PURCHASE_ORDERS, RECEIVING_LOG
    • Analysis:
      SELECT 
          supplier_id,
          COUNT(*) AS total_pos,
          AVG(DATEDIFF(day, promised_date, actual_receipt_date)) AS avg_delay_days,
          SUM(CASE WHEN DATEDIFF(day, promised_date, actual_receipt_date) > 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS late_percentage
      FROM PURCHASE_ORDERS po
      JOIN RECEIVING_LOG rl ON po.po_id = rl.po_id
      WHERE po.order_date > CURRENT_DATE - 365
      GROUP BY supplier_id
      HAVING late_percentage > 20  -- Adjust based on SLA
      ORDER BY avg_delay_days DESC;
      
    • Diagnostic Value: Identifies unreliable suppliers impacting service levels.

    5. What's our inventory turnover by product category?

    • Data Sources: INVENTORY_TRANSACTIONS, PRODUCT_CATEGORIES
    • Analysis:
      SELECT 
          pc.category_name,
          SUM(s.sales_qty) AS total_sales,
          AVG(i.qty_on_hand) AS avg_inventory,
          SUM(s.sales_qty) / AVG(i.qty_on_hand) AS turnover_rate
      FROM SALES s
      JOIN PRODUCTS p ON s.product_id = p.product_id
      JOIN PRODUCT_CATEGORIES pc ON p.category_id = pc.category_id
      JOIN (
          SELECT 
              product_id, 
              AVG(qty_on_hand) AS qty_on_hand 
          FROM INVENTORY_ON_HAND 
          WHERE snapshot_date BETWEEN [start_date] AND [end_date]
          GROUP BY product_id
      ) i ON s.product_id = i.product_id
      WHERE s.sale_date BETWEEN [start_date] AND [end_date]
      GROUP BY pc.category_name
      ORDER BY turnover_rate ASC;
      
    • Diagnostic Value: Highlights slow-moving categories needing demand planning adjustments.

    Implementation Tips:

    1. Automate Alerts: Set thresholds to trigger investigations when metrics exceed tolerances
    2. Root Cause Mapping: Link findings to specific operational processes (e.g., receiving inaccuracies → cycle counting procedures)
    3. Comparative Analysis: Benchmark against historical performance and industry standards
    4. Cross-Functional Review: Share insights with procurement, sales, and finance teams
    5. Actionable Reporting: Visualize data with:
      • Heat maps for OOS hotspots
      • Trend lines for inventory accuracy
      • Pareto charts for supplier delays

    These questions provide a 360° diagnostic view of inventory health, from availability to financial impact.

  5. User

    Only questions that begin with why.

  6. DeepSeek Assistant

    Here are 5 powerful "Why" questions to diagnose inventory management problems, structured for root cause analysis:

    1. Why are specific SKUs consistently out of stock?

    • Probe deeper:
      • Why weren't safety stock levels adjusted for demand variability?
      • Why did replenishment orders fail to cover the shortfall?
      • Why wasn't the demand spike anticipated?

    2. Why is inventory accuracy lower in certain locations?

    • Probe deeper:
      • Why are cycle counts not catching discrepancies?
      • Why do receiving/shipping processes create variances?
      • Why don't employees follow inventory control procedures?

    3. Why do suppliers frequently deliver late?

    • Probe deeper:
      • Why aren't contractual penalties enforcing on-time delivery?
      • Why do we lack visibility into supplier production schedules?
      • Why haven't we diversified our supplier base?

    4. Why does dead stock accumulate in our warehouse?

    • Probe deeper:
      • Why are forecasts not adjusted for declining demand?
      • Why don't liquidation processes trigger sooner?
      • Why are MOQs higher than actual usage?

    5. Why do inventory costs keep increasing?

    • Probe deeper:
      • Why are carrying costs not factored into ordering decisions?
      • Why do we maintain duplicate inventory across locations?
      • Why don't we adjust order frequencies based on demand patterns?

    Root Cause Analysis Template

    graph TD
        A[Surface Problem] --> B[First Why]
        B --> C[Second Why]
        C --> D[Third Why]
        D --> E[Root Cause]
        E --> F[Corrective Action]
    

    Example Drill-Down:

    1. Why is SKU-123 out of stock?
      • Because the replenishment order arrived late
    2. Why did the order arrive late?
      • Because the supplier missed their production deadline
    3. Why did the supplier miss the deadline?
      • Because we didn't share updated demand forecasts
    4. Why weren't forecasts shared?
      • Because there's no automated forecast sharing process
    5. Why no automated process?
      • Because integration wasn't prioritized in IT roadmap

    Key Data to Investigate:

    • Supplier lead time variability
    • Forecast adjustment frequency
    • Inventory write-off history
    • Carrying cost calculations

    These "Why" questions force movement beyond symptoms to systemic causes, typically revealing:

    • Process gaps (40% of cases)
    • Data flow breakdowns (30%)
    • Incentive misalignment (20%)
    • Tool limitations (10%)

    Tip: Use the "5 Whys" technique until you reach an actionable root cause.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.