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)
Trackbacks & Pingbacks