Skip to content

SQL Views – Dynamics GP Purchase Order View

March 11, 2010

This view gives proper names to all the POP10100 fields. I included almost all of the fields in the POP10100 table, but there are a few that were omitted because I never use them. If you would like to modify this view yourself then that is completely fine with me. I used VSToolsForum as a reference for column names. It can help you determine what fields were left out as well.

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

You can name the view whatever you want by change the [tspvPurchaseOrder] 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].[tspvPurchaseOrder]    Script Date: 03/11/2010 15:06:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE view [dbo].[tspvPurchaseOrder]
AS
select
    [PO_Number]=PONUMBER,
    [PO_Status]=case when POSTATUS=’1′ then ‘New’ when POSTATUS=’2′ then ‘Released’ when POSTATUS=’3′ then ‘Change Order’ when POSTATUS=’4′ then ‘Received’ when POSTATUS=’5′ then ‘Closed’ when POSTATUS=’6′ then ‘Canceled’ end,
    [PO_Type]=case when potype=’1′ then ‘Standard’ when potype=’2′ then ‘Drop Ship’ when potype=’3′ then ‘Blanket’ when potype=’4′ then ‘Blanket Drop Ship’ end, 
    [Entered_By]=USER2ENT,
    [PO_Date]=DOCDATE,
    [Last_Edited_By]=LSTEDTDT,
    [Last_Printed_On]=LSTPRTDT,
    [Promised_Date]=PRMDATE,
    [Promised_Ship_Date]=PRMSHPDTE,
    [Required_Date]=REQDATE,
    [Shipping_Method]=SHIPMTHD,
    [Subtotal]=SUBTOTAL,
    [Total] = SUBTOTAL + FRTAMNT – TRDISAMT + MSCCHAMT,
    [Discount_Amount]=TRDISAMT,
    [Misc_Charge]=MSCCHAMT,
    [Freight_Amount]=FRTAMNT,
    [Tax_Amount]=TAXAMNT,
    [Vendor_ID]=VENDORID,
    [Vendor_Name]=VENDNAME,
    [Primary_Bill_To_Address_code]=PRBTADCD,
    [Primary_Ship_To_Address_code]=PRSTADCD,
    [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,
    [Payment_Terms]=PYMTRMID,
    [Due_Date]=DUEDATE,
    [Customer_Num]=CUSTNMBR,
    [Created_On]=CREATDDT,
    [Modified_On]=MODIFDT,
    [Comment_ID]=ph.COMMNTID,
    [Currency_ID]=CURNCYID,
    [Currency_Index]=CURRNIDX,
    [Time]=MODIFDT,
    [Buyer_ID]=BUYERID,
    [Confirm_With]=CONFIRM1,
    [On_Hold]=HOLD,
    [On_Hold_Date]=ONHOLDDATE,
    [On_Hold_By]=ONHOLDBY,
    [Hold_Removed_On]=HOLDREMOVEDATE,
    [Hold_Removed_By]=HOLDREMOVEBY,
    [Revision_Number]=Revision_Number,
    [Tax_Schedule]=TAXSCHID,
    [Currency_Format] = cast(replace(rtrim(‘#,##0 ‘ + substring(‘00000′,1, isnull((select top 1 DECPLCUR-1 from DYNAMICS..MC40200 as a where a.CURNCYID=ph.CURNCYID), 2))), ‘ ‘, ‘.’) as varchar(15)),
    [Vendor_Company]=PURCHCMPNYNAM,
    [Vendor_Contact]=PURCHCONTACT,
    [Vendor_Address_Line_1]=PURCHADDRESS1,
    [Vendor_Address_Line_2]=PURCHADDRESS2,
    [Vendor_Address_Line_3]=PURCHADDRESS3,
    [Vendor_City]=PURCHCITY,
    [Vendor_State]=PURCHSTATE,
    [Vendor_Zip]=PURCHZIPCODE,
    [Vendor_Country]=PURCHCOUNTRY,
    [Vendor_Phone_1]=PURCHPHONE1,
    [Vendor_Phone_2]=PURCHPHONE2,
    [Vendor_Phone_3]=PURCHPHONE3,
    [Vendor_Fax]=PURCHFAX,
    [Vendor_Address_Code] = VADCDPAD,
    [Vendor_Email] = isnull((select INET1 from SY01200 WHERE Master_type=’VEN’ and Master_ID=ph.VENDORID and ADRSCODE=ph.VADCDPAD),”),
    [Comment] = isnull(pc.CMMTTEXT,”),
    [PO_Note] = isnull(TXTFIELD,”)
    from POP10100 as ph (nolock)
        left join POP10150 as pc (nolock) on pc.POPNUMBE=ph.PONUMBER
        left join SY03900 (nolock) on SY03900.NOTEINDX=ph.PONOTIDS_1

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.

Join 27 other followers

%d bloggers like this: