Skip to content

SQL Scripts – Stock Count (Multibin Inventories)

April 23, 2010

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

About these ads

From → Dynamics GP

2 Comments
  1. Ben permalink

    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

    • Ron permalink

      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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 27 other followers

%d bloggers like this: