Skip to content

SQL Views – Inventory In All Locations

March 23, 2010

The following view will show you your inventory quantities across all of your locations. 

This view is also a modification of the spvInventory view from Salespad (

You can name the view whatever you want by change the [tspvInventory] below.  Also the USE [TSP] tells the script to only execute on my database named TSP.  You will need to modify the [TSP] to be whatever your database name is.

/****** Object:  View [dbo].[tspvInventory]    Script Date: 03/23/2010 09:15:48 ******/
create view [dbo].[tspvInventory] AS

    [Item_Type]=case [IV00101].ITEMTYPE when 1 then ‘Sales Inv’ when 2 then ‘Discontinued’ when 3 then ‘Kit’ when 4 then ‘Misc Charge’ when 5 then ‘Services’ when 6 then ‘Flat Fee’ else ” end,
    [Item_Tracking_Option]=case [IV00101].ITMTRKOP when 1 then ‘None’ when 2 then ‘Serial Numbers’ when 3 then ‘Lot Numbers’ else ” end,
    Avail = (loc.QTYONHND -  loc.ATYALLOC) ,
    Onhand = loc.QTYONHND ,
    Allocated = loc.ATYALLOC ,
    Backordered = loc.QTYBKORD ,
    Onorder = loc.QTYONORD,
    Order_Point_Qty = cast(case when loc.ORDRPNTQTY!=0 then loc.ORDRPNTQTY else (select sum(b.ORDRPNTQTY) from IV00102 as b (nolock) where b.ITEMNMBR=loc.ITEMNMBR and b.LOCNCODE!=”) end as decimal)
from IV00101 (nolock)
    join IV00102 as loc (nolock) on loc.ITEMNMBR=IV00101.ITEMNMBR and loc.LOCNCODE=”

From → Dynamics GP

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

%d bloggers like this: