Generating an Inventory No-Movement Report

Last updated: October 7, 2026

This article describes how to generate a report for the parts that you have stored at Cofactr that have never been used in a kit. This is useful for finding stock that was purchased or shipped to Cofactr but has never been used.

Navigate to: https://platform.cofactr.com/reporting/sql

Note: The SQL Explorer feature is not included with all Cofactr plans. Please contact success@cofactr.com to discuss upgrades to a plan that includes SQL Explorer if you do not already have access.

Copy and paste the following query into the query field:

SELECT
    p.custom_id,
    p.mpn,
    p.mfg,
    p.description,
    p.cofactr_id,
    oh.on_hand_quantity,
    oh.lot_count,
    oh.lot_ids,
    (
        SELECT MIN(e."timestamp")
        FROM stock_lot_event e
        JOIN stock_lot slr ON slr.id = e.stock_lot_id
        LEFT JOIN address ar ON ar.id = slr.address_id
        WHERE slr.part_id = oh.part_id
          AND ar.id IS NULL
          AND e.event_type = 'receive'
    ) AS first_received_at
FROM (
    SELECT
        sl.part_id,
        SUM(sl.quantity) AS on_hand_quantity,
        COUNT(*) AS lot_count,
        STRING_AGG(sl.lot_id, ', ' ORDER BY sl.lot_id) AS lot_ids
    FROM stock_lot sl
    LEFT JOIN address a ON a.id = sl.address_id
    WHERE sl.part_id IS NOT NULL
      AND sl.address_id IS NOT NULL
      AND a.id IS NULL
      AND sl.is_expected = FALSE
      AND sl.quantity > 0
    GROUP BY sl.part_id
) oh
JOIN part p ON p.id = oh.part_id
WHERE NOT EXISTS (
    SELECT 1
    FROM kit_line kl
    JOIN kit k ON k.id = kl.kit_id
    WHERE kl.part_id = oh.part_id
      AND kl.ignore IS NOT TRUE
      AND k.approved
)
ORDER BY p.mpn, p.custom_id

Click Run Query

If you have a large library of parts, it may take a minute or two for the data to load.

Optionally, click Export to save the report as a CSV or Excel File