SuiteQL transaction queries: a working cookbook

These are SuiteQL transaction queries in the form I actually keep in a scratch file: short, filtered, and honest about what they assume. Every one runs against the standard analytics tables — transaction, transactionline, transactionaccountingline, account, accountingperiod — through the SuiteTalk REST query/v1/suiteql endpoint, query.runSuiteQL() in SuiteScript, or a Suitelet query tool. Column availability varies with the features your account has enabled, so confirm anything unfamiliar in the Records Catalog before you wire it into a script.

The shape SuiteQL transaction queries share

Transactions are stored as a header row in transaction and one row per line in transactionline. Three filters decide whether your numbers are right:

• `type` — a short code, not a word. CustInvc is an invoice, SalesOrd a sales order, PurchOrd a purchase order.
• `mainline` — 'T' on the header line that carries the document total, 'F' on the item lines. Join lines without this filter and you add the header total to the sum of the lines.
• `taxline` — 'T' on tax summary lines. Exclude them from item-level maths.

One habit before the recipes: every literal date below should be a ? placeholder in production. In N/query you pass params alongside the statement, and the placeholders are positional, so the array order has to match the order the ? marks appear. That is not only about injection — a parameterised query is one string you can log, diff and reuse for every period, instead of a new string built by concatenation each run.

Second habit: never guess a code. Transaction types and statuses are short internal codes, they vary in how they are used between accounts, and a wrong code returns zero rows rather than an error. Start every unfamiliar query with a discovery pass.

SELECT
    t.type,
    MAX(BUILTIN.DF(t.type))     AS type_label,
    COUNT(*)                    AS transactions
FROM transaction t
WHERE t.trandate >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
GROUP BY t.type
ORDER BY COUNT(*) DESC

Swap t.type for t.status with a WHERE t.type = 'SalesOrd' filter and you get the status codes for that document type with their labels. Do this once per account rather than guessing status codes from a blog post — including this one.

Recipe 1 — AR aging by customer

Returns one row per customer with the open balance split into standard aging buckets.

SELECT
    BUILTIN.DF(t.entity)  AS customer,
    SUM(t.foreignamountunpaid) AS open_total,
    SUM(CASE WHEN TO_DATE('2026-06-30','YYYY-MM-DD') - t.duedate <= 0
             THEN t.foreignamountunpaid ELSE 0 END) AS current_amt,
    SUM(CASE WHEN TO_DATE('2026-06-30','YYYY-MM-DD') - t.duedate BETWEEN 1 AND 30
             THEN t.foreignamountunpaid ELSE 0 END) AS d1_30,
    SUM(CASE WHEN TO_DATE('2026-06-30','YYYY-MM-DD') - t.duedate BETWEEN 31 AND 60
             THEN t.foreignamountunpaid ELSE 0 END) AS d31_60,
    SUM(CASE WHEN TO_DATE('2026-06-30','YYYY-MM-DD') - t.duedate BETWEEN 61 AND 90
             THEN t.foreignamountunpaid ELSE 0 END) AS d61_90,
    SUM(CASE WHEN TO_DATE('2026-06-30','YYYY-MM-DD') - t.duedate > 90
             THEN t.foreignamountunpaid ELSE 0 END) AS d90_plus
FROM transaction t
WHERE t.type = 'CustInvc'
  AND t.foreignamountunpaid > 0
  AND t.trandate <= TO_DATE('2026-06-30','YYYY-MM-DD')
GROUP BY BUILTIN.DF(t.entity)
ORDER BY 2 DESC

Assumes foreignamountunpaid is what you want: the open amount in the transaction currency. Multiply by t.exchangerate for a base-currency figure, and understand that is the transaction-date rate, not a period-end revaluation rate.

Recipe 2 — top 10 customers by open balance

Returns the ten largest open balances, including credit memos.

SELECT * FROM (
    SELECT
        BUILTIN.DF(t.entity)        AS customer,
        SUM(t.foreignamountunpaid)  AS open_balance
    FROM transaction t
    WHERE t.type IN ('CustInvc', 'CustCred')
      AND t.foreignamountunpaid <> 0
    GROUP BY BUILTIN.DF(t.entity)
    ORDER BY SUM(t.foreignamountunpaid) DESC
)
WHERE ROWNUM <= 10

Assumes you want the subquery wrapper — and you do. ROWNUM is assigned before ORDER BY runs, so WHERE ROWNUM <= 10 ORDER BY x DESC gives you ten arbitrary rows neatly sorted. Support for OFFSET/FETCH FIRST is inconsistent enough that the subquery form is the one worth memorising. Also check the sign convention on CustCred for one customer before publishing: unapplied credit memos need to reduce the balance, and whether they do depends on how the column is populated in your account.

Recipe 3 — open sales order lines

Returns every order line with quantity still to ship, oldest order first.

SELECT
    t.tranid                                      AS order_no,
    t.trandate,
    BUILTIN.DF(t.entity)                          AS customer,
    BUILTIN.DF(t.status)                          AS status,
    BUILTIN.DF(tl.item)                           AS item,
    tl.quantity                                   AS qty_ordered,
    NVL(tl.quantityshiprecv, 0)                   AS qty_shipped,
    tl.quantity - NVL(tl.quantityshiprecv, 0)     AS qty_open,
    tl.rate,
    (tl.quantity - NVL(tl.quantityshiprecv, 0)) * tl.rate AS open_value
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type       = 'SalesOrd'
  AND tl.mainline  = 'F'
  AND tl.taxline   = 'F'
  AND NVL(tl.isclosed, 'F') = 'F'
  AND tl.quantity - NVL(tl.quantityshiprecv, 0) > 0
ORDER BY t.trandate

Assumes "open" means quantity not yet fulfilled and the line not closed. That is one of at least three defensible definitions — status-based, quantity-based, and billing-based all give different totals, which is the whole subject of the NetSuite open sales orders report. Quantity sign conventions differ by transaction type in this table, so run it for one known order and compare against the record before you trust the aggregate. If you are turning this into a service-level number, the fill rate calculator does the line/order/unit arithmetic.

Recipe 4 — revenue by customer, GL-accurate

Returns posted revenue per customer for a period range, tying to the income statement.

SELECT
    BUILTIN.DF(t.entity)                          AS customer,
    SUM(NVL(tal.credit, 0) - NVL(tal.debit, 0))   AS revenue
FROM transactionaccountingline tal
JOIN transaction      t  ON t.id  = tal.transaction
JOIN account          a  ON a.id  = tal.account
JOIN accountingperiod ap ON ap.id = t.postingperiod
WHERE tal.posting = 'T'
  AND a.accttype  = 'Income'
  AND ap.startdate >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
  AND ap.enddate   <= TO_DATE('2026-06-30', 'YYYY-MM-DD')
GROUP BY BUILTIN.DF(t.entity)
ORDER BY 2 DESC

Assumes you want revenue as the GL sees it. Income accounts carry credit balances, so credit - debit gives a positive figure and credit memos net themselves off automatically. If Multi-Book Accounting is enabled, add an accounting book filter or every number doubles. Filtering on accountingperiod rather than trandate is deliberate: a transaction dated 31 March can post to April, and only the period join gives you the answer the financials will agree with.

Recipe 5 — purchase order status, three-way

Returns PO lines with ordered, received and billed quantities side by side.

SELECT
    t.tranid                                    AS po_no,
    BUILTIN.DF(t.entity)                        AS vendor,
    t.trandate,
    BUILTIN.DF(t.status)                        AS status,
    BUILTIN.DF(tl.item)                         AS item,
    tl.quantity                                 AS qty_ordered,
    NVL(tl.quantityshiprecv, 0)                 AS qty_received,
    NVL(tl.quantitybilled, 0)                   AS qty_billed,
    tl.quantity - NVL(tl.quantityshiprecv, 0)   AS qty_not_received,
    NVL(tl.quantityshiprecv, 0) - NVL(tl.quantitybilled, 0) AS received_not_billed
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type       = 'PurchOrd'
  AND tl.mainline  = 'F'
  AND tl.taxline   = 'F'
  AND NVL(tl.isclosed, 'F') = 'F'
  AND (   tl.quantity - NVL(tl.quantityshiprecv, 0) > 0
       OR NVL(tl.quantityshiprecv, 0) - NVL(tl.quantitybilled, 0) > 0 )
ORDER BY t.trandate

Assumes quantityshiprecv and quantitybilled are populated for your line types — availability varies with the features in play, and drop-ship or special-order lines behave differently. The received_not_billed column is the accrual conversation with your controller, so reconcile it to the unbilled receipts account before quoting it.

Recipe 6 — item margin from posted lines

Returns revenue, COGS and margin percentage per item for a period range.

SELECT
    item,
    revenue,
    cogs,
    revenue - cogs                                  AS gross_profit,
    ROUND((revenue - cogs) / NULLIF(revenue, 0) * 100, 1) AS margin_pct
FROM (
    SELECT
        BUILTIN.DF(tl.item) AS item,
        SUM(CASE WHEN a.accttype = 'Income'
                 THEN NVL(tal.credit, 0) - NVL(tal.debit, 0) ELSE 0 END) AS revenue,
        SUM(CASE WHEN a.accttype = 'COGS'
                 THEN NVL(tal.debit, 0) - NVL(tal.credit, 0) ELSE 0 END) AS cogs
    FROM transactionaccountingline tal
    JOIN transaction      t  ON t.id  = tal.transaction
    JOIN transactionline  tl ON tl.transaction = tal.transaction
                            AND tl.id = tal.transactionline
    JOIN account          a  ON a.id  = tal.account
    JOIN accountingperiod ap ON ap.id = t.postingperiod
    WHERE tal.posting = 'T'
      AND a.accttype IN ('Income', 'COGS')
      AND tl.item IS NOT NULL
      AND ap.startdate >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
      AND ap.enddate   <= TO_DATE('2026-06-30', 'YYYY-MM-DD')
    GROUP BY BUILTIN.DF(tl.item)
)
ORDER BY gross_profit DESC

Assumes revenue and COGS for the same unit land in the same period. They often do not — COGS posts on fulfilment, revenue on invoicing, and a month-end split between the two skews single-period item margin. NULLIF(revenue, 0) is there so a returns-only item does not divide by zero. Landed cost sitting in a different account is the other usual gap; that whole reconciliation is the NetSuite item profitability report.

Recipe 7 — inventory value on hand, per the GL

Returns the cumulative balance of your inventory asset accounts as of a period end.

SELECT
    BUILTIN.DF(tal.account)                       AS account,
    SUM(NVL(tal.debit, 0) - NVL(tal.credit, 0))   AS balance
FROM transactionaccountingline tal
JOIN transaction      t  ON t.id  = tal.transaction
JOIN accountingperiod ap ON ap.id = t.postingperiod
WHERE tal.posting = 'T'
  AND tal.account IN (145, 146)          -- your inventory asset accounts
  AND ap.enddate <= TO_DATE('2026-06-30', 'YYYY-MM-DD')
GROUP BY BUILTIN.DF(tal.account)
ORDER BY 2 DESC

Assumes you list the account internal ids explicitly. NetSuite classes inventory accounts as Other Current Asset, so there is no account type that isolates them. Note this gives value, not quantity: the per-item and per-location quantity balances live in tables whose names and columns depend on which inventory features are switched on, so look yours up in the Records Catalog rather than copying a table name from anywhere. Balance sheet accounts accumulate from inception, which is why the period filter is an upper bound with no lower bound.

Recipe 8 — incremental extract with a keyset cursor

Returns a stable page of changed transactions for an integration to consume.

SELECT
    t.id,
    t.tranid,
    t.type,
    t.trandate,
    t.lastmodifieddate,
    BUILTIN.DF(t.entity) AS entity
FROM transaction t
WHERE t.lastmodifieddate >= TO_DATE('2026-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
  AND t.id > 0                     -- replace 0 with the last id from the previous page
ORDER BY t.id

Assumes ids are monotonic enough to page on, which they are for this purpose. Keyset paging stays fast at page 400 where a growing OFFSET does not. lastmodifieddate is a datetime in the account's timezone, so an integration running in UTC needs to shift its watermark or it will silently skip records. In N/query, swap the literals for ? placeholders and pass them in params — never build the string by concatenation.

Currency and subsidiary, in one paragraph

Two dimensions quietly change every money figure above. Amount columns prefixed foreign are stated in the transaction's currency, so summing them across a customer base that trades in three currencies produces a number with no meaning. transaction.exchangerate converts to base currency at the transaction-date rate, which is right for a revenue figure and wrong for a period-end balance sheet position, because it applies no revaluation. If either of those matters, aggregate from transactionaccountingline instead, where the amounts are already in the book's currency.

Subsidiary is the other one. In a OneWorld account the segment lives on the line, so a header-only query cannot split by subsidiary and a query that joins lines has to be careful not to double-count the header. Add tl.subsidiary to the GROUP BY before anyone asks for the split, because retrofitting it usually means rewriting the aggregate.

Before you trust any of these

1. 1.Run it for one document you can open in the UI. Check the sign, the quantity and the currency against the record.
2. 2.Run it as the role that will run it in production. Permissions filter rows silently.
3. 3.Tie one money figure to a standard report or the trial balance. If it does not tie, the filter set is wrong, not the report.
4. 4.Add COUNT(*) first on anything unbounded, so you find out it is 2.1 million rows before the request times out. More on that in SuiteQL performance tips.

Joins are where most of these queries get extended — a subsidiary here, a custom field there. SuiteQL join examples covers the join paths worth learning by heart.

The uncomfortable truth about a cookbook is that the query you need is always the tenth one. ERPray exists to close that gap: ask "open order value by customer for orders more than 14 days old" in plain English and get the number computed live from your own account, with the SuiteQL printed underneath so you can check the mainline filter yourself before anyone puts it in a board pack.