Sample output from the Order-to-Cash Object-Centric Process Mining prompt in the Sonar AI Prompt Library, run against a NetSuite test account. Every name and number here is test data. Back to the post · The library

Order-to-Cash: Object-Centric Process Mining Analysis

NetSuite account TD3016323 (production, OneWorld, USD) · Scope: all history, 2024-10-01 to 2026-10-01 by transaction date · Prepared 2026-09-04 · Revision 2 · Method: SuiteQL via sqlReduce — transactionline streamed by keyset on uniquekey; nexttransactionlinelink and headers materialised · Document numbers link to the source record when opened inside NetSuite
  1. 0Executive summary
  2. 1Method, lineage and assumptions
  3. 2Event log
  4. 3Structure
  5. 4Process model
  6. 5Variants and timing
  7. 6Bottlenecks
  8. 7Conformance
  9. 8Exception registers
  10. 9Data quality
  11. 10Findings and recommended actions
  12. AAppendix A — SuiteQL used
  13. BAppendix B — Reducer logic
  14. CAppendix C — Worked hand-check
  15. DAppendix D — Glossaries
  16. EAppendix E — Run metadata and revision log

0Executive summary

3,055
Documents = events = objects across five types
845
Connected components (end-to-end cases)
90.5%
Components conforming to the reference model
0 days
Median and P90 cycle time, order to cash, on the main path

The account's order-to-cash flow is structurally simple and fast. Every sales order maps to at most one fulfillment, one invoice and one payment; 704 of 845 cases (83.3%) run the complete Order → Shipped → Invoiced → Paid chain and 654 of them close on a single calendar day. There is no many-to-many structure, so the object-centric graph, the order perspective and the payment perspective agree everywhere except where documents are absent.

The exceptions, not the main path, are where the value lies:

  1. A test-invoice block is distorting A/R. 32 invoices with memo TEST — … (internal ids 42286–42317, no sales order, all status Open) carry $790,017.65 — 85% of the $928,246.62 open invoice balance in scope. Genuine open A/R linked to orders is $138,228.97. Until these are voided or excluded, any A/R aging or DSO figure from this account is wrong by a factor of roughly seven.
  2. Twenty orders were billed and paid before shipping (SO4270–SO4287 plus SO3910, SO3985; $25,790.50). Eighteen follow a metronomic 15 / 18 / 12-day rhythm. If real, revenue is recognised ahead of delivery; if seeded, they should be tagged so they stop appearing as exceptions.
  3. The fulfillment and approval queues contain probable duplicates. 46 orders have no fulfillment; among the 13 in Pending Approval, Design Excellence Ltd. has two identical trios (SO4314/15/16 and SO4318/19/20 at $5,146.21 / $3,273.99 / $2,697.68) and Godric Motors has SO4293 / SO4313 at the same $1,627.03. One deposited payment, PYMT01 ($934.78, Susan Adams, 2026-08-31), mirrors the applied PYMT675 and is unapplied.

1Method, lineage and assumptions

Data lineage

Figure 1 — Row funnel from raw transaction lines to connected components. Nothing is lost between mainline rows and objects; the reduction from 3,055 to 845 is the linking itself.

StageCountNote
transactionline rows streamed21,0325 keyset pages on uniquekey, 5,000 rows/page, 2.3 s total, peak held ≈ 1 page
Mainline rows (one per document)3,055Equals header count — no document dropped
createdfrom candidates → used1,486 → 1,47412 dropped: target outside the five types (return authorisations, transfer orders)
nexttransactionlinelink raw in-scope rows → distinct pairs7,161 → 2,942SO→INV emitted twice per line (OrdBill + OrdRvCom); 7 PickPack pairs coincide with ShipRcpt
Distinct edges after merging both sources2,210SO→IF 742 · SO→INV 732 · INV→PYMT 736
Events / objects3,055 / 3,055One event per document; each event carries its own object plus every upstream object
Connected components845Union-find over the 2,210 edges

Assumptions register

#AssumptionEffect on results
A1Scope is all history (788 sales orders total, too few to trim to twelve months). Window is on trandate, not createddate.169 future-dated documents retained; trailing-twelve-month figures can be derived from the monthly table in §5.
A2One event per document; activity = document type (SalesOrd → Order Created, ItemShip → Shipped, CustInvc → Invoiced, CustPymt → Paid, CustCred → Credited).Approval, pick, pack and deposit are not activities (no reliable timestamps — see §9).
A3Timestamp = trandate at day resolution.All durations are whole days; same-day chains have cycle 0.
A4Same-day tie-break: Order Created < Shipped < Invoiced < Paid < Credited, then internal id.Same-day events read as the canonical order; a same-day bill-before-ship cannot be detected.
A5Links from nexttransactionlinelink (distinct previous/next pairs) unioned with transactionline.createdfrom on the mainline row; self-links and links to out-of-scope types discarded.2,210 edges. A document reachable from only one source is reported in §2.
A6A payment inherits sales-order objects through its invoice(s).Payment-perspective traces include Order Created and Shipped events.
A7A case = connected component of the document graph.Single unlinked documents are components of size 1 and are counted as deviations in §7.
A8Reference model: one Order Created, then Shipped and Invoiced in either order (Shipped repeatable), then exactly one Paid, nothing after Paid; no credit memo.Open prefixes (order only; order + ship; order + ship + invoice) are conforming.
A9Cash sales (1,077) and their deposits, customer deposits (1), deposit applications (1) and refunds (1) are out of scope.The Susan Adams deposit path (CD01 → DEPA01 → INV03) is why INV03 precedes SO3129 in §7.
A10No subsidiary filter applied.Two intercompany orders (SO3148, SO3149, $29.99 each) are included.
A11Percentiles are exact (whole arrays held in the Worker); H.percentile(arr, 0.9).No reservoir sampling anywhere in this report.
A12Money = foreigntotal / foreignamountunpaid (transaction currency; account is single-currency USD).—
A13Status labels are the live BUILTIN.DF(status) text, not the generic reference table.SalesOrd status G reads "Billed" in this account.

2Event log

Events were built from five document types, one event per document, dated by trandate. Example events: SO2515 Order Created 2026-09-11 · SO2738 Order Created 2026-09-24. Example edges: SO3623 2025-02-11 → INV109 2025-02-11 [OrdBill, OrdRvCom, createdfrom] · SO4102 2026-02-20 → IF5423 2026-02-20 [ShipRcpt, createdfrom].

Objects by type and link reachability

Object typeObjectsReached by both sourcesntll onlycreatedfrom onlyUnreachedUnreached examples (raw dates)
Sales order (SalesOrd)788root object45 with no downstreamSO3148 2026-09-25 (Pending Fulfillment) · SO3149 2026-09-25 (Pending Fulfillment)
Item fulfillment (ItemShip)7527420010IF3112 2026-08-26 · IF3113 2026-08-26 — both from transfer orders (§8-E)
Invoice (CustInvc)7647320032INV762 2026-08-27 · INV763 2026-08-27 — both memo TEST (§8-A)
Customer payment (CustPymt)7480736012PYMT01 2026-08-31 · PYMT02 2026-09-17 (§8-D)
Credit memo (CustCred)30003CM01 2026-09-14 · CM03 2026-09-15 (§8-F)

The two link sources agree completely where both apply: all 1,474 order→fulfillment and order→invoice edges are present in both. Invoice→payment edges (736) exist only in nexttransactionlinelink (linktype Payment) because createdfrom is never populated on payments. No edge is reachable from createdfrom alone: it validates the link table but adds no coverage. Credit memos have no links to any in-scope document.

3Structure

Multiplicity012 or moreExample of the "0" case
Fulfillments per order467420SO4143 2026-06-15 · SO4150 2026-07-05
Invoices per order567320SO3151 2026-09-25 (shipped IF4947, not invoiced) · SO3148 2026-09-25
Orders per invoice327320INV790 2025-12-04 · INV782 2026-03-20 (TEST block)
Payments per invoice287360INV759 2026-06-03 ($53,424 open) · INV713 2026-09-04
Invoices per payment127360PYMT01 2026-08-31 · PYMT03 2026-08-31
Orders per payment237250the 12 above plus 11 payments against TEST invoices, e.g. PYMT760 2026-09-13

Component size distribution: 1 document 91 · 2 documents 22 · 3 documents 8 · 4 documents 724. Example 1:1 chains: SO3542 2024-10-01 → IF4990 2024-10-01 → INV28 2024-10-01 → PYMT17 2024-10-01 · SO3544 2024-10-07 → IF4991 → INV30 → PYMT19, all 2024-10-07.

The account has no genuine many-to-many structure. Every multiplicity is 0 or 1; 724 of 788 orders (91.9%) form a strict order → fulfillment → invoice → payment chain. There are no partial shipments, no split or consolidated invoices and no consolidated payments. Consequently the object-centric graph, the order perspective and the payment perspective differ only where documents are missing or unlinked — never because of convergence or divergence between objects — and the rest of this report should be read as a study of exceptions rather than of flow.

4Process model

Object-centric directly-follows graph

Figure 2 — Directly-follows graph across all 845 components. Edge labels: count · median wait in days. The dominant path is highlighted; the two minority arcs are the bill-before-ship route. Three single-digit edges (Shipped → Order Created 2, Invoiced → Order Created 1, Order Created → Paid 1) are date-integrity cases and are listed in the table rather than drawn.

TransitionSalesOrd lifecyclesCustInvc lifecyclesConvergenceReading
Order Created → Shipped720—1Main path
Shipped → Invoiced711—1Main path
Invoiced → Paid724736112 invoice lifecycles carry this transition without a linked order (the 11 paid TEST invoices + INV03)
Order Created → Invoiced20—1Bill-before-ship path
Paid → Shipped20—1Bill-before-ship path (shipment last)
Shipped → Order Created2—1Fulfillment dated before its order: IF4955 2026-09-20 → SO2903 2026-10-01; IF5728 2026-09-07 → SO4305 2026-09-30
Invoiced → Order Created1—1INV03 2026-08-25 precedes SO3129 2026-08-31 (customer-deposit path, A9)
Order Created → Paid1—1Same component (SO3129 → PYMT675, 2026-08-31)

Lifecycle lengths by object type: SalesOrd — 1 event 45, 2 events 11, 3 events 8, 4 events 724; CustInvc — 1 event 28, 2 events 736; ItemShip, CustPymt and CustCred are all single-event. Convergence = 1 on every transition (no event touches two objects of the same type) is the numerical statement of the 1:1 finding.

Order perspective versus payment perspective

TransitionOrder view countmedian / P90 / max daysPayment view countmedian / P90 / max days
Invoiced → Paid7240 / 0 / 197350 / 3 / 273
Order Created → Shipped7200 / 0 / 67040 / 0 / 3
Shipped → Invoiced7110 / 0 / 37040 / 0 / 3
Order Created → Invoiced2015 / 15 / 182015 / 15 / 18
Paid → Shipped2012 / 12.2 / 3652012 / 12.2 / 365
Trace start statesOrder Created 785 · Shipped 2 · Invoiced 1Order Created 724 · Paid 12 · Invoiced 12
Trace end statesPaid 705 · Order Created 47 · Shipped 29 · Invoiced 7Paid 728 · Shipped 20

The perspectives disagree in two places, both explained by object structure rather than error. First, the payment view counts 735 Invoiced → Paid transitions with a P90 of 3 days and a maximum of 273 days, against 724 / 0 / 19 in the order view. The additional eleven are invoice→payment pairs with no linked order — all eleven are TEST-memo invoices (for example INV787 2025-12-14 → PYMT760 2026-09-13, 273 days; INV781 2026-04-27 → PYMT759 2026-09-13, 139 days). They are invisible to the order perspective and they are the only slow payers in the account. Second, the order view sees 83 traces ending before payment (47 order-only, 29 shipped, 7 invoiced) that the payment view cannot see at all, because no payment object exists for them. Neither divergence involves partial shipments or consolidated payments; §3 rules those out.

5Variants and timing

Variants are computed at connected-component level; cycle time is first event to last event in whole days. Percentiles are exact (A11). Twelve distinct variants exist; the top ten cover 842 of 845 components.

Figure 3 — Component count by variant (log-scaled bar length so the minority variants remain legible; exact counts printed).

#VariantCountMedian daysP90 daysMaxExamples (raw dates)
1Order Created → Shipped → Invoiced → Paid704007SO3542 → IF4990 → INV28 → PYMT17, all 2024-10-01
SO3544 → IF4991 → INV30 → PYMT19, all 2024-10-07
2Order Created (only)45000SO3148 2026-09-25 · SO3149 2026-09-25
3Invoiced (only)21000INV762 2026-08-27 · INV763 2026-08-27
4Order Created → Invoiced → Paid → Shipped204546.2365SO3985 2025-02-10 → INV471 2025-02-10 → PYMT460 2025-02-10 → IF5543 2025-02-14
SO3910 2025-02-09 → INV396 2025-02-09 → PYMT385 2025-02-09 → IF5503 2026-02-09
5Paid (only)12000PYMT01 2026-08-31 · PYMT02 2026-09-17
6Invoiced → Paid1135139273INV764 2026-08-27 → PYMT750 2026-09-05
INV765 2026-08-19 → PYMT751 2026-09-06
7Shipped (only)10000IF3112 2026-08-26 · IF3113 2026-08-26
8Order Created → Shipped913.66SO3151 2026-09-25 → IF4947 2026-09-25
SO3150 2026-09-25 → IF4950 2026-09-26
9Order Created → Shipped → Invoiced7333SO4167 2026-09-01 → IF5674 2026-09-01 → INV713 2026-09-04
SO4169 2026-09-04 → IF5675 2026-09-04 → INV715 2026-09-07
10Credited (only)3000CM01 2026-09-14 · CM03 2026-09-15

Remaining two variants: Shipped → Order Created (2; IF4955 2026-09-20 → SO2903 2026-10-01; IF5728 2026-09-07 → SO4305 2026-09-30) and Invoiced → Order Created → Paid (1; INV03 2026-08-25 → SO3129 2026-08-31 → PYMT675 2026-08-31).

Invoiced → Paid waiting-time distribution

Figure 4 — 736 invoice→payment pairs from nexttransactionlinelink, bucketed by whole days between invoice and payment date.

Days to payPairsShareInvoice value
0 (same day)65889.4%1,819,921.62
1–3293.9%105,918.89
4–7202.7%35,077.72
8–30223.0%55,913.19
31–9050.7%20,385.80
91+20.3%979.91
Total736100%2,038,197.13

Monthly volume by document type

Figure 5 — Documents per month by transaction date, 2024-10 to 2026-09 (2026-10 has a single sales order and is omitted). Volume ran at 27–34 orders a month for twenty months, then rose to 45 in July 2026 and 71 in September 2026 — the September figure is inflated by 57 future-dated orders (§9). Payments (48) exceed invoices (28) in September because the 11 TEST invoices from earlier months were paid that month.

Monthly table
MonthSales ordersFulfillmentsInvoicesPaymentsSO valueInvoice value

6Bottlenecks

Waiting time by transition (component traces)

TransitionCountMedianP90MaxSame-day shareExamples
Invoiced → Paid7350327389.5%INV28 2024-10-01 → PYMT17 2024-10-01 (0d) · INV30 2024-10-07 → PYMT19 2024-10-07 (0d)
Order Created → Shipped72000698.6%SO3151 2026-09-25 → IF4947 2026-09-25 (0d) · SO3150 2026-09-25 → IF4950 2026-09-26 (1d)
Shipped → Invoiced71100392.4%IF4990 2024-10-01 → INV28 2024-10-01 (0d) · IF4991 2024-10-07 → INV30 2024-10-07 (0d)
Order Created → Invoiced2015151810.0%SO3985 2025-02-10 → INV471 2025-02-10 (0d) · SO4287 2026-08-13 → INV758 2026-08-31 (18d)
Paid → Shipped201212.23650%PYMT460 2025-02-10 → IF5543 2025-02-14 (4d) · PYMT385 2025-02-09 → IF5503 2026-02-09 (365d)
Shipped → Order Created21721.8230%IF4955 2026-09-20 → SO2903 2026-10-01 (11d) · IF5728 2026-09-07 → SO4305 2026-09-30 (23d)
Invoiced → Order Created16660%INV03 2026-08-25 → SO3129 2026-08-31 (6d)
Order Created → Paid1000100%SO3129 2026-08-31 → PYMT675 2026-08-31 (0d)

The main flow is not a bottleneck: all three normal transitions have a median and P90 of zero days. Waiting time in this account sits entirely in two minority paths — TEST invoices awaiting payment, and orders billed before they ship.

Ten slowest components

ComponentCycle daysLongest wait (what it was waiting on)SO totalNote
SO3910365Paid PYMT385 2025-02-09 → Shipped IF5503 2026-02-09 (365d)60.23Exactly one year — a dating artefact is likely
INV787 (no order)273Invoiced 2025-12-14 → Paid PYMT760 2026-09-13 (273d)—TEST memo; $263.58 still open
INV781 (no order)139Invoiced 2026-04-27 → Paid PYMT759 2026-09-13 (139d)—TEST memo; $416.33 still open
INV776 (no order)71Invoiced 2026-07-02 → Paid PYMT757 2026-09-11 (71d)—TEST memo; $3,135.21 still open
INV779 (no order)64Invoiced 2026-07-10 → Paid PYMT758 2026-09-12 (64d)—TEST memo; $2,810.66 still open
INV773 (no order)51Invoiced 2026-07-21 → Paid PYMT756 2026-09-10 (51d)—TEST memo; $3,555.61 still open
SO428748Order 2026-08-13 → Invoiced INV758 2026-08-31 (18d); shipped IF5721 2026-09-30 after payment462.19Bill-before-ship
SO428146Invoiced INV751 2026-08-17 → Paid PYMT741 2026-09-05 (19d); shipped IF5714 2026-09-171,399.05Bill-before-ship
SO428246Invoiced INV752 2026-08-18 → Paid PYMT742 2026-09-06 (19d); shipped IF5715 2026-09-181,627.03Bill-before-ship
SO428346Invoiced INV753 2026-08-19 → Paid PYMT743 2026-09-07 (19d); shipped IF5716 2026-09-19462.19Bill-before-ship

7Conformance

Reference model (A8): Order Created → {Shipped, Invoiced in either order, Shipped repeatable} → Paid; exactly one order, one invoice and one payment per component; nothing after Paid; no credit memo. Open prefixes of the model are conforming.

Figure 6 — 845 components by conformance outcome.

OutcomeComponentsShare
Complete and conforming (all four activities)70483.3%
Conforming open prefix (order only 45; order + shipped 9; order + shipped + invoiced 7)617.2%
Deviant809.5%
Conforming share765 of 84590.5%

Deviation patterns

PatternCountRoot causeExamples
Component starts with Invoiced (no order)3332 TEST-memo invoices (§8-A) + INV03 customer-deposit pathINV762 2026-08-27 · INV03 2026-08-25 → SO3129 2026-08-31 → PYMT675 2026-08-31
Activity after Paid (shipment after payment)20Bill-before-ship (§8-C)SO3985 2025-02-10 → INV471 → PYMT460 2025-02-10 → IF5543 2025-02-14 · SO3910 → IF5503 2026-02-09
Component starts with Shipped1210 fulfillments from transfer orders / vendor RMAs (§8-E, out of O2C scope) + 2 fulfillments dated before their orderIF3112 2026-08-26 (TO03) · IF4955 2026-09-20 → SO2903 2026-10-01
Component starts with Paid (unlinked payment)12Deposited but unapplied payments (§8-D)PYMT01 2026-08-31 ($934.78) · PYMT02 2026-09-17 ($1,049.79)
Credit memo not linked to any invoice3Stand-alone credits (§8-F)CM01 2026-09-14 · CM03 2026-09-15
Pay-before-ship — 20 components, flagged separately
All 20 follow Order Created → Invoiced → Paid → Shipped and all 20 sales orders are status Billed. Two are historical (SO3985 shipped 4 days after payment; SO3910 shipped 365 days after payment). Eighteen are a cluster, SO4270 to SO4287, ordered June to August 2026, across eleven customers, with a repeating rhythm — invoice 15 days after order, payment 18–19 days later, shipment 12 days after payment (Figure 7 and §8-C). Total order value $25,790.50. Revenue is being billed and collected before goods leave the building; the regularity suggests a scripted scenario rather than eighteen independent decisions.

Figure 7 — The 20 pay-before-ship chains on a common day axis from order date. Markers: order (day 0), invoice, payment, shipment (highlighted). SO3910's shipment at day 365 is clipped at the right edge.

8Exception registers

Complete lists, not samples. Columns are sortable. Amounts in USD. Age and days-past-due are as at 2026-09-04; negative values are future-dated documents.

8-A · Invoices with no sales order (32) — all memo TEST — …

InvoiceInternal idDateDueStatusCustomerTotalPaidUnpaidDays past duePaid by
INV790423142025-12-042026-01-03OpenGlobal Information110,579.170.00110,579.17244—
INV782423062026-03-202026-04-20OpenRed Rivers Consulting102,905.760.00102,905.76137—
INV774422982026-07-222026-08-21OpenMagna Tech Limited97,942.270.0097,942.2714—
INV783423072026-05-312026-07-01OpenFalcon Systems86,007.390.0086,007.3965—
INV791423152025-05-202025-06-19OpenMercury Co.80,079.020.0080,079.02442—
INV789423132025-12-102026-01-09OpenGotter inc.68,119.000.0068,119.00238—
INV788423122025-09-302025-10-29OpenHaskell Associates43,940.750.0043,940.75310—
INV784423082026-04-162026-05-15OpenJohn G. Roche Opticians31,810.190.0031,810.19112—
INV780423042026-03-232026-04-23OpenInformics International29,239.180.0029,239.18134—
INV775422992026-06-252026-07-27OpenGreenwood Consulting26,274.610.0026,274.6139—
INV778423022026-06-262026-07-28OpenHeidelberg Haus22,473.780.0022,473.7838—
INV772422962026-07-192026-08-18OpenDazzlesphere Company20,053.840.0020,053.8417—
INV763422872026-08-272026-09-27OpenMacgruber Incorporated16,884.000.0016,884.00-23— (duplicate of INV762?)
INV762422862026-08-272026-09-27OpenMacgruber Incorporated16,884.000.0016,884.00-23—
INV767422912026-09-122026-10-11OpenMachester Mfg5,134.290.005,134.29-37—
INV777423012026-06-212026-07-23OpenMacomb Industries4,458.820.004,458.8243—
INV766422902026-08-152026-09-15OpenMcCarthy Supplies9,667.335,500.004,167.33-11partial
INV773422972026-07-212026-08-20OpenFernhill Solutions6,555.613,000.003,555.6115PYMT756 2026-09-10
INV776423002026-07-022026-08-01OpenCopper Software4,935.211,800.003,135.2134PYMT757 2026-09-11
INV779423032026-07-102026-08-09OpenPied Piper4,010.661,200.002,810.6626PYMT758 2026-09-12
INV786423102025-11-092025-12-08OpenGhetti Ltd2,740.640.002,740.64270—
INV768422922026-08-272026-09-27OpenGramz LLP4,121.552,000.002,121.55-23partial
INV792423162025-07-062025-08-05OpenSchmidt & Sons Consulting1,769.520.001,769.52395—
INV770422942026-08-032026-09-03OpenSchubert Software3,213.111,500.001,713.111partial
INV764422882026-08-272026-09-27OpenMacgruber Incorporated18,487.9816,884.001,603.98-23PYMT750 2026-09-05
INV769422932026-09-112026-10-10OpenManzo Management1,573.970.001,573.97-36—
INV785423092026-04-222026-05-21OpenMagneto Services787.120.00787.12106—
INV781423052026-04-272026-05-26OpenThe Abbott Inc.616.33200.00416.33101PYMT759 2026-09-13
INV787423112025-12-142026-01-13OpenJasper and Associates363.58100.00263.58234PYMT760 2026-09-13
INV793423172025-04-042025-05-03OpenKasson Ltd262.390.00262.39489—
INV765422892026-08-192026-09-19OpenFinch Computing2,335.372,145.00190.37-15PYMT751 2026-09-06
INV771422952026-07-312026-08-29OpenJupiter Technology1,671.211,551.00120.216partial
32 invoices · contiguous internal ids 42286–42317 · every memo begins "TEST — "825,897.6535,880.00790,017.65

8-B · Sales orders with no fulfillment (46)

OrderInternal idDateStatusCustomerTotalAge daysNote
SO4143393412026-06-15Pending FulfillmentRealpoint inc.3,952.2981Oldest open order
SO4150393482026-07-05Pending FulfillmentDavis Supplies12,594.2261Largest aged open order
SO4181393792026-08-07Pending FulfillmentBrenda Scott58.5828
SO4161393592026-08-09Pending FulfillmentHugo Limited4,314.1726
SO4235394332026-08-17Pending FulfillmentXandra Underwood349.5718
SO4196393942026-08-18Pending FulfillmentDan Coleman94.0217
SO4193393912026-08-19Pending FulfillmentCynthia Rose85.9716
SO3129242632026-08-31Pending FulfillmentSusan Adams1,084.784Invoiced (INV03) and paid via deposit before the order; 2 invoice links
SO4314423412026-09-01Pending ApprovalDesign Excellence Ltd.5,146.213Same amount as SO4318
SO4304420352026-09-01Pending FulfillmentCopper Software4,516.673
SO4317423442026-09-01Pending ApprovalBlockster Inc.2,124.233
SO4316423432026-09-01Pending ApprovalDesign Excellence Ltd.2,697.683Same amount as SO4320
SO4315423422026-09-01Pending ApprovalDesign Excellence Ltd.3,273.993Same amount as SO4319
SO4203394012026-09-02Pending FulfillmentJustin Turner61.512
SO4291412972026-09-04Pending FulfillmentJones Manufacturing461.100
SO4292412982026-09-05Pending FulfillmentMarshall Industries936.33-1
SO4310422562026-09-07Pending ApprovalBlockster Inc.474.87-3
SO4311422572026-09-07Pending ApprovalMarshall Industries56.60-3
SO4321423492026-09-10Pending ApprovalGlobal Information8,949.41-6
SO4303420342026-09-10Pending FulfillmentCustomer 59 (entity 1233)1,725.05-6
SO4199393972026-09-10Pending FulfillmentLex Ansel366.26-6
SO4312422672026-09-11Pending FulfillmentBlockster Inc.11,219.03-7
SO4322423502026-09-11Pending ApprovalMagna Tech Limited15,002.98-7Largest in approval queue
SO4294413002026-09-12Pending FulfillmentDavis Supplies1,655.35-8Same amount as SO4295
SO4295413012026-09-12Pending FulfillmentFalcon Systems1,655.35-8Same amount as SO4294
SO4318423462026-09-14Pending ApprovalDesign Excellence Ltd.5,146.21-10Same amount as SO4314
SO4184393822026-09-14Pending FulfillmentBrittany Thompson408.29-10
SO4320423482026-09-14Pending ApprovalDesign Excellence Ltd.2,697.68-10Same amount as SO4316
SO4319423472026-09-14Pending ApprovalDesign Excellence Ltd.3,273.99-10Same amount as SO4315
SO4269403052026-09-15Pending FulfillmentDesign Excellence Ltd.257.60-11Same amount as SO4298
SO4306422212026-09-17Pending FulfillmentBryan Scott859.67-13
SO4298418042026-09-20Pending FulfillmentDesign Excellence Ltd.257.60-16Same amount as SO4269
SO4226394242026-09-21Pending FulfillmentJolly Baker989.13-17
SO4175393732026-09-22Pending ApprovalJones Manufacturing5,420.84-18
SO4249394472026-09-22Pending FulfillmentTrent Barry80.02-18
SO4307422232026-09-23Pending FulfillmentCopper Software452.63-19
SO4308422252026-09-23Pending FulfillmentBryan Scott8,160.19-19
SO4178393762026-09-24Pending FulfillmentBobby Davis989.13-20
SO4302420332026-09-24Pending FulfillmentCustomer 78 (entity 3885)5,437.19-20
SO3149280852026-09-25Pending FulfillmentInterco Client US1 - US229.99-21Intercompany
SO3148279782026-09-25Pending FulfillmentInterco Client US2 - US129.99-21Intercompany
SO4309422292026-09-29Pending FulfillmentDesign Excellence Ltd.5,181.36-25
SO4293412992026-09-30Pending FulfillmentGodric Motors1,627.03-26Same amount and date as SO4313
SO4313423392026-09-30Pending ApprovalGodric Motors1,627.03-26Same amount and date as SO4293
SO4288412942026-09-30Pending FulfillmentHugo Limited1,604.38-26
SO4289412952026-09-30Pending FulfillmentBlockster Inc.911.60-26
46 orders · 33 Pending Fulfillment · 13 Pending Approval129,046.44

8-C · Pay-before-ship chains (20)

OrderOrder dateCustomerTotalInvoiceInv datePaymentPay dateFulfillmentShip dateO→II→PP→S
SO39102025-02-09Brenda Scott60.23INV3962025-02-09PYMT3852025-02-09IF55032026-02-0900365
SO39852025-02-10Recreational Outfitters4,429.31INV4712025-02-10PYMT4602025-02-10IF55432025-02-14004
SO42702026-06-03Design Excellence Ltd.913.76INV7402026-06-18PYMT7302026-07-06IF57032026-07-18151812
SO42712026-06-03Godric Motors1,627.03INV7412026-06-18PYMT7312026-07-06IF57042026-07-18151812
SO42722026-06-05Blockster Inc.911.60INV7422026-06-20PYMT7322026-07-08IF57052026-07-20151812
SO42732026-06-07Jones Manufacturing461.10INV7432026-06-22PYMT7332026-07-10IF57062026-07-22151812
SO42742026-06-08Meetz Industries1,653.46INV7442026-06-23PYMT7342026-07-11IF57072026-07-23151812
SO42752026-07-02Design Excellence Ltd.1,604.38INV7452026-07-17PYMT7352026-08-04IF57082026-08-16151812
SO42762026-07-03JBL Inc.930.98INV7462026-07-18PYMT7362026-08-05IF57092026-08-17151812
SO42772026-07-05Davidson Supplies942.79INV7472026-07-20PYMT7372026-08-07IF57102026-08-19151812
SO42782026-07-07Hugo Limited1,604.38INV7482026-07-22PYMT7382026-08-09IF57112026-08-21151812
SO42792026-07-10Jones Manufacturing911.60INV7492026-07-25PYMT7392026-08-12IF57122026-08-24151812
SO42802026-07-12Blockster Inc.1,600.60INV7502026-07-27PYMT7402026-08-14IF57132026-08-26151812
SO42812026-08-02Marshall Industries1,399.05INV7512026-08-17PYMT7412026-09-05IF57142026-09-17151912
SO42822026-08-03Godric Motors1,627.03INV7522026-08-18PYMT7422026-09-06IF57152026-09-18151912
SO42832026-08-04Design Excellence Ltd.462.19INV7532026-08-19PYMT7432026-09-07IF57162026-09-19151912
SO42842026-08-05Hugo Limited1,604.38INV7542026-08-20PYMT7442026-09-08IF57182026-09-20151912
SO42852026-08-08Meetz Industries1,653.46INV7562026-08-23PYMT7452026-09-11IF57192026-09-23151912
SO42862026-08-11JBL Inc.930.98INV7572026-08-26PYMT7472026-09-14IF57202026-09-26151912
SO42872026-08-13Design Excellence Ltd.462.19INV7582026-08-31PYMT7482026-09-16IF57212026-09-30181614
20 chains · all orders status Billed · no memo on any25,790.50Order→Invoice median 15d · Invoice→Payment median 18d · Payment→Ship median 12d

8-D · Payments linked to nothing (12)

PaymentInternal idDateStatusCustomerAmountNote
PYMT03268972026-08-31DepositedRobert Huffman960.11
PYMT01242672026-08-31DepositedSusan Adams934.78Identical customer, date and amount to applied PYMT675 (id 39722)
PYMT07295392026-09-06DepositedFinch Computing32.65
PYMT10295452026-09-07DepositedDillan Garcia32.65
PYMT13295522026-09-10DepositedDonnie Rizzo49.99
PYMT14295542026-09-11DepositedBryan Scott27.75
PYMT12295492026-09-12DepositedBryan Scott27.75Same customer and amount as PYMT14
PYMT16295602026-09-14DepositedCarter Drury32.54
PYMT11295472026-09-14DepositedDillan Garcia32.65Same customer and amount as PYMT10, PYMT15
PYMT02260272026-09-17DepositedDonnie Rizzo1,049.79
PYMT15295572026-09-17DepositedDillan Garcia32.65
PYMT05295352026-09-18DepositedFinch Computing32.65
12 payments · all Deposited · none applied to any invoice3,245.96

8-E · Fulfillments with no sales order (10) — out of O2C scope

FulfillmentInternal idDateStatusCreated fromSource type
IF4987317132026-08-01ShippedTO13Transfer order
IF5702404282026-08-03ShippedVRMA21Vendor return authorisation
IF5701404242026-08-16ShippedVRMA20Vendor return authorisation
IF3112209122026-08-26ShippedTO03Transfer order
IF3113209142026-08-26ShippedTO04Transfer order
IF4951288782026-09-01ShippedTO02Transfer order
IF4952288792026-09-01ShippedTO01Transfer order
IF3115241322026-09-08PickedTO05Transfer order
IF4986317102026-09-08ShippedTO12Transfer order
IF3118242712026-09-12ShippedTO06Transfer order

These are legitimate item fulfillments of transfer orders and vendor returns. They were included because the event log took every ItemShip; a tighter scope would filter on createdfrom type. They are not order-to-cash exceptions.

8-F · Credit memos (3) and the customer-deposit path

DocumentDateStatusCustomerAmountLinks
CM012026-09-14OpenSusan Adams-108.48None
CM032026-09-15OpenBobby Davis-152.43None
CM02—Fully Applied——Linked from RtnAuth / CustRfnd (out of scope)
CD01 → DEPA01 → INV032026-08-25 / 08-31Fully Applied / Paid In FullSusan Adams150.00 + 934.78Deposit $150 applied plus PYMT675 $934.78 = INV03 $1,084.78; SO3129 dated 2026-08-31 remains Pending Fulfillment

8-G · Largest genuine open receivable

SO4296 (Blockster Inc., 2026-06-01, $53,424.00, memo "Invoices > 30 Days > 50000") → IF5722 2026-06-02 → INV759 2026-06-03, due 2026-07-03, $53,424.00 open, 63 days past due. The memo names the demo scenario it exercises. It is the single largest item in the $138,228.97 of order-linked open A/R.

9Data quality — what limits the analysis

LimitationEvidenceWhat it prevents
Day-level dates onlyAll 3,055 transaction dates carry time 00:00:00Same-day events cannot be ordered by time; 654 of 724 complete chains (90.3%) span 0 days, so medians collapse to zero and intra-day sequence relies on the assumed activity rank (A4). A same-day bill-before-ship is undetectable.
Null ship datesshipdate and actualshipdate are null on all 752 item fulfillmentsShipment timing must use fulfillment trandate; carrier hand-off and transit time are unmeasurable.
No approval eventsStatus-change system notes (TRANDOC.KSTATUS) exist on 3 sales orders, 32 invoices, 11 payments out of 2,300Approval lead time cannot be measured; 13 orders are in Pending Approval with no record of when they entered it. Pick / pack sub-steps are likewise unobservable.
Demo-load dating169 documents dated after 2026-09-04 (SO 57, PYMT 47, IF 37, INV 26, CM 2); createddate clusters in September–October 2026 for every typecreateddate is unusable as an event timestamp; "days open" for recent items is unreliable; two fulfillments precede their orders (IF4955 → SO2903; IF5728 → SO4305); the SO3910 shipment lands exactly 365 days after payment.
Test fixtures in production data32 invoices and 11 payments carry memo TEST — …; SO4296 memo names a demo scenarioAging, DSO and collection-effectiveness measures are dominated by fixtures until they are excluded (t.memo NOT LIKE 'TEST%') or removed.
Payments not linked to orderscreatedfrom is empty on all 748 payments; links exist only via nexttransactionlinelink linktype PaymentCash application cannot be traced for the 12 unlinked payments; payment perspective depends entirely on the link table.
Credit memos unlinked3 credit memos, 0 links to invoices or ordersReturns and adjustments cannot be attributed to a case.
Status-label driftLive BUILTIN.DF(status) renders SalesOrd G as "Billed"; generic references list G as "Closed"Nothing in this report — live labels are used — but any downstream consumer keying on "Closed" would misread 731 orders.
Synthetic GL journalsMonthly "Beg Balance" journals JE102–JE149 (not in scope for this document-level analysis)Not used here; any reconciliation of these figures to GL revenue or A/R must exclude them.

10Findings and recommended actions

  1. Quarantine the TEST invoice block before anyone reads an A/R report. 32 invoices (§8-A; internal ids 42286–42317; all memo TEST — <customer>; all status Open) hold $790,017.65, or 85% of the $928,246.62 open invoice balance in scope. Genuine order-linked open A/R is $138,228.97. INV790 alone would show Global Information $110,579.17 at 244 days past due; INV762 and INV763 are identical ($16,884.00, Macgruber Incorporated, 2026-08-27). The eleven payments applied to this block are also TEST-tagged and are the account's only slow payers (median 35 days, max 273). Action: confirm with whoever loaded them, then void or delete; until then exclude memo LIKE 'TEST%' from every aging, DSO and collections view.
  2. Explain the bill-before-ship cluster SO4270–SO4287. Eighteen orders across eleven customers (§8-C; $25,790.50) were invoiced 15 days after order, paid 18–19 days later and shipped 12 days after payment, with no memo. Two older cases (SO3985; SO3910 shipped 365 days after payment) share the pattern. If these are real, revenue was recognised and cash collected roughly a month before delivery — a cut-off exposure that the order perspective in §4 hides and only the shipment-last variant reveals. If they are seeded scenarios, tag them so they stop surfacing as exceptions. Either way, a saved search on ItemShip.trandate > CustPymt.trandate via nexttransactionlinelink would catch recurrences.
  3. Clear the fulfillment and approval queues and resolve the duplicates. 46 orders have no fulfillment (§8-B; $129,046.44): 33 Pending Fulfillment — oldest SO4143 2026-06-15 ($3,952.29, 81 days) and SO4150 2026-07-05 ($12,594.22, 61 days) — and 13 Pending Approval. The approval queue contains two identical trios for Design Excellence Ltd. (SO4314/4315/4316 on 09-01 and SO4318/4319/4320 on 09-14 at $5,146.21 / $3,273.99 / $2,697.68) and a Godric Motors pair (SO4293 / SO4313, both $1,627.03, both 2026-09-30, one in each queue). Collect INV759 ($53,424.00, Blockster Inc., 63 days past due; §8-G). Apply or refund the 12 deposited-but-unapplied payments ($3,245.96; §8-D), starting with PYMT01, which duplicates PYMT675 for Susan Adams on 2026-08-31.

AAppendix A — SuiteQL used

Reproduced verbatim as executed. Q3–Q5 are the three inputs to the reducer passes in Appendix B; the rest are probes, registers and supporting figures. All queries run as the current user with role Administrator.

Q1 — Link-type census between O2C document types

SELECT p.type AS prev_type, n.type AS next_type, l.linktype, COUNT(*) AS link_rows,
       COUNT(DISTINCT l.previousdoc || '-' || l.nextdoc) AS distinct_pairs
FROM nexttransactionlinelink l
JOIN transaction p ON p.id = l.previousdoc
JOIN transaction n ON n.id = l.nextdoc
WHERE p.type IN ('SalesOrd','ItemShip','CustInvc','CustPymt','CustCred','CashSale','CustDep','DepAppl','Deposit')
   OR n.type IN ('SalesOrd','ItemShip','CustInvc','CustPymt','CustCred','CashSale','CustDep','DepAppl','Deposit')
GROUP BY p.type, n.type, l.linktype
ORDER BY COUNT(*) DESC

Q2 — Header census with date ranges

SELECT t.type, COUNT(*) AS docs,
       TO_CHAR(MIN(t.trandate),'YYYY-MM-DD') AS first_date, TO_CHAR(MAX(t.trandate),'YYYY-MM-DD') AS last_date,
       TO_CHAR(MIN(t.createddate),'YYYY-MM-DD') AS min_created, TO_CHAR(MAX(t.createddate),'YYYY-MM-DD') AS max_created
FROM transaction t
WHERE t.type IN ('SalesOrd','ItemShip','CustInvc','CustPymt','CustCred','CashSale','CustDep','DepAppl','CustRfnd')
GROUP BY t.type ORDER BY COUNT(*) DESC

Q3 — Streamed line query (keyset on uk, no ORDER BY, 5,000 rows/page)

SELECT tl.uniquekey AS uk, tl.transaction AS tid, tl.mainline, tl.createdfrom
FROM transactionline tl
JOIN transaction t ON t.id = tl.transaction
WHERE t.type IN ('SalesOrd','ItemShip','CustInvc','CustPymt','CustCred')

Q4 — Materialised headers

SELECT t.id, t.type, t.tranid, TO_CHAR(t.trandate,'YYYY-MM-DD') AS trandate, TO_CHAR(t.trandate,'HH24:MI:SS') AS ttime,
       t.status, t.entity, t.foreigntotal AS total, t.foreignamountunpaid AS unpaid,
       TO_CHAR(t.createddate,'YYYY-MM-DD HH24:MI:SS') AS created
FROM transaction t
WHERE t.type IN ('SalesOrd','ItemShip','CustInvc','CustPymt','CustCred')

Q5 — Materialised distinct links

SELECT DISTINCT l.previousdoc, l.nextdoc, l.linktype
FROM nexttransactionlinelink l
JOIN transaction p ON p.id = l.previousdoc
JOIN transaction n ON n.id = l.nextdoc
WHERE p.type IN ('SalesOrd','ItemShip','CustInvc','CustPymt','CustCred')
  AND n.type IN ('SalesOrd','ItemShip','CustInvc','CustPymt','CustCred')

Q6 — Ship-date population

SELECT COUNT(*) AS fulfillments,
       SUM(CASE WHEN t.shipdate IS NULL THEN 1 ELSE 0 END) AS null_shipdate,
       SUM(CASE WHEN t.actualshipdate IS NULL THEN 1 ELSE 0 END) AS null_actualshipdate
FROM transaction t WHERE t.type = 'ItemShip'

Q7 — Status-change system notes (approval observability)

SELECT t.type, COUNT(DISTINCT sn.recordid) AS docs_with_status_notes, COUNT(*) AS notes
FROM systemnote sn JOIN transaction t ON t.id = sn.recordid
WHERE sn.recordtypeid = -30 AND sn.field = 'TRANDOC.KSTATUS'
  AND t.type IN ('SalesOrd','ItemShip','CustInvc','CustPymt')
GROUP BY t.type

Q8 — Register 8-A: invoices with no inbound link

SELECT t.id, t.tranid, TO_CHAR(t.trandate,'YYYY-MM-DD') AS trandate, TO_CHAR(t.duedate,'YYYY-MM-DD') AS duedate,
       t.status, BUILTIN.DF(t.status) AS status_label, t.entity, c.entityid AS customer,
       ROUND(t.foreigntotal,2) AS total, ROUND(t.foreignamountpaid,2) AS paid, ROUND(t.foreignamountunpaid,2) AS unpaid,
       TRUNC(SYSDATE)-TRUNC(t.trandate) AS age_days, TRUNC(SYSDATE)-TRUNC(t.duedate) AS days_past_due,
       (SELECT COUNT(*) FROM nexttransactionlinelink l WHERE l.previousdoc = t.id) AS outbound_links, t.memo
FROM transaction t LEFT JOIN customer c ON c.id = t.entity
WHERE t.type = 'CustInvc'
  AND NOT EXISTS (SELECT 1 FROM nexttransactionlinelink l WHERE l.nextdoc = t.id)
ORDER BY t.foreignamountunpaid DESC

Q9 — Register 8-B: sales orders with no fulfillment

SELECT t.id, t.tranid, TO_CHAR(t.trandate,'YYYY-MM-DD') AS trandate, t.status, BUILTIN.DF(t.status) AS status_label,
       t.entity, c.entityid AS customer, ROUND(t.foreigntotal,2) AS total, TRUNC(SYSDATE)-TRUNC(t.trandate) AS age_days,
       (SELECT COUNT(*) FROM nexttransactionlinelink l JOIN transaction n ON n.id = l.nextdoc
         WHERE l.previousdoc = t.id AND n.type = 'CustInvc') AS invoices
FROM transaction t LEFT JOIN customer c ON c.id = t.entity
WHERE t.type = 'SalesOrd'
  AND NOT EXISTS (SELECT 1 FROM nexttransactionlinelink l JOIN transaction n ON n.id = l.nextdoc
                  WHERE l.previousdoc = t.id AND n.type = 'ItemShip')
ORDER BY t.trandate

Q10 — Registers 8-D / 8-F: documents with no links in either direction

SELECT t.type, t.id, t.tranid, TO_CHAR(t.trandate,'YYYY-MM-DD') AS trandate, t.status, BUILTIN.DF(t.status) AS status_label,
       t.entity, c.entityid AS customer, ROUND(t.foreigntotal,2) AS total, ROUND(t.foreignamountunpaid,2) AS unpaid, t.memo
FROM transaction t LEFT JOIN customer c ON c.id = t.entity
WHERE t.type IN ('CustPymt','ItemShip','CustCred')
  AND NOT EXISTS (SELECT 1 FROM nexttransactionlinelink l WHERE l.nextdoc = t.id)
  AND NOT EXISTS (SELECT 1 FROM nexttransactionlinelink l WHERE l.previousdoc = t.id)
  AND NOT EXISTS (SELECT 1 FROM transactionline tl WHERE tl.transaction = t.id AND tl.mainline = 'T' AND tl.createdfrom IS NOT NULL)
ORDER BY t.type, t.trandate

Q11 — Register 8-E: source of fulfillments with no sales-order link

SELECT t.id, t.tranid, TO_CHAR(t.trandate,'YYYY-MM-DD') AS trandate, BUILTIN.DF(t.status) AS status_label,
       tl.createdfrom, src.type AS source_type, src.tranid AS source_tranid, t.memo
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id AND tl.mainline = 'T'
LEFT JOIN transaction src ON src.id = tl.createdfrom
WHERE t.type = 'ItemShip'
  AND NOT EXISTS (SELECT 1 FROM nexttransactionlinelink l JOIN transaction p ON p.id = l.previousdoc
                  WHERE l.nextdoc = t.id AND p.type = 'SalesOrd')
ORDER BY t.trandate

Q12 — Monthly volumes (Figure 5)

SELECT TO_CHAR(t.trandate,'YYYY-MM') AS ym, t.type, COUNT(*) AS docs,
       ROUND(SUM(CASE WHEN t.type IN ('SalesOrd','CustInvc') THEN t.foreigntotal ELSE 0 END),2) AS total
FROM transaction t
WHERE t.type IN ('SalesOrd','ItemShip','CustInvc','CustPymt')
GROUP BY TO_CHAR(t.trandate,'YYYY-MM'), t.type
ORDER BY 1, 2

Q13 — Invoiced→Paid buckets (Figure 4)

SELECT CASE WHEN d = 0 THEN 'a 0' WHEN d BETWEEN 1 AND 3 THEN 'b 1-3' WHEN d BETWEEN 4 AND 7 THEN 'c 4-7'
            WHEN d BETWEEN 8 AND 30 THEN 'd 8-30' WHEN d BETWEEN 31 AND 90 THEN 'e 31-90' ELSE 'f 91+' END AS bucket,
       COUNT(*) AS pairs, ROUND(SUM(amt),2) AS invoice_total
FROM (SELECT DISTINCT i.id, TRUNC(p.trandate)-TRUNC(i.trandate) AS d, i.foreigntotal AS amt
      FROM nexttransactionlinelink l
      JOIN transaction i ON i.id = l.previousdoc AND i.type = 'CustInvc'
      JOIN transaction p ON p.id = l.nextdoc AND p.type = 'CustPymt')
GROUP BY CASE WHEN d = 0 THEN 'a 0' WHEN d BETWEEN 1 AND 3 THEN 'b 1-3' WHEN d BETWEEN 4 AND 7 THEN 'c 4-7'
              WHEN d BETWEEN 8 AND 30 THEN 'd 8-30' WHEN d BETWEEN 31 AND 90 THEN 'e 31-90' ELSE 'f 91+' END
ORDER BY 1

Q14 — TEST-memo census

SELECT t.type, SUM(CASE WHEN t.memo LIKE 'TEST%' THEN 1 ELSE 0 END) AS test_memo, COUNT(*) AS docs,
       ROUND(SUM(CASE WHEN t.memo LIKE 'TEST%' THEN t.foreignamountunpaid ELSE 0 END),2) AS test_unpaid
FROM transaction t
WHERE t.type IN ('SalesOrd','ItemShip','CustInvc','CustPymt','CustCred')
GROUP BY t.type ORDER BY 1

Q15 — Status glossary (Appendix D)

SELECT x.type, x.status, x.docs, BUILTIN.DF(t.status) AS label
FROM (SELECT type, status, COUNT(*) AS docs, MIN(id) AS sample_id FROM transaction
      WHERE type IN ('SalesOrd','ItemShip','CustInvc','CustPymt','CustCred') GROUP BY type, status) x
JOIN transaction t ON t.id = x.sample_id
ORDER BY x.type, x.docs DESC

Q16 — Link pairs for the pay-before-ship register (reduced in Worker, Appendix B pass 4)

SELECT DISTINCT l.linktype, p.id AS pid, p.type AS ptype, p.tranid AS ptran, TO_CHAR(p.trandate,'YYYY-MM-DD') AS pdate,
       n.id AS nid, n.type AS ntype, n.tranid AS ntran, TO_CHAR(n.trandate,'YYYY-MM-DD') AS ndate,
       ROUND(p.foreigntotal,2) AS ptotal, BUILTIN.DF(p.status) AS pstatus, c.entityid AS pcustomer, p.memo AS pmemo
FROM nexttransactionlinelink l
JOIN transaction p ON p.id = l.previousdoc
JOIN transaction n ON n.id = l.nextdoc
LEFT JOIN customer c ON c.id = p.entity
WHERE l.linktype IN ('ShipRcpt','PickPack','OrdBill','Payment')
  AND p.type IN ('SalesOrd','CustInvc') AND n.type IN ('ItemShip','CustInvc','CustPymt')
Query notes
Two direct-SQL attempts at the pay-before-ship register (a four-way self-join with TRUNC(sh.trandate) > TRUNC(py.trandate), with and without GROUP BY) returned "Invalid or unsupported search"; the register was therefore assembled in the Worker from Q16. Selecting t.shipdate alongside a transactionline join also errored and was moved to the header-only probe Q6. BUILTIN.DF(t.status) inside a GROUP BY errored; Q15 works around it with a sample-id subquery.

BAppendix B — Reducer logic

Four sqlReduce passes were run. Passes 1–3 share the same three inputs (Q3 streamed, Q4 and Q5 materialised) and the same graph-building core; each returns a different summary so that no single result exceeded the 50 KB cap. Raw rows never entered the model context — only the returned summaries did.

Core: graph and components (shared by passes 1–3)

// fold: keep only mainline rows and their createdfrom pointer
fold(acc, page) { for (r of page) { acc.linesIn++; if (r.mainline === 'T') { acc.mainlines++;
  if (r.createdfrom) acc.cf.push([+r.createdfrom, +r.tid]); } } return acc; }

// finalize: docs from Q4; edges from Q5 (ntll) ∪ acc.cf (createdfrom); drop self-links and out-of-scope ids
addEdge(p, n, src, linktype)  // records which source(s) produced each distinct edge
// events: one per doc; objects = own doc + all upstream docs; payments inherit SalesOrd via CustInvc
// same-day tie-break
RANK = { 'Order Created':0, 'Shipped':1, 'Invoiced':2, 'Paid':3, 'Credited':4 }
cmp  = (a,b) => a.ts < b.ts ? -1 : a.ts > b.ts ? 1 : (RANK[a.activity]-RANK[b.activity]) || (a.docId-b.docId)
// components: union-find over edges; each component -> sorted event trace, variant string, cycle = daysBetween(first,last)

Pass 1 — event log, reachability, multiplicity

Counts edges by source (both / ntll-only / createdfrom-only), classifies each non-order document by how it was reached, and histograms fulfillments-per-order, invoices-per-order, orders-per-invoice, payments-per-invoice, invoices-per-payment and orders-per-payment (via invoice).

Pass 2 — OC-DFG, perspectives, variants, transitions, conformance

oc = H.ocdfg(events, { tieBreak })            // per-object-type lifecycles and transitions
orderTraces   = SalesOrd → all events whose objects include that order
paymentTraces = CustPymt → { payment, its invoices, their orders, those orders' fulfillments }
H.dfg(orderTraces), H.dfg(paymentTraces)      // perspective edges with median / p90 / max
variants  = groupBy(components, variant) → count, exact median / p90 / max of cycle days
transitions = consecutive event pairs within each component trace → count, median, p90, max, same-day share
conformance per component:
  starts with Order Created; exactly one Order, ≤1 Invoiced, ≤1 Paid; no Credited; nothing after Paid;
  Paid requires Invoiced and Shipped; Shipped after Paid → 'paid before shipped' (flagged separately)
  else complete (has Paid) or open prefix

Pass 3 — registers and open items

Variants 5–12, transition table with examples, ten slowest components with their longest wait, open components by last activity and order status, and the orphan-document registers with totals.

Pass 4 — pay-before-ship register (whole contract over Q16)

Indexes SO→IF, SO→INV, INV→PYMT from the 2,210 distinct pairs; for every order with all three, emits the chain where if_date > pymt_date with the three inter-document gaps; also returns the two hand-check chains in Appendix C.

CAppendix C — Worked hand-check

Two components from the dominant variant, with every internal id, so the chain can be re-derived from the record pages or from Q5 with WHERE l.previousdoc IN (…).

StepDocumentInternal idDateStatusAmountLink that produced the edge
Order CreatedSO3542 (Gail Mack)347362024-10-01Billed33.41—
ShippedIF4990357982024-10-01Shipped—ntll ShipRcpt 34736→35798; createdfrom = 34736
InvoicedINV28357992024-10-01Paid In Full33.41ntll OrdBill + OrdRvCom 34736→35799; createdfrom = 34736
PaidPYMT17358002024-10-01Deposited—ntll Payment 35799→35800
Cycle = daysBetween(2024-10-01, 2024-10-01) = 0. Variant "Order Created > Shipped > Invoiced > Paid". Conforming, complete. Component size 4.
Order CreatedSO3544 (Cherry Hines)347382024-10-07Billed558.46—
ShippedIF4991358032024-10-07Shipped—ntll ShipRcpt; createdfrom = 34738
InvoicedINV30358042024-10-07Paid In Full558.46ntll OrdBill + OrdRvCom; createdfrom = 34738
PaidPYMT19358052024-10-07Deposited—ntll Payment 35804→35805
Cycle = 0. Same variant. Note the internal-id adjacency (35798–35800, 35803–35805): each chain was loaded as a unit, consistent with A1's demo-load observation.

Arithmetic checks. Open invoice balance: 790,017.65 (TEST, §8-A) + 138,228.97 (order-linked) = 928,246.62 ✓. Conformance: 704 + 61 + 80 = 845 ✓; (704 + 61) / 845 = 0.9053 ✓. Edges: 742 + 732 + 736 = 2,210 ✓. Deviations: 33 + 20 + 12 + 12 + 3 = 80 ✓. Invoiced→Paid pairs: 658 + 29 + 20 + 22 + 5 + 2 = 736 ✓ (the component-level transition count of 735 excludes SO3129's payment, which follows Order Created in its trace).

DAppendix D — Glossaries

Status codes observed (live labels, Q15)

TypeCodeLabelDocs
SalesOrdGBilled731
SalesOrdBPending Fulfillment40
SalesOrdAPending Approval13
SalesOrdFPending Billing3
SalesOrdEPending Billing / Partially Fulfilled1
ItemShipCShipped744
ItemShipBPacked4
ItemShipAPicked4
CustInvcBPaid In Full725
CustInvcAOpen39
CustPymtCDeposited746
CustPymtBNot Deposited2
CustCredAOpen2
CustCredBFully Applied1

Link types (nexttransactionlinelink.linktype)

LinktypeFrom → ToRowsMeaning
ShipRcptSalesOrd → ItemShip2,142Fulfillment of order lines
PickPackSalesOrd → ItemShip11Pick/pack stage fulfillment
OrdBillSalesOrd → CustInvc2,136Billing of order lines
OrdRvComSalesOrd → CustInvc2,136Revenue-commitment twin of OrdBill (deduplicated)
PaymentCustInvc → CustPymt / DepAppl737Cash or deposit applied to invoice
DepRfndCashSale → Deposit1,026Cash-sale sweep (out of scope)
SaleRetSalesOrd → RtnAuth8Return authorisation (out of scope)
DropShip / SpecOrdSalesOrd → PurchOrd / WorkOrd4Procurement side (out of scope)

Terms

Object-centric DFG — directly-follows graph where each event carries the set of objects it touches, and transitions are counted per object lifecycle. Convergence — number of objects of one type a single event touches; 1 means no fan-out. Component — maximal set of documents connected by links (the case notion used here). Variant — the ordered activity sequence of a component. ntll — nexttransactionlinelink.

EAppendix E — Run metadata and revision log

ItemValue
Account / environmentTD3016323 · production · OneWorld · USD
Analysis date2026-09-04 (SYSDATE for age calculations)
ToolingSonar AI v1.15.0 · sqlReduce streaming fold (keyset on transactionline.uniquekey) · runSql for registers · no records were created, modified or deleted
Stream statistics (Q3)21,032 rows · 5 pages (5,000 × 4 + 1,032) · uniquekey range within 3,401–520,341 · 1.9–2.8 s per pass · ~30 KB on the wire per pass (≈7:1 compression vs 1.28 MB decoded) · peak held ≈ 0.9 MB including materialised headers
Materialised inputsQ4 headers 3,055 rows · Q5 links 2,942 rows · Q16 pairs 2,210 rows
Worker timePass 1 2.9 s · Pass 2 2.3 s · Pass 3 1.9 s · Pass 4 13 ms
Percentile methodExact (A11)
PrivacyCustomer names appear in §8 registers and Appendix C as recorded on the transactions. Privacy Mode was not active for this run; a redacted edition can be produced on request.

Revision log

RevDateChange
12026-09-04Initial analysis: event log, structure, OC-DFG, variants, bottlenecks, conformance, data quality, three findings.
22026-09-04Added executive summary, lineage figure, assumptions register, six charts, full exception registers (8-A to 8-G), monthly trend, Invoiced→Paid distribution, deep links, sortable tables, verbatim SuiteQL, reducer logic, hand-check, glossaries. Material correction: the 32 order-less invoices are memo-tagged TEST fixtures — finding 1 reframed from "collections" to "data quarantine". The 10 unlinked fulfillments were traced to transfer orders / vendor RMAs and reclassified as out of scope. Probable duplicate sales orders and one duplicate payment identified. Status label for SalesOrd G corrected to "Billed" from the live account.