Purpose of this query
This SuiteQL statement calculates open Accounts Receivable by customer and automatically breaks balances into standard aging buckets based on due date. It combines regular customer transactions and journal entries into a single aging summary using a two-leg UNION ALL structure. The query filters to posting transactions, excludes voided and intercompany AR accounts, and limits results to a specific subsidiary and accounting book. Users can modify the subsidiary ID, accounting book, aging logic, or date logic as needed. This format is ideal for Excel dashboards, AR reviews, credit risk analysis, and month-end reporting.
-- AR Aging Summary: one row per customer with aging bucket breakdown
-- Same 2-leg pattern as the detail query
SELECT
"Customer Name",
SUM( "Net Amount" ) AS "Total Open AR",
SUM( CASE WHEN FLOOR( CURRENT_DATE - "Due Date" ) < 1
THEN "Net Amount" ELSE 0 END ) AS "Current",
SUM( CASE WHEN FLOOR( CURRENT_DATE - "Due Date" ) BETWEEN 1 AND 30
THEN "Net Amount" ELSE 0 END ) AS "1-30 Days",
SUM( CASE WHEN FLOOR( CURRENT_DATE - "Due Date" ) BETWEEN 31 AND 60
THEN "Net Amount" ELSE 0 END ) AS "31-60 Days",
SUM( CASE WHEN FLOOR( CURRENT_DATE - "Due Date" ) BETWEEN 61 AND 90
THEN "Net Amount" ELSE 0 END ) AS "61-90 Days",
SUM( CASE WHEN FLOOR( CURRENT_DATE - "Due Date" ) > 90
THEN "Net Amount" ELSE 0 END ) AS "90+ Days"
FROM (
-- LEG 1: All customer transactions
SELECT
BUILTIN.DF( t.entity ) AS "Customer Name",
COALESCE( t.duedate, t.trandate ) AS "Due Date", -- payments have no duedate; fall back to trandate
COALESCE( tal.amountunpaid, 0 )
- COALESCE( tal.paymentamountunused, 0 ) AS "Net Amount"
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 -- primary book only; prevents double-counting in multi-book environments
AND t.voided = 'F'
AND t.subsidiary = 1
AND a.accttype = 'AcctRec'
AND a.eliminate = 'F' -- excludes intercompany AR accounts (eliminate=T); universally portable across NetSuite customers
AND ( tal.amountunpaid <> 0 OR tal.paymentamountunused > 0 )
UNION ALL
-- LEG 2: Journal entries (entity on TransactionLine, not header)
SELECT
BUILTIN.DF( tl.entity ) AS "Customer Name",
t.trandate AS "Due Date",
COALESCE( tal.amountunpaid, 0 ) AS "Net Amount"
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 -- primary book only; prevents double-counting in multi-book environments
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' -- excludes intercompany AR accounts (eliminate=T); universally portable across NetSuite customers
AND tal.amountunpaid > 0
)
WHERE "Net Amount" <> 0
GROUP BY "Customer Name"
ORDER BY SUM( "Net Amount" ) DESC
Sample Output
How the query works
Here is a simple breakdown of how this AR aging summary operates.
Calculating aging based on due date
The outer query calculates the difference between CURRENT_DATE and the transaction’s Due Date. Based on the number of days outstanding, balances are grouped into Current, 1–30, 31–60, 61–90, and 90+ day buckets.
Combining invoices, payments, and credits
The first leg pulls all customer AR transactions, including invoices and payments. It calculates net open balance by subtracting unused payment amounts from unpaid amounts.
Including journal entries posted to AR
The second leg captures journal entries that post to Accounts Receivable, where the customer is stored at the transaction line level rather than the transaction header.
Using FLOOR for stable bucket assignment
The bucket calculation uses FLOOR(CURRENT_DATE - Due Date) to convert fractional day differences into whole days before evaluating ranges. This prevents off-by-one issues caused by time-of-day fractions and ensures bucket assignment matches expected aging boundaries.
Preventing double counting in multi-book environments
The filter tal.accountingbook = 1 ensures only the primary accounting book is included.
Excluding intercompany AR
The filter a.eliminate = ‘F’ excludes elimination accounts, making the query portable across NetSuite environments.
Restricting to a specific subsidiary
The filter t.subsidiary = 1 limits results to one subsidiary. Users can change this value to run the aging for another subsidiary.
Returning one row per customer
After combining both legs, the outer query groups by customer name and produces a single summary row per customer.
Notes for customization
Users can modify the following elements.
Subsidiary: change t.subsidiary = 1 to another subsidiary ID.
Accounting book: change tal.accountingbook = 1 if needed.
Aging logic: adjust bucket ranges in the CASE expressions.
As-of date: replace CURRENT_DATE with a fixed date if you need historical aging.
Currency or consolidation: add BUILTIN.CONSOLIDATE if consolidated reporting is required.