Purpose of this query
This SuiteQL statement merges inventory balances with transactional sales activity to provide a unified inventory and sales performance view. It includes open Sales Orders (demand), Cash Sales (realized revenue), and standalone Invoices not linked to Sales Orders, while excluding invoices generated from Sales Orders to prevent double counting. The query is designed to be OneWorld-safe and avoids inflation of quantities by using careful transaction filters. It returns item, location, subsidiary, current inventory balances, total quantity sold, and rolling 30-day and 90-day sales metrics. This is ideal for demand planning, replenishment modeling, sales velocity analysis, and executive dashboards in Excel.
SuiteQL
/*==============================
Inventory Status + Full Sales Picture
Includes:
- Sales Orders (open demand)
- Cash Sales (realized)
- Invoices not linked to Sales Orders
Excludes:
- Invoices created from Sales Orders
OneWorld-safe / No-inflation
==============================*/
SELECT
l.subsidiary AS subsidiary_id,
i.id AS item_id,
i.itemid AS item_name,
i.displayname AS display_name,
l.id AS location_id,
l.name AS location_name,
/* Inventory balances */
MAX(ib.quantityonhand) AS qty_on_hand,
MAX(ib.quantityavailable) AS qty_available,
/* Combined sales demand + fulfillment */
SUM(
CASE
WHEN t.type = 'SalesOrd' THEN (tl.quantity * -1)
WHEN t.type = 'CashSale' THEN (tl.quantity * -1)
WHEN t.type = 'CustInvc' AND tl.createdfrom IS NULL THEN (tl.quantity * -1)
ELSE 0
END
) AS qty_sold_total,
/* Last 30 days */
SUM(
CASE
WHEN t.type IN ('SalesOrd','CashSale')
AND t.trandate >= ADD_MONTHS(CURRENT_DATE, -1)
THEN (tl.quantity * -1)
WHEN t.type = 'CustInvc'
AND tl.createdfrom IS NULL
AND t.trandate >= ADD_MONTHS(CURRENT_DATE, -1)
THEN (tl.quantity * -1)
ELSE 0
END
) AS qty_sold_30d,
/* Last 90 days */
SUM(
CASE
WHEN t.type IN ('SalesOrd','CashSale')
AND t.trandate >= ADD_MONTHS(CURRENT_DATE, -3)
THEN (tl.quantity * -1)
WHEN t.type = 'CustInvc'
AND tl.createdfrom IS NULL
AND t.trandate >= ADD_MONTHS(CURRENT_DATE, -3)
THEN (tl.quantity * -1)
ELSE 0
END
) AS qty_sold_90d
FROM
inventorybalance ib
JOIN item i
ON i.id = ib.item
LEFT JOIN location l
ON l.id = ib.location
LEFT JOIN transactionline tl
ON tl.item = i.id
AND tl.location = l.id
LEFT JOIN transaction t
ON t.id = tl.transaction
GROUP BY
l.subsidiary, i.id, i.itemid, i.displayname, l.id, l.name
ORDER BY
i.itemid, l.name;
Sample Output
How the query works
Pulling real-time inventory balances
The query starts from inventorybalance to retrieve quantity on hand and quantity available by item and location. MAX is used to prevent duplication from transactional joins.
Including open demand and realized sales
Sales Orders represent open demand. Cash Sales represent immediate fulfillment. Invoices are included only if they are not created from a Sales Order, preventing double counting.
Preventing double counting
The condition tl.createdfrom IS NULL ensures invoices generated from Sales Orders are excluded, since the Sales Order demand has already been counted.
Handling quantity direction
Quantities are multiplied by -1 because sales transactions store quantities as negative inventory movements. This standardizes output to positive sold quantities.
Rolling 30-day and 90-day sales velocity
The query calculates recent sales using ADD_MONTHS(CURRENT_DATE, -1) and ADD_MONTHS(CURRENT_DATE, -3) to support trend analysis and replenishment planning.
OneWorld safe design
The query groups by subsidiary and location, making it compatible with multi-subsidiary environments without inflating totals.
Why this report is useful
• Demand planning: compare quantity available with open sales demand.
• Replenishment decisions: identify fast-moving items using 30-day and 90-day velocity.
• Sales performance analysis: see total demand plus realized sales in one dataset.
• Inventory risk management: spot items with declining availability and high recent sales.
• Executive dashboards: export to Excel and build pivot tables by subsidiary, location, or item category.
Customization notes
• Add item filters: add WHERE i.itemtype IN (‘InvtPart’) if you want only inventory items.
• Add date filters: adjust the rolling windows if you prefer fixed reporting periods.
• Add class, department, or location filtering for operational segmentation.
• Add BUILTIN.CONSOLIDATE if you need currency-based reporting alongside quantity metrics.
• Add average daily sales calculations in Excel using qty_sold_30d / 30.