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=”
Trackbacks & Pingbacks