Inventory Fulfillment & Transfers: Process Mining Analysis

1. 0Executive summary
2. 1Method and assumptions
3. 2Structure — the fulfillment graph
4. 3Fulfillment lag by location
5. 4Built-to-order assemblies
6. 5Unshipped demand
7. 6Transfer orders
8. 7Work orders and builds
9. 8Exception registers
10. 9Data quality — the component-line trap
11. 10Findings and actions
12. ASuiteQL used
13. BHand-check

0Executive summary

Fulfillment in this account is a same-day process with two well-defined exceptions. Across 2,248 sellable sales-order lines, 2,142 have shipped; 2,096 of the 2,149 measurable line-to-fulfillment pairs (97.5%) shipped on the order date, and every location — Los Angeles DC, Miami, San Francisco, New York — has a median lag of zero. The first exception is built-to-order assemblies: 18 lines of AS_SAF001 (Solar Powered Fan) and AS_MBK001 waited 45–48 days, spanning the monthly assembly build on the 16th–20th; both items are now backordered (37 and 8 units) with 55 units of AS_SAF001 released to work orders and 7 units of AS_MBK001 still only planned. The second is a single 365-day outlier, SO3910 → IF5503 (one eye shadow, San Francisco), which is almost certainly a wrong-year fulfillment date.

1. Half the unshipped backlog is not the warehouse's problem yet. Of 106 unshipped sellable lines ($121,041), 37 lines ($52,123) sit on 13 sales orders in Pending Approval, and 63 lines are on orders dated after today. The genuinely aged backlog is 18 lines on 5 orders, 31–90 days old ($15,511): 8 assembly lines at LA DC and one 10-line Miami order (SO for 148 units, $3,711) that has waited since June.
2. Eight fulfillments are stuck between pick and ship. 4 Picked (IF5724/5725/5726 since 5–15 July, plus IF3115 for transfer TO05) and 4 Packed (IF4947/4950/4953/4955, late September). Their 11 source lines show as unshipped on the order while inventory is already pulled — 60–90 days of "picked, not shipped" at LA DC.
3. Transfers are new and already half-stuck. 14 transfer orders, all since 1 Aug 2026: 3 received (same-day ship and receive), 4 in transit (22 units, TO03 unreceived for 9 days), 7 not yet shipped, including TO15 (LA→Chicago, 34 days). There is no transfer-order history before August.

1Method and assumptions

2Structure — the fulfillment graph

Figure 1 — Sales-order lines to shipment, with the two side-flows that feed stock: assembly builds (work order → build) and inter-location transfers. Labels: count · median days.

3Fulfillment lag by location

Every location has a median lag of zero. All 18 lags over 7 days at LA DC are the built-to-order assemblies of §4; the single >7-day lag at San Francisco is the 365-day SO3910 outlier. New York has never shipped anything on any day other than the order date. The 30 unassigned lines — the most valuable unshipped cohort at $43,099 — are on orders that have not been approved or located yet.

4Built-to-order assemblies

Every one of the 18 slow fulfillments is an assembly ordered in the first half of a month and shipped 45–48 days later; each window contains at least one build of that item, i.e. the order waited for the next build cycle and then some. The pattern is monthly builds (16th–20th) of 3–10 units against demand of 1–3 units per order — supply arrives in lumps, orders queue. AS_SAF001 now has 37 units backordered on 5 orders (SO4312 alone wants 25) against 55 units on released work orders WO01/21/22/25; AS_MBK001 has 8 backordered against only planned work orders WO16/19/20 (7 units), none released.

5Unshipped demand

Items with the most unshipped units (open orders)

6Transfer orders

All three completed transfers shipped and received on the same day; the four in transit have no receipt after 0–9 days. Transfer orders appear only from 1 Aug 2026 — the process is new to this account. Two InvTrnfr (direct inventory transfers), 3 adjustments, 5 counts and 2 worksheets exist in the same window; none is linked to a sales order.

7Work orders and builds

8Exception registers

8-A · Backorder register (14 lines, 91 units)

Grey Cotton Hoodie has 140 units on hand; its 23 backordered units are on lines with no location, so nothing can commit. The two assemblies account for 45 of 91 backordered units.

8-B · Fulfillments picked or packed but not shipped (8)

8-C · Date anomalies (2)

SO3910 (2025-02-09, 1 × LOT_Posh Collection Eye Shadow, San Francisco) fulfilled by IF5503 dated 2026-02-09 — exactly one year later, the only lag above 48 days in the account; a wrong-year entry. IF4955 is dated 2026-09-20 against SO2903 dated 2026-10-01 — a fulfillment 11 days before its order.

9Data quality — the component-line trap

10Findings and recommended actions

1. Release AS_MBK001 supply and set the assemblies' expected lead time honestly. Both built-to-order assemblies run a 45-day order-to-ship cycle because builds happen once a month; AS_MBK001 has 8 units backordered and only planned work orders. Release WO16/19/20 now, and either move to a fortnightly build or publish a 6-week lead time on these two items so the sales order carries the right promise date.
2. Ship the three July pick tickets and fix the two wrong dates. IF5724, IF5725, IF5726 have been Picked for 51–61 days at LA DC — either ship them or put the stock back. Correct IF5503 (2026 → 2025) and IF4955 (dated before its order) so cycle-time reporting stops carrying a 365-day and a −11-day pair.
3. Assign a location before approval. 30 unshipped lines worth $43,099 — including 23 backordered units of a hoodie with 140 in stock — have no location and therefore cannot commit inventory. Make location mandatory on the sales-order line for inventory items, and approve or cancel the 13 Pending-Approval orders ($52,123) that make up a third of the unshipped backlog.

AAppendix A — SuiteQL used

Q1 — Sellable vs component rows (the exclusion test)

SELECT CASE WHEN sl.assemblycomponent='T' THEN 'assembly component' WHEN sl.kitcomponent='T' THEN 'kit component' ELSE 'sellable' END AS kind,
 COUNT(*) AS lines, SUM(CASE WHEN COALESCE(sl.quantityshiprecv,0) >= ABS(sl.quantity) THEN 1 ELSE 0 END) AS fully_shipped,
 SUM(CASE WHEN COALESCE(sl.quantityshiprecv,0) = 0 THEN 1 ELSE 0 END) AS unshipped,
 SUM(CASE WHEN COALESCE(sl.quantityshiprecv,0) = 0 AND so.status NOT IN ('C','H') THEN 1 ELSE 0 END) AS unshipped_open_so,
 ROUND(SUM(CASE WHEN COALESCE(sl.quantityshiprecv,0) = 0 AND so.status NOT IN ('C','H') THEN ABS(COALESCE(sl.netamount,0)) ELSE 0 END),2) AS value_unshipped_open
FROM transactionline sl JOIN transaction so ON so.id=sl.transaction AND so.type='SalesOrd'
WHERE sl.mainline='F' AND sl.taxline='F' AND sl.item IS NOT NULL AND sl.itemtype IN ('InvtPart','Assembly','Kit')
GROUP BY CASE WHEN sl.assemblycomponent='T' THEN 'assembly component' WHEN sl.kitcomponent='T' THEN 'kit component' ELSE 'sellable' END

Q2 — Per-location lag and backlog

SELECT COALESCE(BUILTIN.DF(sl.location),'(unassigned)') AS loc, COUNT(*) AS lines,
 SUM(CASE WHEN COALESCE(sl.quantityshiprecv,0)>0 THEN 1 ELSE 0 END) AS shipped, SUM(CASE WHEN COALESCE(sl.quantityshiprecv,0)=0 THEN 1 ELSE 0 END) AS unshipped,
 ROUND(SUM(CASE WHEN COALESCE(sl.quantityshiprecv,0)=0 THEN ABS(COALESCE(sl.netamount,0)) ELSE 0 END),2) AS unshipped_value,
 SUM(CASE WHEN x.lag=0 THEN 1 ELSE 0 END) AS same_day, SUM(CASE WHEN x.lag BETWEEN 1 AND 7 THEN 1 ELSE 0 END) AS d1_7, SUM(CASE WHEN x.lag > 7 THEN 1 ELSE 0 END) AS d_over7, MAX(x.lag) AS max_lag
FROM transactionline sl JOIN transaction so ON so.id=sl.transaction AND so.type='SalesOrd'
LEFT JOIN (SELECT l.previousdoc, l.previousline, MIN(TRUNC(f.trandate)-TRUNC(s.trandate)) AS lag FROM nexttransactionlinelink l
           JOIN transaction f ON f.id=l.nextdoc AND f.type='ItemShip' JOIN transaction s ON s.id=l.previousdoc GROUP BY l.previousdoc, l.previousline) x
  ON x.previousdoc=so.id AND x.previousline=sl.id
WHERE sl.mainline='F' AND sl.taxline='F' AND sl.item IS NOT NULL AND sl.itemtype IN ('InvtPart','Assembly','Kit') AND sl.assemblycomponent='F' AND sl.kitcomponent='F'
GROUP BY COALESCE(BUILTIN.DF(sl.location),'(unassigned)') ORDER BY lines DESC

Q3 — Slow fulfillments with builds in the window

SELECT BUILTIN.DF(sl.item) AS item, so.tranid AS so, TO_CHAR(so.trandate,'YYYY-MM-DD') AS so_date, f.tranid AS if_doc, TO_CHAR(f.trandate,'YYYY-MM-DD') AS if_date,
 TRUNC(f.trandate)-TRUNC(so.trandate) AS lag, ROUND(ABS(sl.quantity),0) AS qty,
 (SELECT LISTAGG(DISTINCT b.tranid || ' ' || TO_CHAR(b.trandate,'MM-DD'), '; ') FROM transaction b JOIN transactionline bl ON bl.transaction=b.id AND bl.mainline='T'
   WHERE b.type='Build' AND bl.item=sl.item AND b.trandate BETWEEN so.trandate AND f.trandate) AS builds_in_window
FROM nexttransactionlinelink l JOIN transaction so ON so.id=l.previousdoc AND so.type='SalesOrd' JOIN transaction f ON f.id=l.nextdoc AND f.type='ItemShip'
JOIN transactionline sl ON sl.transaction=so.id AND sl.id=l.previousline
WHERE TRUNC(f.trandate)-TRUNC(so.trandate) > 30 ORDER BY lag DESC, so.tranid

Q4 — Unshipped by status, location, age

SELECT COALESCE(BUILTIN.DF(sl.location),'(unassigned)') AS loc, BUILTIN.DF(so.status) AS so_status,
 CASE WHEN TRUNC(SYSDATE)-TRUNC(so.trandate) < 0 THEN 'a future' WHEN TRUNC(SYSDATE)-TRUNC(so.trandate) <= 7 THEN 'b 0-7' WHEN TRUNC(SYSDATE)-TRUNC(so.trandate) <= 30 THEN 'c 8-30'
      WHEN TRUNC(SYSDATE)-TRUNC(so.trandate) <= 90 THEN 'd 31-90' ELSE 'e >90' END AS age,
 COUNT(*) AS lines, COUNT(DISTINCT so.id) AS orders, ROUND(SUM(ABS(sl.quantity)),0) AS qty, ROUND(SUM(COALESCE(sl.quantitybackordered,0)),0) AS backordered, ROUND(SUM(ABS(COALESCE(sl.netamount,0))),2) AS value
FROM transactionline sl JOIN transaction so ON so.id=sl.transaction AND so.type='SalesOrd'
WHERE sl.mainline='F' AND sl.taxline='F' AND sl.item IS NOT NULL AND sl.itemtype IN ('InvtPart','Assembly','Kit') AND sl.assemblycomponent='F' AND sl.kitcomponent='F'
 AND COALESCE(sl.quantityshiprecv,0)=0 AND so.status NOT IN ('C','H')
GROUP BY [same three expressions] ORDER BY 1,2,3

Q5 — Top unshipped items with on-hand

SELECT BUILTIN.DF(sl.item) AS item, sl.itemtype, COUNT(*) AS lines, ROUND(SUM(ABS(sl.quantity)),0) AS qty_unshipped, ROUND(SUM(COALESCE(sl.quantitybackordered,0)),0) AS backordered,
 ROUND(SUM(ABS(COALESCE(sl.netamount,0))),2) AS value, MIN(TO_CHAR(so.trandate,'YYYY-MM-DD')) AS oldest_so,
 (SELECT ROUND(SUM(il.quantityonhand),0) FROM inventoryitemlocations il WHERE il.item=sl.item) AS onhand_all_locs
FROM transactionline sl JOIN transaction so ON so.id=sl.transaction AND so.type='SalesOrd'
WHERE [sellable, unshipped, open — as Q4] GROUP BY sl.item, BUILTIN.DF(sl.item), sl.itemtype ORDER BY qty_unshipped DESC FETCH FIRST 12 ROWS ONLY

Q6 — Transfer orders

SELECT t.id, t.tranid, TO_CHAR(t.trandate,'YYYY-MM-DD') AS trandate, BUILTIN.DF(t.status) AS status,
 (SELECT BUILTIN.DF(tl.location) FROM transactionline tl WHERE tl.transaction=t.id AND tl.mainline='T') AS from_loc, BUILTIN.DF(t.transferlocation) AS to_loc,
 (SELECT COUNT(*) FROM transactionline tl WHERE tl.transaction=t.id AND tl.mainline='F' AND tl.taxline='F' AND tl.item IS NOT NULL) AS lines,
 (SELECT ROUND(SUM(ABS(tl.quantity)),0) FROM transactionline tl WHERE tl.transaction=t.id AND tl.mainline='F' AND tl.taxline='F' AND tl.item IS NOT NULL) AS qty,
 (SELECT LISTAGG(DISTINCT n.tranid || ' ' || TO_CHAR(n.trandate,'MM-DD'), '; ') FROM nexttransactionlinelink l JOIN transaction n ON n.id=l.nextdoc AND n.type='ItemShip' WHERE l.previousdoc=t.id) AS shipments,
 (SELECT LISTAGG(DISTINCT n.tranid || ' ' || TO_CHAR(n.trandate,'MM-DD'), '; ') FROM nexttransactionlinelink l JOIN transaction n ON n.id=l.nextdoc AND n.type='ItemRcpt' WHERE l.previousdoc=t.id) AS receipts
FROM transaction t WHERE t.type='TrnfrOrd' ORDER BY t.trandate

Q7 — Work orders with builds and origin

SELECT t.id, t.tranid, TO_CHAR(t.trandate,'YYYY-MM-DD') AS trandate, BUILTIN.DF(t.status) AS status,
 (SELECT BUILTIN.DF(tl.location) FROM transactionline tl WHERE tl.transaction=t.id AND tl.mainline='T') AS loc,
 (SELECT BUILTIN.DF(tl.item) FROM transactionline tl WHERE tl.transaction=t.id AND tl.mainline='T') AS assembly,
 (SELECT ROUND(ABS(tl.quantity),0) FROM transactionline tl WHERE tl.transaction=t.id AND tl.mainline='T') AS qty,
 (SELECT LISTAGG(DISTINCT n.tranid || ' ' || TO_CHAR(n.trandate,'MM-DD') || ' ' || l.linktype, '; ') FROM nexttransactionlinelink l JOIN transaction n ON n.id=l.nextdoc WHERE l.previousdoc=t.id) AS next_docs,
 (SELECT LISTAGG(DISTINCT p.tranid || ' ' || p.type || ' ' || l.linktype, '; ') FROM nexttransactionlinelink l JOIN transaction p ON p.id=l.previousdoc WHERE l.nextdoc=t.id) AS prev_docs
FROM transaction t WHERE t.type='WorkOrd' ORDER BY t.trandate

Q8 — Backorder register · Q9 — Unshipped fulfillments

SELECT so.tranid, TO_CHAR(so.trandate,'YYYY-MM-DD') AS so_date, BUILTIN.DF(sl.item) AS item, COALESCE(BUILTIN.DF(sl.location),'(unassigned)') AS loc,
 ROUND(ABS(sl.quantity),0) AS qty, ROUND(sl.quantitybackordered,0) AS backordered, ROUND(COALESCE(sl.quantitycommitted,0),0) AS committed
FROM transactionline sl JOIN transaction so ON so.id=sl.transaction AND so.type='SalesOrd'
WHERE sl.mainline='F' AND sl.taxline='F' AND sl.assemblycomponent='F' AND sl.kitcomponent='F' AND sl.quantitybackordered > 0 ORDER BY sl.quantitybackordered DESC;

SELECT f.tranid AS if_doc, BUILTIN.DF(f.status) AS if_status, TO_CHAR(f.trandate,'YYYY-MM-DD') AS if_date, p.type AS src_type, p.tranid AS src, TO_CHAR(p.trandate,'YYYY-MM-DD') AS src_date,
 TRUNC(f.trandate)-TRUNC(p.trandate) AS lag, BUILTIN.DF(p.status) AS src_status
FROM transaction f JOIN (SELECT DISTINCT previousdoc, nextdoc FROM nexttransactionlinelink) l ON l.nextdoc=f.id JOIN transaction p ON p.id=l.previousdoc
WHERE f.type='ItemShip' AND f.status <> 'C' ORDER BY f.trandate

BAppendix B — Hand-check

Lines: 2,248 sellable + 116 component = 2,364 ✓ · 2,142 shipped + 106 unshipped = 2,248 ✓ · Location rows: 1,021 + 691 + 289 + 217 + 30 = 2,248; shipped 970 + 681 + 277 + 214 + 0 = 2,142; unshipped 51 + 10 + 12 + 3 + 30 = 106 ✓ · Unshipped value 62,823.96 + 3,711.30 + 9,288.18 + 2,119.00 + 43,098.53 = 121,040.97 ✓ · Lag pairs 2,096 + 34 + 19 = 2,149 (2,142 shipped + 11 picked/packed − 4 without line-level link) · Status/age table 37 + 32 + 4 + 12 + 18 + 3 = 106; 52,122.58 + 42,712.04 + 5,713.80 + 4,565.89 + 15,510.68 + 415.98 = 121,040.97 ✓ · Backorder 25 + 20 + 10 + 10 + 15 + 5 + 2 + 2 + 2 = 91 ✓ · TOs 3 + 4 + 7 = 14; units 16 + 22 + 79 = 117 ✓ · WOs 13 + 6 + 5 = 24; units 79 + 71 + 9 = 159 ✓ · IFs 744 + 4 + 4 = 752 ✓.