Purpose of this query
This SuiteQL statement calculates open Accounts Receivable by customer and breaks balances into standard aging buckets based on due date. It queries the Transaction table directly, joining to Customer for the company name. The query works across all NetSuite account types: single subsidiary, OneWorld multi-subsidiary, single currency, and multi-currency. Currency conversion to the subsidiary's base currency is handled automatically using the exchange rate stored on each transaction, which is the same methodology NetSuite uses in its native AR Aging report. This format is ideal for Excel dashboards, AR reviews, credit risk analysis, and month-end reporting.
-- AR Aging Summary by Customer
-- Returns one row per customer with open invoice balances 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
customer_name,
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
c.companyName AS customer_name,
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
)
GROUP BY customer_name
ORDER BY SUM( open_base ) DESCSample Output
How the query works
Here is a plain-English breakdown of how this AR aging summary operates.
Open invoices only
The inner query filters to transaction type CustInvc (customer invoices) where foreignamountunpaid is not null and not zero. This returns only invoices with a remaining balance, automatically excluding fully paid invoices 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. NetSuite records the exchange rate between the invoice currency and the subsidiary's base currency at the time the invoice was created. COALESCE(t.exchangerate, 1) handles single-currency accounts where the rate is null or 1, making the query portable across all account types without any additional currency setup.
Matches NetSuite's native AR Aging methodology
NetSuite's own AR Aging report converts foreign currency balances to base currency using the exchange rate stored on the transaction — not a current or period-end rate. This query uses the same approach, so results align with the native report. Unrealized foreign exchange gains or losses appear as separate revaluation transactions in NetSuite 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 as the due date for any invoice that has no payment terms set. This prevents those invoices from being excluded and places them in the Current bucket rather than being dropped from the report.
FLOOR for stable bucket assignment
FLOOR(CURRENT_DATE - due_date) converts fractional day differences into whole days before evaluating bucket ranges. This prevents off-by-one issues caused by time-of-day differences and keeps bucket assignment consistent regardless of when the query is run.
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 since all invoices are on the only subsidiary. 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.
One row per customer
The outer query groups by customer name and sums open_base into each aging bucket, producing a single summary row per customer sorted by largest total open balance descending.
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.
Aging logic: adjust bucket ranges in the CASE expressions to match your business terms.
As-of date: replace CURRENT_DATE with TO_DATE('YYYY-MM-DD','YYYY-MM-DD') to run historical aging as of a specific date.
Transaction types: this query includes only CustInvc. To include credit memos or unapplied payments, change or remove the t.type filter and adjust the open balance calculation accordingly.
Currency display: results are in each subsidiary's base currency, converted at transaction-date rates, consistent with NetSuite's native behavior.