SQL Views – Dynamics GP Purchase Order View
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
Trackbacks & Pingbacks