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

> A real Claude session on a Fishbowl database (ILC's demo company, sample data), connected through ILC MCP for Fishbowl. The answer below is what the AI said; the queries are what it ran. Page: https://israellopezconsulting.com/fishbowl-mcp/questions/open-sales-orders

## Question

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

### Answer

Nine sales orders are open, all out of the Main warehouse, and all are past their scheduled dates. None is blocked by a customer hold, and only one has a real stock problem (1056).

| SO | Customer | Value | Scheduled | What's holding it up |
|---|---|---|---|---|
| 1005 | Mark Bennett | $397.00 | Jan 10 | Fully picked and staged; shipment S1005 was never packed or shipped. |
| 1007 | Patricia Beverages | $66.00 | Jul 11 | Line 1 shipped. Lines 2–3 (BD302, C501) are in stock but the pick hasn't been started. |
| 1038 | Karen Smith | $8.89 | Jul 15 | Lines 1–2 are fulfilled. Line 3 (BB2005) is a drop ship waiting on PO 19 from Rocky Mountain Chocolate, issued Aug 14 and not yet fulfilled. |
| 1022 | Mountaineer Catering | $128.35 | Jul 16 | All 4 lines are in stock; pick S1022 not started. |
| 1024 | Allen's Groceries | $30.00 | Jul 17 | B202 is in stock; pick S1024 not started. |
| 1054 | Jim's Bake Shop | $185.94 | Aug 14 | 50 lbs of B202 is in stock; pick S1054 not started. |
| 1023 | Mountaineer Catering | $180.96 | Aug 15 | All 4 lines are in stock; pick S1023 not started. |
| 1055 | Baker Frontier | -$85.00 | Sep 4 | Credit return of 3 lines (B200, B201, B203); the order stays open until the returned goods are received. |
| 1056 | Mark Bennett | $397.00 | Sep 11 | Pick not started, and line 3 (CR401) is short at Main: the only unit there is committed to SO 1005. |

- **Warehouse backlog:** six of the nine (1007, 1022, 1023, 1024, 1054, 1056) are simply picks nobody has started.
- **CR401 for 1056:** LA has 197 oz and MI has 99 oz, so a transfer to Main would clear it.
- **PO 19 quantity:** the PO line is for 0.05 against a sales line of 1 ea. That may be a unit-of-measure difference (I didn't check the PO line's unit), but it's worth a look before chasing the vendor.

"Open" here means status Issued or In Progress; estimates are excluded.

## Behind the scenes

Every step the AI took, as recorded in the query log. All queries are read-only and row-capped.

1. Checked the saved queries — 6 saved queries
2. Read the Fishbowl notes index — 13 topic notes
3. Read Fishbowl notes (SALES.md)
4. Read Fishbowl notes (CORE.md)
5. Read Fishbowl notes (SHIPPING.md)
6. Ran a read-only query: List open sales orders with customer, status, warehouse and dates — 9 rows, 312 ms

   ```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
   ```

7. Ran a read-only query: Open sales order lines with remaining qty and inventory position at the order's warehouse — 19 rows, 248 ms

   ```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
   ```

8. Ran a read-only query: Pick and shipment progress for open sales orders — 18 rows, 250 ms

   ```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
   ```

9. Ran a read-only query: All lines on open orders 1055, 1038 and 1007 to see what remains — 9 rows, 314 ms

   ```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
   ```

10. Ran a read-only query: Purchase order behind open drop-ship sales order lines — 3 rows, 254 ms

   ```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
   ```

11. Ran a read-only query: Where CR401 and BB2002 stock sits, to confirm shortages on open order lines — 9 rows, 190 ms

   ```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
   ```

Learn more: https://israellopezconsulting.com/fishbowl-mcp · More examples: https://israellopezconsulting.com/fishbowl-mcp/questions