Skip to content

SQL View – Purchase Order Line Items

March 23, 2010

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

About these ads

From → Dynamics GP

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: