Cash Application: Process Mining Analysis

1. 0Executive summary
2. 1Method and assumptions
3. 2Structure — two cash paths
4. 3Touches to settle
5. 4Timing — invoice to cash, cash to bank
6. 5Unapplied cash — aging and cause
7. 6Open A/R and partial payments
8. 7Exception registers
9. 8Data quality
10. 9Findings and actions
11. ASuiteQL used
12. BHand-check

0Executive summary

Cash application in this account has two distinct shapes. Invoice cash is strictly one-to-one: 736 payments each settle exactly one invoice, 89% on the invoice date, and only one invoice (INV03) has ever received two applications (a $150 customer-deposit application plus a $934.78 payment). Store cash is many-to-one: 49 deposits sweep 1,026 cash sales in batches of 12–29 on the 23rd–25th of each month, so a cash sale waits a uniform 1–24 days (median 12) to reach the bank. There is no write-off activity and no journal against A/R in the period.

1. Unapplied cash is small but entirely avoidable. Twelve payments carry $3,245.96 unapplied. Three are material ($1,049.79, $960.11, $934.78) and belong to customers with no open invoice at all; one of them, PYMT01 (Susan Adams, $934.78, 2026-08-31), is a same-day, same-amount twin of PYMT675, which was applied to INV03 — a duplicate receipt awaiting refund. Two $32.65 payments from Finch Computing sit unapplied while Finch's INV765 has $190.37 open. Nine small payments ($27–$50, seven customers) have nothing to apply to.
2. Open A/R is 85% test data. Of $928,246.62 open across 39 invoices, 32 invoices ($790,017.65) carry a "TEST —" memo and were created on 2026-05-14 by the administrator, including all 11 partially-paid invoices and the two undeposited payments. Real open A/R is 7 invoices, $138,228.97, of which one — INV759 Blockster Inc., $53,424, 63 days past due — is overdue.
3. The store sweep leaves a tail. 51 cash sales are not linked to any deposit: 43 (status Deposited, two per month since Oct 2024, $6,370.47) were banked directly, bypassing Undeposited Funds — a second, unrecorded path; 8 in Sep 2026 ($2,265.58) are genuinely unswept, 7 of them with status Unapproved Payment.

1Method and assumptions

2Structure — two cash paths

Figure 1 — Two flows. Top: invoice cash, 1:1, 736 payments. Bottom: store cash, M:1, 1,026 cash sales into 49 deposits. Labels: count · median days.

3Touches to settle

System notes exist on 13 of 748 payments: the 11 test receipts and 2 (PYMT695, PYMT697) with a TRANLINE.SRECONCILIATIONSTATUS note written by -System- on 2026-09-04 02:03 — the only bank-reconciliation trace in the account.

4Timing — invoice to cash, cash to bank

Customers pay on or before due date 94% of the time; 443 invoices (60%) were settled exactly on their due date and 658 (89%) on their invoice date — the two coincide because most invoices carry due-on-receipt terms. The tail is 44 late payments, 4 of them more than 30 days (max 243). Store cash reaches the bank in a median of 12 days (uniform 1–24) because the sweep runs on the 23rd–25th regardless of when the sale occurred.

5Unapplied cash — aging and cause

All 12 are numbered PYMT01–PYMT16 (a separate sequence from the PYMT6xx/7xx production run) and status Deposited. No customer deposit record, no unapplied-payment journal, and no write-off exists against any of them.

6Open A/R and partial payments

7Exception registers

7-A · Cash sales not linked to a deposit (51, $8,636.05)

7-B · Production open invoices (7)

7-C · Late payments over 30 days (4)

Four invoice→payment pairs settled 34–243 days after due date; the 139- and 273-day invoice-to-payment lags belong to this cohort. Query Q6 lists them; they are production invoices from the Oct 2024 – Feb 2025 load.

8Data quality

9Findings and recommended actions

1. Clear the twelve. Apply PYMT05 + PYMT07 ($65.30) to Finch's INV765; refund PYMT01 ($934.78) as a duplicate of PYMT675; contact Donnie Rizzo ($1,099.78 across two receipts) and Robert Huffman ($960.11) for the invoice these were meant for or convert to customer deposits; bill Dillan Garcia and Bryan Scott for the recurring $32.65 / $27.75 receipts that have no document. Total effort: an afternoon. Total unapplied afterwards: zero.
2. Purge or flag the test A/R before the next aging report. $790K of $928K open A/R and every partial payment is test data created in one sitting. Either reverse the 32 invoices and 11 payments or exclude memo 'TEST —%' from every A/R saved search and dashboard; today the aging report is 85% noise. The one production overdue — INV759 Blockster $53,424, 63 days — is the only collection call to make.
3. Close the second cash path. 43 cash sales over 23 months were banked directly rather than through Undeposited Funds and the twice-monthly sweep. Decide whether direct banking is permitted; if so, record it as a deposit so reconciliation sees it; if not, find the form or role that allows it. Approve the 7 Sep 2026 cash-sale payments so they can be swept.

AAppendix A — SuiteQL used

Q1 — Census with unapplied amounts

SELECT x.type, x.status, x.docs, BUILTIN.DF(t.status) AS label, x.total, x.unapplied, x.with_unapplied
FROM (SELECT type, status, COUNT(*) AS docs, MIN(id) AS sample_id, ROUND(SUM(ABS(foreigntotal)),2) AS total,
             ROUND(SUM(foreignpaymentamountunused),2) AS unapplied, SUM(CASE WHEN foreignpaymentamountunused > 0 THEN 1 ELSE 0 END) AS with_unapplied
      FROM transaction WHERE type IN ('CustPymt','Deposit','DepAppl','CustDep','CustCred') GROUP BY type, status) x
JOIN transaction t ON t.id = x.sample_id ORDER BY x.type, x.docs DESC

Q2 — Multiplicity

SELECT 'invoices_per_payment' AS metric, k AS n, COUNT(*) AS docs FROM (SELECT l.nextdoc, COUNT(DISTINCT l.previousdoc) AS k FROM nexttransactionlinelink l
  JOIN transaction p ON p.id=l.previousdoc AND p.type='CustInvc' JOIN transaction n ON n.id=l.nextdoc AND n.type='CustPymt' WHERE l.linktype='Payment' GROUP BY l.nextdoc) GROUP BY k
UNION ALL SELECT 'payments_per_invoice', k, COUNT(*) FROM (SELECT l.previousdoc, COUNT(DISTINCT l.nextdoc) AS k FROM nexttransactionlinelink l
  JOIN transaction p ON p.id=l.previousdoc AND p.type='CustInvc' JOIN transaction n ON n.id=l.nextdoc AND n.type IN ('CustPymt','DepAppl','CustCred') WHERE l.linktype='Payment' GROUP BY l.previousdoc) GROUP BY k
UNION ALL SELECT 'cashsales_per_deposit', k, COUNT(*) FROM (SELECT l.nextdoc, COUNT(DISTINCT l.previousdoc) AS k FROM nexttransactionlinelink l
  JOIN transaction n ON n.id=l.nextdoc AND n.type='Deposit' GROUP BY l.nextdoc) GROUP BY k ORDER BY 1, 2

Q3 — Touches to settle

SELECT touches, COUNT(*) AS payments, ROUND(SUM(total),2) AS total FROM (
  SELECT p.id, ABS(p.foreigntotal) AS total,
    (SELECT COUNT(DISTINCT l.previousdoc) FROM nexttransactionlinelink l WHERE l.nextdoc=p.id)
  + (SELECT COUNT(*) FROM nexttransactionlinelink l WHERE l.previousdoc=p.id) + 1
  + (SELECT COUNT(*) FROM systemnote sn WHERE sn.recordid=p.id AND sn.recordtypeid=-30 AND sn.field='TRANDOC.KSTATUS') AS touches
  FROM transaction p WHERE p.type='CustPymt') GROUP BY touches ORDER BY touches

Q4 — Lag distributions

SELECT 'inv_to_pymt' AS metric, TRUNC(n.trandate) - TRUNC(p.trandate) AS days, COUNT(*) AS pairs FROM nexttransactionlinelink l
  JOIN transaction p ON p.id=l.previousdoc AND p.type='CustInvc' JOIN transaction n ON n.id=l.nextdoc AND n.type='CustPymt' WHERE l.linktype='Payment' GROUP BY TRUNC(n.trandate) - TRUNC(p.trandate)
UNION ALL SELECT 'cashsale_to_deposit', TRUNC(n.trandate) - TRUNC(p.trandate), COUNT(*) FROM nexttransactionlinelink l
  JOIN transaction p ON p.id=l.previousdoc AND p.type='CashSale' JOIN transaction n ON n.id=l.nextdoc AND n.type='Deposit' GROUP BY TRUNC(n.trandate) - TRUNC(p.trandate) ORDER BY 1, 2

Q5 — Payment vs due-date buckets

SELECT CASE WHEN d < -30 THEN 'a >30 early' WHEN d < -7 THEN 'b 8-30 early' WHEN d < 0 THEN 'c 1-7 early' WHEN d = 0 THEN 'd on due'
            WHEN d <= 7 THEN 'e 1-7 late' WHEN d <= 30 THEN 'f 8-30 late' ELSE 'g >30 late' END AS bucket, COUNT(*) AS pairs, MIN(d), MAX(d)
FROM (SELECT TRUNC(n.trandate) - TRUNC(p.duedate) AS d FROM nexttransactionlinelink l JOIN transaction p ON p.id=l.previousdoc AND p.type='CustInvc'
      JOIN transaction n ON n.id=l.nextdoc AND n.type='CustPymt' WHERE l.linktype='Payment') GROUP BY [same CASE] ORDER BY 1

Q6 — Unapplied payments with customer context

SELECT p.tranid, TO_CHAR(p.trandate,'YYYY-MM-DD') AS pay_date, BUILTIN.DF(p.entity) AS customer, ROUND(p.foreignpaymentamountunused,2) AS unapplied,
  (SELECT COUNT(*) FROM transaction i WHERE i.type='CustInvc' AND i.entity = p.entity AND i.foreignamountunpaid > 0) AS customer_open_invoices,
  (SELECT LISTAGG(i.tranid || ' $' || ROUND(i.foreignamountunpaid,2), '; ') WITHIN GROUP (ORDER BY i.trandate) FROM transaction i WHERE i.type='CustInvc' AND i.entity = p.entity AND i.foreignamountunpaid > 0) AS open_invoices,
  (SELECT LISTAGG(a.tranid || ' ' || TO_CHAR(a.trandate,'YYYY-MM-DD'), '; ') WITHIN GROUP (ORDER BY a.trandate) FROM transaction a
     WHERE a.type='CustPymt' AND a.entity=p.entity AND a.id<>p.id AND ABS(a.foreigntotal)=ABS(p.foreigntotal) AND a.foreignpaymentamountunused=0) AS applied_twin
FROM transaction p WHERE p.type='CustPymt' AND p.foreignpaymentamountunused > 0 ORDER BY p.foreignpaymentamountunused DESC

Q7 — Open A/R

SELECT i.id, i.tranid, TO_CHAR(i.trandate,'YYYY-MM-DD') AS trandate, TO_CHAR(i.duedate,'YYYY-MM-DD') AS duedate, BUILTIN.DF(i.entity) AS customer,
       ROUND(ABS(i.foreigntotal),2) AS total, ROUND(i.foreignamountpaid,2) AS paid, ROUND(i.foreignamountunpaid,2) AS unpaid, TRUNC(SYSDATE)-TRUNC(i.duedate) AS days_past_due, i.memo
FROM transaction i WHERE i.type='CustInvc' AND i.foreignamountunpaid > 0 ORDER BY i.foreignamountunpaid DESC

Q8 — Deposit sweep by month; unswept cash sales

SELECT TO_CHAR(d.trandate,'YYYY-MM') AS ym, COUNT(DISTINCT d.id) AS deposits, COUNT(DISTINCT l.previousdoc) AS cash_sales_swept, ROUND(SUM(ABS(d.foreigntotal)),2) AS deposited,
       MIN(TO_CHAR(d.trandate,'DD')) AS first_day, MAX(TO_CHAR(d.trandate,'DD')) AS last_day
FROM transaction d LEFT JOIN nexttransactionlinelink l ON l.nextdoc = d.id WHERE d.type='Deposit' GROUP BY TO_CHAR(d.trandate,'YYYY-MM') ORDER BY 1;

SELECT TO_CHAR(c.trandate,'YYYY-MM') AS ym, c.status, COUNT(*) AS cash_sales, ROUND(SUM(ABS(c.foreigntotal)),2) AS total, MIN(c.tranid) AS sample
FROM transaction c WHERE c.type='CashSale' AND NOT EXISTS (SELECT 1 FROM nexttransactionlinelink l JOIN transaction d ON d.id=l.nextdoc AND d.type='Deposit' WHERE l.previousdoc=c.id)
GROUP BY TO_CHAR(c.trandate,'YYYY-MM'), c.status ORDER BY 1

BAppendix B — Hand-check

Payments 746 + 2 = 748 ✓ · Touch cohorts 12 + 725 + 11 = 748; $3,245.96 + $1,982,069.19 + $35,880.00 = $2,021,195.15 = Σ payment value ($2,018,495.15 + $2,700) ✓ · Invoice→payment buckets 658 + 2 + 46 + 22 + 6 + 2 = 736 ✓ · Due-date buckets 70 + 178 + 1 + 443 + 39 + 1 + 4 = 736 ✓ · INV03: $150 + $934.78 = $1,084.78 = invoice total ✓ · Unapplied: 1,049.79 + 960.11 + 934.78 + 49.99 + 65.30 + 97.95 + 55.50 + 32.54 = $3,245.96 ✓ · Open A/R: 790,017.65 + 138,228.97 = 928,246.62 ✓ · Production open: 53,424 + 25,355.33 + 15,687.25 + 13,841.83 + 12,447 + 11,302.23 + 6,171.33 = 138,228.97 ✓ · Unswept: 43 + 7 + 1 = 51; 6,370.47 + 2,153.77 + 111.81 = 8,636.05 ✓ · Deposits: Σ monthly = 49; Σ cash sales swept = 1,026 ✓.