SQL View – Inventory Serial/Lot Numbers

The following view lists all your inventory item’s serial numbers, warehouse locations, bin locations, unit costs, and allocations.

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

You can name the view whatever you want by change the [tspvInventorySerialNum] 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].[tspvInventorySerialNum]    Script Date: 04/19/2010 16:58:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE view [dbo].[tspvInventorySerialNum] AS
select
    Item_Number = ITEMNMBR,
    Location = LOCNCODE,
    Serial_Num = SERLNMBR,
    Qty_Type = case QTYTYPE when 1 then ‘Onhand’ when 2 then ‘Returned’ when 3 then ‘In Use’ when 4 then ‘In Service’ when 5 then ‘Damaged’ else ‘Unknown’ end,
    Qty = cast(1 as decimal),
    Source = ‘Open’,
    Sales_Doc_Type = (case SERLNSLD when 0 then ” else (select top 1 (CASE SOPTYPE when 1 then ‘QUOTE’ when 2 then ‘ORDER’ when 3 then ‘INVOICE’ when 4 then ‘RETURN’ when 5 then ‘BACKORDER’ end) from SOP10201 (nolock) where SERLTNUM=SERLNMBR and SOP10201.ITEMNMBR=IV00200.ITEMNMBR and POSTED=0) end),
    Sales_Doc_Num = (case SERLNSLD when 0 then ” else (select top 1 SOPNUMBE from SOP10201 (nolock) where SERLTNUM=SERLNMBR and SOP10201.ITEMNMBR=IV00200.ITEMNMBR and POSTED=0) end),
    Qty_Allocated = cast(case when SERLNSLD=1 then 1 else 0 end as decimal),
    Bin = BIN,
    [Unit_Cost] = UNITCOST
from IV00200 (nolock)