Purpose of this query
This SuiteQL statement returns detailed Accounts Receivable aging at the transaction level, with one row per open invoice. It includes the sales representative recorded on the invoice at the time it was created — not the current rep on the customer record — giving you a true point-in-time view of who owned the sale. 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 collections prioritization, rep-level AR reviews, credit risk analysis, and Excel dashboards.
SuiteQL
-- AR Aging Detail by Transaction with Sales Rep
-- Returns one row per open invoice with customer, transaction details,
-- days past due, aging bucket, and the sales rep stamped on the invoice at creation.
-- 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
c.companyName AS "Customer Name",
BUILTIN.DF( t.employee ) AS "Sales Rep",
t.tranid AS "Transaction ID",
t.type AS "Type",
t.trandate AS "Transaction Date",
COALESCE( t.duedate, t.trandate ) AS "Due Date",
FLOOR( CURRENT_DATE - COALESCE( t.duedate, t.trandate ) ) AS "Days Past Due",
t.foreignamountunpaid * COALESCE( t.exchangerate, 1 ) AS "Net Amount",
CASE
WHEN FLOOR( CURRENT_DATE - COALESCE( t.duedate, t.trandate ) ) < 1 THEN 'Current'
WHEN FLOOR( CURRENT_DATE - COALESCE( t.duedate, t.trandate ) ) BETWEEN 1 AND 30 THEN '1-30 Days'
WHEN FLOOR( CURRENT_DATE - COALESCE( t.duedate, t.trandate ) ) BETWEEN 31 AND 60 THEN '31-60 Days'
WHEN FLOOR( CURRENT_DATE - COALESCE( t.duedate, t.trandate ) ) BETWEEN 61 AND 90 THEN '61-90 Days'
ELSE '90+ Days'
END AS "Aging Bucket"
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
ORDER BY c.companyName, COALESCE( t.duedate, t.trandate )Sample Output
How the query works
Here is a plain-English breakdown of how this AR aging detail query operates.
Open invoices only
Filters to CustInvc where foreignamountunpaid is not null and not zero. Returns one row per invoice with a remaining balance, excluding fully paid invoices automatically.
Currency conversion using the transaction exchange rate
Net Amount is 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.
Point-in-time sales rep
BUILTIN.DF(t.employee) returns the rep stamped on the invoice header when it was created. This will not change if the rep is later updated on the customer record. If t.employee is null on some invoices, the rep was not set at transaction time; use BUILTIN.DF(c.salesrep) as a fallback to pull the current rep from the customer record.
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.
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.
Why sales rep matters
Including the point-in-time rep lets finance and collections assign follow-up to the rep who owned the sale, identify reps with slow-paying customers, tie AR risk to sales performance, and build dashboards showing aging by rep alongside sales KPIs.
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.
Sales rep fallback: if BUILTIN.DF(t.employee) returns null, replace with BUILTIN.DF(c.salesrep) to use the current rep on the customer record.
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.