Every Path an Order Actually Takes
reconstructed from 24,579 lineage links

SELECT linktype, COUNT(*) AS cnt
FROM nexttransactionlinelink
GROUP BY linktype
ORDER BY COUNT(*) DESC

SELECT
    previoustype || '>' || nexttype || '|' || linktype AS edge,
    COUNT(*)            AS doc_pairs,
    MEDIAN(lag_days)    AS median_days,
    ROUND(AVG(lag_days), 1) AS avg_days,
    MAX(lag_days)       AS max_days
FROM (
    -- collapse line-level links to distinct document pairs
    SELECT previoustype, nexttype, linktype, previousdoc, nextdoc,
           MIN(nextdate - previousdate) AS lag_days
    FROM nexttransactionlinelink
    GROUP BY previoustype, nexttype, linktype, previousdoc, nextdoc
)
GROUP BY previoustype, nexttype, linktype
ORDER BY COUNT(*) DESC

WITH edges AS (
    SELECT previousdoc, nexttype, nextdoc,
           MIN(previousdate) AS pd, MIN(nextdate) AS nd
    FROM nexttransactionlinelink
    GROUP BY previousdoc, nexttype, nextdoc
),
per_so AS (
    SELECT so.id, so.trandate AS so_date, so.status AS so_status,
        ABS(so.foreigntotal) AS order_total,
        COUNT(DISTINCT CASE WHEN e1.nexttype = 'ItemShip' THEN e1.nextdoc END) AS if_cnt,
        COUNT(DISTINCT CASE WHEN e1.nexttype = 'CustInvc' THEN e1.nextdoc END) AS inv_cnt,
        COUNT(DISTINCT CASE WHEN e1.nexttype = 'RtnAuth'  THEN e1.nextdoc END) AS ra_cnt,
        COUNT(DISTINCT CASE WHEN e1.nexttype = 'WorkOrd'  THEN e1.nextdoc END) AS wo_cnt,
        COUNT(DISTINCT CASE WHEN e1.nexttype = 'PurchOrd' THEN e1.nextdoc END) AS po_cnt,
        COUNT(DISTINCT CASE WHEN e1.nexttype = 'CustInvc'
              AND e2.nexttype IN ('CustPymt','DepAppl') THEN e2.nextdoc END) AS pmt_cnt,
        COUNT(DISTINCT CASE WHEN e1.nexttype = 'RtnAuth'
              AND e2.nexttype = 'CustCred' THEN e2.nextdoc END) AS cm_cnt,
        MIN(CASE WHEN e1.nexttype = 'CustInvc' AND e2.nexttype IN ('CustPymt','DepAppl')
              THEN e2.nd END) - so.trandate AS lag_so_cash
    FROM transaction so
    LEFT JOIN edges e1 ON e1.previousdoc = so.id
    LEFT JOIN edges e2 ON e2.previousdoc = e1.nextdoc
    WHERE so.type = 'SalesOrd'
    GROUP BY so.id, so.trandate, so.status, ABS(so.foreigntotal)
)
SELECT
    'SO' ||
    CASE WHEN wo_cnt  > 0 THEN '+WO'  ELSE '' END ||
    CASE WHEN po_cnt  > 0 THEN '+PO'  ELSE '' END ||
    CASE WHEN if_cnt  > 0 THEN '>IF'  ELSE '' END ||
    CASE WHEN inv_cnt > 0 THEN '>INV' ELSE '' END ||
    CASE WHEN pmt_cnt > 0 THEN '>PMT' ELSE '' END ||
    CASE WHEN ra_cnt  > 0 THEN '+RA'  ELSE '' END ||
    CASE WHEN cm_cnt  > 0 THEN '+CM'  ELSE '' END AS variant,
    COUNT(*) AS orders,
    MEDIAN(lag_so_cash) AS med_so_cash,
    ROUND(MEDIAN(order_total), 0) AS med_value,
    ROUND(SUM(order_total), 0)    AS total_value
FROM per_so
GROUP BY /* same signature expression */ ...
ORDER BY COUNT(*) DESC

WITH edges AS ( -- as in Q3 ),
per_so AS ( -- if_cnt / inv_cnt / pmt_cnt per SO, as in Q3 )
SELECT
    CASE WHEN if_cnt = 0 AND inv_cnt = 0 THEN 'A_not_started'
         WHEN if_cnt > 0 AND inv_cnt = 0 THEN 'B_shipped_not_invoiced'
         WHEN inv_cnt > 0 AND pmt_cnt = 0 THEN 'C_invoiced_not_paid' END AS bucket,
    status, COUNT(*) AS orders,
    ROUND(SUM(order_total), 0) AS value,
    MEDIAN(TRUNC(SYSDATE) - trandate) AS med_age_days
FROM per_so
WHERE (if_cnt = 0 AND inv_cnt = 0) OR inv_cnt = 0 OR pmt_cnt = 0
GROUP BY bucket_expr, status
ORDER BY 1, COUNT(*) DESC

WITH pairs AS (
    SELECT previoustype, nexttype, previousdoc, nextdoc,
           MIN(nextdate - previousdate) AS lag_days
    FROM nexttransactionlinelink
    WHERE (previoustype, nexttype) IN ( -- the 7 core stages
        ('SalesOrd','ItemShip'), ('SalesOrd','CustInvc'),
        ('CustInvc','CustPymt'), ('CashSale','Deposit'),
        ('PurchOrd','ItemRcpt'), ('PurchOrd','VendBill'),
        ('VendBill','VendPymt') )
    GROUP BY previoustype, nexttype, previousdoc, nextdoc
)
SELECT previoustype || '>' || nexttype AS stage,
    COUNT(*) AS pairs, MEDIAN(lag_days) AS p50,
    SUM(CASE WHEN lag_days = 0 THEN 1 ELSE 0 END) AS same_day,
    SUM(CASE WHEN lag_days BETWEEN 1 AND 7  THEN 1 ELSE 0 END) AS d1_7,
    SUM(CASE WHEN lag_days BETWEEN 8 AND 30 THEN 1 ELSE 0 END) AS d8_30,
    SUM(CASE WHEN lag_days > 30 THEN 1 ELSE 0 END) AS over_30,
    MAX(lag_days) AS max_d
FROM pairs GROUP BY previoustype, nexttype ORDER BY COUNT(*) DESC

WITH pairs AS ( -- as Q5, filtered to the 3 slow edges ),
ranked AS (
    SELECT p.*, ROW_NUMBER() OVER (PARTITION BY p.previoustype, p.nexttype
                                   ORDER BY p.lag_days DESC) AS rn
    FROM pairs p WHERE p.lag_days > 30 )
SELECT r.previoustype || '>' || r.nexttype AS edge,
    tp.tranid, TO_CHAR(tp.trandate,'YYYY-MM-DD') AS prev_date,
    tn.tranid AS next_tranid, r.lag_days,
    ROUND(ABS(tp.foreigntotal),0) AS prev_total
FROM ranked r
JOIN transaction tp ON tp.id = r.previousdoc
JOIN transaction tn ON tn.id = r.nextdoc
WHERE r.rn <= 6 ORDER BY r.previoustype, r.lag_days DESC

SELECT l.previoustype, l.linktype, l.nexttype,
    tp.tranid AS prev_tranid, tn.tranid AS next_tranid,
    TO_CHAR(tp.trandate,'YYYY-MM-DD') AS prev_date,
    TO_CHAR(tn.trandate,'YYYY-MM-DD') AS next_date,
    ROUND(ABS(tn.foreigntotal),0) AS next_total
FROM nexttransactionlinelink l
JOIN transaction tp ON tp.id = l.previousdoc
JOIN transaction tn ON tn.id = l.nextdoc
WHERE l.linktype IN ('SaleRet','CostRtrn','PurchRet')
   OR l.nexttype IN ('RtnAuth','CustCred','CashRfnd','VendCred','VendAuth')
   OR l.previoustype IN ('RtnAuth','VendAuth','CustRfnd')
GROUP BY /* all selected cols */ ...
ORDER BY l.previoustype, tp.tranid

SELECT
    CASE WHEN l.previousdoc IS NOT NULL THEN 'from_SO' ELSE 'standalone' END AS origin,
    CASE WHEN t.foreignamountpaid > 0 THEN 'partially_paid' ELSE 'no_payment' END AS pay_state,
    COUNT(DISTINCT t.id) AS invoices,
    ROUND(SUM(t.foreignamountunpaid),0) AS open_amt,
    MEDIAN(TRUNC(SYSDATE) - t.trandate) AS med_age_days
FROM transaction t
LEFT JOIN (SELECT DISTINCT nextdoc, previousdoc FROM nexttransactionlinelink
           WHERE previoustype = 'SalesOrd' AND nexttype = 'CustInvc') l
       ON l.nextdoc = t.id
WHERE t.type = 'CustInvc' AND t.status = 'A'
GROUP BY origin_expr, pay_state_expr
ORDER BY open_amt DESC
-- Result: standalone/no_payment 21 · $769,920 · 157d
--         from_SO/no_payment     7 · $138,229 ·  10d
--         standalone/partial    11 ·  $20,098 ·  57d