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

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 · MANUFACTURING.md
4. 4
   Looked up a table · mo
   16 columns
5. 5
   Looked up a table · wo
   23 columns
6. 6
   Looked up a table · woitem
   14 columns
7. 7
   Looked up a table · moitem
   26 columns
8. 8
   List open manufacture orders with their BOMs, build quantities and work order status

   ✓ passed read-only checkrow cap applied

   Show the 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

   8 rows · 237 ms
9. 9
   For each open manufacture order, list finished goods and raw goods still outstanding with stock availability in the MO's location group

   ✓ passed read-only checkrow cap applied

   Show the 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

   53 rows · 315 ms
10. 10
    Check stock in every location group and part type for the raw goods that show zero stock in Main

    ✓ passed read-only checkrow cap applied

    Show the 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

    12 rows · 264 ms