Purpose of this query
This SuiteQL statement returns detailed Accounts Receivable aging at the transaction level and includes the sales rep for each transaction. It matches NetSuite’s native A/R Aging logic while using FLOOR to ensure correct bucket assignment. Adding sales rep lets you monitor collector performance, link overdue amounts to responsible reps for follow up, and combine credit control with sales performance analysis. The query uses a two-leg UNION ALL pattern so invoices, payments, credits, and AR journal entries are all included. Change subsidiary, accounting book, or replace CURRENT_DATE with a fixed as-of date in the SQL comments.
SuiteQL
SELECT
"Customer Name",
"Transaction ID",
"Type",
"Transaction Date",
"Due Date",
FLOOR( CURRENT_DATE - "Due Date" ) AS "Days Past Due",
"Net Amount",
"Sales Rep",
CASE
WHEN FLOOR( CURRENT_DATE - "Due Date" ) < 1 THEN 'Current'
WHEN FLOOR( CURRENT_DATE - "Due Date" ) BETWEEN 1 AND 30 THEN '1-30 Days'
WHEN FLOOR( CURRENT_DATE - "Due Date" ) BETWEEN 31 AND 60 THEN '31-60 Days'
WHEN FLOOR( CURRENT_DATE - "Due Date" ) BETWEEN 61 AND 90 THEN '61-90 Days'
ELSE '90+ Days'
END AS "Aging Bucket"
FROM (
-- LEG 1: All customer transactions (invoices, credit memos, cashsales, unapplied payments)
SELECT
BUILTIN.DF( t.entity ) AS "Customer Name",
t.tranid AS "Transaction ID",
t.recordtype AS "Type",
t.trandate AS "Transaction Date",
COALESCE( t.duedate, t.trandate ) AS "Due Date",
COALESCE( tal.amountunpaid, 0 )
- COALESCE( tal.paymentamountunused, 0 ) AS "Net Amount",
BUILTIN.DF( t.employee ) AS "Sales Rep"
FROM Transaction t
INNER JOIN TransactionAccountingLine tal ON ( tal.transaction = t.id )
INNER JOIN Account a ON ( a.id = tal.account )
INNER JOIN Customer c ON ( c.id = t.entity )
WHERE t.posting = 'T'
AND tal.posting = 'T'
AND tal.accountingbook = 1
AND t.voided = 'F'
AND t.subsidiary = 1
AND a.accttype = 'AcctRec'
AND a.eliminate = 'F'
AND ( tal.amountunpaid <> 0 OR tal.paymentamountunused > 0 )
UNION ALL
-- LEG 2: Journal entries where entity is on TransactionLine, not transaction header
SELECT
BUILTIN.DF( tl.entity ) AS "Customer Name",
t.tranid AS "Transaction ID",
t.recordtype AS "Type",
t.trandate AS "Transaction Date",
t.trandate AS "Due Date",
COALESCE( tal.amountunpaid, 0 ) AS "Net Amount",
BUILTIN.DF( t.employee ) AS "Sales Rep"
FROM Transaction t
INNER JOIN TransactionLine tl ON ( tl.transaction = t.id )
INNER JOIN TransactionAccountingLine tal
ON ( tal.transaction = t.id AND tal.transactionline = tl.id )
INNER JOIN Account a ON ( a.id = tal.account )
WHERE t.posting = 'T'
AND tal.posting = 'T'
AND tal.accountingbook = 1
AND t.voided = 'F'
AND t.subsidiary = 1
AND t.recordtype = 'journalentry'
AND t.entity IS NULL
AND a.accttype = 'AcctRec'
AND a.eliminate = 'F'
AND tal.amountunpaid > 0
)
WHERE "Net Amount" <> 0
ORDER BY "Customer Name", "Due Date"
Sample Output
How the query works
Here is a simple breakdown of how this AR aging detail query operates and why sales rep matters
Transaction-level detail
The query returns one row per open AR transaction so you can see invoice/credit/payment amounts, due dates, days past due, aging bucket, and the assigned sales rep.
Stable aging buckets using FLOOR
FLOOR(CURRENT_DATE - Due Date) ensures fractional day differences do not cause off-by-one bucket assignments and keeps bucketing consistent with expectations.
Two-leg design includes all AR activity
The first leg pulls normal customer transactions using transaction accounting lines so unpaid amounts and unapplied payments net correctly. The second leg captures AR journal entries where the customer is on the transaction line.
Sales rep enables action and insight
Adding sales rep lets finance and collections:
Assign follow-up to the responsible rep, improving collection throughput.
Identify reps with recurring slow-paying customers and coach or escalate.
Tie AR risk to sales performance for balanced incentive design.
Build dashboards that show aging by rep alongside sales KPIs.
Matches NetSuite aging logic
The query uses the same building blocks NetSuite uses: posting-only lines, primary accounting book, and elimination filters so results match native A/R Aging where configured similarly.
Filters to avoid double counting
tal.accountingbook = 1 restricts to the primary book. a.eliminate = ‘F’ excludes intercompany AR accounts.
Customization notes
• Subsidiary: change t.subsidiary = 1 to run for a different subsidiary or remove to include all.
• Accounting book: change tal.accountingbook if you use non-primary books.
• As-of date: replace CURRENT_DATE with TO_DATE(‘YYYY-MM-DD’,‘YYYY-MM-DD’) for historical aging.
• Sales rep source: this uses t.salesrep; if your org stores rep on lines or uses a different field, update the BUILTIN.DF call accordingly.
• Buckets: modify FLOOR(…) ranges in CASE expressions to change aging boundaries.
• Consolidation: add BUILTIN.CONSOLIDATE if you need consolidated currency values across subsidiaries.