Unfulfilled Multi-Supplier Order Lines

You are managing a global procurement database for a heavy machinery manufacturer. You need to audit the supply chain to find order lines that are currently unfulfilled across multiple suppliers. An order line is considered unfulfilled if the total quantity ordered across all associated shipments for that line is strictly less than the quantity requested in the original purchase order item.

Tables

• suppliers: supplier_id (INT, Primary Key), supplier_name (TEXT), country (TEXT).
• purchase_orders: po_id (INT, Primary Key), supplier_id (INT, Foreign Key), order_date (DATE).
• po_items: item_id (INT, Primary Key), po_id (INT, Foreign Key), part_number (TEXT), quantity_ordered (INT).
• shipments: shipment_id (INT, Primary Key), item_id (INT, Foreign Key), quantity_shipped (INT), shipment_date (DATE).

Write a query that returns all unfulfilled purchase order items. For each item, return the supplier_name, the po_id, the part_number, the quantity_ordered, and the total quantity shipped so far (total_shipped, defaulting to 0 if no shipments have been made for that item).

Sort the results by quantity_ordered descending, then by po_id ascending, and finally by part_number ascending.

Example

For a visible dataset where Supplier 'Acme Corp' has a purchase order with po_id 101 containing two items (part 'BOLT-01' with 100 ordered, and 'NUT-02' with 50 ordered), and shipments show 40 bolts shipped but zero nuts shipped, the query should return both items because both have total_shipped less than quantity_ordered.