# Which manufacturing orders are open, and what do they still need?

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

## Question

Which manufacturing orders are open, and what do they still need?

### Answer

Three manufacturing orders are open: 45, 46 and 49. All are in Main, still in Entered status with no work orders, and nothing has been built on any of them, so each still needs its full bill of materials. All three are past their scheduled dates.

| MO | Scheduled | Builds outstanding (1 batch each) |
|---|---|---|
| 49 | Jul 7 | Chocolate Fudge (25 ea) |
| 45 | Jul 18 | Chocolate Chip (24 ea), Blue Raspberry Lemonade (12 floz) |
| 46 | Aug 8 | Oatmeal Chocolate Chip (200 ea), Chocolate Fudge (25 ea), Sugar Cookie (48 ea), Blue Raspberry Lemonade (12 floz) twice |

Main has enough of most raw goods, but six parts have no stock there, and they block every build on all three orders:

| Raw good | Needed in total | Needed by | Stock elsewhere |
|---|---|---|---|
| BP203 Butter | 48.5 oz | MO 45 (8 oz), MO 46 (40.5 oz) | 3,453 oz each in LA, MI and SLC |
| T1001 Devil's Food Cake mix | 4 lbs | MO 46 (2 lbs), MO 49 (2 lbs) | LA 120 lbs, MI 60 lbs |
| S601 Blue Raspberry Syrup | 3 oz | MO 45 (1 oz), MO 46 (2 oz) | LA 182 oz, MI 91 oz |
| S604 Lemonade Powder Mix | 3 oz (0.19 lbs) | MO 45 (1 oz), MO 46 (2 oz) | LA 195 lbs, MI 98 lbs |
| BP205 Baking Powder | 0.08 oz | MO 46 | LA 185 oz, MI 92 oz |
| Water | 36.33 floz | MO 45 (12.33 floz), MO 46 (24 floz) | None in any location group |

- **Transfers cover five of the six:** everything but Water is in stock at LA or MI, so moving it to Main clears those shortages without any purchasing.
- **Water:** it is set up as an inventory part with no stock anywhere, so it will show short on these orders until stock is added or the part setup changes.
- **Covered from Main:** flour, sugars, eggs, oats, chocolate chips, walnuts, vanilla, baking soda, salt and vegetable oil are all well above what these orders need.
- **MO 46:** Blue Raspberry Lemonade is on it as two separate one-build lines, which may be a duplicate entry.

Each need was compared against Main's on-hand stock on its own, without netting out what other orders have already allocated.

## 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 (MANUFACTURING.md)
4. Looked up a table (mo) — 16 columns
5. Looked up a table (wo) — 23 columns
6. Looked up a table (woitem) — 14 columns
7. Looked up a table (moitem) — 26 columns
8. Ran a read-only query: List open manufacture orders with their BOMs, build quantities and work order status — 8 rows, 237 ms

   ```sql
   SELECT m.num AS moNum, ms.name AS moStatus, lg.name AS locationGroup, CAST(m.dateCreated as CHAR) AS dateCreated, CAST(m.dateIssued as CHAR) AS dateIssued, CAST(m.dateScheduled as CHAR) AS dateScheduled, so.num AS soNum, b.num AS bomNum, mi.qtyToFulfill AS buildsOrdered, mi.qtyFulfilled AS buildsDone, mis.name AS bomLineStatus, w.num AS woNum, ws.name AS woStatus
   FROM mo AS m
   JOIN mostatus AS ms ON ms.id = m.statusId
   JOIN locationgroup AS lg ON lg.id = m.locationGroupId
   LEFT JOIN so ON so.id = m.soId
   JOIN moitem AS mi ON mi.moId = m.id AND mi.typeId = 50
   JOIN moitemstatus AS mis ON mis.id = mi.statusId
   LEFT JOIN bom AS b ON b.id = mi.bomId
   LEFT JOIN wo AS w ON w.moItemId = mi.id
   LEFT JOIN wostatus AS ws ON ws.id = w.statusId
   WHERE m.statusId IN (10, 20, 50)
   ORDER BY m.dateScheduled, m.num, mi.id
   LIMIT 501
   ```

9. Ran a read-only query: For each open manufacture order, list finished goods and raw goods still outstanding with stock availability in the MO's location group — 53 rows, 315 ms

   ```sql
   SELECT m.num AS moNum, pb.num AS bomNum, bit.name AS lineType, p.num AS partNum, p.description, mis.name AS lineStatus, mi.qtyToFulfill, mi.qtyFulfilled, mi.qtyToFulfill - mi.qtyFulfilled AS qtyRemaining, lu.code AS lineUom, ROUND((mi.qtyToFulfill - mi.qtyFulfilled) * CASE WHEN mi.uomId <> p.uomId AND uc.id IS NOT NULL THEN uc.multiply / uc.factor ELSE 1 END, 4) AS qtyRemainingStockUom, pu.code AS stockUom, (mi.uomId <> p.uomId AND uc.id IS NULL) AS missingConversion, ROUND(COALESCE(t.QTYONHAND, 0), 4) AS onHandLG, ROUND(COALESCE(t.QTYONHAND, 0) - COALESCE(t.QTYNOTAVAILABLE, 0), 4) AS usableOnHandLG, ROUND(COALESCE(t.QTYALLOCATED, 0), 4) AS allocatedLG, ROUND(COALESCE(t.QTYONORDER, 0), 4) AS onOrderLG
   FROM mo AS m
   JOIN moitem AS mi ON mi.moId = m.id AND mi.typeId IN (10, 20)
   JOIN bomitemtype AS bit ON bit.id = mi.typeId
   JOIN moitemstatus AS mis ON mis.id = mi.statusId
   LEFT JOIN moitem AS par ON par.id = mi.parentId
   LEFT JOIN bom AS pb ON pb.id = par.bomId
   JOIN part AS p ON p.id = mi.partId
   JOIN uom AS pu ON pu.id = p.uomId
   LEFT JOIN uom AS lu ON lu.id = mi.uomId
   LEFT JOIN uomconversion AS uc ON uc.fromUomId = mi.uomId AND uc.toUomId = p.uomId
   LEFT JOIN qtyinventorytotals AS t ON t.PARTID = p.id AND t.LOCATIONGROUPID = m.locationGroupId
   WHERE m.statusId IN (10, 20, 50)
   ORDER BY m.num, par.id, mi.typeId, p.num
   LIMIT 1001
   ```

10. Ran a read-only query: Check stock in every location group and part type for the raw goods that show zero stock in Main — 12 rows, 264 ms

   ```sql
   SELECT p.num AS partNum, p.description, pt.name AS partType, pu.code AS stockUom, lg.name AS locationGroup, ROUND(t.QTYONHAND, 4) AS onHand, ROUND(t.QTYONHAND - t.QTYALLOCATED - t.QTYNOTAVAILABLE, 4) AS available, ROUND(t.QTYONORDER, 4) AS onOrder
   FROM part AS p
   JOIN parttype AS pt ON pt.id = p.typeId
   JOIN uom AS pu ON pu.id = p.uomId
   LEFT JOIN qtyinventorytotals AS t ON t.PARTID = p.id
   LEFT JOIN locationgroup AS lg ON lg.id = t.LOCATIONGROUPID
   WHERE p.num IN ('BP203', 'Water', 'S601', 'S604', 'T1001', 'BP205')
   ORDER BY p.num, lg.name
   LIMIT 101
   ```

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