Skip to main content

Finance | AR Aging Total Summary (Single Row with Aging Buckets)

This query returns a single AR aging summary row with total open invoices 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 SuiteQL produces a single summary row showing total open Accounts Receivable split into Current, 1–30, 31–60, 61–90, and 90+ day aging buckets. It uses the open-balance method — querying TransactionAccountingLine and netting unapplied payments and credit memos against outstanding invoice amounts — which is the same approach NetSuite's native AR Aging report uses. This means the grand total aligns with the native report rather than being an invoice-only approximation. The query works across all NetSuite account types: single subsidiary, OneWorld multi-subsidiary, single currency, and multi-currency, with no account-specific values hard-coded. Use it for dashboard KPI tiles, executive summaries, and month-end snapshot reporting.

Sample Output

Sample data — actual results will vary.

The SuiteQL

/*
  AR Aging Total Summary: ONE row of total open A/R split into Current, 1-30,
  31-60, 61-90, and 90+ day buckets. Buckets always sum to Total Open AR.
  Universal: runs on any account, OneWorld or single-subsidiary, any currency.
  Open-balance method (amountunpaid - paymentamountunused on the A/R control
  account): nets unapplied customer payments AND credit memos, so it matches
  the per-customer "AR Aging Summary" grand total and tracks the native A/R
  Aging report closely. It is NOT a penny tie-out: stale amountunpaid, demo
  data, and consolidation eliminations/revaluation live outside these fields.
  Default scope is ALL subsidiaries in each subsidiary base currency; on a
  multi-currency OneWorld account that single number MIXES currencies, so
  enable the consolidated option below to translate to the parent currency.
  Aging is by due date (fallback transaction date); FLOOR makes 30/60/90 exact.
*/
SELECT
  SUM( net_open )                                                           AS "Total Open AR",
  /* Current = not yet due / due today / no date (the complement of the aged buckets) */
  SUM( CASE WHEN FLOOR( CURRENT_DATE - due_date ) >= 1
            THEN 0 ELSE net_open END )                                      AS "Current",
  SUM( CASE WHEN FLOOR( CURRENT_DATE - due_date ) BETWEEN 1  AND 30
            THEN net_open ELSE 0 END )                                      AS "1-30 Days",
  SUM( CASE WHEN FLOOR( CURRENT_DATE - due_date ) BETWEEN 31 AND 60
            THEN net_open ELSE 0 END )                                      AS "31-60 Days",
  SUM( CASE WHEN FLOOR( CURRENT_DATE - due_date ) BETWEEN 61 AND 90
            THEN net_open ELSE 0 END )                                      AS "61-90 Days",
  SUM( CASE WHEN FLOOR( CURRENT_DATE - due_date ) > 90
            THEN net_open ELSE 0 END )                                      AS "90+ Days"
FROM (
  SELECT
    COALESCE( t.duedate, t.trandate )                                       AS due_date,
    /* Net open A/R on this accounting line, in the subsidiary base currency.
       Append the cer factor below to translate to the parent (consolidated) currency. */
    COALESCE( tal.amountunpaid, 0 ) - COALESCE( tal.paymentamountunused, 0 )
      /* enable for consolidated view:  ) * COALESCE( cer.currentrate, 1 )  (and wrap the line above in parens) */
                                                                            AS net_open
  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 the cer factor above to translate every
       subsidiary to the top-level parent at the period-end rate of the period holding today.
       It no-ops on non-OneWorld accounts (no rows), so it is safe to leave enabled anywhere.
    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 */
)

How the Query Works

  1. Open-balance method — netting credits and unapplied payments. The inner subquery joins to TransactionAccountingLine (aliased tal) filtered on accounttype = 'AcctRec'. NetSuite stores two key fields on each AR accounting line: amountunpaid (the remaining balance on an invoice or charge) and paymentamountunused (unapplied customer payments and credit memos). Subtracting the second from the first gives the net open AR per line. This means credits and unapplied payments reduce the total naturally rather than requiring a separate filter to exclude them. The five buckets always sum to Total Open AR.

  2. TransactionAccountingLine — why this table, not Transaction. Filtering to the AR control account lines via tal.accounttype = 'AcctRec' restricts the result set to exactly the population NetSuite's AR Aging considers — invoices, credit memos, payments, and adjustments that post to AR. Using the Transaction table with a type filter (e.g., CustInvc only) would omit credits and unapplied payments, producing totals that are higher than the native report shows.

  3. Posted and non-voided transactions only. t.posting = 'T' ensures only transactions posted to the general ledger are included; t.voided = 'F' excludes any transactions voided after posting. Together these filters keep the result aligned with your actual GL balance.

  4. Two-layer structure: inner subquery then outer aggregation. The inner query computes the net open amount (net_open) and the effective due date for each accounting line. The outer query then sums across all lines and distributes the total into aging buckets using CASE expressions. Separating these layers keeps the bucket logic clean and makes it easy to add columns (such as subsidiary or customer) by extending the inner query and updating the outer GROUP BY.

  5. 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 — unapplied payments and credit memos often carry no due date and would otherwise be dropped. FLOOR() converts the fractional day difference (timestamps include a time component) into a whole number before evaluating bucket boundaries, ensuring a balance exactly 30 days past due lands in the 1–30 bucket and not 31–60.

  6. Credits and unapplied payments appear as negative amounts within buckets. Because unapplied payments and credit memos produce a negative net_open, they reduce the bucket total they fall into rather than appearing as separate rows. This is consistent with how NetSuite's native AR Aging displays credit balances. If Total Open AR comes back lower than expected compared to an invoice-only total, the difference is the credits and payments being netted.

  7. Mixed currencies on multi-subsidiary accounts. By default, amounts across all subsidiaries are summed without currency translation. On a single-currency or single-subsidiary account this produces a clean total. On a OneWorld account with multiple currencies the sum mixes currencies and is not a meaningful financial figure — enable the optional consolidatedexchangerate join shown in the query comments to translate everything to the parent currency before summing. That consolidated total is an approximation; NetSuite's period-end revaluation and intercompany eliminations live inside the consolidation engine and cannot be reproduced in SuiteQL.

Customization Notes

  • Subsidiary scope: the default returns all subsidiaries. To limit to one, add AND t.subsidiary = [id] inside the inner subquery's WHERE clause. Run SELECT id, name FROM Subsidiary ORDER BY id to find IDs.

  • Credits and unapplied payments: the open-balance method includes them automatically as negative amounts. If you want a gross invoice total without credits netted in, replace amountunpaid - paymentamountunused with just amountunpaid and add AND t.type = 'CustInvc' to the WHERE clause — but be aware that result will be higher than NetSuite's native AR Aging grand total.

  • Consolidated multi-currency view: uncomment the LEFT JOIN consolidatedexchangerate and the * COALESCE(cer.currentrate, 1) factor in the inner subquery to translate every subsidiary's amounts to the parent currency. The join no-ops safely on non-OneWorld accounts. The resulting consolidated total is approximate — not a penny tie-out to the native report.

  • As-of date: replace CURRENT_DATE with TO_DATE('YYYY-MM-DD','YYYY-MM-DD') to age as of a past date. The open balances (amountunpaid, paymentamountunused) always reflect current payment state — this only shifts the aging calculation, not the balance itself.

  • Aging buckets: adjust the FLOOR(...) ranges in the outer CASE expressions to match your business terms.

Related Articles
Did this answer your question?