SQL Scripts – Stock Count (Multibin Inventories)

The following script will return a result set that can be exported (or copy and pasted) to Excel, for an easy to use stock count sheet for multibin inventory GP environments.  I have a SQL Reporting Services report written for our stock count sheets that makes it even easier. 

The script using the following views:

- Inventory Multibin (tspvInventoryMultibin)

- Item Master (tspvItemMaster)

- Inventory Serial/Lot Numbers (tspvInventorySerialNum)

**SQL Script***

SELECT  a.item_number,
        item_description,
        item_class_code,
        a.location,
        c.location AS serial_location,
        a.bin,
        CASE WHEN item_tracking_option = ‘none’ THEN a.quantity
             WHEN item_tracking_option = ’serial numbers’ THEN c.qty
        END AS quantity,
        selling_u_of_m,
        item_tracking_option,
        xboxupc,
        serial_num,
        FROM    tspvinventorymultibin a
        JOIN tspvitemmaster b ON a.item_number = b.item_number
        LEFT JOIN tspvinventoryserialnum c ON a.item_number = c.item_number
                                             AND a.bin = c.bin
WHERE   quantity > 0
        AND a.location = ‘tsp’
ORDER BY a.location,
        bin,
        a.item_number