SuiteStep, LLC

SELECT t.type, COUNT(DISTINCT t.id) AS txns, ROUND(SUM(-tal.amount),0) AS revenue
FROM transactionaccountingline tal
JOIN transaction t ON tal.transaction = t.id
JOIN account a ON tal.account = a.id
WHERE t.posting = 'T' AND a.accttype = 'Income'
  AND t.trandate >= TO_DATE('2026-01-01','YYYY-MM-DD')
  AND t.trandate < TO_DATE('2026-09-01','YYYY-MM-DD')
GROUP BY t.type ORDER BY SUM(-tal.amount) DESC
-- Result: Journal 16 txns $7,036,333 · CustInvc 280 $1,290,378 · CashSale 363 $62,867 · minor others

SELECT COUNT(DISTINCT t.id) AS je_count, ROUND(SUM(tal.amount),0) AS net_amount
FROM transactionaccountingline tal
JOIN transaction t ON tal.transaction = t.id
JOIN account a ON tal.account = a.id
WHERE t.type = 'Journal' AND a.accttype = 'AcctRec'
-- A/R: 51 JEs net +$1.239M · A/P (accttype='AcctPay'): 52 JEs net −$1.082M
-- Inventory (fullname LIKE '%Inventory in Stock%'): 51 JEs net +$1,007,015

SELECT a.fullname,
       TO_CHAR(t.trandate,'YYYY') AS yr,
       ROUND(SUM(tal.credit),0) AS accrued,
       ROUND(SUM(tal.debit),0)  AS remitted
FROM transactionaccountingline tal
JOIN transaction t ON tal.transaction = t.id
JOIN account a ON tal.account = a.id
WHERE a.fullname LIKE '%Sales Tax%' AND t.posting = 'T'
GROUP BY a.fullname, TO_CHAR(t.trandate,'YYYY')
ORDER BY a.fullname, yr
-- CA: $78,411 accrued vs $8.50 remitted · NY: $57,221 vs $70.83 · MA: $33,349 vs $9.09 · KY+8 states: $0 remitted

SELECT id, periodname, startdate, closed
FROM accountingperiod
WHERE isquarter = 'F' AND isyear = 'F'
ORDER BY startdate
-- Last closed='T': Nov 2021 (id 101). Dec 2021 (102) → Dec 2029: all closed='F'.

SELECT
  CASE
    WHEN t.duedate >= TRUNC(SYSDATE) THEN 'Current'
    WHEN TRUNC(SYSDATE)-TRUNC(t.duedate) <= 30 THEN '1-30'
    WHEN TRUNC(SYSDATE)-TRUNC(t.duedate) <= 60 THEN '31-60'
    WHEN TRUNC(SYSDATE)-TRUNC(t.duedate) <= 90 THEN '61-90'
    ELSE '>90' END AS bucket,
  COUNT(*) AS invoices, ROUND(SUM(t.foreignamountunpaid),2) AS unpaid
FROM transaction t
WHERE t.type = 'CustInvc' AND t.status = 'A' AND t.foreignamountunpaid > 0
GROUP BY /* bucket expression repeated */
-- Current $133,364 · 1-30 $123,385 · 31-60 $59,153 · 61-90 $139,431 · >90 $472,913 (13 invoices, max 499 days)

-- nexttransactionlink is unpopulated for SO→ItemShip in this account;
-- lineage derived via the shipment header line's createdfrom reference:
SELECT shipline.location,
       COUNT(*) AS ships,
       ROUND(AVG(TRUNC(ship.trandate)-TRUNC(so.trandate)),2) AS avg_days,
       MAX(TRUNC(ship.trandate)-TRUNC(so.trandate)) AS max_days
FROM transaction ship
JOIN transactionline shipline ON shipline.transaction = ship.id AND shipline.mainline = 'T'
JOIN transaction so ON shipline.createdfrom = so.id AND so.type = 'SalesOrd'
WHERE ship.type = 'ItemShip'
  AND so.trandate >= TO_DATE('2026-01-01','YYYY-MM-DD')
GROUP BY shipline.location
-- Loc 1 (SF): 92 ships avg 0.01d · Loc 3 (NY): 69 avg 0.0d · Loc 5 (LA DC): 73 avg 11.34d max 49 · Loc 12 (Miami): 32 avg −0.75d

SELECT tl.location, COUNT(DISTINCT t.id) AS txns,
       ROUND(SUM(ABS(tl.netamount)),2) AS revenue
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
 AND tl.mainline = 'F' AND tl.taxline = 'F' AND tl.subsidiary <> 4
WHERE t.type IN ('CustInvc','CashSale')
  AND t.trandate >= TO_DATE('2026-01-01','YYYY-MM-DD')
GROUP BY tl.location ORDER BY SUM(ABS(tl.netamount)) DESC

SELECT v.entityid AS vendor, COUNT(DISTINCT t.id) AS bills,
       ROUND(SUM(ABS(t.foreigntotal)),0) AS spend
FROM transaction t
JOIN vendor v ON t.entity = v.id
WHERE t.type = 'VendBill'
  AND t.trandate >= TO_DATE('2026-01-01','YYYY-MM-DD')
GROUP BY v.entityid ORDER BY SUM(ABS(t.foreigntotal)) DESC
-- $1,794,815 total · 31 billed vendors · top 7 = 82.5% of spend

SELECT COUNT(*) AS paid_bills,
       ROUND(AVG(TRUNC(t.closedate)-TRUNC(t.trandate)),1) AS avg_dpo,
       MAX(TRUNC(t.closedate)-TRUNC(t.trandate)) AS max_days
FROM transaction t
WHERE t.type = 'VendBill' AND t.status = 'B'
  AND t.trandate >= TO_DATE('2026-01-01','YYYY-MM-DD')
  AND t.closedate IS NOT NULL
-- 394 bills · avg 3.2 days · max 165

-- By location:
SELECT l.id, l.name, SUM(ail.quantityonhand) AS qty,
       ROUND(SUM(ail.onhandvaluemli),0) AS value_on_hand
FROM aggregateitemlocation ail
JOIN location l ON ail.location = l.id
WHERE ail.quantityonhand <> 0
GROUP BY l.id, l.name ORDER BY SUM(ail.onhandvaluemli) DESC

-- Dead stock:
SELECT COUNT(DISTINCT i.id) AS slow_items,
       ROUND(SUM(ail.onhandvaluemli),0) AS slow_value
FROM item i
JOIN aggregateitemlocation ail ON ail.item = i.id AND ail.quantityonhand > 0
WHERE i.itemtype = 'InvtPart'
  AND NOT EXISTS (
    SELECT 1 FROM transactionline tl
    JOIN transaction t ON tl.transaction = t.id
    WHERE tl.item = i.id AND t.type IN ('CustInvc','CashSale')
      AND t.trandate >= TO_DATE('2026-02-15','YYYY-MM-DD'))
-- 48 items · $40,855

-- Terms coverage: 219 of 274 customers have terms IS NULL; 55 = Net 30.
-- 100% of overdue open A/R traces to Net-30 customers.
SELECT COUNT(*) FROM customer WHERE terms IS NULL

-- Duplicates (0 found on both entities):
SELECT companyname, COUNT(*) FROM customer
GROUP BY companyname HAVING COUNT(*) > 1

-- Unapplied payments/credits (0 found):
SELECT COUNT(*) FROM transaction
WHERE type IN ('CustPymt','CustCred') AND foreignamountunpaid > 0