Inventory, graded in both directions.
$425.9K trapped in excess stock. $12.6K of margin walking out the door.

SELECT
    i.id                              AS item_id,
    i.itemid                          AS sku,
    ROUND(MAX(i.averagecost), 2)      AS avg_cost,
    SUM(iil.quantityonhand)           AS qty_on_hand,
    ROUND(SUM(iil.onhandvaluemli), 2) AS on_hand_value,
    SUM(NVL(iil.quantitycommitted,0)) AS qty_committed,
    SUM(NVL(iil.quantitybackordered,0)) AS qty_backordered,
    SUM(NVL(iil.quantityonorder,0))   AS qty_on_order
FROM item i
JOIN inventoryitemlocations iil ON iil.item = i.id
WHERE i.itemtype IN ('InvtPart', 'Assembly')
GROUP BY i.id, i.itemid
HAVING SUM(iil.quantityonhand) <> 0 OR SUM(iil.quantitybackordered) <> 0
ORDER BY SUM(iil.onhandvaluemli) DESC

SELECT
    tl.item                                  AS item_id,
    SUM(ABS(tl.quantity))                    AS qty_sold_12m,
    ROUND(SUM(ABS(tl.netamount)), 2)         AS rev_12m,
    ROUND(SUM(ABS(NVL(tl.costestimate,0))), 2) AS cogs_12m,
    COUNT(DISTINCT t.id)                     AS order_count_12m,
    TO_CHAR(MAX(t.trandate), 'YYYY-MM-DD')   AS last_sale
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type IN ('CustInvc', 'CashSale')
  AND tl.mainline = 'F' AND tl.taxline = 'F'
  AND tl.item IS NOT NULL
  AND tl.subsidiary <> 4                    -- exclude elimination sub
  AND t.trandate >= ADD_MONTHS(TRUNC(SYSDATE), -12)
GROUP BY tl.item

SELECT
    tl.item AS item_id,
    SUM(ABS(tl.quantity) - NVL(tl.quantityshiprecv,0)) AS open_unshipped_qty,
    ROUND(SUM(NVL(tl.estgrossprofit,0)
        * (ABS(tl.quantity) - NVL(tl.quantityshiprecv,0))
        / NULLIF(ABS(tl.quantity),0)), 2)              AS open_unshipped_gp,
    ROUND(AVG(TRUNC(SYSDATE) - TRUNC(t.trandate)), 0)  AS avg_line_age_days,
    MAX(TRUNC(SYSDATE) - TRUNC(t.trandate))            AS max_line_age_days,
    COUNT(*)                                           AS open_lines
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type = 'SalesOrd'
  AND tl.mainline = 'F' AND tl.taxline = 'F'
  AND tl.item IS NOT NULL AND tl.subsidiary <> 4
  AND tl.isclosed = 'F' AND t.status <> 'G'
  AND ABS(tl.quantity) - NVL(tl.quantityshiprecv,0) > 0
GROUP BY tl.item

SELECT
    tl.item AS item_id,
    COUNT(*) AS closed_lines,
    SUM(ABS(tl.quantity) - NVL(tl.quantityshiprecv,0)) AS qty_cancelled,
    ROUND(SUM(ABS(NVL(tl.foreignamount,0))
        * (ABS(tl.quantity) - NVL(tl.quantityshiprecv,0))
        / NULLIF(ABS(tl.quantity),0)), 2)              AS cancelled_amount
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type = 'SalesOrd'
  AND tl.mainline = 'F' AND tl.taxline = 'F'
  AND tl.item IS NOT NULL AND tl.subsidiary <> 4
  AND (tl.isclosed = 'T' OR t.status = 'G')
  AND ABS(tl.quantity) - NVL(tl.quantityshiprecv,0) > 0
  AND t.trandate >= ADD_MONTHS(TRUNC(SYSDATE), -12)
GROUP BY tl.item

SELECT
    tls.item AS item_id,
    COUNT(*) AS ship_lines,
    ROUND(AVG(TRUNC(ts.trandate) - TRUNC(so.trandate)), 1) AS avg_days_to_ship,
    COUNT(CASE WHEN TRUNC(ts.trandate) - TRUNC(so.trandate) > 21 THEN 1 END) AS lines_over_21d
FROM transaction ts
JOIN transactionline tls ON tls.transaction = ts.id
JOIN transaction so      ON tls.createdfrom = so.id
WHERE ts.type = 'ItemShip'
  AND tls.item IS NOT NULL AND tls.mainline = 'F'
  AND so.type = 'SalesOrd'
  AND ts.trandate >= ADD_MONTHS(TRUNC(SYSDATE), -12)
GROUP BY tls.item

months_cover   = qty_on_hand / (qty_sold_12m / 12)
excess_units   = MAX(qty_on_hand - qty_committed - qty_sold_12m/2, 0)   -- 6-mo target
excess_cost    = excess_units * (on_hand_value / qty_on_hand)
grade          = A: cover ≤ 6 | B: ≤ 12 | C: ≤ 24 | D: > 24 | E: zero 12-mo sales
cash_release   = excess_cost * recovery{B:.95, C:.70, D:.50, E:.30}
carry_drag     = excess_cost * 20%/yr

p_walk(age)    = 10% (≤14d) | 25% (≤30d) | 45% (≤60d) | 70% (>60d)
               × 0.5 if item has proven wait history (measured via Q5)
exp_gp_loss    = open_unshipped_gp * p_walk
realized_loss  = cancelled demand (Q4), reported separately — no double count