SQL View – Purchase Order Line Items

The following view will show you all line items on purchase orders that have not been moved to the purchase order history table.  It also gives the fields proper names.

This view is also a modification of the spvPurchaseOrderLineItem view from Salespad (www.salespad.net).

You can name the view whatever you want by change the [tspvPurchaseOrderLineItem] 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].[tspvPurchaseLineItem]    Script Date: 03/23/2010 13:06:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE view [dbo].[tspvPurchaseLineItem]

AS
select
    PO_Number = d.PONUMBER,
    Line_Seq = d.ORD,
    PO_Line_Status = POLNESTA, –case POLNESTA when 1 then ‘New’ when 2 then ‘Released’ when 3 then ‘Change Order’ when 4 then ‘Received’ when 5 then ‘Closed’ when 6 then ‘Cancelled’ end,
    PO_Type = CASE WHEN POTYPE=’1′ THEN ‘Standard’ WHEN POTYPE=’2′ THEN ‘Dropship’ WHEN POTYPE=’3′ THEN ‘Blanket’ WHEN potype=’4′ THEN ‘Blanket Dropship’ END,
    Item_Number = d.ITEMNMBR,
    Item_Description = d.ITEMDESC,
    Vendor_ID = d.VENDORID,
    Vendor_Name = (select VENDNAME from PM00200 as v with (NOLOCK) where v.VENDORID=d.VENDORID),
    Vendor_Item_Num = VNDITNUM,
    Vendor_Item_Desc = VNDITDSC,
    Is_NonInventory = NONINVEN, –cast(NONINVEN as bit),
    Location = LOCNCODE,
    UOfM = UOFM,
    Qty_Ordered = d.QTYORDER-d.QTYCANCE,
    Qty_Canceled = d.QTYCANCE,
    Qty_Shipped = isnull((select SUM(QTYSHPPD) from POP10500 as r (nolock) where r.PONUMBER=d.PONUMBER and r.POLNENUM=d.ORD), 0),
    Qty_Committed = QTYCMTBASE,
    Unit_Cost = d.UNITCOST,
    Extended_Cost = d.EXTDCOST,
    Required_Date = d.REQDATE,
    Promised_Date = d.PRMDATE,
    Promised_Ship_Date = d.PRMSHPDTE,
    Requested_By = ”,
    Comment_ID = d.COMMNTID,
    Decimal_Places_Curr=cast(DECPLCUR-7 as smallint),
    Decimal_Places_Qty=cast(DECPLQTY-1 as smallint),
    PO_Line_Status_Orig = cast(0 as smallint), –”,
    Job_Number = JOBNUMBR,
    Cost_Code = COSTCODE,
    Currency_ID = ”,
    Currency_Index = cast(0 as smallint),
    Line_Origin = cast(0 as smallint),
    FOB = cast(0 as smallint),
    Capital_Item = Capital_Item,
    Source_Document_Number = ”,
    Source_Document_Line_Num = cast(0 as smallint),
    Release_By_Date = cast(‘1/1/1900’ as smalldatetime),
    Released_Date = cast(‘1/1/1900’ as smalldatetime),
    Change_Order_Flag = cast(99 as smallint),
    Tax_Amount=cast(0 as numeric(19,5)),
    Shipping_Method = ”,
    [Company]=CMPNYNAM,
    [Contact]=CONTACT,
    [Address_Line_1]=ADDRESS1,
    [Address_Line_2]=ADDRESS2,
    [Address_Line_3]=ADDRESS3,
    [City]=CITY,
    [State]=STATE,
    [Zip]=ZIPCODE,
    [Country]=COUNTRY,
    [Phone_1]=PHONE1,
    [Phone_2]=PHONE2,
    [Phone_3]=PHONE3,
    [Fax]=FAX,
    [Base_UOfM] = isnull((select BASEUOFM from IV00101 as i with (nolock) join IV40201 as u with (NOLOCK) on u.UOMSCHDL=i.UOMSCHDL where i.ITEMNMBR=d.ITEMNMBR and d.NONINVEN=0),d.UOFM),
    [UOfM_Factor] = UMQTYINB,
    Comment = isnull(c.CMMTTEXT, ”)
from POP10110 as d (nolock)
    left join POP10550 as c (nolock) on c.POPNUMBE=d.PONUMBER and c.ORD=d.ORD