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>*/
Trackbacks & Pingbacks