Skip to content

SQL View – Item Master

March 24, 2010

The following view breaks down the IV00101 table into a proper field names and includes the List Price from table IV00105).

This view is also a modification of the spvItemMaster view from Salespad (

You can name the view whatever you want by change the [tspvItemMaster] 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.

/****** Object:  View [dbo].[tspvItemMaster]    Script Date: 03/23/2010 13:34:56 ******/

— View

CREATE view [dbo].[tspvItemMaster]
    [Item_Type]=case [IV00101].ITEMTYPE when 1 then ‘Sales Inv’ when 2 then ‘Discontinued’ when 3 then ‘Kit’ when 4 then ‘Misc Charge’ when 5 then ‘Services’ when 6 then ‘Flat Fee’ else ” end,
    [List_Price] = cast(isnull((select top 1 LISTPRCE from IV00105 (nolock) left join MC40000 (nolock) on IV00105.ITEMNMBR=IV00101.ITEMNMBR and (IV00105.CURNCYID=MC40000.FUNLCURR or IV00105.CURNCYID=”) where IV00105.ITEMNMBR=IV00101.ITEMNMBR order by IV00105.CURNCYID desc), 0) as numeric(19,5)),
    [Item_Qty_Dec]=cast([IV00101].DECPLQTY-1 as smallint),
    [Item_Curr_Dec]=cast([IV00101].DECPLCUR-1 as smallint),
    [Item_Tracking_Option]=    case when ITEMTYPE=3 then ‘None (Kit)’ else case isnull(ITMTRKOP,1) when 1 then ‘None’ when 2 then ‘Serial Numbers’ when 3 then ‘Lot Numbers’ else ” end end,
    [Allow_Backorders]=cast([IV00101].ALWBKORD as bit),
    [Purchase_Tax_Options]=cast([IV00101].Purchase_Tax_Options as smallint), –Backwards comp.
    [Note] = isnull(SY03900.TXTFIELD, ”)
from IV00101 (nolock)
left join SY03900  (nolock) on SY03900.NOTEINDX = IV00101.NOTEINDX
/*+where*/ /*<where>*/

Technorati Tags: ,

From → Dynamics GP

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

%d bloggers like this: