01
About this report
Every active item and location with a reorder point set, where available stock is at or below it. Each row gives on hand, available, on order, the reorder point, the preferred stock level and a suggested order. The suggested order is the preferred stock level, or the reorder point when no preferred level is set, less what is available and already on order.
A suggested order of zero means enough is already on order. Filter on Location for one buyer's list.
02
Sample preview
Fictional figures in the shape you will see. Yours come from your account.
| Item | Description | Location | On hand | Available | On order | Reorder point | Preferred level | Suggested order |
|---|---|---|---|---|---|---|---|---|
| CTL-V3 | Controller board v3 | Main Warehouse | 42 | 2 | 120 | 50 | 200 | 78 |
| HNG-KIT | Hinge kit | Main Warehouse | 0 | -150 | 750 | 100 | 600 | 0 |
| CRT-L | Shipping carton, large | West Distribution Center | 180 | 180 | 0 | 250 | 1,000 | 820 |
03
The SuiteQL
This is the query each report runs. Read it, copy it, or change it after import.
SELECT i.itemid AS item,
i.displayname AS description,
BUILTIN.DF(a.location) AS location,
a.quantityonhand AS on_hand,
a.quantityavailable AS available,
a.quantityonorder AS on_order,
a.reorderpoint AS reorder_point,
a.preferredstocklevel AS preferred_level,
GREATEST(NVL(a.preferredstocklevel, a.reorderpoint) - NVL(a.quantityavailable, 0) - NVL(a.quantityonorder, 0), 0) AS suggested_qty
FROM aggregateitemlocation a
JOIN item i ON i.id = a.item
WHERE i.isinactive = 'F'
AND a.reorderpoint > 0
AND NVL(a.quantityavailable, 0) <= a.reorderpoint
ORDER BY location, i.itemid04