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

From → Dynamics GP

2 Comments
  1. I do agree with all the ideas you’ve presented on your post.

    They are very convincing and will definitely work.
    Nonetheless, the posts are very brief for beginners.
    Could you please lengthen them a little from next time?
    Thanks for the post.

Trackbacks & Pingbacks

  1. SQL Scripts–Vendor Invoice Pricing Discrepancy « Real Life Dynamics User

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 33 other followers

%d bloggers like this: