SQL View – Item Master

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 (www.salespad.net).

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.

USE [TSP]
GO
/****** Object:  View [dbo].[tspvItemMaster]    Script Date: 03/23/2010 13:34:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

— View

CREATE view [dbo].[tspvItemMaster]
AS
select
    [Item_Number]=[IV00101].ITEMNMBR,
    [Item_Description]=[IV00101].ITEMDESC,
    [Note_Index]=[IV00101].NOTEINDX,
    [Item_Short_Name]=[IV00101].ITMSHNAM,
    [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,
    [Item_Generic_Description]=[IV00101].ITMGEDSC,
    [Standard_Cost]=[IV00101].STNDCOST,
    [Current_Cost]=[IV00101].CURRCOST,
    [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_Shipping_Weight]=[IV00101].ITEMSHWT,
    [Item_Qty_Dec]=cast([IV00101].DECPLQTY-1 as smallint),
    [Item_Curr_Dec]=cast([IV00101].DECPLCUR-1 as smallint),
    [Item_Tax_Schedule_ID]=[IV00101].ITMTSHID,
    [Item_Class_Code]=[IV00101].ITMCLSCD,
    [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,
    [Lot_Type]=[IV00101].LOTTYPE,
    [Allow_Backorders]=cast([IV00101].ALWBKORD as bit),
    [Valuation_Method]=[IV00101].VCTNMTHD,
    [UOfM_Schedule]=[IV00101].UOMSCHDL,
    [Alternate_Item1]=[IV00101].ALTITEM1,
    [Alternate_Item2]=[IV00101].ALTITEM2,
    [USCATVLS_1]=[IV00101].USCATVLS_1,
    [USCATVLS_2]=[IV00101].USCATVLS_2,
    [USCATVLS_3]=[IV00101].USCATVLS_3,
    [USCATVLS_4]=[IV00101].USCATVLS_4,
    [USCATVLS_5]=[IV00101].USCATVLS_5,
    [USCATVLS_6]=[IV00101].USCATVLS_6,
    [Modified_Date]=[IV00101].MODIFDT,
    [Created_Date]=[IV00101].CREATDDT,
    [Warranty_Days]=[IV00101].WRNTYDYS,
    [Price_Level]=[IV00101].PRCLEVEL,
    [Location]=[IV00101].LOCNCODE,
    [Item_Code]=[IV00101].ITEMCODE,
    [Tax_Commodity_Code]=[IV00101].TCC,
    [Price_Group]=[IV00101].PriceGroup,
    [Price_Method]=[IV00101].PRICMTHD,
    [Purchasing_U_of_M]=[IV00101].PRCHSUOM,
    [Selling_U_of_M]=[IV00101].SELNGUOM,
    [ABC_Code]=[IV00101].ABCCODE,
    [Purchase_Item_Tax_Schedu]=[IV00101].Purchase_Item_Tax_Schedu,
    [Purchase_Tax_Option]=[IV00101].Purchase_Tax_Options,
    [Purchase_Tax_Options]=cast([IV00101].Purchase_Tax_Options as smallint), –Backwards comp.
    [Sales_Tax_Option]=[IV00101].TAXOPTNS,
    [Item_Planning_Type]=[IV00101].ITMPLNNNGTYP,
    [Country_Origin]=[IV00101].CNTRYORGN,
    [Note] = isnull(SY03900.TXTFIELD, ”)
from IV00101 (nolock)
left join SY03900  (nolock) on SY03900.NOTEINDX = IV00101.NOTEINDX
/*+where*/ /*<where>*/

Technorati Tags: SQL Views,Inventory