SQL Views – Inventory In All Locations

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 (www.salespad.net).

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.

USE [TSP]
GO
/****** Object:  View [dbo].[tspvInventory]    Script Date: 03/23/2010 09:15:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[tspvInventory] AS

select
    [Item_Number]=[IV00101].ITEMNMBR,
    [Item_Description]=[IV00101].ITEMDESC,
    [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=”