Skip to main content

Finance | AR Aging Detail by Transaction with Sales Rep

One row per open invoice with customer, transaction details, days past due, aging bucket, and the point-in-time sales rep stamped on the invoice. Runs on any NetSuite account — single or multi-subsidiary, single or multi-currency.

Purpose

This SuiteQL returns Accounts Receivable aging at the transaction level — one row for every open customer invoice — and includes the sales rep recorded on the invoice when it was created, giving a true point-in-time view of who owned the sale rather than whoever sits on the customer record today. Each row carries the customer, invoice details, days past due, aging bucket, and net open amount. It runs on any NetSuite account (single or multi-subsidiary, single or multi-currency) and by default returns invoices across all subsidiaries, with each amount shown in that subsidiary's base currency. Because this is a transaction-detail report, standalone credit memos and unapplied payments are intentionally excluded — they do not appear as rows. Use this query for collections prioritization, rep-level AR reviews, credit-risk analysis, and Excel dashboards.

Sample Output

Sample data — actual results will vary.

The SuiteQL

/*
  AR Aging Detail by Transaction with Sales Rep: one row per open customer invoice.
  Columns: customer, sales rep (stamped at creation), type, subsidiary, dates,
  days past due, aging bucket, and net open amount in the subsidiary's base currency.
  Universal: runs on any account, OneWorld or single-subsidiary, any currency.
  Default scope is ALL subsidiaries (complete AR); each amount stays in its
  subsidiary base currency, so use the Subsidiary column before summing in OneWorld.
  Aging is by due date (fallback transaction date); FLOOR makes 30/60/90 exact.
  For a single-currency consolidated view, enable the consolidated-rate option below
  (approximate only: it cannot reproduce NetSuite period-end revaluation or the
  intercompany eliminations that live in the consolidation engine, not in the data).
*/
SELECT
  c.companyName                                              AS "Customer Name",
  BUILTIN.DF( t.employee )                                   AS "Sales Rep",
  t.tranid                                                   AS "Transaction ID",
  t.type                                                     AS "Type",
  BUILTIN.DF( t.subsidiary )                                 AS "Subsidiary",
  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",
  /* Open balance: transaction currency (foreignamountunpaid) x exchangerate = subsidiary
     base currency. Append the commented factor to translate to the parent currency. */
  t.foreignamountunpaid * COALESCE( t.exchangerate, 1 )
    /* enable for consolidated view:  * COALESCE( cer.currentrate, 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 )
  /* 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.type                = 'CustInvc'      /* open invoices only (no standalone credits/payments) */
  AND t.foreignamountunpaid IS NOT NULL
  AND t.foreignamountunpaid <> 0              /* still owed */
  AND t.voided              = 'F'
  AND t.posting             = 'T'             /* posted to the GL */
  /* 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 */
ORDER BY BUILTIN.DF( t.subsidiary ), c.companyName, COALESCE( t.duedate, t.trandate )

How the Query Works

  1. One row per open invoice — credits and unapplied payments excluded by design. The query filters to t.type = 'CustInvc' where foreignamountunpaid is not null and not zero, so it returns one line for every invoice that still carries a balance. Fully paid invoices fall away automatically. Standalone credit memos and unapplied customer payments are not returned as rows here — this is intentional. If you need credits and payments netted into the totals, use the AR Aging Summary by Customer query, which uses the open-balance method on TransactionAccountingLine.

  2. Point-in-time sales rep. BUILTIN.DF(t.employee) returns the rep stamped on the invoice header when it was created, not the rep currently on the customer record. That stays fixed even if the account is later reassigned, so credit is attributed to whoever actually owned the sale. Where an invoice had no rep at creation, this column comes back blank.

  3. Net amount in the subsidiary's base currency. Net Amount is foreignamountunpaid multiplied by the exchange rate stored on the transaction at invoice time. COALESCE(t.exchangerate, 1) makes single-currency accounts a no-op and handles cases where the rate is stored as null. This approach mirrors NetSuite's native AR Aging, which also converts at the transaction-stored rate rather than a current or period-end rate. Unrealized FX gains and losses are recorded as separate revaluation transactions and are intentionally not included here, matching the native report's behavior.

  4. Due-date aging with a transaction-date fallback. COALESCE(t.duedate, t.trandate) ages each invoice by its due date, falling back to the transaction date for any invoice with no payment terms. Those term-less invoices land in Current rather than being dropped from the report.

  5. Whole-day buckets with FLOOR. FLOOR(CURRENT_DATE - due date) turns the fractional day difference (the current timestamp carries a time component) into a whole number of days before the bucket ranges are evaluated. This keeps the 30 / 60 / 90 boundaries exact no matter what time of day the query runs.

  6. All subsidiaries by default, each in its own currency. There is no subsidiary filter in the default query, so results cover complete AR across every entity in the account. Because each amount stays in its subsidiary's base currency, you should not sum Net Amount across subsidiaries in a OneWorld account without translating first — which is why results are ordered by subsidiary so each entity's invoices group together naturally.

  7. Optional translation to a single consolidated currency. A commented LEFT JOIN to consolidatedexchangerate translates every subsidiary to the top-level parent at the period-end rate of the period containing today; enable it together with the * COALESCE(cer.currentrate, 1) factor on Net Amount for a one-currency consolidated view. It no-ops safely on non-OneWorld accounts. Treat the consolidated total as an approximation: NetSuite's period-end revaluation and intercompany eliminations live inside its consolidation engine, not in queryable open-balance data, so the total lands within a few percent of the native report rather than to the penny.

  8. Why the point-in-time rep matters. Carrying the rep who owned each sale lets finance route collections follow-up to the right person, surface reps whose customers consistently pay late, tie AR risk back to sales performance, and build dashboards that show aging by rep alongside sales KPIs.

Customization Notes

  • Subsidiary scope: the default returns all subsidiaries for complete AR. To limit to one subsidiary, uncomment the AND t.subsidiary = 0 line and set the internal ID — run SELECT id, name FROM Subsidiary ORDER BY id to find it.

  • Credits and unapplied payments: this query deliberately excludes standalone credit memos and unapplied payments — only invoices with an open balance appear as rows. To include credits and payments netted into customer totals, use the AR Aging Summary by Customer query instead (which queries TransactionAccountingLine using the open-balance method).

  • Single-currency consolidated view: uncomment the consolidatedexchangerate join and append the * COALESCE(cer.currentrate, 1) factor to Net Amount to translate every subsidiary into the parent currency. Leave it off to keep each row in its subsidiary's base currency; switch it on knowing the consolidated total is approximate (it excludes NetSuite's period-end revaluation and intercompany eliminations).

  • Sales rep fallback: if BUILTIN.DF(t.employee) comes back blank on some invoices, swap it for BUILTIN.DF(c.salesrep) to pull the current rep from the customer record instead of the point-in-time rep stamped on the invoice.

  • As-of date: replace both CURRENT_DATE references with TO_DATE('YYYY-MM-DD','YYYY-MM-DD') to age as of a chosen date. Note the open balance is real-time — an as-of date in the past still reflects today's payment state, not a historical rewind.

  • Aging buckets: adjust the day ranges in the CASE expressions to match your terms; the defaults are Current, 1–30, 31–60, 61–90, and 90+.

Related Articles
Did this answer your question?