SQL Views – Inventory Mulitbin

The following view lists all your inventory bin quantities for GP installations that use Mulit-bin.

This view is also a modification of the spvInventoryMultiBin view from Salespad (www.salespad.net).

You can name the view whatever you want by change the [tspvInventoryMultiBin] 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].[tspvInventoryMultibin]    Script Date: 04/19/2010 16:51:43 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE view [dbo].[tspvInventoryMultibin]
AS
select top 2147483647
    Item_Number = ITEMNMBR,
    Location = LOCNCODE,
    Bin = BIN,
    Qty_Type = cast(CASE QTYTYPE when 1 then ‘On Hand’ when 2 then ‘Returned’ when 3 then ‘In Use’ when 4 then ‘In Service’ when 5 then ‘Damaged’ end as varchar(10)),
    Quantity = QUANTITY,
    Qty_Allocated =    ATYALLOC ,
dex_row_id
from IV00112 (nolock)
order by dex_row_id