AP aging by vendor · Report library | HiScale Advanced Reports, native NetSuite reporting

Screenshot of the AP aging by vendor report in HiScale Advanced Reports, filled with fictional sample data — Sample preview, fictional dataYour own figures appear after import.

About this report

One row per vendor. Open bill balances are split by days past due into Current, 1-30, 31-60, 61-90 and Over 90. Unapplied vendor credits and payments sit in their own negative column, and Balance is the net. Amounts are in base currency at each transaction's exchange rate.

Click a bucket or the balance to open Open payables filtered to that vendor and bucket. The documents listed add up to the figure you clicked.

Sample preview

Fictional figures in the shape you will see. Yours come from your account.

Vendor	Current	1-30 days	31-60 days	Over 90 days	Unapplied credits	Balance
Cloudway Hosting	14,200	0	0	0	0	14,200
Pacific Office Partners	3,120.40	1,890	0	0	0	5,010.40
Ridgeline Contractors	0	4,500	2,250	0	-500	6,250
Metro Courier Services	480	0	0	215	0	695

The SuiteQL

This is the query each report runs. Read it, copy it, or change it after import.

AP aging by vendor

SELECT o.vendor__id,
       NVL(v.altname, v.entityid) AS vendor,
       SUM(CASE WHEN o.open_amount >= 0 AND TRUNC(SYSDATE) - TRUNC(o.due_date) <= 0 THEN o.open_amount ELSE 0 END) AS current_due,
       SUM(CASE WHEN o.open_amount >= 0 AND TRUNC(SYSDATE) - TRUNC(o.due_date) BETWEEN 1 AND 30 THEN o.open_amount ELSE 0 END) AS days_1_30,
       SUM(CASE WHEN o.open_amount >= 0 AND TRUNC(SYSDATE) - TRUNC(o.due_date) BETWEEN 31 AND 60 THEN o.open_amount ELSE 0 END) AS days_31_60,
       SUM(CASE WHEN o.open_amount >= 0 AND TRUNC(SYSDATE) - TRUNC(o.due_date) BETWEEN 61 AND 90 THEN o.open_amount ELSE 0 END) AS days_61_90,
       SUM(CASE WHEN o.open_amount >= 0 AND TRUNC(SYSDATE) - TRUNC(o.due_date) > 90 THEN o.open_amount ELSE 0 END) AS over_90,
       SUM(CASE WHEN o.open_amount < 0 THEN o.open_amount ELSE 0 END) AS unapplied,
       SUM(o.open_amount) AS balance
  FROM (SELECT t.entity AS vendor__id,
               t.id AS document__id,
               NVL(t.tranid, t.transactionnumber) AS document,
               BUILTIN.DF(t.type) AS type,
               t.trandate AS tran_date,
               NVL(t.duedate, t.trandate) AS due_date,
               BUILTIN.DF(t.currency) AS currency,
               t.foreignamountunpaid AS open_fx,
               t.foreignamountunpaid * t.exchangerate AS open_amount
          FROM transaction t
         WHERE t.type = 'VendBill'
           AND t.posting = 'T'
           AND t.foreignamountunpaid > 0
        UNION ALL
        SELECT t.entity,
               t.id,
               NVL(t.tranid, t.transactionnumber),
               BUILTIN.DF(t.type),
               t.trandate,
               t.trandate,
               BUILTIN.DF(t.currency),
               -t.foreignpaymentamountunused,
               -t.foreignpaymentamountunused * t.exchangerate
          FROM transaction t
         WHERE t.type IN ('VendCred', 'VendPymt')
           AND t.foreignpaymentamountunused > 0) o
  JOIN entity v ON v.id = o.vendor__id
 GROUP BY o.vendor__id, NVL(v.altname, v.entityid)
 ORDER BY balance DESC

Open payables

SELECT o.document__id,
       o.document,
       o.type,
       NVL(v.altname, v.entityid) AS vendor,
       TO_CHAR(o.tran_date, 'YYYY-MM-DD') AS tran_date,
       TO_CHAR(o.due_date, 'YYYY-MM-DD') AS due_date,
       CASE WHEN o.open_amount < 0 THEN NULL ELSE TRUNC(SYSDATE) - TRUNC(o.due_date) END AS days_overdue,
       CASE WHEN o.open_amount < 0 THEN 'Unapplied credit'
            WHEN TRUNC(SYSDATE) - TRUNC(o.due_date) <= 0 THEN 'Current'
            WHEN TRUNC(SYSDATE) - TRUNC(o.due_date) <= 30 THEN '1-30'
            WHEN TRUNC(SYSDATE) - TRUNC(o.due_date) <= 60 THEN '31-60'
            WHEN TRUNC(SYSDATE) - TRUNC(o.due_date) <= 90 THEN '61-90'
            ELSE 'Over 90' END AS aging_bucket,
       o.currency,
       o.open_fx,
       o.open_amount
  FROM (SELECT t.entity AS vendor__id,
               t.id AS document__id,
               NVL(t.tranid, t.transactionnumber) AS document,
               BUILTIN.DF(t.type) AS type,
               t.trandate AS tran_date,
               NVL(t.duedate, t.trandate) AS due_date,
               BUILTIN.DF(t.currency) AS currency,
               t.foreignamountunpaid AS open_fx,
               t.foreignamountunpaid * t.exchangerate AS open_amount
          FROM transaction t
         WHERE t.type = 'VendBill'
           AND t.posting = 'T'
           AND t.foreignamountunpaid > 0
        UNION ALL
        SELECT t.entity,
               t.id,
               NVL(t.tranid, t.transactionnumber),
               BUILTIN.DF(t.type),
               t.trandate,
               t.trandate,
               BUILTIN.DF(t.currency),
               -t.foreignpaymentamountunused,
               -t.foreignpaymentamountunused * t.exchangerate
          FROM transaction t
         WHERE t.type IN ('VendCred', 'VendPymt')
           AND t.foreignpaymentamountunused > 0) o
  JOIN entity v ON v.id = o.vendor__id
 ORDER BY o.due_date