dataexport: 20
This data as json
| id | dataQuery | dateCreated | dateLastModified | description | lastChangedUserId | name |
|---|---|---|---|---|---|---|
| 20 | SELECT lg.name AS `Location Group`, p.num AS `Part Number`, p.description AS `Part Description`, /* Reorder levels (will be NULL when No ROP/OUL is true) */ pr.reorderPoint AS `Reorder Point`, pr.orderUpToLevel AS `Order Up To`, /* On Hand per Forecast definition: OnHand subtracts Not Available */ (COALESCE(qi.qtyOnHand, 0) - COALESCE(qi.qtyNotAvailable, 0)) AS `On Hand`, -- [1](https://help.fishbowlinventory.com/resource/advancedreports)[4](https://fishbowlhelp.com/files/database/tables/qtyinventory.html) /* On Order = totals across sources (PO + SO + TO + MO) */ (COALESCE(qi.qtyOnOrderPO, 0) + COALESCE(qi.qtyOnOrderSO, 0) + COALESCE(qi.qtyOnOrderTO, 0) + COALESCE(qi.qtyOnOrderMO, 0) ) AS `On Order`, -- [4](https://fishbowlhelp.com/files/database/tables/qtyinventory.html) /* Allocated = totals across sources (PO + SO + TO + MO) */ (COALESCE(qi.qtyAllocatedPO, 0) + COALESCE(qi.qtyAllocatedSO, 0) + COALESCE(qi.qtyAllocatedTO, 0) + COALESCE(qi.qtyAllocatedMO, 0) ) AS `Allocated`, -- [4](https://fishbowlhelp.com/files/database/tables/qtyinventory.html) /* Shortage Qty (Include No ROP/OUL): Needed = OrderUpTo - OnOrder - OnHand + Allocated Shortage = max(Needed, 0) For No ROP/OUL parts, OrderUpTo is treated as 0 via COALESCE. */ GREATEST( ( COALESCE(pr.orderUpToLevel, 0) - (COALESCE(qi.qtyOnOrderPO, 0) + COALESCE(qi.qtyOnOrderSO, 0) + COALESCE(qi.qtyOnOrderTO, 0) + COALESCE(qi.qtyOnOrderMO, 0) ) - (COALESCE(qi.qtyOnHand, 0) - COALESCE(qi.qtyNotAvailable, 0)) + (COALESCE(qi.qtyAllocatedPO, 0) + COALESCE(qi.qtyAllocatedSO, 0) + COALESCE(qi.qtyAllocatedTO, 0) + COALESCE(qi.qtyAllocatedMO, 0) ) ), 0 ) AS `Shortage Qty` -- [1](https://help.fishbowlinventory.com/resource/advancedreports) FROM qtyinventory qi JOIN part p ON p.id = qi.partId JOIN locationgroup lg ON lg.id = qi.locationGroupId /* LEFT JOIN is what “includes No ROP/OUL” rows */ LEFT JOIN partreorder pr ON pr.partId = p.id AND pr.locationGroupId = lg.id -- [2](https://fishbowlhelp.com/files/database/tables/partreorder.html)[4](https://fishbowlhelp.com/files/database/tables/qtyinventory.html) WHERE /* Only include parts that do NOT have a BOM */ (p.defaultBomId IS NULL OR p.defaultBomId = 0) -- [5](https://fishbowlhelp.com/files/database/tables/bom.html) ORDER BY lg.name, p.num | 2026-02-24 16:00:52.049000 | 2026-02-24 16:00:52.049000 | Below is the updated query (NO BOM parts only) with Shortage Qty and No ROP/OUL included. | 17 | Shortage Report |