SQL View – Inventory Summaries – By Location
The following view will show you your inventory quantities for each of your locations.
This view is also a modification of the spvInventoryByLoc view from Salespad (www.salespad.net).
You can name the view whatever you want by change the [tspvInventoryByLoc] 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].[tspvInventoryByLoc] Script Date: 04/06/2010 15:13:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [dbo].[tspvInventoryByLoc]
AS
SELECT
Item_Number = i.ITEMNMBR,
Location = i.LOCNCODE,
[Default_Bin] = i.BINNMBR,
Onhand = i.QTYONHND,
Allocated = i.ATYALLOC,
Backordered = i.QTYBKORD,
Onorder = i.QTYONORD,
Available = (i.QTYONHND – i.ATYALLOC),
Next_Expected_Receipt = cast(isnull((select top 1 convert(varchar(10), REQDATE, 1) from POP10110 as pl where (i.LOCNCODE=” or pl.LOCNCODE=i.LOCNCODE) and pl.ITEMNMBR=i.ITEMNMBR and POLNESTA<4 order by REQDATE asc), ’1/1/1900′) as DateTime),
Order_Policy = case i.ORDERPOLICY when 1 then ‘Not Planned’ when 2 then ‘Lot for Lot’ when 3 then ‘Fixed Order Qty’ when 4 then ‘Period Order Qty’ when 5 then ‘Order Point’ when 6 then ‘Manually Planned’ else ” end,
Fixed_Order_Qty = cast(i.FXDORDRQTY as decimal),
Order_Point_Qty = cast(i.ORDRPNTQTY as decimal),
Order_Up_To_Qty = cast(i.ORDRUPTOLVL as decimal),
Replenishment_Level = case i.ReplenishmentLevel when 1 then ‘Order Point Qty’ when 2 then ‘Order-Up-To Level’ when 3 then ‘Vendor EOQ’ else ” end,
Primary_Vendor_ID = i.PRIMVNDR,
Primary_Vendor_Name = v.VENDNAME
FROM IV00102 as i (nolock) left join PM00200 as v (nolock) on v.VENDORID=i.PRIMVNDR
/*+where*/ /*<where>*/
–order by ITEMNMBR, LOCNCODE
Trackbacks & Pingbacks