Skip to content

SQL Views – Inventory Mulitbin

April 19, 2010

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

About these ads

From → Dynamics GP

9 Comments
  1. Hi Ron,

    Is there any specific reason for mentioning “top 2147483647″? I know that the value is MAX for row count in SQL.

    But what would happen if we do not mention it? Or what’s the advantage of mentioning it towards conventional querying?

    Thanks
    Vaidy Mohan

  2. Ron permalink

    You know that is a good question. As you will notice on most of my SQL View (only the SQL Views) I have a reference to http://www.salespad.net. Most of the views I post are variations of Salespad views. This is an example of a view that I built off of a Salespad view. I wondered that myself, but I just posted it as it was.

    Ron

  3. Ben permalink

    I get the following error:

    Msg 102, Level 15, State 1, Procedure tspvInventoryMultibin, Line 7
    Incorrect syntax near ‘‘’.
    Msg 154, Level 15, State 1, Procedure tspvInventoryMultibin, Line 7
    a USE database statement is not allowed in a procedure, function or trigger.

    Are you running this script in SQL Studio as a Query?

  4. Ron permalink

    Ben,

    Sorry for the delayed response…I have been out of the office this week. I am using SQL Server Management Studio to execute this statement. I am not sure why you are receiving the error message.

    One option to try would be to remove the USE [XXX] and the following GO statement and just make sure you are executing the query on the proper database in the SSMS database selection drop down. Does that make sense?

    Ron

    • Ron & Vaidy,

      SQL Server 2000 allowed veiws to be sorted with an order by clause at the end of it like “order by dex_row_id”. However, when SQL Server 2005 came out a view could no longer be ordered by defaults unless you were grabbing a set of records. By grabbing top 2147483647, which was the max number of results for a view you were allowed to order by and mimic the behavior of SQL Server 2000. This is why we did it.

      However, It was not a good way of doing things. By grabbing the top X you force a table scan, which then slows performance. SalesPad no longer uses top x in its latest versions of its product. Instead we do the ordering of code in the core product with C#. Hope this helps.

      Sincerely,
      Joseph A Alt III

      • Ron permalink

        Thank you very much for the insight Joseph.

        *Joseph is a developer at Salespad.

Trackbacks & Pingbacks

  1. SQL Views – Inventory Mulitbin - DynamicAccounting.net
  2. SQL Scripts – Stock Count (Multibin Inventories) « Real Life Dynamics User
  3. SQL Scripts – Stock Count (Multibin Inventories) - Real Life Dynamics User (RLDU)

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.

%d bloggers like this: