“Which sales orders are still open, and what's holding each one up?”

Every step the AI took

1. 1
   Checked the saved queries
   6 saved queries
2. 2
   Read the Fishbowl notes index
   13 topic notes
3. 3
   Read Fishbowl notes · SALES.md
4. 4
   Read Fishbowl notes · CORE.md
5. 5
   Read Fishbowl notes · SHIPPING.md
6. 6
   List open sales orders with customer, status, warehouse and dates

   ✓ passed read-only checkrow cap applied

   Show the SQL

   SELECT so.id, so.num, ss.name AS status, c.name AS customer, cs.name AS customerStatus, lg.name AS locationGroup, DATE_FORMAT(so.dateIssued, '%Y-%m-%d') AS issued, ROUND(so.totalPrice, 2) AS totalPrice, (SELECT DATE_FORMAT(MIN(si.dateScheduledFulfillment), '%Y-%m-%d')
   FROM soitem AS si
   WHERE si.soId = so.id AND si.typeId IN (10, 12, 80) AND si.qtyToFulfill > si.qtyFulfilled) AS earliestScheduled, (SELECT COUNT(*)
   FROM soitem AS si
   WHERE si.soId = so.id AND si.typeId IN (10, 12, 80) AND si.statusId NOT IN (50, 60, 70, 75, 95) AND si.qtyToFulfill > si.qtyFulfilled) AS openLines
   FROM so
   JOIN sostatus AS ss ON ss.id = so.statusId
   JOIN customer AS c ON c.id = so.customerId
   LEFT JOIN customerstatus AS cs ON cs.id = c.statusId
   LEFT JOIN locationgroup AS lg ON lg.id = so.locationGroupId
   WHERE so.statusId IN (20, 25)
   ORDER BY so.dateIssued
   LIMIT 501

   9 rows · 312 ms
7. 7
   Open sales order lines with remaining qty and inventory position at the order's warehouse

   ✓ passed read-only checkrow cap applied

   Show the SQL

   SELECT so.num AS soNum, si.id AS soItemId, si.soLineItem, sit.name AS lineType, sis.name AS lineStatus, si.productNum, p.num AS partNum, su.code AS lineUom, ROUND(si.qtyToFulfill - si.qtyFulfilled, 2) AS qtyRemaining, ROUND(si.qtyPicked, 2) AS qtyPicked, ROUND((si.qtyToFulfill - si.qtyFulfilled) * CASE WHEN si.uomId = p.uomId THEN 1 ELSE uc.multiply / uc.factor END, 2) AS qtyRemainingPartUom, pu.code AS partUom, ROUND(COALESCE(inv.QTYONHAND, 0), 2) AS onHandAtWh, ROUND(COALESCE(inv.QTYALLOCATED, 0), 2) AS allocatedAtWh, ROUND(COALESCE(inv.QTYNOTAVAILABLE, 0), 2) AS notAvailAtWh, ROUND(COALESCE(inv.QTYONORDER, 0), 2) AS onOrderAtWh, ROUND(COALESCE(tot.onHand, 0), 2) AS onHandAll, DATE_FORMAT(si.dateScheduledFulfillment, '%Y-%m-%d') AS scheduled
   FROM soitem AS si
   JOIN so ON so.id = si.soId
   JOIN soitemtype AS sit ON sit.id = si.typeId
   JOIN soitemstatus AS sis ON sis.id = si.statusId
   LEFT JOIN product AS pr ON pr.id = si.productId
   LEFT JOIN part AS p ON p.id = pr.partId
   LEFT JOIN uom AS su ON su.id = si.uomId
   LEFT JOIN uom AS pu ON pu.id = p.uomId
   LEFT JOIN uomconversion AS uc ON uc.fromUomId = si.uomId AND uc.toUomId = p.uomId
   LEFT JOIN qtyinventorytotals AS inv ON inv.PARTID = p.id AND inv.LOCATIONGROUPID = so.locationGroupId
   LEFT JOIN (SELECT PARTID, SUM(QTYONHAND) AS onHand
   FROM qtyinventorytotals
   GROUP BY PARTID) AS tot ON tot.PARTID = p.id
   WHERE so.statusId IN (20, 25) AND si.typeId IN (10, 12, 80) AND si.statusId NOT IN (50, 60, 70, 75, 95) AND si.qtyToFulfill > si.qtyFulfilled
   ORDER BY so.num, si.soLineItem
   LIMIT 1001

   19 rows · 248 ms
8. 8
   Pick and shipment progress for open sales orders

   ✓ passed read-only checkrow cap applied

   Show the SQL

   SELECT so.num AS soNum, pk.num AS pickNum, pks.name AS pickStatus, pi.soItemId, pis.name AS pickItemStatus, ROUND(pi.qty, 2) AS qty, sh.num AS shipNum, shs.name AS shipStatus
   FROM pickitem AS pi
   JOIN pick AS pk ON pk.id = pi.pickId
   JOIN pickstatus AS pks ON pks.id = pk.statusId
   JOIN pickitemstatus AS pis ON pis.id = pi.statusId
   JOIN so ON so.id = pi.orderId AND pi.orderTypeId = 20
   LEFT JOIN ship AS sh ON sh.id = pi.shipId
   LEFT JOIN shipstatus AS shs ON shs.id = sh.statusId
   WHERE so.statusId IN (20, 25) AND (sh.id IS NULL OR sh.statusId <> 30)
   ORDER BY so.num, pi.id
   LIMIT 1001

   18 rows · 250 ms
9. 9
   All lines on open orders 1055, 1038 and 1007 to see what remains

   ✓ passed read-only checkrow cap applied

   Show the SQL

   SELECT so.num AS soNum, si.soLineItem, sit.name AS lineType, sis.name AS lineStatus, si.productNum, si.description, ROUND(si.qtyOrdered, 2) AS qtyOrdered, ROUND(si.qtyFulfilled, 2) AS qtyFulfilled, ROUND(si.totalPrice, 2) AS totalPrice, DATE_FORMAT(si.dateScheduledFulfillment, '%Y-%m-%d') AS scheduled
   FROM soitem AS si
   JOIN so ON so.id = si.soId
   JOIN soitemtype AS sit ON sit.id = si.typeId
   JOIN soitemstatus AS sis ON sis.id = si.statusId
   WHERE so.num IN ('1055', '1038', '1007')
   ORDER BY so.num, si.soLineItem
   LIMIT 101

   9 rows · 314 ms
10. 10
    Purchase order behind open drop-ship sales order lines

    ✓ passed read-only checkrow cap applied

    Show the SQL

    SELECT so.num AS soNum, si.soLineItem, po.num AS poNum, pos.name AS poStatus, v.name AS vendor, pi.poLineItem, pis.name AS poItemStatus, ROUND(pi.qtyToFulfill, 2) AS poQty, ROUND(pi.qtyFulfilled, 2) AS poQtyFulfilled, DATE_FORMAT(po.dateIssued, '%Y-%m-%d') AS poIssued, DATE_FORMAT(pi.dateScheduledFulfillment, '%Y-%m-%d') AS poScheduled
    FROM soitem AS si
    JOIN so ON so.id = si.soId
    LEFT JOIN objecttoobject AS o ON o.typeId = 10 AND o.recordId1 = si.id
    LEFT JOIN poitem AS pi ON pi.id = o.recordId2
    LEFT JOIN po ON po.id = pi.poId
    LEFT JOIN postatus AS pos ON pos.id = po.statusId
    LEFT JOIN poitemstatus AS pis ON pis.id = pi.statusId
    LEFT JOIN vendor AS v ON v.id = po.vendorId
    WHERE so.statusId IN (20, 25) AND si.typeId = 12
    LIMIT 101

    3 rows · 254 ms
11. 11
    Where CR401 and BB2002 stock sits, to confirm shortages on open order lines

    ✓ passed read-only checkrow cap applied

    Show the SQL

    SELECT p.num AS part, lg.name AS locationGroup, l.name AS location, lt.name AS locationType, ROUND(SUM(t.qty), 2) AS qty, ROUND(SUM(COALESCE(t.qtyCommitted, 0)), 2) AS committed
    FROM tag AS t
    JOIN part AS p ON p.id = t.partId
    JOIN location AS l ON l.id = t.locationId
    JOIN locationgroup AS lg ON lg.id = l.locationGroupId
    JOIN locationtype AS lt ON lt.id = l.typeId
    WHERE p.num IN ('CR401', 'BB2002') AND t.qty > 0
    GROUP BY p.num, lg.name, l.name, lt.name
    ORDER BY p.num, lg.name
    LIMIT 101

    9 rows · 190 ms