01
About this report
Every item with quantity on hand across all locations whose last invoice, cash sale or item fulfillment is more than 90 days old, or that has never had one. Each row gives the on-hand quantity and value, the last sold or shipped date and the days since.
Sort by On-hand value to see where cash is tied up. Items marked Never have not gone out once. These are the candidates for a promotion, a return to the vendor or a write-down.
02
Sample preview
Fictional figures in the shape you will see. Yours come from your account.
| Item | Description | Item type | On hand | On-hand value | Last sold or shipped | Days since |
|---|---|---|---|---|---|---|
| PNL-OAK | Oak side panel | InvtPart | 420 | 18,480 | 2026-03-02 | 206 |
| BRK-25 | Steel bracket 25mm | InvtPart | 2,600 | 11,960 | 2026-05-19 | 128 |
| DSP-7 | 7-inch display, legacy | InvtPart | 95 | 7,125 | Never | |
| CBL-3M | Cable 3m | InvtPart | 800 | 1,280 | 2026-06-10 | 106 |
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,
i.itemtype AS item_type,
oh.on_hand,
oh.value,
NVL(TO_CHAR(lo.last_out, 'YYYY-MM-DD'), 'Never') AS last_outbound,
TRUNC(SYSDATE) - TRUNC(lo.last_out) AS days_since
FROM (SELECT a.item, SUM(a.quantityonhand) AS on_hand, SUM(a.onhandvaluemli) AS value
FROM aggregateitemlocation a
GROUP BY a.item
HAVING SUM(a.quantityonhand) > 0) oh
JOIN item i ON i.id = oh.item
LEFT JOIN (SELECT tl.item, MAX(t.trandate) AS last_out
FROM transactionline tl
JOIN transaction t ON t.id = tl.transaction
WHERE t.type IN ('CustInvc', 'CashSale', 'ItemShip')
AND tl.mainline = 'F'
GROUP BY tl.item) lo ON lo.item = oh.item
WHERE lo.last_out IS NULL
OR lo.last_out < TRUNC(SYSDATE) - 90
ORDER BY oh.value DESC04