Skip to main content

Finance | AR Aging Summary by Customer (Consolidated, One Row per Customer)

This SuiteQL query returns one row per customer with open invoice balances split into Current, 1–30, 31–60, 61–90, and 90+ day buckets. Works across single and multi-currency, single and multi-subsidiary NetSuite accounts.

Purpose

This query calculates open accounts receivable by customer, broken into standard aging buckets based on how many days past due each balance is. It returns one row per customer per subsidiary, with amounts expressed in each subsidiary's functional (base) currency. Unlike a simple invoice balance query, it uses the open-balance method — netting unapplied payments and credit memos against outstanding invoice amounts — so the totals align with NetSuite's native AR Aging report. The query runs on any NetSuite account type: single subsidiary, OneWorld multi-subsidiary, single currency, and multi-currency, with nothing account-specific hard-coded. It is well-suited for Excel AR dashboards, credit risk reviews, and month-end reporting.

Sample Output

Sample data — actual results will vary.

The SuiteQL

/*
  AR Aging Summary by Customer: one row per customer per subsidiary, with total open
  AR split into Current, 1-30, 31-60, 61-90, and 90+ day buckets. The five buckets
  sum to total_open. Universal: runs on any account, OneWorld or single-subsidiary,
  any currency, with nothing account-specific hard-coded.
  Open-balance method (amountunpaid - paymentamountunused on the AR control account):
  nets unapplied payments and credit memos, so it tracks the native AR Aging closely.
  Amounts are in each subsidiary base currency; rows are grouped by subsidiary so every
  row is one currency. Do not sum total_open across subsidiaries of different currencies
  on a OneWorld account; enable the consolidated option to translate to the parent first.
  Aging is by due date (fallback transaction date); FLOOR makes 30/60/90 exact.
*/
SELECT
  c.entityid                                                 AS customer_no,
  MIN( c.companyname )                                       AS customer,
  t.subsidiary                                               AS subsidiary_id,
  MIN( BUILTIN.DF( t.subsidiary ) )                          AS subsidiary,
  SUM( COALESCE( tal.amountunpaid, 0 ) - COALESCE( tal.paymentamountunused, 0 ) )    AS total_open,
  /* Current = not yet due / due today / no date (the complement of the aged buckets) */
  SUM( CASE WHEN FLOOR( CURRENT_DATE - COALESCE( t.duedate, t.trandate ) ) >= 1
            THEN 0 ELSE COALESCE( tal.amountunpaid, 0 ) - COALESCE( tal.paymentamountunused, 0 ) END )   AS current_not_due,
  SUM( CASE WHEN FLOOR( CURRENT_DATE - COALESCE( t.duedate, t.trandate ) ) BETWEEN 1  AND 30
            THEN COALESCE( tal.amountunpaid, 0 ) - COALESCE( tal.paymentamountunused, 0 ) ELSE 0 END )   AS days_1_30,
  SUM( CASE WHEN FLOOR( CURRENT_DATE - COALESCE( t.duedate, t.trandate ) ) BETWEEN 31 AND 60
            THEN COALESCE( tal.amountunpaid, 0 ) - COALESCE( tal.paymentamountunused, 0 ) ELSE 0 END )   AS days_31_60,
  SUM( CASE WHEN FLOOR( CURRENT_DATE - COALESCE( t.duedate, t.trandate ) ) BETWEEN 61 AND 90
            THEN COALESCE( tal.amountunpaid, 0 ) - COALESCE( tal.paymentamountunused, 0 ) ELSE 0 END )   AS days_61_90,
  SUM( CASE WHEN FLOOR( CURRENT_DATE - COALESCE( t.duedate, t.trandate ) ) > 90
            THEN COALESCE( tal.amountunpaid, 0 ) - COALESCE( tal.paymentamountunused, 0 ) ELSE 0 END )   AS days_over_90
FROM Transaction t
  JOIN TransactionAccountingLine tal ON ( tal.transaction = t.id )
  JOIN Customer c                    ON ( c.id = t.entity )
  /* CONSOLIDATED option: uncomment this join AND multiply every amountunpaid - paymentamountunused
     by COALESCE( cer.currentrate, 1 ), then remove t.subsidiary from SELECT and GROUP BY so each
     customer collapses to one consolidated row. No-ops on non-OneWorld accounts (approximate only).
  LEFT JOIN consolidatedexchangerate cer
       ON  cer.fromsubsidiary = t.subsidiary
       AND cer.tosubsidiary   = ( SELECT MIN( id ) FROM Subsidiary WHERE parent IS NULL )
       AND cer.postingperiod  = ( SELECT MIN( id ) FROM AccountingPeriod
                                   WHERE startdate <= CURRENT_DATE AND enddate >= CURRENT_DATE
                                     AND isquarter = 'F' AND isyear = 'F' AND isadjust = 'F' ) */
WHERE t.posting       = 'T'                  /* posted to the GL */
  AND t.voided        = 'F'                  /* exclude voided */
  AND tal.accounttype = 'AcctRec'            /* A/R control-account lines only */
  AND t.entity IS NOT NULL                   /* must belong to a customer */
  AND ( tal.amountunpaid != 0 OR tal.paymentamountunused != 0 )   /* drop fully-settled lines */
  /* SCOPE: default is all subsidiaries. To limit to one, uncomment and set the internal ID
     (find IDs with:  SELECT id, name FROM Subsidiary ORDER BY id):
  AND t.subsidiary = 0 */
GROUP BY c.entityid, t.subsidiary
ORDER BY total_open DESC

How the Query Works

  1. Open-balance method — netting credits and unapplied payments. NetSuite's AR Aging report derives open balances using two fields on the accounting line: amountunpaid (the remaining balance owed on an invoice or charge) and paymentamountunused (unapplied customer payments and credit memos sitting on the AR control account). Subtracting the second from the first gives the net open AR per line — the same arithmetic NetSuite's own report uses. This means unapplied payments and credits reduce the totals naturally rather than appearing as separate rows, and the results align closely with the native AR Aging rather than being an approximation from invoice amounts alone.

  2. TransactionAccountingLine — why this table, not Transaction. The query joins to TransactionAccountingLine (aliased tal) and filters on accounttype = 'AcctRec'. This restricts results to lines posted to the AR control account, which is exactly the population NetSuite's AR Aging considers. Querying the Transaction table alone would include all transaction lines — header amounts, tax, shipping — producing inflated or incorrect totals. The TAL approach also picks up credit memos and unapplied payment lines that would be missed by an invoice-only filter.

  3. Posted and non-voided transactions only. t.posting = 'T' limits results to transactions posted to the general ledger; t.voided = 'F' excludes any transactions voided after posting. Together these two filters ensure the query reflects your actual GL balance rather than draft or reversed activity.

  4. Due-date fallback and FLOOR for precise bucket assignment. COALESCE(t.duedate, t.trandate) falls back to the transaction date for records with no payment terms — payments, credit memos, and journals often carry no due date. Without this fallback those records would be dropped. FLOOR() is applied before comparing to bucket boundaries because CURRENT_DATE carries a time component, making the raw subtraction fractional (e.g., 30.4 days). FLOOR() converts that to a whole number so a balance exactly 30 days past due lands in the 1–30 bucket, not 31–60.

  5. Aging buckets cover the full balance including credits and unapplied payments. Each CASE expression assigns the net open amount (amountunpaid - paymentamountunused) to one aging bucket based on days past due. Because unapplied payments and credit memos produce a negative net value, they appear as negative amounts within their respective bucket. The five buckets always sum to total_open. This is consistent with how NetSuite's native AR Aging displays credit balances — as reductions within the bucket rather than separate rows.

  6. One row per customer per subsidiary. GROUP BY c.entityid, t.subsidiary produces one summary row per customer within each subsidiary. Amounts in each row are in that subsidiary's functional currency. If a customer has open balances in multiple subsidiaries they will appear as multiple rows — one per subsidiary. Do not sum total_open across subsidiaries that use different currencies without first translating them to a common currency.

  7. BUILTIN.DF resolves the subsidiary name. BUILTIN.DF(t.subsidiary) is a NetSuite-specific function that translates an internal record ID into its display name — in this case the subsidiary name as it appears in NetSuite's UI. Because it is an aggregate expression wrapped in MIN(), it works correctly inside a GROUP BY without causing a grouping error. The subsidiary ID is also returned as subsidiary_id for use as a filter or reference key in downstream tools.

Customization Notes

  • Subsidiary scope: By default the query returns all subsidiaries a customer has open balances in. To restrict to one subsidiary, add AND t.subsidiary = [id] to the WHERE clause. Run SELECT id, name FROM Subsidiary ORDER BY name to find IDs. If you add a subsidiary filter, each customer will return at most one row.

  • Aging as of today vs. a specific date: The query ages balances as of CURRENT_DATE. To run it as of a past date, replace both occurrences of CURRENT_DATE with TO_DATE('2025-03-31','YYYY-MM-DD'). This only changes the aging calculation — the open balances themselves (amountunpaid, paymentamountunused) always reflect current payment state. The query cannot rewind to show balances as they stood on the as-of date; for a true historical aging you would need transaction snapshots that SuiteQL cannot reconstruct.

  • Bucket ranges: The aging buckets are defined in the five CASE expressions in the SELECT. Adjust the BETWEEN values to match your business terminology. If you add or remove buckets, update both the CASE expressions and the column aliases to match.

  • Credits and unapplied payments: The open-balance method automatically reduces each customer's balance by any unapplied payments or credit memos on the AR control account. If you see a negative total_open for a customer, that customer has more credits on hand than open invoices. This is correct behavior — it matches NetSuite's native AR Aging rather than suppressing credits.

  • Sub-customers and jobs: If your NetSuite account uses sub-customers or jobs, each sub-customer appears as its own row. To roll them up under their top-level parent, join the Customer table on the parent field and group by the parent customer instead of t.entity.

  • Consolidated multi-currency total: Because amounts are in each subsidiary's functional currency, summing total_open across subsidiaries with different currencies produces a meaningless mixed-currency total. To produce a single consolidated number, multiply each amount by the period-end consolidated exchange rate from the subsidiary to the parent using the optional LEFT JOIN consolidatedexchangerate overlay shown in the query comments. Note that a consolidated total from this method is an approximation — NetSuite's own consolidated AR Aging applies intercompany eliminations that this query cannot reproduce, so results may differ by a small percentage. For audited consolidated figures, use the native NetSuite report.

  • Demo or sample data accounts: Fresh NetSuite accounts include a built-in sample customer ("NetSuite, Inc.") with old open invoices that will appear in the Over-90 bucket. This is legitimate data from the query's perspective — filter it out in your Excel model or consuming application if needed.

Related Articles
Did this answer your question?