# Can we build 40 batches of chocolate chip cookies (BB2005) by Friday? What's short?

> 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/build-check

## Question

Can we build 40 batches of chocolate chip cookies (BB2005) by Friday? What's short?

### Answer

**Yes on materials, with two things to resolve.** Every ingredient for 40 batches of BB2005 is covered by available stock company-wide, except the items below.

| Issue | Need | Status |
|---|---|---|
| **Water** | 13.2 fl oz | Zero on hand anywhere. It's set up as an Inventory part, so Fishbowl will treat it as short and the pick won't fully fill until stock is adjusted in. |
| **Butter (BP203)** at Main | 320 oz (20 lbs) | Main has none. LA, MI and SLC each have 3,453 oz, so this only matters if you build at Main. |

Everything else has plenty of room. The tightest ingredients are still well clear:

| Ingredient | Need for 40 | Available (all sites) | Batches covered |
|---|---|---|---|
| Chocolate Chips (CR403) | 40 lbs | 349.5 lbs | 349 |
| All Purpose Flour (B201) | 60 lbs | 591 lbs | 394 |
| Chopped Walnuts (H902) | 20 lbs | 247.5 lbs | 495 |
| White egg (BP204) | 80 ea | 1,455 ea | 727 |
| Brown Sugar (C502) | 20 lbs | 481.5 lbs | 963 |

By site, LA and MI can each build all 40 from their own stock (apart from water). Main can too once butter is transferred in; its walnuts are the closest call at 25 lbs against 20 needed. SLC can't, since it only holds butter.

Two caveats:
- This checks materials only. I didn't look at labor, oven capacity, or the schedule for the week, so "by Friday" depends on those.
- Available stock already nets out current allocations, but not Entered manufacture orders that haven't been issued yet, which could be competing for the same ingredients.

Which site is the build planned for? I can check open manufacture orders there to confirm nothing else is competing for the butter or walnuts.

## Follow-up

Main. Is anything else competing for the butter or walnuts there?

### Answer

**Two open manufacture orders at Main want the same ingredients, but they're small.** Walnuts still cover everything with 2 lbs to spare, and the butter transfer needs to grow by about 3 lbs.

| MO | Status | Scheduled | Builds | Butter | Walnuts |
|---|---|---|---|---|---|
| 45 | Entered | Jul 18 | 1 × BB2005 Chocolate Chip Batch | 8 oz | 0.5 lb |
| 46 | Entered | Aug 8 | 1 × BB2003 Oatmeal Chocolate Chip Batch, 1 × FG2200 Sugar Cookie Batch | 40.5 oz | 2.5 lbs |
| **Total competing** | | | | **48.5 oz (about 3 lbs)** | **3 lbs** |

What that does to your 40 batches at Main:

| Ingredient | On hand at Main | Your 40 batches | Other MOs | Result |
|---|---|---|---|---|
| Walnuts (H902) | 25 lbs | 20 lbs | 3 lbs | 2 lbs left over |
| Butter (BP203) | 0 | 320 oz | 48.5 oz | Transfer in 368.5 oz (about 23 lbs) to cover all three |

A few things worth knowing:
- **Both MOs are past their scheduled dates and still Entered**, so neither has been issued and nothing is allocated yet. Whichever order gets issued and picked first takes the stock.
- **MO 45 is itself one batch of BB2005.** If it's meant to be part of your 40, the real competition is only MO 46.
- **No open sales orders** are selling butter or walnuts directly, and nothing is on order for either at Main, so the butter has to come from LA, MI or SLC (3,453 oz each).

I didn't check for transfer orders already moving butter toward Main.

## 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. Ran saved query `bom_build_requirements`: Can we build 40 batches of chocolate chip cookies (BB2005) by Friday? What's short? — 11 rows, 184 ms

   ```sql
   SELECT b.num AS bomNum, p.num AS rawPart, p.description, bi.quantity AS qtyPerBuild, lu.code AS bomUom, @builds AS builds, bi.quantity * CASE WHEN bi.oneTimeItem = 1 THEN 1 ELSE @builds END AS qtyNeeded, ROUND(bi.quantity * CASE WHEN bi.oneTimeItem = 1 THEN 1 ELSE @builds END * CASE WHEN bi.uomId = p.uomId THEN 1 ELSE uc.multiply / uc.factor END, 4) AS qtyNeededStockUom, pu.code AS stockUom, ROUND(COALESCE(inv.onHand, 0), 4) AS qtyOnHand, ROUND(COALESCE(inv.available, 0), 4) AS qtyAvailable, ROUND(COALESCE(inv.onOrder, 0), 4) AS qtyOnOrder, ROUND(GREATEST(bi.quantity * CASE WHEN bi.oneTimeItem = 1 THEN 1 ELSE @builds END * CASE WHEN bi.uomId = p.uomId THEN 1 ELSE uc.multiply / uc.factor END - COALESCE(inv.available, 0), 0), 4) AS shortfall, CASE WHEN bi.oneTimeItem = 1 THEN NULL ELSE FLOOR(COALESCE(inv.available, 0) / NULLIF(bi.quantity * CASE WHEN bi.uomId = p.uomId THEN 1 ELSE uc.multiply / uc.factor END, 0)) END AS buildsCoveredByAvailable, bi.oneTimeItem + 0 AS oneTimeItem, bi.variableQty + 0 AS variableQty, bg.name AS optionGroup, bi.groupDefault + 0 AS isGroupDefault, sb.num AS subAssemblyBom
   FROM bom AS b
   JOIN bomitem AS bi ON bi.bomId = b.id AND bi.typeId = 20
   JOIN part AS p ON p.id = bi.partId
   JOIN uom AS pu ON pu.id = p.uomId
   LEFT JOIN uom AS lu ON lu.id = bi.uomId
   LEFT JOIN uomconversion AS uc ON uc.fromUomId = bi.uomId AND uc.toUomId = p.uomId
   LEFT JOIN bomitemgroup AS bg ON bg.id = bi.bomItemGroupId
   LEFT JOIN bom AS sb ON sb.id = bi.stageBomId AND bi.stage = 1
   LEFT JOIN (SELECT t.PARTID AS partId, SUM(t.QTYONHAND) AS onHand, SUM(t.QTYONHAND - t.QTYALLOCATED - t.QTYNOTAVAILABLE) AS available, SUM(t.QTYONORDER) AS onOrder
   FROM qtyinventorytotals AS t
   GROUP BY t.PARTID) AS inv ON inv.partId = p.id
   WHERE b.num = @bom_num AND (@part_num IS NULL OR p.num = @part_num)
   ORDER BY bi.sortIdConfig, bi.id
   LIMIT 101
   ```

4. Read Fishbowl notes (MANUFACTURING.md)
5. Ran a read-only query: Can we build 40 batches of BB2005 by Friday, what's short: check part type of Water and per-warehouse availability of each raw good — 33 rows, 250 ms

   ```sql
   SELECT p.num, pt.name AS partType, lg.name AS locationGroup, ROUND(t.QTYONHAND, 2) AS onHand, ROUND(t.QTYONHAND - t.QTYALLOCATED - t.QTYNOTAVAILABLE, 2) AS available, u.code AS uom
   FROM part AS p
   JOIN parttype AS pt ON pt.id = p.typeId
   JOIN uom AS u ON u.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 ('B201', 'BD301', 'BP203', 'C502', 'BP204', 'BD302', 'B202', 'Water', 'B204', 'H902', 'CR403')
   ORDER BY p.num, lg.name
   LIMIT 101
   ```

6. Ran a read-only query: Is anything else competing for butter (BP203) or walnuts (H902) at Main: open manufacture orders consuming them — 5 rows, 239 ms

   ```sql
   SELECT m.num AS moNum, ms.name AS moStatus, lg.name AS locationGroup, CAST(m.dateScheduled as CHAR) AS dateScheduled, pb.num AS bomNum, pmi.qtyToFulfill AS builds, p.num AS rawPart, mi.qtyToFulfill AS qtyNeeded, mi.qtyFulfilled AS qtyDone, u.code AS uom, mis.name AS lineStatus
   FROM mo AS m
   JOIN mostatus AS ms ON ms.id = m.statusId
   JOIN locationgroup AS lg ON lg.id = m.locationGroupId
   JOIN moitem AS mi ON mi.moId = m.id AND mi.typeId = 20
   JOIN moitemstatus AS mis ON mis.id = mi.statusId
   JOIN part AS p ON p.id = mi.partId
   LEFT JOIN uom AS u ON u.id = mi.uomId
   LEFT JOIN moitem AS pmi ON pmi.id = mi.parentId
   LEFT JOIN bom AS pb ON pb.id = pmi.bomId
   WHERE m.statusId IN (10, 20, 50) AND mi.statusId NOT IN (50, 60, 70) AND p.num IN ('BP203', 'H902')
   ORDER BY lg.name, m.dateScheduled, m.num
   LIMIT 101
   ```

7. Ran a read-only query: Is anything else competing for butter (BP203) or walnuts (H902) at Main: allocation and on-order breakdown per warehouse — 6 rows, 277 ms

   ```sql
   SELECT p.num, lg.name AS locationGroup, t.*
   FROM qtyinventorytotals AS t
   JOIN part AS p ON p.id = t.PARTID
   JOIN locationgroup AS lg ON lg.id = t.LOCATIONGROUPID
   WHERE p.num IN ('BP203', 'H902')
   ORDER BY p.num, lg.name
   LIMIT 101
   ```

8. Ran a read-only query: Is anything else competing for butter (BP203) or walnuts (H902) at Main: open sales orders selling these parts directly — 0 rows, 203 ms

   ```sql
   SELECT 'SO' AS src, so.num AS orderNum, ss.name AS status, lg.name AS locationGroup, p.num AS part, si.qtyToFulfill AS qtyOrdered, si.qtyFulfilled AS qtyDone, u.code AS uom, CAST(si.dateScheduledFulfillment as CHAR) AS dateScheduled
   FROM soitem AS si
   JOIN so ON so.id = si.soId
   JOIN sostatus AS ss ON ss.id = so.statusId
   JOIN locationgroup AS lg ON lg.id = so.locationGroupId
   JOIN product AS pr ON pr.id = si.productId
   JOIN part AS p ON p.id = pr.partId
   LEFT JOIN uom AS u ON u.id = si.uomId
   WHERE so.statusId IN (10, 20, 25) AND si.qtyToFulfill > si.qtyFulfilled AND p.num IN ('BP203', 'H902')
   ORDER BY lg.name, so.num
   LIMIT 101
   ```

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