Skip to content

SQL View – Inventory Summaries – By Location

April 12, 2010

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

About these ads

From → Dynamics GP

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 28 other followers

%d bloggers like this: