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:
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.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.
| Stage | Count | Note |
|---|---|---|
transactionline rows streamed | 21,032 | 5 keyset pages on uniquekey, 5,000 rows/page, 2.3 s total, peak held ≈ 1 page |
| Mainline rows (one per document) | 3,055 | Equals header count — no document dropped |
createdfrom candidates → used | 1,486 → 1,474 | 12 dropped: target outside the five types (return authorisations, transfer orders) |
nexttransactionlinelink raw in-scope rows → distinct pairs | 7,161 → 2,942 | SO→INV emitted twice per line (OrdBill + OrdRvCom); 7 PickPack pairs coincide with ShipRcpt |
| Distinct edges after merging both sources | 2,210 | SO→IF 742 · SO→INV 732 · INV→PYMT 736 |
| Events / objects | 3,055 / 3,055 | One event per document; each event carries its own object plus every upstream object |
| Connected components | 845 | Union-find over the 2,210 edges |
| # | Assumption | Effect on results |
|---|---|---|
| A1 | Scope 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. |
| A2 | One 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). |
| A3 | Timestamp = trandate at day resolution. | All durations are whole days; same-day chains have cycle 0. |
| A4 | Same-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. |
| A5 | Links 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. |
| A6 | A payment inherits sales-order objects through its invoice(s). | Payment-perspective traces include Order Created and Shipped events. |
| A7 | A case = connected component of the document graph. | Single unlinked documents are components of size 1 and are counted as deviations in §7. |
| A8 | Reference 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. |
| A9 | Cash 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. |
| A10 | No subsidiary filter applied. | Two intercompany orders (SO3148, SO3149, $29.99 each) are included. |
| A11 | Percentiles are exact (whole arrays held in the Worker); H.percentile(arr, 0.9). | No reservoir sampling anywhere in this report. |
| A12 | Money = foreigntotal / foreignamountunpaid (transaction currency; account is single-currency USD). | — |
| A13 | Status labels are the live BUILTIN.DF(status) text, not the generic reference table. | SalesOrd status G reads "Billed" in this account. |
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].
| Object type | Objects | Reached by both sources | ntll only | createdfrom only | Unreached | Unreached examples (raw dates) |
|---|---|---|---|---|---|---|
| Sales order (SalesOrd) | 788 | root object | 45 with no downstream | SO3148 2026-09-25 (Pending Fulfillment) · SO3149 2026-09-25 (Pending Fulfillment) | ||
| Item fulfillment (ItemShip) | 752 | 742 | 0 | 0 | 10 | IF3112 2026-08-26 · IF3113 2026-08-26 — both from transfer orders (§8-E) |
| Invoice (CustInvc) | 764 | 732 | 0 | 0 | 32 | INV762 2026-08-27 · INV763 2026-08-27 — both memo TEST (§8-A) |
| Customer payment (CustPymt) | 748 | 0 | 736 | 0 | 12 | PYMT01 2026-08-31 · PYMT02 2026-09-17 (§8-D) |
| Credit memo (CustCred) | 3 | 0 | 0 | 0 | 3 | CM01 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.
| Multiplicity | 0 | 1 | 2 or more | Example of the "0" case |
|---|---|---|---|---|
| Fulfillments per order | 46 | 742 | 0 | SO4143 2026-06-15 · SO4150 2026-07-05 |
| Invoices per order | 56 | 732 | 0 | SO3151 2026-09-25 (shipped IF4947, not invoiced) · SO3148 2026-09-25 |
| Orders per invoice | 32 | 732 | 0 | INV790 2025-12-04 · INV782 2026-03-20 (TEST block) |
| Payments per invoice | 28 | 736 | 0 | INV759 2026-06-03 ($53,424 open) · INV713 2026-09-04 |
| Invoices per payment | 12 | 736 | 0 | PYMT01 2026-08-31 · PYMT03 2026-08-31 |
| Orders per payment | 23 | 725 | 0 | the 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.
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.
| Transition | SalesOrd lifecycles | CustInvc lifecycles | Convergence | Reading |
|---|---|---|---|---|
| Order Created → Shipped | 720 | — | 1 | Main path |
| Shipped → Invoiced | 711 | — | 1 | Main path |
| Invoiced → Paid | 724 | 736 | 1 | 12 invoice lifecycles carry this transition without a linked order (the 11 paid TEST invoices + INV03) |
| Order Created → Invoiced | 20 | — | 1 | Bill-before-ship path |
| Paid → Shipped | 20 | — | 1 | Bill-before-ship path (shipment last) |
| Shipped → Order Created | 2 | — | 1 | Fulfillment dated before its order: IF4955 2026-09-20 → SO2903 2026-10-01; IF5728 2026-09-07 → SO4305 2026-09-30 |
| Invoiced → Order Created | 1 | — | 1 | INV03 2026-08-25 precedes SO3129 2026-08-31 (customer-deposit path, A9) |
| Order Created → Paid | 1 | — | 1 | Same 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.
| Transition | Order view count | median / P90 / max days | Payment view count | median / P90 / max days |
|---|---|---|---|---|
| Invoiced → Paid | 724 | 0 / 0 / 19 | 735 | 0 / 3 / 273 |
| Order Created → Shipped | 720 | 0 / 0 / 6 | 704 | 0 / 0 / 3 |
| Shipped → Invoiced | 711 | 0 / 0 / 3 | 704 | 0 / 0 / 3 |
| Order Created → Invoiced | 20 | 15 / 15 / 18 | 20 | 15 / 15 / 18 |
| Paid → Shipped | 20 | 12 / 12.2 / 365 | 20 | 12 / 12.2 / 365 |
| Trace start states | Order Created 785 · Shipped 2 · Invoiced 1 | Order Created 724 · Paid 12 · Invoiced 12 | ||
| Trace end states | Paid 705 · Order Created 47 · Shipped 29 · Invoiced 7 | Paid 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.
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).
| # | Variant | Count | Median days | P90 days | Max | Examples (raw dates) |
|---|---|---|---|---|---|---|
| 1 | Order Created → Shipped → Invoiced → Paid | 704 | 0 | 0 | 7 | SO3542 → IF4990 → INV28 → PYMT17, all 2024-10-01 SO3544 → IF4991 → INV30 → PYMT19, all 2024-10-07 |
| 2 | Order Created (only) | 45 | 0 | 0 | 0 | SO3148 2026-09-25 · SO3149 2026-09-25 |
| 3 | Invoiced (only) | 21 | 0 | 0 | 0 | INV762 2026-08-27 · INV763 2026-08-27 |
| 4 | Order Created → Invoiced → Paid → Shipped | 20 | 45 | 46.2 | 365 | SO3985 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 |
| 5 | Paid (only) | 12 | 0 | 0 | 0 | PYMT01 2026-08-31 · PYMT02 2026-09-17 |
| 6 | Invoiced → Paid | 11 | 35 | 139 | 273 | INV764 2026-08-27 → PYMT750 2026-09-05 INV765 2026-08-19 → PYMT751 2026-09-06 |
| 7 | Shipped (only) | 10 | 0 | 0 | 0 | IF3112 2026-08-26 · IF3113 2026-08-26 |
| 8 | Order Created → Shipped | 9 | 1 | 3.6 | 6 | SO3151 2026-09-25 → IF4947 2026-09-25 SO3150 2026-09-25 → IF4950 2026-09-26 |
| 9 | Order Created → Shipped → Invoiced | 7 | 3 | 3 | 3 | SO4167 2026-09-01 → IF5674 2026-09-01 → INV713 2026-09-04 SO4169 2026-09-04 → IF5675 2026-09-04 → INV715 2026-09-07 |
| 10 | Credited (only) | 3 | 0 | 0 | 0 | CM01 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).
Figure 4 — 736 invoice→payment pairs from nexttransactionlinelink, bucketed by whole days between invoice and payment date.
| Days to pay | Pairs | Share | Invoice value |
|---|---|---|---|
| 0 (same day) | 658 | 89.4% | 1,819,921.62 |
| 1–3 | 29 | 3.9% | 105,918.89 |
| 4–7 | 20 | 2.7% | 35,077.72 |
| 8–30 | 22 | 3.0% | 55,913.19 |
| 31–90 | 5 | 0.7% | 20,385.80 |
| 91+ | 2 | 0.3% | 979.91 |
| Total | 736 | 100% | 2,038,197.13 |
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.
| Month | Sales orders | Fulfillments | Invoices | Payments | SO value | Invoice value |
|---|
| Transition | Count | Median | P90 | Max | Same-day share | Examples |
|---|---|---|---|---|---|---|
| Invoiced → Paid | 735 | 0 | 3 | 273 | 89.5% | INV28 2024-10-01 → PYMT17 2024-10-01 (0d) · INV30 2024-10-07 → PYMT19 2024-10-07 (0d) |
| Order Created → Shipped | 720 | 0 | 0 | 6 | 98.6% | SO3151 2026-09-25 → IF4947 2026-09-25 (0d) · SO3150 2026-09-25 → IF4950 2026-09-26 (1d) |
| Shipped → Invoiced | 711 | 0 | 0 | 3 | 92.4% | IF4990 2024-10-01 → INV28 2024-10-01 (0d) · IF4991 2024-10-07 → INV30 2024-10-07 (0d) |
| Order Created → Invoiced | 20 | 15 | 15 | 18 | 10.0% | SO3985 2025-02-10 → INV471 2025-02-10 (0d) · SO4287 2026-08-13 → INV758 2026-08-31 (18d) |
| Paid → Shipped | 20 | 12 | 12.2 | 365 | 0% | PYMT460 2025-02-10 → IF5543 2025-02-14 (4d) · PYMT385 2025-02-09 → IF5503 2026-02-09 (365d) |
| Shipped → Order Created | 2 | 17 | 21.8 | 23 | 0% | IF4955 2026-09-20 → SO2903 2026-10-01 (11d) · IF5728 2026-09-07 → SO4305 2026-09-30 (23d) |
| Invoiced → Order Created | 1 | 6 | 6 | 6 | 0% | INV03 2026-08-25 → SO3129 2026-08-31 (6d) |
| Order Created → Paid | 1 | 0 | 0 | 0 | 100% | 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.
| Component | Cycle days | Longest wait (what it was waiting on) | SO total | Note |
|---|---|---|---|---|
| SO3910 | 365 | Paid PYMT385 2025-02-09 → Shipped IF5503 2026-02-09 (365d) | 60.23 | Exactly one year — a dating artefact is likely |
| INV787 (no order) | 273 | Invoiced 2025-12-14 → Paid PYMT760 2026-09-13 (273d) | — | TEST memo; $263.58 still open |
| INV781 (no order) | 139 | Invoiced 2026-04-27 → Paid PYMT759 2026-09-13 (139d) | — | TEST memo; $416.33 still open |
| INV776 (no order) | 71 | Invoiced 2026-07-02 → Paid PYMT757 2026-09-11 (71d) | — | TEST memo; $3,135.21 still open |
| INV779 (no order) | 64 | Invoiced 2026-07-10 → Paid PYMT758 2026-09-12 (64d) | — | TEST memo; $2,810.66 still open |
| INV773 (no order) | 51 | Invoiced 2026-07-21 → Paid PYMT756 2026-09-10 (51d) | — | TEST memo; $3,555.61 still open |
| SO4287 | 48 | Order 2026-08-13 → Invoiced INV758 2026-08-31 (18d); shipped IF5721 2026-09-30 after payment | 462.19 | Bill-before-ship |
| SO4281 | 46 | Invoiced INV751 2026-08-17 → Paid PYMT741 2026-09-05 (19d); shipped IF5714 2026-09-17 | 1,399.05 | Bill-before-ship |
| SO4282 | 46 | Invoiced INV752 2026-08-18 → Paid PYMT742 2026-09-06 (19d); shipped IF5715 2026-09-18 | 1,627.03 | Bill-before-ship |
| SO4283 | 46 | Invoiced INV753 2026-08-19 → Paid PYMT743 2026-09-07 (19d); shipped IF5716 2026-09-19 | 462.19 | Bill-before-ship |
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.
| Outcome | Components | Share |
|---|---|---|
| Complete and conforming (all four activities) | 704 | 83.3% |
| Conforming open prefix (order only 45; order + shipped 9; order + shipped + invoiced 7) | 61 | 7.2% |
| Deviant | 80 | 9.5% |
| Conforming share | 765 of 845 | 90.5% |
| Pattern | Count | Root cause | Examples |
|---|---|---|---|
| Component starts with Invoiced (no order) | 33 | 32 TEST-memo invoices (§8-A) + INV03 customer-deposit path | INV762 2026-08-27 · INV03 2026-08-25 → SO3129 2026-08-31 → PYMT675 2026-08-31 |
| Activity after Paid (shipment after payment) | 20 | Bill-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 Shipped | 12 | 10 fulfillments from transfer orders / vendor RMAs (§8-E, out of O2C scope) + 2 fulfillments dated before their order | IF3112 2026-08-26 (TO03) · IF4955 2026-09-20 → SO2903 2026-10-01 |
| Component starts with Paid (unlinked payment) | 12 | Deposited but unapplied payments (§8-D) | PYMT01 2026-08-31 ($934.78) · PYMT02 2026-09-17 ($1,049.79) |
| Credit memo not linked to any invoice | 3 | Stand-alone credits (§8-F) | CM01 2026-09-14 · CM03 2026-09-15 |
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.
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.
TEST — …| Invoice | Internal id | Date | Due | Status | Customer | Total | Paid | Unpaid | Days past due | Paid by |
|---|---|---|---|---|---|---|---|---|---|---|
| INV790 | 42314 | 2025-12-04 | 2026-01-03 | Open | Global Information | 110,579.17 | 0.00 | 110,579.17 | 244 | — |
| INV782 | 42306 | 2026-03-20 | 2026-04-20 | Open | Red Rivers Consulting | 102,905.76 | 0.00 | 102,905.76 | 137 | — |
| INV774 | 42298 | 2026-07-22 | 2026-08-21 | Open | Magna Tech Limited | 97,942.27 | 0.00 | 97,942.27 | 14 | — |
| INV783 | 42307 | 2026-05-31 | 2026-07-01 | Open | Falcon Systems | 86,007.39 | 0.00 | 86,007.39 | 65 | — |
| INV791 | 42315 | 2025-05-20 | 2025-06-19 | Open | Mercury Co. | 80,079.02 | 0.00 | 80,079.02 | 442 | — |
| INV789 | 42313 | 2025-12-10 | 2026-01-09 | Open | Gotter inc. | 68,119.00 | 0.00 | 68,119.00 | 238 | — |
| INV788 | 42312 | 2025-09-30 | 2025-10-29 | Open | Haskell Associates | 43,940.75 | 0.00 | 43,940.75 | 310 | — |
| INV784 | 42308 | 2026-04-16 | 2026-05-15 | Open | John G. Roche Opticians | 31,810.19 | 0.00 | 31,810.19 | 112 | — |
| INV780 | 42304 | 2026-03-23 | 2026-04-23 | Open | Informics International | 29,239.18 | 0.00 | 29,239.18 | 134 | — |
| INV775 | 42299 | 2026-06-25 | 2026-07-27 | Open | Greenwood Consulting | 26,274.61 | 0.00 | 26,274.61 | 39 | — |
| INV778 | 42302 | 2026-06-26 | 2026-07-28 | Open | Heidelberg Haus | 22,473.78 | 0.00 | 22,473.78 | 38 | — |
| INV772 | 42296 | 2026-07-19 | 2026-08-18 | Open | Dazzlesphere Company | 20,053.84 | 0.00 | 20,053.84 | 17 | — |
| INV763 | 42287 | 2026-08-27 | 2026-09-27 | Open | Macgruber Incorporated | 16,884.00 | 0.00 | 16,884.00 | -23 | — (duplicate of INV762?) |
| INV762 | 42286 | 2026-08-27 | 2026-09-27 | Open | Macgruber Incorporated | 16,884.00 | 0.00 | 16,884.00 | -23 | — |
| INV767 | 42291 | 2026-09-12 | 2026-10-11 | Open | Machester Mfg | 5,134.29 | 0.00 | 5,134.29 | -37 | — |
| INV777 | 42301 | 2026-06-21 | 2026-07-23 | Open | Macomb Industries | 4,458.82 | 0.00 | 4,458.82 | 43 | — |
| INV766 | 42290 | 2026-08-15 | 2026-09-15 | Open | McCarthy Supplies | 9,667.33 | 5,500.00 | 4,167.33 | -11 | partial |
| INV773 | 42297 | 2026-07-21 | 2026-08-20 | Open | Fernhill Solutions | 6,555.61 | 3,000.00 | 3,555.61 | 15 | PYMT756 2026-09-10 |
| INV776 | 42300 | 2026-07-02 | 2026-08-01 | Open | Copper Software | 4,935.21 | 1,800.00 | 3,135.21 | 34 | PYMT757 2026-09-11 |
| INV779 | 42303 | 2026-07-10 | 2026-08-09 | Open | Pied Piper | 4,010.66 | 1,200.00 | 2,810.66 | 26 | PYMT758 2026-09-12 |
| INV786 | 42310 | 2025-11-09 | 2025-12-08 | Open | Ghetti Ltd | 2,740.64 | 0.00 | 2,740.64 | 270 | — |
| INV768 | 42292 | 2026-08-27 | 2026-09-27 | Open | Gramz LLP | 4,121.55 | 2,000.00 | 2,121.55 | -23 | partial |
| INV792 | 42316 | 2025-07-06 | 2025-08-05 | Open | Schmidt & Sons Consulting | 1,769.52 | 0.00 | 1,769.52 | 395 | — |
| INV770 | 42294 | 2026-08-03 | 2026-09-03 | Open | Schubert Software | 3,213.11 | 1,500.00 | 1,713.11 | 1 | partial |
| INV764 | 42288 | 2026-08-27 | 2026-09-27 | Open | Macgruber Incorporated | 18,487.98 | 16,884.00 | 1,603.98 | -23 | PYMT750 2026-09-05 |
| INV769 | 42293 | 2026-09-11 | 2026-10-10 | Open | Manzo Management | 1,573.97 | 0.00 | 1,573.97 | -36 | — |
| INV785 | 42309 | 2026-04-22 | 2026-05-21 | Open | Magneto Services | 787.12 | 0.00 | 787.12 | 106 | — |
| INV781 | 42305 | 2026-04-27 | 2026-05-26 | Open | The Abbott Inc. | 616.33 | 200.00 | 416.33 | 101 | PYMT759 2026-09-13 |
| INV787 | 42311 | 2025-12-14 | 2026-01-13 | Open | Jasper and Associates | 363.58 | 100.00 | 263.58 | 234 | PYMT760 2026-09-13 |
| INV793 | 42317 | 2025-04-04 | 2025-05-03 | Open | Kasson Ltd | 262.39 | 0.00 | 262.39 | 489 | — |
| INV765 | 42289 | 2026-08-19 | 2026-09-19 | Open | Finch Computing | 2,335.37 | 2,145.00 | 190.37 | -15 | PYMT751 2026-09-06 |
| INV771 | 42295 | 2026-07-31 | 2026-08-29 | Open | Jupiter Technology | 1,671.21 | 1,551.00 | 120.21 | 6 | partial |
| 32 invoices · contiguous internal ids 42286–42317 · every memo begins "TEST — " | 825,897.65 | 35,880.00 | 790,017.65 | |||||||
| Order | Internal id | Date | Status | Customer | Total | Age days | Note |
|---|---|---|---|---|---|---|---|
| SO4143 | 39341 | 2026-06-15 | Pending Fulfillment | Realpoint inc. | 3,952.29 | 81 | Oldest open order |
| SO4150 | 39348 | 2026-07-05 | Pending Fulfillment | Davis Supplies | 12,594.22 | 61 | Largest aged open order |
| SO4181 | 39379 | 2026-08-07 | Pending Fulfillment | Brenda Scott | 58.58 | 28 | |
| SO4161 | 39359 | 2026-08-09 | Pending Fulfillment | Hugo Limited | 4,314.17 | 26 | |
| SO4235 | 39433 | 2026-08-17 | Pending Fulfillment | Xandra Underwood | 349.57 | 18 | |
| SO4196 | 39394 | 2026-08-18 | Pending Fulfillment | Dan Coleman | 94.02 | 17 | |
| SO4193 | 39391 | 2026-08-19 | Pending Fulfillment | Cynthia Rose | 85.97 | 16 | |
| SO3129 | 24263 | 2026-08-31 | Pending Fulfillment | Susan Adams | 1,084.78 | 4 | Invoiced (INV03) and paid via deposit before the order; 2 invoice links |
| SO4314 | 42341 | 2026-09-01 | Pending Approval | Design Excellence Ltd. | 5,146.21 | 3 | Same amount as SO4318 |
| SO4304 | 42035 | 2026-09-01 | Pending Fulfillment | Copper Software | 4,516.67 | 3 | |
| SO4317 | 42344 | 2026-09-01 | Pending Approval | Blockster Inc. | 2,124.23 | 3 | |
| SO4316 | 42343 | 2026-09-01 | Pending Approval | Design Excellence Ltd. | 2,697.68 | 3 | Same amount as SO4320 |
| SO4315 | 42342 | 2026-09-01 | Pending Approval | Design Excellence Ltd. | 3,273.99 | 3 | Same amount as SO4319 |
| SO4203 | 39401 | 2026-09-02 | Pending Fulfillment | Justin Turner | 61.51 | 2 | |
| SO4291 | 41297 | 2026-09-04 | Pending Fulfillment | Jones Manufacturing | 461.10 | 0 | |
| SO4292 | 41298 | 2026-09-05 | Pending Fulfillment | Marshall Industries | 936.33 | -1 | |
| SO4310 | 42256 | 2026-09-07 | Pending Approval | Blockster Inc. | 474.87 | -3 | |
| SO4311 | 42257 | 2026-09-07 | Pending Approval | Marshall Industries | 56.60 | -3 | |
| SO4321 | 42349 | 2026-09-10 | Pending Approval | Global Information | 8,949.41 | -6 | |
| SO4303 | 42034 | 2026-09-10 | Pending Fulfillment | Customer 59 (entity 1233) | 1,725.05 | -6 | |
| SO4199 | 39397 | 2026-09-10 | Pending Fulfillment | Lex Ansel | 366.26 | -6 | |
| SO4312 | 42267 | 2026-09-11 | Pending Fulfillment | Blockster Inc. | 11,219.03 | -7 | |
| SO4322 | 42350 | 2026-09-11 | Pending Approval | Magna Tech Limited | 15,002.98 | -7 | Largest in approval queue |
| SO4294 | 41300 | 2026-09-12 | Pending Fulfillment | Davis Supplies | 1,655.35 | -8 | Same amount as SO4295 |
| SO4295 | 41301 | 2026-09-12 | Pending Fulfillment | Falcon Systems | 1,655.35 | -8 | Same amount as SO4294 |
| SO4318 | 42346 | 2026-09-14 | Pending Approval | Design Excellence Ltd. | 5,146.21 | -10 | Same amount as SO4314 |
| SO4184 | 39382 | 2026-09-14 | Pending Fulfillment | Brittany Thompson | 408.29 | -10 | |
| SO4320 | 42348 | 2026-09-14 | Pending Approval | Design Excellence Ltd. | 2,697.68 | -10 | Same amount as SO4316 |
| SO4319 | 42347 | 2026-09-14 | Pending Approval | Design Excellence Ltd. | 3,273.99 | -10 | Same amount as SO4315 |
| SO4269 | 40305 | 2026-09-15 | Pending Fulfillment | Design Excellence Ltd. | 257.60 | -11 | Same amount as SO4298 |
| SO4306 | 42221 | 2026-09-17 | Pending Fulfillment | Bryan Scott | 859.67 | -13 | |
| SO4298 | 41804 | 2026-09-20 | Pending Fulfillment | Design Excellence Ltd. | 257.60 | -16 | Same amount as SO4269 |
| SO4226 | 39424 | 2026-09-21 | Pending Fulfillment | Jolly Baker | 989.13 | -17 | |
| SO4175 | 39373 | 2026-09-22 | Pending Approval | Jones Manufacturing | 5,420.84 | -18 | |
| SO4249 | 39447 | 2026-09-22 | Pending Fulfillment | Trent Barry | 80.02 | -18 | |
| SO4307 | 42223 | 2026-09-23 | Pending Fulfillment | Copper Software | 452.63 | -19 | |
| SO4308 | 42225 | 2026-09-23 | Pending Fulfillment | Bryan Scott | 8,160.19 | -19 | |
| SO4178 | 39376 | 2026-09-24 | Pending Fulfillment | Bobby Davis | 989.13 | -20 | |
| SO4302 | 42033 | 2026-09-24 | Pending Fulfillment | Customer 78 (entity 3885) | 5,437.19 | -20 | |
| SO3149 | 28085 | 2026-09-25 | Pending Fulfillment | Interco Client US1 - US2 | 29.99 | -21 | Intercompany |
| SO3148 | 27978 | 2026-09-25 | Pending Fulfillment | Interco Client US2 - US1 | 29.99 | -21 | Intercompany |
| SO4309 | 42229 | 2026-09-29 | Pending Fulfillment | Design Excellence Ltd. | 5,181.36 | -25 | |
| SO4293 | 41299 | 2026-09-30 | Pending Fulfillment | Godric Motors | 1,627.03 | -26 | Same amount and date as SO4313 |
| SO4313 | 42339 | 2026-09-30 | Pending Approval | Godric Motors | 1,627.03 | -26 | Same amount and date as SO4293 |
| SO4288 | 41294 | 2026-09-30 | Pending Fulfillment | Hugo Limited | 1,604.38 | -26 | |
| SO4289 | 41295 | 2026-09-30 | Pending Fulfillment | Blockster Inc. | 911.60 | -26 | |
| 46 orders · 33 Pending Fulfillment · 13 Pending Approval | 129,046.44 | ||||||
| Order | Order date | Customer | Total | Invoice | Inv date | Payment | Pay date | Fulfillment | Ship date | O→I | I→P | P→S |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| SO3910 | 2025-02-09 | Brenda Scott | 60.23 | INV396 | 2025-02-09 | PYMT385 | 2025-02-09 | IF5503 | 2026-02-09 | 0 | 0 | 365 |
| SO3985 | 2025-02-10 | Recreational Outfitters | 4,429.31 | INV471 | 2025-02-10 | PYMT460 | 2025-02-10 | IF5543 | 2025-02-14 | 0 | 0 | 4 |
| SO4270 | 2026-06-03 | Design Excellence Ltd. | 913.76 | INV740 | 2026-06-18 | PYMT730 | 2026-07-06 | IF5703 | 2026-07-18 | 15 | 18 | 12 |
| SO4271 | 2026-06-03 | Godric Motors | 1,627.03 | INV741 | 2026-06-18 | PYMT731 | 2026-07-06 | IF5704 | 2026-07-18 | 15 | 18 | 12 |
| SO4272 | 2026-06-05 | Blockster Inc. | 911.60 | INV742 | 2026-06-20 | PYMT732 | 2026-07-08 | IF5705 | 2026-07-20 | 15 | 18 | 12 |
| SO4273 | 2026-06-07 | Jones Manufacturing | 461.10 | INV743 | 2026-06-22 | PYMT733 | 2026-07-10 | IF5706 | 2026-07-22 | 15 | 18 | 12 |
| SO4274 | 2026-06-08 | Meetz Industries | 1,653.46 | INV744 | 2026-06-23 | PYMT734 | 2026-07-11 | IF5707 | 2026-07-23 | 15 | 18 | 12 |
| SO4275 | 2026-07-02 | Design Excellence Ltd. | 1,604.38 | INV745 | 2026-07-17 | PYMT735 | 2026-08-04 | IF5708 | 2026-08-16 | 15 | 18 | 12 |
| SO4276 | 2026-07-03 | JBL Inc. | 930.98 | INV746 | 2026-07-18 | PYMT736 | 2026-08-05 | IF5709 | 2026-08-17 | 15 | 18 | 12 |
| SO4277 | 2026-07-05 | Davidson Supplies | 942.79 | INV747 | 2026-07-20 | PYMT737 | 2026-08-07 | IF5710 | 2026-08-19 | 15 | 18 | 12 |
| SO4278 | 2026-07-07 | Hugo Limited | 1,604.38 | INV748 | 2026-07-22 | PYMT738 | 2026-08-09 | IF5711 | 2026-08-21 | 15 | 18 | 12 |
| SO4279 | 2026-07-10 | Jones Manufacturing | 911.60 | INV749 | 2026-07-25 | PYMT739 | 2026-08-12 | IF5712 | 2026-08-24 | 15 | 18 | 12 |
| SO4280 | 2026-07-12 | Blockster Inc. | 1,600.60 | INV750 | 2026-07-27 | PYMT740 | 2026-08-14 | IF5713 | 2026-08-26 | 15 | 18 | 12 |
| SO4281 | 2026-08-02 | Marshall Industries | 1,399.05 | INV751 | 2026-08-17 | PYMT741 | 2026-09-05 | IF5714 | 2026-09-17 | 15 | 19 | 12 |
| SO4282 | 2026-08-03 | Godric Motors | 1,627.03 | INV752 | 2026-08-18 | PYMT742 | 2026-09-06 | IF5715 | 2026-09-18 | 15 | 19 | 12 |
| SO4283 | 2026-08-04 | Design Excellence Ltd. | 462.19 | INV753 | 2026-08-19 | PYMT743 | 2026-09-07 | IF5716 | 2026-09-19 | 15 | 19 | 12 |
| SO4284 | 2026-08-05 | Hugo Limited | 1,604.38 | INV754 | 2026-08-20 | PYMT744 | 2026-09-08 | IF5718 | 2026-09-20 | 15 | 19 | 12 |
| SO4285 | 2026-08-08 | Meetz Industries | 1,653.46 | INV756 | 2026-08-23 | PYMT745 | 2026-09-11 | IF5719 | 2026-09-23 | 15 | 19 | 12 |
| SO4286 | 2026-08-11 | JBL Inc. | 930.98 | INV757 | 2026-08-26 | PYMT747 | 2026-09-14 | IF5720 | 2026-09-26 | 15 | 19 | 12 |
| SO4287 | 2026-08-13 | Design Excellence Ltd. | 462.19 | INV758 | 2026-08-31 | PYMT748 | 2026-09-16 | IF5721 | 2026-09-30 | 18 | 16 | 14 |
| 20 chains · all orders status Billed · no memo on any | 25,790.50 | Order→Invoice median 15d · Invoice→Payment median 18d · Payment→Ship median 12d | ||||||||||
| Payment | Internal id | Date | Status | Customer | Amount | Note |
|---|---|---|---|---|---|---|
| PYMT03 | 26897 | 2026-08-31 | Deposited | Robert Huffman | 960.11 | |
| PYMT01 | 24267 | 2026-08-31 | Deposited | Susan Adams | 934.78 | Identical customer, date and amount to applied PYMT675 (id 39722) |
| PYMT07 | 29539 | 2026-09-06 | Deposited | Finch Computing | 32.65 | |
| PYMT10 | 29545 | 2026-09-07 | Deposited | Dillan Garcia | 32.65 | |
| PYMT13 | 29552 | 2026-09-10 | Deposited | Donnie Rizzo | 49.99 | |
| PYMT14 | 29554 | 2026-09-11 | Deposited | Bryan Scott | 27.75 | |
| PYMT12 | 29549 | 2026-09-12 | Deposited | Bryan Scott | 27.75 | Same customer and amount as PYMT14 |
| PYMT16 | 29560 | 2026-09-14 | Deposited | Carter Drury | 32.54 | |
| PYMT11 | 29547 | 2026-09-14 | Deposited | Dillan Garcia | 32.65 | Same customer and amount as PYMT10, PYMT15 |
| PYMT02 | 26027 | 2026-09-17 | Deposited | Donnie Rizzo | 1,049.79 | |
| PYMT15 | 29557 | 2026-09-17 | Deposited | Dillan Garcia | 32.65 | |
| PYMT05 | 29535 | 2026-09-18 | Deposited | Finch Computing | 32.65 | |
| 12 payments · all Deposited · none applied to any invoice | 3,245.96 | |||||
| Fulfillment | Internal id | Date | Status | Created from | Source type |
|---|---|---|---|---|---|
| IF4987 | 31713 | 2026-08-01 | Shipped | TO13 | Transfer order |
| IF5702 | 40428 | 2026-08-03 | Shipped | VRMA21 | Vendor return authorisation |
| IF5701 | 40424 | 2026-08-16 | Shipped | VRMA20 | Vendor return authorisation |
| IF3112 | 20912 | 2026-08-26 | Shipped | TO03 | Transfer order |
| IF3113 | 20914 | 2026-08-26 | Shipped | TO04 | Transfer order |
| IF4951 | 28878 | 2026-09-01 | Shipped | TO02 | Transfer order |
| IF4952 | 28879 | 2026-09-01 | Shipped | TO01 | Transfer order |
| IF3115 | 24132 | 2026-09-08 | Picked | TO05 | Transfer order |
| IF4986 | 31710 | 2026-09-08 | Shipped | TO12 | Transfer order |
| IF3118 | 24271 | 2026-09-12 | Shipped | TO06 | Transfer 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.
| Document | Date | Status | Customer | Amount | Links |
|---|---|---|---|---|---|
| CM01 | 2026-09-14 | Open | Susan Adams | -108.48 | None |
| CM03 | 2026-09-15 | Open | Bobby Davis | -152.43 | None |
| CM02 | — | Fully Applied | — | — | Linked from RtnAuth / CustRfnd (out of scope) |
| CD01 → DEPA01 → INV03 | 2026-08-25 / 08-31 | Fully Applied / Paid In Full | Susan Adams | 150.00 + 934.78 | Deposit $150 applied plus PYMT675 $934.78 = INV03 $1,084.78; SO3129 dated 2026-08-31 remains Pending Fulfillment |
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.
| Limitation | Evidence | What it prevents |
|---|---|---|
| Day-level dates only | All 3,055 transaction dates carry time 00:00:00 | Same-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 dates | shipdate and actualshipdate are null on all 752 item fulfillments | Shipment timing must use fulfillment trandate; carrier hand-off and transit time are unmeasurable. |
| No approval events | Status-change system notes (TRANDOC.KSTATUS) exist on 3 sales orders, 32 invoices, 11 payments out of 2,300 | Approval 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 dating | 169 documents dated after 2026-09-04 (SO 57, PYMT 47, IF 37, INV 26, CM 2); createddate clusters in September–October 2026 for every type | createddate 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 data | 32 invoices and 11 payments carry memo TEST — …; SO4296 memo names a demo scenario | Aging, DSO and collection-effectiveness measures are dominated by fixtures until they are excluded (t.memo NOT LIKE 'TEST%') or removed. |
| Payments not linked to orders | createdfrom is empty on all 748 payments; links exist only via nexttransactionlinelink linktype Payment | Cash application cannot be traced for the 12 unlinked payments; payment perspective depends entirely on the link table. |
| Credit memos unlinked | 3 credit memos, 0 links to invoices or orders | Returns and adjustments cannot be attributed to a case. |
| Status-label drift | Live 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 journals | Monthly "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. |
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.ItemShip.trandate > CustPymt.trandate via nexttransactionlinelink would catch recurrences.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.
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
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
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')
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')
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')
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'
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
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
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
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
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
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
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
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
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
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')
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.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.
// 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)
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).
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
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.
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.
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 (…).
| Step | Document | Internal id | Date | Status | Amount | Link that produced the edge |
|---|---|---|---|---|---|---|
| Order Created | SO3542 (Gail Mack) | 34736 | 2024-10-01 | Billed | 33.41 | — |
| Shipped | IF4990 | 35798 | 2024-10-01 | Shipped | — | ntll ShipRcpt 34736→35798; createdfrom = 34736 |
| Invoiced | INV28 | 35799 | 2024-10-01 | Paid In Full | 33.41 | ntll OrdBill + OrdRvCom 34736→35799; createdfrom = 34736 |
| Paid | PYMT17 | 35800 | 2024-10-01 | Deposited | — | 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 Created | SO3544 (Cherry Hines) | 34738 | 2024-10-07 | Billed | 558.46 | — |
| Shipped | IF4991 | 35803 | 2024-10-07 | Shipped | — | ntll ShipRcpt; createdfrom = 34738 |
| Invoiced | INV30 | 35804 | 2024-10-07 | Paid In Full | 558.46 | ntll OrdBill + OrdRvCom; createdfrom = 34738 |
| Paid | PYMT19 | 35805 | 2024-10-07 | Deposited | — | 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).
| Type | Code | Label | Docs |
|---|---|---|---|
| SalesOrd | G | Billed | 731 |
| SalesOrd | B | Pending Fulfillment | 40 |
| SalesOrd | A | Pending Approval | 13 |
| SalesOrd | F | Pending Billing | 3 |
| SalesOrd | E | Pending Billing / Partially Fulfilled | 1 |
| ItemShip | C | Shipped | 744 |
| ItemShip | B | Packed | 4 |
| ItemShip | A | Picked | 4 |
| CustInvc | B | Paid In Full | 725 |
| CustInvc | A | Open | 39 |
| CustPymt | C | Deposited | 746 |
| CustPymt | B | Not Deposited | 2 |
| CustCred | A | Open | 2 |
| CustCred | B | Fully Applied | 1 |
| Linktype | From → To | Rows | Meaning |
|---|---|---|---|
| ShipRcpt | SalesOrd → ItemShip | 2,142 | Fulfillment of order lines |
| PickPack | SalesOrd → ItemShip | 11 | Pick/pack stage fulfillment |
| OrdBill | SalesOrd → CustInvc | 2,136 | Billing of order lines |
| OrdRvCom | SalesOrd → CustInvc | 2,136 | Revenue-commitment twin of OrdBill (deduplicated) |
| Payment | CustInvc → CustPymt / DepAppl | 737 | Cash or deposit applied to invoice |
| DepRfnd | CashSale → Deposit | 1,026 | Cash-sale sweep (out of scope) |
| SaleRet | SalesOrd → RtnAuth | 8 | Return authorisation (out of scope) |
| DropShip / SpecOrd | SalesOrd → PurchOrd / WorkOrd | 4 | Procurement side (out of scope) |
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.
| Item | Value |
|---|---|
| Account / environment | TD3016323 · production · OneWorld · USD |
| Analysis date | 2026-09-04 (SYSDATE for age calculations) |
| Tooling | Sonar 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 inputs | Q4 headers 3,055 rows · Q5 links 2,942 rows · Q16 pairs 2,210 rows |
| Worker time | Pass 1 2.9 s · Pass 2 2.3 s · Pass 3 1.9 s · Pass 4 13 ms |
| Percentile method | Exact (A11) |
| Privacy | Customer 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. |
| Rev | Date | Change |
|---|---|---|
| 1 | 2026-09-04 | Initial analysis: event log, structure, OC-DFG, variants, bottlenecks, conformance, data quality, three findings. |
| 2 | 2026-09-04 | Added 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. |