Transaction Flow Reconstruction & Conformance Analysis

Executive summary

This analysis reconstructed the account's end-to-end transaction flows directly from the document-link graph (nexttransactionlinelink, 22,762 line-level link events across 40 distinct transition types) and the record-level audit trail (systemnote), rather than from process documentation. Four conclusions stand out.

First, execution discipline in operations is high. 88.4% of fulfilled orders ship and invoice on the same day; the purchase-to-receipt cycle runs at a median of 0 days with a maximum of 3. The transactional machinery is fast and consistent.

Second, the constraint is not operations — it is receivables. The open invoice queue totals $964,127 (32.7% of all-time invoiced revenue) at an average age of 128 days. $472.9K is more than 90 days past due, including single invoices outstanding 460 and 327 days. By contrast, vendor bills are paid at a median of 3 days — the account systematically extends credit it does not collect while surrendering payables float it is entitled to.

Third, a structured anomaly ran May–July 2026. Eighteen orders followed a uniform inverted pattern — invoiced exactly ~15 days after order, shipped ~46 days after order — inverting the fulfill-then-bill sequence. The same window contains the account's only fulfillment backlog (average ship lag rose from a 0-day baseline to 6–9 days) and virtually all post-creation edit churn. The pattern's uniformity indicates a deliberate arrangement (bill-and-hold-like) or scripted activity, not organic delay; it warrants a policy determination because it carries revenue-recognition implications.

Fourth, the rework loops are small but currently unmanaged. Of four return authorizations opened since July, only one has completed the receive-and-credit cycle; one has held received goods without a credit memo for 37 days. The quotation stage is bypassed for ~99% of orders, and of the 10 quotes that do exist, half expired unactioned.

Method & evidence base

Classic process mining requires an event log: case ID, activity, timestamp. NetSuite does not expose one directly, so three sources were combined to synthesize it:

Cases were built per order: each Sales Order (743 with downstream links) and Purchase Order (724) was traced through its first shipment/receipt, first invoice/bill, and payment, with lag computed at each hop. Conformance was tested by classifying every case into its observed variant (the actual sequence of steps) and comparing against the reference model below. All 14 queries used are reproduced verbatim in the Appendix.

The process as observed

The transition-frequency map below is drawn purely from link events — it shows where documents actually go, weighted by how many document pairs traverse each edge. Solid edges carry the volume; dashed nodes are stages the standard model expects but the data shows are nearly vacant.

What the map reveals

• The quote stage functionally does not exist. 788 sales orders vs. 10 estimates all-time; exactly one estimate ever converted (EST17). Quoting began only in July 2026 and 5 of 10 quotes have already expired unactioned.
• A second, parallel revenue engine runs on cash sales — 1,077 cash-sale documents ($176.6K, avg ticket ≈$164, the retail stores) flowing straight to deposit, versus 764 invoices ($2.95M, avg ≈$3,857, the wholesale/DC channel). The two channels have entirely different risk profiles: cash sales settle instantly; invoices create the receivables exposure quantified in Section 7.
• Minor but real side-flows exist that documentation rarely captures: drop-ship (SO→PO, 2 cases), special-order work orders (SO→WO, 1), transfer-order logistics (7), and landed-cost allocation (VendBill→ItemRcpt, 1). None are volume concerns.
• Light manufacturing runs quietly: 13 work orders → 72 build links, fully decoupled from the O2C flow.

Conformance: designed vs. actual

Every linked sales order was classified into its observed variant. The standard sequence (fulfill, then invoice) holds for 95.7% of cases — but the shape of conformance is a same-day collapse, and the exceptions are not random.

4.1 The inverted-sequence cluster

The 20 invoice-before-shipment cases are dominated by an 18-order cluster (SO4270–SO4287, May–July 2026) with a strikingly uniform signature: invoice raised ~15 days after order date, goods shipped ~46 days after order date — a 29–31 day window in which revenue was billed against unshipped inventory. The affected customers repeat (Design Excellence Ltd. ×4, Meetz Industries, JBL Inc., Godric Motors ×2, Hugo Limited, Marshall Industries, Blockster Inc., Jones Manufacturing). The uniform offsets rule out organic delay; this is either a deliberate billing arrangement (bill-and-hold-like terms) or scripted/batch activity.

4.2 Shipped but never invoiced — direct revenue leakage

Eleven orders show a completed shipment with no downstream invoice link: $4,836.79 of delivered goods with no bill raised. Individually small, but this is precisely the defect class that recurs silently. Largest: SO3174 (Shirley Stephens, $2,270.64, shipped 2026-08-08); the rest span June–September 2026 (Q8 lists all 11).

4.3 Procure-to-pay conformance

P2P is the mirror image: near-perfect. 722 of 724 traced POs received before billing (only 2 billed before receipt), median receipt lag 0 days, maximum 3 days, bill raised ~1 day post-receipt. The 3-way-match sequence is effectively self-enforcing here.

Bottlenecks & cycle times

5.1 The May–July 2026 fulfillment backlog — visible only in the event log

Monthly average ship-lag was zero for eight consecutive months, then rose sharply in May 2026 and self-cleared by August. The backlog is entirely attributable to the 18-order inverted cluster plus a handful of adjacent delays — 18 orders exceeded 30 days in the window. No aggregate KPI on the standard dashboards would surface this: monthly shipment counts stayed normal (33–44/month) while individual orders quietly aged.

5.2 Current open queues (as of Aug 22, 2026)

Rework loops

6.1 The returns loop stalls at the credit step

Return volume is negligible (4 RMAs against 788 orders — 0.5%), and returns only began in July 2026. But the loop that exists does not close: the designed sequence RMA → receive goods → issue credit has completed exactly once.

All four RMAs belong to one customer — Design Excellence Ltd. — who is also the most frequent name in the inverted-billing cluster (§4.1). This account concentrates a disproportionate share of every anomaly in the dataset and merits a dedicated account review.

6.2 Edit churn: rework concentrated on exactly the anomalous orders

Post-creation edits are the invisible rework of ERP processes. The audit trail shows a starkly bimodal distribution: 774 of 788 sales orders (98.2%) were never edited after creation, while 14 orders absorbed all the churn — 9 to 39 field-change events each, touching 12–16 distinct fields (amounts, revenue status, addresses, shipped/picked quantities). Those 14 churned orders sit in the same April–August window and the same SO42xx range as the inverted-billing cluster. Actor analysis (Q10) attributes the edits to a single administrator, executed predominantly through Suitelet and RESTlet contexts (SLT 71%, RST 12%) rather than the UI — i.e., tool-driven activity, consistent with the scripted-pattern hypothesis in §4.1.

6.3 The quote stage: a loop that never engages

10 estimates all-time vs. 788 orders. One converted (10%), five expired (50%), four remain open. Quoting appeared in July 2026, presumably as a new practice — at current conversion it adds an administrative step with no measurable funnel value. Either commit to it (route the wholesale channel through quotes, track conversion) or drop it.

The working-capital asymmetry

Putting both cash cycles side by side exposes the account's most expensive habit. Vendor bills are settled at a median of 3 days — 991 of 1,004 within 5 days — surrendering essentially all payables float (on Net-30 terms, ~26 days of free financing per bill). Customers who pay do so quickly, but the unpaid queue is aging without intervention: 39 invoices, $964K, of which 49% is beyond 90 days past due.

Ten invoices are 87% of the problem

The paid population proves customers in this book can pay fast (median 0 days; only 44 of 736 paid invoices were ever late). The aged queue is therefore not a customer-behavior problem across the portfolio — it is a small set of large accounts with no active collections process. Ten phone calls address $704K.

Findings register

Recommendations

1. Stand up a collections cadence this week (F1). Ten invoices are 73% of open AR. Assign an owner, contact the top ten, and instrument the queue: a saved search on CustInvc status A bucketed by days-past-due, reviewed weekly. Target: nothing crosses 60 days without a documented action.
2. Adopt a payables calendar (F4). Schedule bill payments to terms minus a safety margin (e.g., day 25 on Net 30) unless a discount justifies early payment. This is a settings-and-habit change, not a build.
3. Make a policy determination on the billed-before-shipped pattern (F2). If bill-and-hold is intended for these wholesale accounts, document acceptance criteria on the orders; if not, enforce sequence with an invoice-time validation (block or warn when invoicing an order with unfulfilled quantity).
4. Close the leakage set (F3). Invoice or formally close the 11 shipped-uninvoiced orders, then keep a permanent exception search: fulfillments > N days old with no linked invoice. This report's Q8 is the exact query.
5. Publish SLAs on the three approval queues (F6) — vendor-bill approval, SO approval, receipt-to-bill — with a weekly aging review. The event log shows queues only misbehave when nobody is measured on them.
6. Close the RMA24 credit and put a 7-day receive-to-credit SLA on returns (F7). Volume is tiny now; the habit is cheap to install before volume grows.
7. Decide the quote stage's fate (F8) — mandate it for the wholesale channel with conversion tracking, or retire it. The current state (10 quotes, half expiring) is the worst of both.
8. Institutionalize this analysis. Every query herein is re-runnable. A monthly "flow-conformance" pack — variants, lag trend, exception sets — would have caught F2/F5 in May rather than August. This pairs naturally with the account's existing monthly-management-reporting process definition, or as a small standalone process.

Assumptions & limitations

• A1 — Event timestamps are business dates. nexttransactionlinelink exposes document dates (previousdate/nextdate), not system event times, at day granularity. Same-day sequences cannot be internally ordered, and backdated documents (F10) can produce negative lags. All cycle times are day-granular best estimates.
• A2 — First-link convention. Where an order has multiple downstream documents, stage lag is measured to the first shipment/invoice. In this dataset the point is moot: no sales order had more than one shipment or invoice (verified, Q4 footnote query).
• A3 — Payment-terms baseline. Vendor terms were not individually inspected; the "~26 days of surrendered float" reading assumes Net-30-class terms, consistent with the observed customer invoice due dates (≈30 days after invoice date). If dominant vendor terms are shorter, F4's magnitude shrinks accordingly.
• A4 — Reference model. The "standard process" is the canonical NetSuite O2C/P2P design (§2 callout). No account-specific SOP document was provided; if written procedures differ, conformance percentages should be re-based against them.
• A5 — Status codes. NetSuite single-letter status codes were interpreted per the standard mapping (e.g., SalesOrd G = closed/billed, B = pending fulfillment, VendBill D = pending approval), cross-checked against open-amount and link behavior.
• A6 — Population scope. Elimination subsidiary (xElim, id 4) activity is immaterial to these flows and was not separately excluded from case counts; monetary figures for AR/AP come from document headers (foreign* columns; the account is single-currency USD).
• A7 — Cash-sale channel. Cash sales (1,077 docs) settle at capture and were profiled for channel contrast only; conformance and bottleneck analysis targets the credit-based flows where sequence and timing carry risk.
• A8 — Data provenance caveat. The uniform offsets in the F2 cluster and the concentration of anomalies on a single customer are consistent with scripted or demonstration activity in parts of this account. The findings describe what the ledger records; where a pattern looked mechanical rather than organic, the report says so explicitly rather than dramatizing it.
• A9 — Small header-link table. The header-level nexttransactionlink table contains only 22 rows in this account; all flow reconstruction uses the line-level nexttransactionlinelink table (22,762 rows), which is complete.

Queries used

All queries are SuiteQL, run read-only against the live account on August 22, 2026. Each is reproducible as-is. Conventions follow the account's house style (uppercase keywords, standard aliases, rounded money).

SELECT
    previoustype,
    linktype,
    nexttype,
    COUNT(DISTINCT previousdoc || '-' || nextdoc) AS doc_pairs,
    COUNT(*)                                      AS line_links
FROM nexttransactionlinelink
GROUP BY previoustype, linktype, nexttype
ORDER BY COUNT(*) DESC

SELECT type, status, COUNT(*) AS n
FROM transaction
WHERE type IN ('SalesOrd','CustInvc','ItemShip','CustPymt','RtnAuth','CustCred',
               'Estimate','PurchOrd','ItemRcpt','VendBill','VendPymt','CashSale','CashRfnd')
GROUP BY type, status
ORDER BY type, COUNT(*) DESC

SELECT
    COUNT(*)                                                  AS so_count,
    ROUND(AVG(first_ship - so_date), 1)                       AS avg_so_to_ship,
    ROUND(MEDIAN(first_ship - so_date), 1)                    AS med_so_to_ship,
    MAX(first_ship - so_date)                                 AS max_so_to_ship,
    ROUND(AVG(first_inv - so_date), 1)                        AS avg_so_to_inv,
    SUM(CASE WHEN first_inv < first_ship THEN 1 ELSE 0 END)   AS invoiced_before_ship,
    SUM(CASE WHEN first_inv = first_ship THEN 1 ELSE 0 END)   AS invoiced_same_day
FROM (
    SELECT l.previousdoc AS so_id,
           MIN(l.previousdate)                                          AS so_date,
           MIN(CASE WHEN l.nexttype = 'ItemShip' THEN l.nextdate END)   AS first_ship,
           MIN(CASE WHEN l.nexttype = 'CustInvc' THEN l.nextdate END)   AS first_inv
    FROM nexttransactionlinelink l
    WHERE l.previoustype = 'SalesOrd'
    GROUP BY l.previousdoc
)

SELECT
    TO_CHAR(so_date, 'YYYY-MM')                    AS month,
    COUNT(*)                                       AS shipped_orders,
    ROUND(AVG(lag), 1)                             AS avg_lag,
    MAX(lag)                                       AS max_lag,
    SUM(CASE WHEN lag > 30 THEN 1 ELSE 0 END)      AS over_30d
FROM (
    SELECT MIN(l.previousdate) AS so_date,
           MIN(CASE WHEN l.nexttype = 'ItemShip' THEN l.nextdate END)
             - MIN(l.previousdate) AS lag
    FROM nexttransactionlinelink l
    WHERE l.previoustype = 'SalesOrd'
    GROUP BY l.previousdoc
)
WHERE lag IS NOT NULL AND so_date >= TO_DATE('2025-09-01', 'YYYY-MM-DD')
GROUP BY TO_CHAR(so_date, 'YYYY-MM')
ORDER BY TO_CHAR(so_date, 'YYYY-MM')

-- PO -> receipt / bill
SELECT COUNT(*) AS po_count,
       ROUND(AVG(first_rcpt - po_date), 1)  AS avg_po_to_rcpt,
       MAX(first_rcpt - po_date)            AS max_po_to_rcpt,
       ROUND(AVG(first_bill - po_date), 1)  AS avg_po_to_bill,
       SUM(CASE WHEN first_bill < first_rcpt THEN 1 ELSE 0 END) AS billed_before_receipt
FROM (
    SELECT l.previousdoc AS po_id,
           MIN(l.previousdate)                                        AS po_date,
           MIN(CASE WHEN l.nexttype = 'ItemRcpt' THEN l.nextdate END) AS first_rcpt,
           MIN(CASE WHEN l.nexttype = 'VendBill' THEN l.nextdate END) AS first_bill
    FROM nexttransactionlinelink l
    WHERE l.previoustype = 'PurchOrd'
    GROUP BY l.previousdoc
);

-- Bill -> payment (DPO proxy)
SELECT COUNT(*)                                    AS bill_count,
       ROUND(MEDIAN(pay_date - bill_date), 1)      AS med_days_to_pay,
       SUM(CASE WHEN pay_date - bill_date <= 5 THEN 1 ELSE 0 END) AS paid_within_5d
FROM (
    SELECT l.previousdoc AS bill_id,
           MIN(l.previousdate) AS bill_date,
           MIN(l.nextdate)     AS pay_date
    FROM nexttransactionlinelink l
    WHERE l.previoustype = 'VendBill' AND l.linktype = 'Payment'
    GROUP BY l.previousdoc
)

SELECT variant, COUNT(*) AS orders
FROM (
    SELECT l.previousdoc,
           CASE
             WHEN MIN(CASE WHEN l.nexttype='ItemShip' THEN l.nextdate END) IS NULL
               THEN 'No shipment yet'
             WHEN MIN(CASE WHEN l.nexttype='CustInvc' THEN l.nextdate END) IS NULL
               THEN 'Shipped, never invoiced'
             WHEN MIN(CASE WHEN l.nexttype='CustInvc' THEN l.nextdate END)
                < MIN(CASE WHEN l.nexttype='ItemShip' THEN l.nextdate END)
               THEN 'Invoiced BEFORE shipped'
             WHEN MIN(CASE WHEN l.nexttype='CustInvc' THEN l.nextdate END)
                = MIN(CASE WHEN l.nexttype='ItemShip' THEN l.nextdate END)
               THEN 'Shipped + invoiced same day'
             ELSE 'Shipped then invoiced later'
           END AS variant
    FROM nexttransactionlinelink l
    WHERE l.previoustype = 'SalesOrd'
    GROUP BY l.previousdoc
)
GROUP BY variant
ORDER BY COUNT(*) DESC

SELECT so.tranid AS so_num,
       TO_CHAR(so.trandate,  'YYYY-MM-DD') AS so_date,
       TO_CHAR(x.first_inv,  'YYYY-MM-DD') AS invoiced,
       TO_CHAR(x.first_ship, 'YYYY-MM-DD') AS shipped,
       x.first_ship - x.first_inv          AS inv_leads_ship_by
FROM (
    SELECT l.previousdoc AS so_id,
           MIN(CASE WHEN l.nexttype='ItemShip' THEN l.nextdate END) AS first_ship,
           MIN(CASE WHEN l.nexttype='CustInvc' THEN l.nextdate END) AS first_inv
    FROM nexttransactionlinelink l
    WHERE l.previoustype = 'SalesOrd'
    GROUP BY l.previousdoc
) x
JOIN transaction so ON so.id = x.so_id
WHERE x.first_inv < x.first_ship
ORDER BY x.first_ship - x.first_inv DESC

SELECT so.tranid,
       TO_CHAR(so.trandate, 'YYYY-MM-DD')   AS so_date,
       so.status,
       c.entityid                           AS customer,
       ROUND(ABS(so.foreigntotal), 2)       AS amt,
       TO_CHAR(x.first_ship, 'YYYY-MM-DD')  AS shipped
FROM (
    SELECT l.previousdoc AS so_id,
           MIN(CASE WHEN l.nexttype='ItemShip' THEN l.nextdate END) AS first_ship,
           MIN(CASE WHEN l.nexttype='CustInvc' THEN l.nextdate END) AS first_inv
    FROM nexttransactionlinelink l
    WHERE l.previoustype = 'SalesOrd'
    GROUP BY l.previousdoc
) x
JOIN transaction so   ON so.id = x.so_id
LEFT JOIN customer c  ON c.id  = so.entity
WHERE x.first_ship IS NOT NULL AND x.first_inv IS NULL
ORDER BY ABS(so.foreigntotal) DESC

SELECT t.type, t.status, COUNT(*) AS n,
       ROUND(AVG(TRUNC(SYSDATE) - t.trandate), 0) AS avg_age_days,
       MAX(TRUNC(SYSDATE) - t.trandate)           AS max_age_days,
       ROUND(SUM(t.foreigntotal), 2)              AS total_value
FROM transaction t
WHERE (t.type = 'SalesOrd' AND t.status IN ('A','B','E','F'))
   OR (t.type = 'CustInvc' AND t.status = 'A')
   OR (t.type = 'PurchOrd' AND t.status IN ('A','B','F'))
   OR (t.type = 'VendBill' AND t.status IN ('A','D'))
   OR (t.type = 'Estimate' AND t.status = 'A')
   OR (t.type = 'RtnAuth'  AND t.status IN ('A','B'))
GROUP BY t.type, t.status
ORDER BY t.type, t.status

-- Distribution of post-creation edits per sales order
SELECT churn_bucket, COUNT(*) AS orders
FROM (
    SELECT t.id,
           CASE WHEN COUNT(sn.recordid) = 0  THEN '0 edits'
                WHEN COUNT(sn.recordid) <= 3  THEN '1-3'
                WHEN COUNT(sn.recordid) <= 8  THEN '4-8'
                WHEN COUNT(sn.recordid) <= 15 THEN '9-15'
                ELSE '16+' END AS churn_bucket
    FROM transaction t
    LEFT JOIN systemnote sn
           ON sn.recordid = t.id AND sn.recordtypeid = -30 AND sn.type = 2
    WHERE t.type = 'SalesOrd'
    GROUP BY t.id
)
GROUP BY churn_bucket;

-- Actor and originating context (UI form vs Suitelet vs RESTlet)
SELECT e.entityid AS actor, sn.context, COUNT(*) AS edits
FROM systemnote sn
JOIN transaction t   ON t.id = sn.recordid
LEFT JOIN employee e ON e.id = sn.name
WHERE sn.recordtypeid = -30 AND sn.type = 2 AND t.type = 'SalesOrd'
GROUP BY e.entityid, sn.context
ORDER BY COUNT(*) DESC

SELECT CASE
         WHEN TRUNC(SYSDATE) - inv.duedate <= 0   THEN 'Current'
         WHEN TRUNC(SYSDATE) - inv.duedate <= 30  THEN '1-30'
         WHEN TRUNC(SYSDATE) - inv.duedate <= 90  THEN '31-90'
         WHEN TRUNC(SYSDATE) - inv.duedate <= 180 THEN '91-180'
         ELSE '180+' END                       AS aging_bucket,
       COUNT(*)                                AS invoices,
       ROUND(SUM(inv.foreignamountunpaid), 2)  AS open_amt
FROM transaction inv
WHERE inv.type = 'CustInvc' AND inv.status = 'A'
GROUP BY CASE
         WHEN TRUNC(SYSDATE) - inv.duedate <= 0   THEN 'Current'
         WHEN TRUNC(SYSDATE) - inv.duedate <= 30  THEN '1-30'
         WHEN TRUNC(SYSDATE) - inv.duedate <= 90  THEN '31-90'
         WHEN TRUNC(SYSDATE) - inv.duedate <= 180 THEN '91-180'
         ELSE '180+' END

SELECT inv.tranid,
       TO_CHAR(inv.trandate, 'YYYY-MM-DD') AS inv_date,
       TO_CHAR(inv.duedate,  'YYYY-MM-DD') AS due,
       TRUNC(SYSDATE) - inv.duedate        AS days_overdue,
       c.entityid                          AS customer,
       ROUND(inv.foreignamountunpaid, 2)   AS open_amt
FROM transaction inv
LEFT JOIN customer c ON c.id = inv.entity
WHERE inv.type = 'CustInvc' AND inv.status = 'A'
ORDER BY inv.foreignamountunpaid DESC
FETCH FIRST 12 ROWS ONLY

SELECT vb.tranid,
       TO_CHAR(vb.trandate, 'YYYY-MM-DD') AS bill_date,
       vb.status,
       TRUNC(SYSDATE) - vb.trandate       AS age_days,
       v.entityid                         AS vendor,
       ROUND(ABS(vb.foreigntotal), 2)     AS amt
FROM transaction vb
LEFT JOIN vendor v ON v.id = vb.entity
WHERE vb.type = 'VendBill' AND vb.status IN ('A','D')
ORDER BY vb.status DESC, ABS(vb.foreigntotal) DESC

-- Every estimate and whether it ever converted
SELECT est.tranid,
       TO_CHAR(est.trandate, 'YYYY-MM-DD') AS est_date,
       est.status,
       ROUND(est.foreigntotal, 2)          AS total,
       MAX(CASE WHEN l.nexttype = 'SalesOrd' THEN 1 ELSE 0 END) AS converted
FROM transaction est
LEFT JOIN nexttransactionlinelink l
       ON l.previousdoc = est.id AND l.previoustype = 'Estimate'
WHERE est.type = 'Estimate'
GROUP BY est.tranid, est.trandate, est.status, est.foreigntotal
ORDER BY est.trandate;

-- Each RMA traced through receipt and credit
SELECT ra.tranid,
       TO_CHAR(ra.trandate, 'YYYY-MM-DD') AS ra_date,
       ra.status,
       MAX(CASE WHEN l.nexttype = 'ItemRcpt' THEN 1 ELSE 0 END) AS received,
       MAX(CASE WHEN l.nexttype = 'CustCred' THEN 1 ELSE 0 END) AS credited,
       c.entityid AS customer
FROM transaction ra
LEFT JOIN nexttransactionlinelink l
       ON l.previousdoc = ra.id AND l.previoustype = 'RtnAuth'
LEFT JOIN customer c ON c.id = ra.entity
WHERE ra.type = 'RtnAuth'
GROUP BY ra.tranid, ra.trandate, ra.status, c.entityid
ORDER BY ra.trandate