What are the tables in my ERP to refer for diagnosing Out of stocks
- User
What are the tables in my ERP to refer for diagnosing Out of stocks
Assistant
Here are the key ERP tables to analyze for diagnosing out-of-stock (OOS) situations, categorized by functional area:
Core Inventory Tables
-
INVENTORY_MASTER
- SKU details, lead times, MOQs, safety stock levels
- Key fields:
product_id,min_stock_level,reorder_point,lead_time_days
-
INVENTORY_ON_HAND
- Current stock levels by location
- Key fields:
product_id,location_id,qty_available,qty_allocated,qty_on_order
-
INVENTORY_TRANSACTIONS
- Movement history (receipts, issues, adjustments)
- Key fields:
transaction_date,transaction_type,qty,from_location,to_location
Demand & Planning Tables
-
DEMAND_FORECAST
- Projected demand by SKU/location
- Key fields:
product_id,location_id,forecast_date,forecast_qty,confidence_level
-
SALES_ORDERS
- Customer demand (booked orders)
- Key fields:
order_date,requested_ship_date,product_id,qty,fulfillment_status
-
PURCHASE_REQUISITIONS
- Generated replenishment requests
- Key fields:
product_id,request_date,required_date,qty,approval_status
Supply Chain Tables
-
PURCHASE_ORDERS
- Supplier commitments
- Key fields:
po_date,expected_delivery_date,product_id,qty_ordered,qty_received
-
RECEIVING_LOG
- Actual supplier deliveries
- Key fields:
receipt_date,product_id,qty_accepted,qty_rejected,carrier_delay_hours
-
SUPPLIER_PERFORMANCE
- Vendor reliability metrics
- Key fields:
supplier_id,avg_delay_days,otif_score,quality_reject_rate
Operational Tables
-
ALLOCATION_RULES
- Stock distribution logic
- Key fields:
product_id,priority_group,min_alloc_qty,location_priority
-
PICKING_EXCEPTIONS
- Warehouse fulfillment issues
- Key fields:
product_id,location_id,exception_type("not found", "damaged", etc.)
-
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_DATE2. 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_cause3. 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 DESCKey Relationships to Analyze:
Table 1 Table 2 Join Condition Insight INVENTORY_ON_HAND SALES_ORDERS product_id+location_idDemand vs. availability gap PURCHASE_ORDERS RECEIVING_LOG po_idSupplier delivery reliability DEMAND_FORECAST INVENTORY_TRANSACTIONS product_id+dateForecast accuracy impact Pro Tip: Cross-reference with
PICKING_EXCEPTIONSto identify "phantom stock" issues where system shows availability but physical inventory is missing.-
- User
Give me 5 questions on inventory management for diagnosing problems
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:
- Automate Alerts: Set thresholds to trigger investigations when metrics exceed tolerances
- Root Cause Mapping: Link findings to specific operational processes (e.g., receiving inaccuracies → cycle counting procedures)
- Comparative Analysis: Benchmark against historical performance and industry standards
- Cross-Functional Review: Share insights with procurement, sales, and finance teams
- 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.
- Data Sources:
- User
Only questions that begin with why.
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:
- Why is SKU-123 out of stock?
- Because the replenishment order arrived late
- Why did the order arrive late?
- Because the supplier missed their production deadline
- Why did the supplier miss the deadline?
- Because we didn't share updated demand forecasts
- Why weren't forecasts shared?
- Because there's no automated forecast sharing process
- 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.
- Probe deeper:
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.