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
Ron, we are also using similar stock count sheets and I was wondering if you ever evaluated any WMS systems built for GP. We are talking to a few vendors now and would like to know if you ever ventured into the relam of wireless scanners and barcode systems for GP. THANKS
Yes we are using Data Collection by Salespad. It is a very simple WMS that does a perfect job for what we need. Multibin, barcode, wireless scanning, pick/pack/ship, bin transfers, etc. I would definitely give them a call. They have handheld scanner software as well as netbook software (a project I worked with them on). We actually are running Dell Mini’s mounted to our picking cards with bluetooth barcode scanners. The Dell Mini’s give our warehouse folks a much better keyboard and screen than the handheld PC scanners.
I also have a Dolphin handheld I am looking to get rid of. It was being used in our testing facility but we chose to go with the Dell Mini’s instead. Let me know if you are interested.
Ron