Payables overview · Report library | HiScale Advanced Reports, native NetSuite reporting

Screenshot of the Payables overview dashboard in HiScale Advanced Reports, filled with fictional sample data — Sample preview, fictional dataYour own figures appear after import.

About this dashboard

Four tiles give the payables balance, the amount due within 30 days including overdue bills, vendor spend this year and the number of expense reports waiting. Below them are payables by aging bucket, the ten largest vendors by spend over the last twelve months, spend by month, and the bills due in the next 30 days.

The Vendor filter applies to every widget except the expense report tile.

What it carries

The dashboard reads 6 reports. They travel in the same file, so the import creates them first.

Sample preview

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

Widget	Kind	Reads
Payables balance	stat	Open payables
Due within 30 days, overdue included	stat	Bills due in the next 30 days
Vendor spend, this year	stat	Vendor spend by month
Expense reports waiting	stat	Expense reports awaiting approval or payment
Payables by aging bucket	bar	Open payables
Top 10 vendors, last 12 months	hbar	Top vendors by spend (12 months)
Vendor spend by month	line	Vendor spend by month
Bills due in the next 30 days	table	Bills due in the next 30 days

The SuiteQL

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

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

Bills due in the next 30 days

SELECT t.id AS bill__id,
       NVL(t.tranid, t.transactionnumber) AS bill,
       NVL(v.altname, v.entityid) AS vendor,
       TO_CHAR(t.trandate, 'YYYY-MM-DD') AS bill_date,
       TO_CHAR(t.duedate, 'YYYY-MM-DD') AS due_date,
       TRUNC(t.duedate) - TRUNC(SYSDATE) AS days_until_due,
       BUILTIN.DF(t.currency) AS currency,
       t.foreignamountunpaid AS unpaid_fx,
       t.foreignamountunpaid * t.exchangerate AS unpaid
  FROM transaction t
  JOIN entity v ON v.id = t.entity
 WHERE t.type = 'VendBill'
   AND t.posting = 'T'
   AND t.foreignamountunpaid > 0
   AND NVL(t.duedate, t.trandate) <= TRUNC(SYSDATE) + 30
 ORDER BY NVL(t.duedate, t.trandate)

Vendor spend by month

SELECT TO_CHAR(t.trandate, 'YYYY-MM') AS period,
       t.entity AS vendor__id,
       NVL(v.altname, v.entityid) AS vendor,
       COUNT(DISTINCT t.id) AS bills,
       SUM(tl.foreignamount * t.exchangerate) AS spend
  FROM transaction t
  JOIN transactionline tl ON tl.transaction = t.id
  JOIN entity v ON v.id = t.entity
 WHERE t.type IN ('VendBill', 'VendCred')
   AND t.posting = 'T'
   AND tl.mainline = 'F'
   AND tl.taxline = 'F'
   AND NVL(tl.itemtype, 'x') NOT IN ('TaxGroup', 'TaxItem', 'Subtotal', 'Description', 'EndGroup')
   AND t.trandate >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -23)
 GROUP BY TO_CHAR(t.trandate, 'YYYY-MM'), t.entity, NVL(v.altname, v.entityid)
 ORDER BY period DESC, spend DESC

Vendor bill lines

SELECT TO_CHAR(t.trandate, 'YYYY-MM') AS period,
       TO_CHAR(t.trandate, 'YYYY-MM-DD') AS tran_date,
       NVL(v.altname, v.entityid) AS vendor,
       BUILTIN.DF(t.type) AS type,
       NVL(t.tranid, t.transactionnumber) AS document,
       BUILTIN.DF(tl.expenseaccount) AS account,
       BUILTIN.DF(tl.item) AS item,
       tl.memo AS memo,
       tl.foreignamount * t.exchangerate AS amount
  FROM transaction t
  JOIN transactionline tl ON tl.transaction = t.id
  JOIN entity v ON v.id = t.entity
 WHERE t.type IN ('VendBill', 'VendCred')
   AND t.posting = 'T'
   AND tl.mainline = 'F'
   AND tl.taxline = 'F'
   AND NVL(tl.itemtype, 'x') NOT IN ('TaxGroup', 'TaxItem', 'Subtotal', 'Description', 'EndGroup')
   AND t.trandate >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -23)
 ORDER BY t.trandate DESC, t.tranid

Expense reports awaiting approval or payment

SELECT t.id AS report__id,
       NVL(t.tranid, t.transactionnumber) AS report_no,
       BUILTIN.DF(t.entity) AS employee,
       BUILTIN.DF(t.status) AS status,
       TO_CHAR(t.trandate, 'YYYY-MM-DD') AS report_date,
       TRUNC(SYSDATE) - TRUNC(t.trandate) AS days_waiting,
       t.memo AS purpose,
       BUILTIN.DF(t.currency) AS currency,
       ABS(t.foreigntotal) AS total_fx,
       ABS(t.foreigntotal) * t.exchangerate AS total
  FROM transaction t
 WHERE t.type = 'ExpRept'
   AND t.status IN ('ExpRept:B', 'ExpRept:C', 'ExpRept:F', 'ExpRept:G', 'B', 'C', 'F', 'G')
 ORDER BY t.trandate

Top vendors by spend (12 months)

SELECT x.vendor__id,
       NVL(v.altname, v.entityid) AS vendor,
       x.bills,
       x.spend,
       x.spend / NULLIF(SUM(x.spend) OVER (), 0) AS share,
       TO_CHAR(x.last_bill, 'YYYY-MM-DD') AS last_bill
  FROM (SELECT t.entity AS vendor__id,
               COUNT(DISTINCT t.id) AS bills,
               SUM(tl.foreignamount * t.exchangerate) AS spend,
               MAX(t.trandate) AS last_bill
          FROM transaction t
          JOIN transactionline tl ON tl.transaction = t.id
          JOIN entity v ON v.id = t.entity
         WHERE t.type IN ('VendBill', 'VendCred')
           AND t.posting = 'T'
           AND tl.mainline = 'F'
           AND tl.taxline = 'F'
           AND NVL(tl.itemtype, 'x') NOT IN ('TaxGroup', 'TaxItem', 'Subtotal', 'Description', 'EndGroup')
           AND t.trandate > TRUNC(SYSDATE) - 365
         GROUP BY t.entity) x
  JOIN entity v ON v.id = x.vendor__id
 ORDER BY x.spend DESC