The company spent $2.26M with 31 vendors across 555 bills in the trailing 12 months — and the run-rate nearly doubled mid-year. Second-half spend (+91.7% vs. first half) is being driven by genuine volume growth plus two August one-offs ($120K lease prepayment, $52.5K consulting). Cash paid out to vendors in August alone was $538K.
The purchasing machine is disciplined where it's automated and loose where it's manual. The inventory pipeline is exemplary: 388 POs flowed to bills in 0–3 days, receipts matched, no stale commitments. But 45% of spend ($1.02M) arrives with no PO, 23.6% carries no department attribution, and two contracts totaling $334K/yr have billed identical amounts for 12 straight months without a re-bid.
The payment policy is exactly backwards. Bills are paid on average 4.2 days after receipt — 24 days before due — surrendering ~$137K of average float (~$6,800/yr at 5%). Meanwhile all five bills that offered early-pay discounts missed their windows, forfeiting ~$1,810. Fixing both is recommendation #2 and worth ~$8,600/yr at current volume.
Three items need eyes this week: an open bill with a 285× price anomaly (VB397, $2,995 vs. the established $10.48 rate), a first-ever $120K round-number bill from a brand-new vendor due Sep 2 (Davidson Leasing — verify bank details out-of-band), and a $31K round-number bill dated Christmas Day. A first-digit (Benford) test on all 555 amounts also fails at the 1% significance level — §06 explains why that is probably structural, and how to confirm.
Monthly vendor-bill totals [Q1]. August 2026 hit 199% of the monthly average — the biggest month of the year. Dashed overlay = August restated without its two one-off transactions.
*Aug restated excl. Davidson Leasing $120,000 (Prepaid Expenses) and Cloud Consulting $52,550 (Training) [Q8]. The underlying growth is real regardless: Apr–Aug averages $262K/mo vs. $122K/mo for Sep–Jan. Two structural drivers: bill counts jumped from ~31/mo to ~57–81/mo starting April (new vendors Johnson Supply, Crown Equipment, Cloud Consulting onboarded May–Jul), and cash-out followed one month behind, peaking at $538K in August [Q9]. If H2 is the new normal, annualized purchasing is ~$3.0M — budget accordingly.
Top 10 of 31 vendors by 12-month spend [Q2].
Top 10 = 90.9%. Herfindahl-Hirschman index ≈ 1,290 (computed over vendor spend shares; 1,000–1,800 = moderately concentrated by DOJ convention). Losing Generation N or Bedline tomorrow would disrupt a quarter to a sixth of the supply base overnight — and §04 shows 79 items have no second source at all.
Over half the active vendor base (18 of 31) collectively bills under $113K. Nine sent exactly one bill all year — including "Well" ($1,000), "China Manufacturer" ($750) and "Harris Technology" ($20). Each one-off vendor is onboarding overhead and fraud surface with almost zero purchasing leverage in return.
Vendors first billed after 2026-02-24 already account for $233,426 (10.3%) of 12-month spend [Q13] — led by Davidson Leasing ($120K, single bill) and Cloud Consulting ($53.6K in 3 bills over 5 weeks). Fast vendor onboarding without a verification protocol is how payment-diversion fraud gets in.
A 31-vendor base on $2.26M spend (~$73K/vendor) is lean — many mid-market companies run 3–5× more vendors per spend dollar. The problem isn't count; it's the shape: heavy head, unmanaged tail, no mid-tier redundancy.
Vendor-bill spend by GL posting account, A/P offset excluded [Q3].
70% of spend is resale inventory — healthy for retail/distribution. The tells are in the indirect lines: $84,207/yr of telephone service (all Brocade, 24 bills of ~$3.5K like clockwork) and $249,755/yr of advertising (all FrisCo US, ~$10.4K/mo, identical cadence). Steady, identical, never re-bid — the exact profile of contracts running on autopilot. Also note $12.1K of fixed-asset purchases (accounts 1610/1620) came through as plain vendor bills — confirm they were also registered in Fixed Assets Management, which is installed in this account.
| Item | Bills | Qty | Spend |
|---|---|---|---|
| INV_Black Leather Jacket | 38 | 586 | $117,200 |
| INV_Black Leather Valise | 38 | 638 | $82,302 |
| INV_Gold Watch w/ Leather Strap | 36 | 319 | $70,180 |
| INV_Brown Leather Satchel | 36 | 196 | $58,798 |
| INV_Brown Leather Valise | 37 | 333 | $53,280 |
| INV_Canvas Backpack | 36 | 345 | $42,090 |
| 6 more… | $199,346 |
The top 6 items alone are $424K — and per §05, 79 of 154 purchased items are single-sourced. Where the top items and the single-source list intersect is your highest supply-chain risk.
| Dimension | Share of spend |
|---|---|
| Subsidiary 1 [Q14] | $1,635,133 · 72.3% |
| Subsidiary 2 | $625,308 · 27.6% |
| Parent Company | $1,564 · 0.1% |
| By department [Q15] | |
| Warehouse Operations | $999,538 · 44.2% |
| (no department) | $534,437 · 23.6% |
| Sales | $499,841 · 22.1% |
| Store Ops / Production / Admin | $228,190 · 10.1% |
Nearly a quarter of line-level spend has no department — which means departmental P&Ls understate real cost consumption by up to $534K. Most of it is the big no-PO bills (lease, advertising, consulting). A make-department-mandatory rule on bill entry closes this.
Anomalies surfaced by cross-cutting queries — each verified against live transaction data, each with its query in Appendix B.
Item INV_2-Layer Copper costs $10.48/unit on all 5 Core4Solutions bills this year. Then bill VB397 (Generation N, 2026-08-01) billed 1 unit at $2,995.00 — 285× the established rate, from a vendor that doesn't normally supply this item. VB397 is still open ($2,995 unpaid, due Aug 31). Verify before paying: likely a mis-keyed line or wrong item selected.
Only five bills all year carried 1%/2% 10-day discount terms — including Davidson Leasing's $120,000 (worth $1,200) and Cloud Consulting's $52,550 (worth $525). Every one of the five 10-day windows expired unpaid by Aug 13. Total forfeited: ~$1,810. The irony: you pay everything else 24 days early (next card) — just not the bills that reward it.
Average bill-to-payment: 4.2 days against predominantly Net 30 terms (87% of bills are Net 30 [Q10]). 533 of 542 paid bills (98.3%) settled 10+ days early; only 8 were ever late. That's ~$137K of average float handed to vendors ($2.078M paid spend × 24/365), worth ~$6,832/yr at a 5% cost of capital — with zero discounts captured in return. Monthly trend [Q16] shows this is policy, not accident: every single month averages under 10 days.
$1.02M across 167 bills hit A/P with no PO behind them, vs. $1.24M PO-backed (388 bills). No PO = no receiving match, no pre-commitment approval. Some is legitimately non-PO (lease, telecom, consulting) — but at 45% it's a habit, not an exception. The PO-backed pipeline, by contrast, is airtight: PO→bill in 0–3 days, average 1.1 [Q17].
161 bills totaling $660,720 carry Sat/Sun transaction dates; heaviest weekend biller is Bedline ($163K over 15 weekend bills). And bill VB04 — a round $31,000 to Generation N — is dated December 25, 2025 [Q8]. Weekend dating usually means backdated batch entry (consistent with the Monday entry spike in §07), but round-number holiday bills are textbook audit flags. Pull the paper on VB04.
51% of purchased items — $1.02M of 12-month spend — came from exactly one vendor. Combined with top-5 concentration of 72.6% and 70% of spend being resale inventory, a single vendor failure could stall both stores and DCs. Start with alternates for the top-6 leather items in §04.
Davidson Leasing first appeared 2026-08-03 with a single $120,000 round-number bill (no document number recorded), booked to Prepaid Expenses, carrying a 1%-10 discount nobody took, due 2026-09-02. Large + round + first-ever + missing tranid is the classic new-vendor-fraud checklist. Presumably a legitimate lease prepayment — but confirm bank details out-of-band before the payment run, and attach the lease to the vendor record.
A $1,500 Bedline bill has sat in approval since June 1. Two Flexsteel "interco alloc" bills ($1,000 each) are 30 and 61 days past due — intercompany allocations that miss month-end distort both subsidiaries' P&Ls. And 6 bills totaling $3,833 have no payment terms, so their due dates are guesses [Q10].
First digits of naturally-occurring amounts follow a logarithmic curve (30.1% start with 1, 4.6% with 9). Auditors test invoice populations against it. Solid bars = your 555 bill amounts [Q20]; hollow bars = Benford expectation.
Labels = observed% / expected%. χ² = 48.9 on 8 degrees of freedom — the population fails conformity at the 1% level (critical value 20.09); mean absolute deviation 2.29pp ("nonconformity" on the Nigrini scale). Two digits drive it: digit 3 is over-represented (19.3% vs 12.5%) and digit 8 is nearly absent (4 bills vs ~28 expected).
Read this carefully before reaching for a pitchfork. Benford assumes amounts spanning several orders of magnitude from many independent processes. This population is 555 bills from 31 vendors, several billing fixed installments — Broyhill alone repeats $13,900 ×4, $10,530.50 ×4, $9,500.50 ×4, $9,103 ×4 (all digit-consumers for 1, 9 and 3). Repeated contract amounts mechanically distort digit frequencies; that's the innocent explanation and the likely one. The useful signal: the digit-3 excess and digit-8 hole are concentrated in mid-size ($3K–$40K) bills — if you ever commission a fraud review, that's the stratum to sample first. This is a screening statistic, not an accusation.
| Pattern | Evidence | Read |
|---|---|---|
| Recurring "subscription-like" bills | FrisCo US, Dell US, Brocade, Staples, XCOM, CDW each billed exactly 24× (2/mo), same accounts, near-identical amounts [Q2/Q3] | Predictable — automate matching |
| Repeated same-amount bills | 25 vendor+amount pairs repeat ≥3× — Broyhill $13,900 ×4, Bedline $12,373.50 ×4, etc. [Q21] | Verify installments, not duplicates |
| PO discipline where it counts | 388 closed POs worth $1.24M flowed PO → receipt → bill in 0–3 days (avg 1.1) [Q17]; only 2 POs pending receipt ($2,050) + 19 pending billing ($29.7K) [Q22] | Inventory pipeline is airtight |
| Monday is billing day | 99 bills / $629K land on Mondays — the heaviest day; Tuesday quietest (49) [Q18]. Consistent with weekend-dated bills being entered Monday. | Batch-entry cadence |
| Payment velocity is policy | Monthly avg days-to-pay ranged 1.8–9.9 all year; since April it's locked at 2.3–3.2 days [Q16] — payments accelerated exactly as spend doubled | Cash going out faster as volume grows |
Every finding, scored. Severity = likelihood × financial impact; effort = time to remediate.
| # | Risk | Exposure | Severity | Effort | Owner (suggested) |
|---|---|---|---|---|---|
| R1 | VB397 price anomaly paid as-billed | $2,985 | High | 10 min | A/P |
| R2 | Davidson Leasing unverified before $120K payment | $120,000 | High | 1 hour | Controller |
| R3 | Single-vendor dependency (top 5 = 72.6%; 79 single-sourced items) | ~$1.0M | High | Quarters | Purchasing |
| R4 | Backwards payment policy (float + missed discounts) | ~$8,600/yr | Medium | 1 week | Controller |
| R5 | No-PO spend at 45% — no pre-approval on $1.02M | $1.02M flow | Medium | 2 weeks | Purchasing |
| R6 | Autopilot contracts never re-bid (telecom + advertising) | $334K/yr | Medium | 1 month | Purchasing |
| R7 | $534K spend with no department attribution | Reporting | Medium | 1 day | Controller |
| R8 | VB04 — $31K round-number bill dated Dec 25 | $31,000 | Medium | 30 min | Controller |
| R9 | Approval queue stalls (55-day pending bill; late interco allocs) | $4.5K + P&L timing | Low | 1 hour | A/P |
| R10 | Benford nonconformity (χ²=48.9) — screening flag only | Unknown | Low | If audited | — |
Ordered by urgency, then by return on effort.
Three specific transactions (R1, R2, R8). VB397: confirm the $2,995 line with Generation N or correct it. Davidson Leasing: phone-verify bank details using an independently-sourced number before the Sep 2 due date — and take the $1,200 discount while you're at it if paying by Aug 13 had been possible, or negotiate its extension. VB04: retrieve the source document for a $31K round bill dated Christmas Day.
Pay discount-term bills inside their 10-day window (a 2/10 Net 30 discount is a ~36% annualized return — nothing else you do with cash beats it); schedule everything else at day 28 via payment batches. Today the policy is precisely inverted: 98% of ordinary bills paid 10+ days early, 100% of discount bills paid late.
Telephone at $84K/yr (Brocade) and advertising at $250K/yr (FrisCo US) have billed identical amounts for 12 straight months. Telecom is a market where competitive quotes routinely save 20–40%; media buying at $250K/yr justifies an agency review or at minimum a rate benchmark.
Require a PO (or named contract reference) for any non-inventory purchase over ~$2,500, and make department mandatory on bill entry. The first rule puts pre-approval on ~$800K/yr that currently arrives as a surprise; the second closes the $534K attribution hole so departmental P&Ls mean something.
For the top 5 vendors (72.6% of spend): document alternates for the 79 single-sourced items, starting with the six leather SKUs that alone carry $424K. For the tail: retire or consolidate the 9 one-bill vendors and route future one-offs through an existing vendor or a P-card.
Three saved searches close the loop: (a) bills in Pending Approval > 7 days; (b) new-vendor first bills > $10K; (c) line rate > 3× trailing-average rate for the same item. Each maps directly to an incident found in this review (55-day stall, Davidson Leasing, VB397). Sonar can build all three on request.
Every number in this report traces to one of these queries, run live against account TD3016323 on 2026-08-24 via Sonar AI. Paste any of them into the SuiteQL Query Tool to reproduce. Conventions: foreigntotal is negative on vendor bills (wrapped in ABS for display); statuses use single-letter codes (A=Open, B=Paid, D=Pending Approval).
SELECT TO_CHAR(t.trandate, 'YYYY-MM') AS month,
COUNT(*) AS bill_count,
ROUND(SUM(t.foreigntotal), 2) AS total_billed
FROM transaction t
WHERE t.type = 'VendBill'
AND t.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
GROUP BY TO_CHAR(t.trandate, 'YYYY-MM')
ORDER BY TO_CHAR(t.trandate, 'YYYY-MM')SELECT v.id AS vendor_id, v.entityid AS vendor,
COUNT(t.id) AS bills,
ROUND(SUM(t.foreigntotal), 2) AS total_spend,
ROUND(AVG(t.foreigntotal), 2) AS avg_bill,
MIN(TO_CHAR(t.trandate, 'YYYY-MM-DD')) AS first_bill,
MAX(TO_CHAR(t.trandate, 'YYYY-MM-DD')) AS last_bill
FROM transaction t
JOIN vendor v ON t.entity = v.id
WHERE t.type = 'VendBill'
AND t.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
GROUP BY v.id, v.entityid
ORDER BY SUM(t.foreigntotal) DESCSELECT a.accttype, a.acctnumber, a.fullname,
COUNT(DISTINCT t.id) AS bills,
ROUND(SUM(tal.amount), 2) AS total_amount
FROM transactionaccountingline tal
JOIN transaction t ON tal.transaction = t.id
JOIN account a ON tal.account = a.id
WHERE t.type = 'VendBill' AND t.posting = 'T'
AND t.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
AND a.accttype <> 'AcctPay'
GROUP BY a.accttype, a.acctnumber, a.fullname
ORDER BY SUM(tal.amount) DESCSELECT origin, COUNT(*) AS bills, ROUND(SUM(total), 2) AS total
FROM (
SELECT t.id, ABS(t.foreigntotal) AS total,
CASE WHEN MAX(CASE WHEN tl.createdfrom IS NOT NULL
THEN 1 ELSE 0 END) = 1
THEN 'PO-backed' ELSE 'No PO' END AS origin
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type = 'VendBill'
AND t.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
GROUP BY t.id, ABS(t.foreigntotal)
)
GROUP BY originSELECT ROUND(AVG(TRUNC(t.closedate) - TRUNC(t.trandate)), 1) AS avg_days_to_pay,
ROUND(AVG(TRUNC(t.closedate) - TRUNC(t.duedate)), 1) AS avg_days_vs_due,
SUM(CASE WHEN TRUNC(t.closedate) > TRUNC(t.duedate) THEN 1 ELSE 0 END) AS paid_late,
SUM(CASE WHEN TRUNC(t.closedate) <= TRUNC(t.duedate) - 10 THEN 1 ELSE 0 END) AS paid_10plus_days_early,
COUNT(*) AS paid_bills
FROM transaction t
WHERE t.type = 'VendBill' AND t.status = 'B'
AND t.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
AND t.duedate IS NOT NULL AND t.closedate IS NOT NULLSELECT i.itemid, i.displayname, COUNT(DISTINCT t.id) AS bills,
ROUND(MIN(ABS(tl.rate)), 2) AS min_rate,
ROUND(MAX(ABS(tl.rate)), 2) AS max_rate,
ROUND((MAX(ABS(tl.rate)) - MIN(ABS(tl.rate)))
/ NULLIF(MIN(ABS(tl.rate)), 0) * 100, 1) AS pct_swing,
ROUND(SUM(ABS(tl.netamount)), 2) AS total_spend
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
AND tl.mainline = 'F' AND tl.taxline = 'F'
JOIN item i ON i.id = tl.item
WHERE t.type = 'VendBill'
AND t.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
AND tl.rate IS NOT NULL AND ABS(tl.rate) > 0
GROUP BY i.itemid, i.displayname
HAVING MAX(ABS(tl.rate)) > MIN(ABS(tl.rate))
ORDER BY (MAX(ABS(tl.rate)) - MIN(ABS(tl.rate)))
/ NULLIF(MIN(ABS(tl.rate)), 0) DESC
FETCH FIRST 15 ROWS ONLYSELECT i.itemid, ROUND(ABS(tl.rate), 2) AS rate,
ROUND(ABS(tl.netamount), 2) AS amount, tl.quantity,
t.tranid, TO_CHAR(t.trandate, 'YYYY-MM-DD') AS bill_date,
v.entityid AS vendor
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
AND tl.mainline = 'F' AND tl.taxline = 'F'
JOIN item i ON i.id = tl.item
JOIN vendor v ON v.id = t.entity
WHERE t.type = 'VendBill' AND i.itemid = 'INV_2-Layer Copper'
AND t.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
ORDER BY t.trandateSELECT v.entityid AS vendor, t.tranid,
TO_CHAR(t.trandate, 'YYYY-MM-DD') AS trandate,
ROUND(ABS(t.foreigntotal), 2) AS amount
FROM transaction t
JOIN vendor v ON t.entity = v.id
WHERE t.type = 'VendBill'
AND t.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
AND MOD(ABS(t.foreigntotal), 1000) = 0
AND ABS(t.foreigntotal) >= 5000
ORDER BY ABS(t.foreigntotal) DESCSELECT TO_CHAR(t.trandate, 'YYYY-MM') AS month,
COUNT(*) AS payments,
ROUND(SUM(ABS(t.foreigntotal)), 2) AS cash_out
FROM transaction t
WHERE t.type = 'VendPymt'
AND t.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
GROUP BY TO_CHAR(t.trandate, 'YYYY-MM')
ORDER BY TO_CHAR(t.trandate, 'YYYY-MM')SELECT COALESCE(tm.name, '(no terms)') AS terms,
COUNT(*) AS bills,
ROUND(SUM(ABS(t.foreigntotal)), 2) AS total
FROM transaction t
LEFT JOIN term tm ON t.terms = tm.id
WHERE t.type = 'VendBill'
AND t.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
GROUP BY COALESCE(tm.name, '(no terms)')
ORDER BY SUM(ABS(t.foreigntotal)) DESCSELECT v.entityid AS vendor, t.tranid,
TO_CHAR(t.trandate, 'YYYY-MM-DD') AS bill_date,
TO_CHAR(t.duedate, 'YYYY-MM-DD') AS due_date,
ROUND(t.foreignamountunpaid, 2) AS unpaid,
TRUNC(SYSDATE) - TRUNC(t.duedate) AS days_past_due, t.status
FROM transaction t
JOIN vendor v ON t.entity = v.id
WHERE t.type = 'VendBill' AND t.status IN ('A', 'D')
AND t.foreignamountunpaid > 0
ORDER BY t.duedate;
SELECT v.entityid AS vendor, t.tranid, tm.name AS terms, t.status,
TO_CHAR(t.trandate, 'YYYY-MM-DD') AS bill_date,
TO_CHAR(t.duedate, 'YYYY-MM-DD') AS due_date,
ROUND(ABS(t.foreigntotal), 2) AS amount
FROM transaction t
JOIN vendor v ON v.id = t.entity
JOIN term tm ON tm.id = t.terms
WHERE t.type = 'VendBill'
AND t.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
AND tm.name LIKE '%10 Net%'
ORDER BY t.trandateSELECT i.itemid, i.itemtype, COUNT(DISTINCT t.id) AS bills,
ROUND(SUM(ABS(tl.netamount)), 2) AS spend,
SUM(ABS(tl.quantity)) AS qty
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
AND tl.mainline = 'F' AND tl.taxline = 'F'
JOIN item i ON i.id = tl.item
WHERE t.type = 'VendBill'
AND t.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
GROUP BY i.itemid, i.itemtype
ORDER BY SUM(ABS(tl.netamount)) DESC
FETCH FIRST 12 ROWS ONLYSELECT v.entityid AS vendor,
TO_CHAR(MIN(t.trandate), 'YYYY-MM-DD') AS first_bill_ever,
COUNT(*) AS bills_12mo,
ROUND(SUM(ABS(t.foreigntotal)), 2) AS spend_12mo
FROM transaction t
JOIN vendor v ON v.id = t.entity
WHERE t.type = 'VendBill'
GROUP BY v.entityid
HAVING MIN(t.trandate) >= TO_DATE('2026-02-24', 'YYYY-MM-DD')
ORDER BY SUM(ABS(t.foreigntotal)) DESCSELECT s.name AS subsidiary, COUNT(DISTINCT t.id) AS bills,
ROUND(SUM(ABS(t.foreigntotal)), 2) AS total
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id AND tl.mainline = 'T'
JOIN subsidiary s ON s.id = tl.subsidiary
WHERE t.type = 'VendBill'
AND t.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
GROUP BY s.name
ORDER BY SUM(ABS(t.foreigntotal)) DESCSELECT COALESCE(d.name, '(none)') AS department,
COUNT(DISTINCT t.id) AS bills,
ROUND(SUM(ABS(tl.netamount)), 2) AS total
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
AND tl.mainline = 'F' AND tl.taxline = 'F'
LEFT JOIN department d ON d.id = tl.department
WHERE t.type = 'VendBill'
AND t.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
GROUP BY COALESCE(d.name, '(none)')
ORDER BY SUM(ABS(tl.netamount)) DESCSELECT TO_CHAR(t.trandate, 'YYYY-MM') AS month,
ROUND(AVG(TRUNC(t.closedate) - TRUNC(t.trandate)), 1) AS avg_days_to_pay,
COUNT(*) AS paid_bills
FROM transaction t
WHERE t.type = 'VendBill' AND t.status = 'B'
AND t.closedate IS NOT NULL
AND t.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
GROUP BY TO_CHAR(t.trandate, 'YYYY-MM')
ORDER BY TO_CHAR(t.trandate, 'YYYY-MM')SELECT ROUND(AVG(TRUNC(vb.trandate) - TRUNC(po.trandate)), 1) AS avg_po_to_bill_days,
MIN(TRUNC(vb.trandate) - TRUNC(po.trandate)) AS min_days,
MAX(TRUNC(vb.trandate) - TRUNC(po.trandate)) AS max_days,
COUNT(DISTINCT vb.id) AS bills
FROM transaction vb
JOIN transactionline tl ON tl.transaction = vb.id
AND tl.createdfrom IS NOT NULL
JOIN transaction po ON po.id = tl.createdfrom AND po.type = 'PurchOrd'
WHERE vb.type = 'VendBill'
AND vb.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')SELECT TO_CHAR(t.trandate, 'DY') AS weekday, COUNT(*) AS bills,
ROUND(SUM(ABS(t.foreigntotal)), 2) AS total
FROM transaction t
WHERE t.type = 'VendBill'
AND t.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
GROUP BY TO_CHAR(t.trandate, 'DY')
ORDER BY COUNT(*) DESC;
SELECT v.entityid AS vendor, COUNT(*) AS weekend_bills,
ROUND(SUM(ABS(t.foreigntotal)), 2) AS total
FROM transaction t
JOIN vendor v ON t.entity = v.id
WHERE t.type = 'VendBill'
AND t.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
AND TO_CHAR(t.trandate, 'DY') IN ('SAT', 'SUN')
GROUP BY v.entityid
ORDER BY SUM(ABS(t.foreigntotal)) DESCSELECT COUNT(DISTINCT i.id) AS single_sourced_items,
ROUND(SUM(spend), 2) AS spend
FROM (
SELECT tl.item AS itemid, COUNT(DISTINCT t.entity) AS vendors,
SUM(ABS(tl.netamount)) AS spend
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
AND tl.mainline = 'F' AND tl.taxline = 'F'
WHERE t.type = 'VendBill'
AND t.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
AND tl.item IS NOT NULL
GROUP BY tl.item
HAVING COUNT(DISTINCT t.entity) = 1
) x
JOIN item i ON i.id = x.itemid
-- Companion: total distinct items = 154, vendors = 20 (same query without HAVING)SELECT SUBSTR(TO_CHAR(TRUNC(ABS(t.foreigntotal))), 1, 1) AS first_digit,
COUNT(*) AS cnt
FROM transaction t
WHERE t.type = 'VendBill'
AND t.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
AND ABS(t.foreigntotal) >= 1
GROUP BY SUBSTR(TO_CHAR(TRUNC(ABS(t.foreigntotal))), 1, 1)
ORDER BY SUBSTR(TO_CHAR(TRUNC(ABS(t.foreigntotal))), 1, 1)SELECT v.entityid AS vendor, ROUND(ABS(t.foreigntotal), 2) AS amount,
COUNT(*) AS occurrences,
MIN(TO_CHAR(t.trandate, 'YYYY-MM-DD')) AS first_date,
MAX(TO_CHAR(t.trandate, 'YYYY-MM-DD')) AS last_date
FROM transaction t
JOIN vendor v ON t.entity = v.id
WHERE t.type = 'VendBill'
AND t.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
AND ABS(t.foreigntotal) >= 1000
GROUP BY v.entityid, ABS(t.foreigntotal)
HAVING COUNT(*) >= 3
ORDER BY ABS(t.foreigntotal) * COUNT(*) DESC
FETCH FIRST 25 ROWS ONLYSELECT COUNT(*) AS pos, ROUND(SUM(ABS(t.foreigntotal)), 2) AS po_value, t.status
FROM transaction t
WHERE t.type = 'PurchOrd'
AND t.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
GROUP BY t.status ORDER BY COUNT(*) DESC;
SELECT t.status, COUNT(*) AS cnt,
ROUND(SUM(t.foreigntotal), 2) AS total,
ROUND(SUM(t.foreignamountunpaid), 2) AS unpaid
FROM transaction t
WHERE t.type = 'VendBill'
AND t.trandate >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
GROUP BY t.status ORDER BY COUNT(*) DESC