-- Items currently at an external vendor (PO open, not fully received)
-- Outside process items identified by item_type='O' and classcode_code='EXTLAB'
SELECT
    v.vend_name AS vendor,
    ph.pohead_number AS po_number,
    fi.item_number AS finished_pn,
    fi.item_descrip1 AS finished_desc,
    COALESCE(mi.item_number, '') AS manufactured_pn,
    opi.item_number AS ext_labor_pn,
    pi.poitem_qty_ordered AS qty_ordered,
    pi.poitem_qty_received AS qty_received,
    to_char(ph.pohead_orderdate, 'YYYY-MM-DD') AS date_shipped,
    to_char(ph.pohead_orderdate + interval '7 days', 'YYYY-MM-DD') AS expected_return,
    (CURRENT_DATE - ph.pohead_orderdate::date) AS days_out,
    COALESCE((
        SELECT SUM(w2.wo_qtyord - w2.wo_qtyrcv)
        FROM wo w2
        JOIN itemsite is2 ON w2.wo_itemsite_id = is2.itemsite_id
        WHERE is2.itemsite_item_id = fi.item_id
          AND w2.wo_status IN ('R','E','I')
    ), 0) AS wo_demand
FROM poitem pi
JOIN pohead ph ON pi.poitem_pohead_id = ph.pohead_id
JOIN vendinfo v ON ph.pohead_vend_id = v.vend_id
JOIN itemsite opis ON pi.poitem_itemsite_id = opis.itemsite_id
JOIN item opi ON opis.itemsite_item_id = opi.item_id
JOIN classcode cc ON opi.item_classcode_id = cc.classcode_id
-- Link PO outside process item back to the WO that needs it
JOIN womatl wm ON wm.womatl_itemsite_id = opis.itemsite_id
JOIN wo parent_wo ON wm.womatl_wo_id = parent_wo.wo_id
    AND parent_wo.wo_status IN ('R','E','I')
JOIN itemsite fis ON parent_wo.wo_itemsite_id = fis.itemsite_id
JOIN item fi ON fis.itemsite_item_id = fi.item_id
-- Find the manufactured part (non-outside-process material on same WO)
LEFT JOIN LATERAL (
    SELECT mi2.item_number
    FROM womatl wm2
    JOIN itemsite mis2 ON wm2.womatl_itemsite_id = mis2.itemsite_id
    JOIN item mi2 ON mis2.itemsite_item_id = mi2.item_id
    JOIN classcode cc2 ON mi2.item_classcode_id = cc2.classcode_id
    WHERE wm2.womatl_wo_id = parent_wo.wo_id
      AND wm2.womatl_id != wm.womatl_id
      AND cc2.classcode_code != 'EXTLAB'
      AND mi2.item_type != 'O'
    ORDER BY mi2.item_number
    LIMIT 1
) mi ON true
WHERE cc.classcode_code = 'EXTLAB'
  AND opi.item_type = 'O'
  AND ph.pohead_status = 'O'
  AND pi.poitem_status = 'O'
  AND pi.poitem_qty_received < pi.poitem_qty_ordered
ORDER BY days_out DESC, vendor, finished_pn;