Purpose of this query
This SuiteQL statement produces one summary row showing total open Accounts Receivable and the amounts in Current, 1–30, 31–60, 61–90, and 90+ day buckets. It queries the Transaction table directly, joining to Customer to restrict results to customer invoices only. The query works across all NetSuite account types: single subsidiary, OneWorld multi-subsidiary, single currency, and multi-currency. Currency conversion uses the exchange rate stored on each transaction, matching NetSuite's native AR Aging methodology. Use this for dashboard KPI tiles, executive summaries, and month-end snapshot reporting.
SuiteQL
-- AR Aging Total Summary (Single Row)
-- Returns one row with total open AR split into aging buckets.
-- Default: root (top-level) subsidiary only. This matches NetSuite's native AR Aging
-- when run with Consolidated subsidiary context and excludes intercompany balances.
-- For OneWorld accounts with AR posted across child subsidiaries, see options below.SELECT
SUM( open_base ) AS "Total Open AR",
SUM( CASE WHEN FLOOR( CURRENT_DATE - due_date ) < 1
THEN open_base ELSE 0 END ) AS "Current",
SUM( CASE WHEN FLOOR( CURRENT_DATE - due_date ) BETWEEN 1 AND 30
THEN open_base ELSE 0 END ) AS "1-30 Days",
SUM( CASE WHEN FLOOR( CURRENT_DATE - due_date ) BETWEEN 31 AND 60
THEN open_base ELSE 0 END ) AS "31-60 Days",
SUM( CASE WHEN FLOOR( CURRENT_DATE - due_date ) BETWEEN 61 AND 90
THEN open_base ELSE 0 END ) AS "61-90 Days",
SUM( CASE WHEN FLOOR( CURRENT_DATE - due_date ) > 90
THEN open_base ELSE 0 END ) AS "90+ Days"
FROM (
SELECT
COALESCE( t.duedate, t.trandate ) AS due_date,
t.foreignamountunpaid * COALESCE( t.exchangerate, 1 ) AS open_base
FROM Transaction t
JOIN Customer c ON ( c.id = t.entity )
WHERE t.type = 'CustInvc'
AND t.foreignamountunpaid IS NOT NULL
AND t.foreignamountunpaid <> 0
AND t.voided = 'F'
-- SUBSIDIARY FILTER (default: root subsidiary only):
-- Restricts to invoices posted directly to the top-level subsidiary.
-- This excludes intercompany balances and matches NetSuite's consolidated view.
-- NOTE: if this query returns no data in a OneWorld account, your invoices are
-- likely posted to child subsidiaries — remove this line and use Option 1 below.
AND t.subsidiary = ( SELECT id FROM Subsidiary WHERE parent IS NULL )
--
-- SUBSIDIARY OPTIONS — uncomment one of the following to change scope:
--
-- Option 1: All subsidiaries (single-subsidiary accounts or when you want
-- complete AR across all entities). Note: in OneWorld accounts
-- this may include intercompany customers and mixed currencies.
-- (remove or comment out the AND t.subsidiary line above)
--
-- Option 2: Specific subsidiary — replace 0 with the subsidiary internal ID.
-- Run: SELECT id, name FROM Subsidiary ORDER BY id to find IDs.
-- AND t.subsidiary = 0
)Sample Output
How the query works
Open invoices only
The inner query filters to transaction type CustInvc where foreignamountunpaid is not null and not zero. The JOIN to Customer restricts results to customer transactions only. Fully paid invoices are excluded automatically without any additional filtering.
Currency conversion using the transaction exchange rate
open_base is calculated as foreignamountunpaid multiplied by the exchange rate stored on the transaction at invoice creation. COALESCE(t.exchangerate, 1) handles single-currency accounts where the rate is null or 1, making the query portable across all account types.
Matches NetSuite's native AR Aging methodology
NetSuite's AR Aging report converts foreign currency balances using the exchange rate on the transaction, not a current or period-end rate. This query uses the same approach so results align with the native report. Unrealized FX gains and losses appear as separate revaluation transactions and are not included here, consistent with NetSuite's standard behavior.
Due date fallback for invoices without terms
COALESCE(t.duedate, t.trandate) uses the transaction date for invoices with no payment terms, placing them in the Current bucket rather than dropping them from the report.
FLOOR for stable bucket assignment
FLOOR(CURRENT_DATE - due_date) converts fractional day differences into whole days before evaluating ranges, preventing off-by-one issues regardless of when the query runs.
Root subsidiary default
The default subsidiary filter restricts to invoices posted to the top-level (root) subsidiary — the one with no parent. This matches NetSuite's native AR Aging when run with Consolidated subsidiary context and excludes intercompany balances. For single-subsidiary accounts the filter is a no-op. For OneWorld accounts with AR spread across child subsidiaries, the query will return no data — in that case remove the subsidiary filter line and use Option 1 instead.
Single summary row
The outer query sums open_base across all rows and distributes the total into each aging bucket using CASE expressions, returning one row with Total Open AR and each bucket column.
Customization notes
Subsidiary: the default uses the root subsidiary. If the query returns no data in a OneWorld account, your invoices are likely posted to child subsidiaries — remove the AND t.subsidiary line to include all subsidiaries. To target a specific subsidiary, replace the subquery with a hardcoded ID. Run SELECT id, name FROM Subsidiary ORDER BY id to find IDs.
As-of date: replace CURRENT_DATE with TO_DATE('YYYY-MM-DD','YYYY-MM-DD') for historical aging.
Aging buckets: modify the FLOOR(…) ranges in the CASE expressions to match your business terms.
Transaction types: includes only CustInvc. To add credit memos or unapplied payments, change or remove the t.type filter and adjust the balance calculation accordingly.
Currency display: results are in each subsidiary's base currency at transaction-date rates, consistent with NetSuite's native behavior.