Component Shortage SQL Query Generator

-- Component shortage list, horizon 21 days, buyer EL01
-- Table and column names are generic: map them to your ERP schema.
WITH demand AS (
  SELECT d.item_no, SUM(d.qty_required - d.qty_issued) AS open_demand
  FROM wo_component d
  WHERE d.need_date <= CURRENT_DATE + INTERVAL '21 days'
    AND d.qty_required > d.qty_issued
  GROUP BY d.item_no
),
supply AS (
  SELECT p.item_no, SUM(p.qty_ordered - p.qty_received) AS inbound
  FROM po_line p
  WHERE p.status = 'OPEN'
    AND p.due_date <= CURRENT_DATE + INTERVAL '21 days'
  GROUP BY p.item_no
)
SELECT i.item_no,
       i.description,
       i.mpn,
       i.lead_time_days,
       COALESCE(s.qty_on_hand, 0) AS on_hand,
       COALESCE(sp.inbound, 0) AS inbound,
       dm.open_demand,
       dm.open_demand - COALESCE(s.qty_on_hand, 0) - COALESCE(sp.inbound, 0) AS shortage
FROM demand dm
JOIN item i ON i.item_no = dm.item_no
LEFT JOIN item_stock s ON s.item_no = dm.item_no
LEFT JOIN supply sp ON sp.item_no = dm.item_no
WHERE i.buyer_code = 'EL01'
  AND dm.open_demand - COALESCE(s.qty_on_hand, 0) - COALESCE(sp.inbound, 0) >= 1
ORDER BY shortage DESC, i.lead_time_days DESC;

-- Add this condition to the final WHERE clause to show parts whose
-- earliest need date is inside the replenishment lead time.
--   AND dm.earliest_need <= CURRENT_DATE + (i.lead_time_days * INTERVAL '1 day')
-- You also need to add MIN(d.need_date) AS earliest_need to the demand CTE.