Skip to content

SQL View – Sales Documents (History)

May 26, 2010

The following view returns historical sales order header information.  Combine this with historical sales line items to get a full view of a sales order.

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

You can name the view whatever you want by change the [tspvSalesDocumentHistory] 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].[tspvSalesDocumentHistory]    Script Date: 05/26/2010 10:23:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE  view [dbo].[tspvSalesDocumentHistory] AS
select
[Sales_Doc_Type]=sdt.Sales_Doc_Type,
[Sales_Doc_Num]=SOP10100.SOPNUMBE,
[Sales_Doc_ID]=DOCID,
[Original_Num]=ORIGNUMB,
[Doc_Date]=DOCDATE,
[Posted_Date]=sop10100.POSTEDDT,
[Actual_Ship_Date]=ACTLSHIP,
[Fulfillment_Date]=FUFILDAT,
[Source]=cast(‘History’ as varchar(7)),
[Sales_Batch]=BACHNUMB,
[Customer_Num]=CUSTNMBR,
[Customer_Name]=CUSTNAME,
[Bill_To_Address_Code]=PRBTADCD,
[Ship_To_Address_Code]=PRSTADCD,
[Ship_To_Name]=ShipToName,
[Contact_Person]=CNTCPRSN,
[Address_Line_1]=Address1,
[Address_Line_2]=Address2,
[Address_Line_3]=Address3,
[City]=City,
[State]=State,
[Zip]=ZipCode,
[Country]=Country,
[Phone_1]=PHNUMBR1,
[Phone_2]=PHNUMBR2,
[Phone_3]=PHONE3,
[Fax]=FAXNUMBR,
[Price_Level]=PRCLEVEL,
[Customer_PO_Num]=CSTPONBR,
[Status]= case when QTYTBAOR is null then ‘ZL’ else case when QTYTBAOR>0 then ‘BO ‘ else ” end + case QTYREMAI-QTYFULFI when 0 then ‘FP’ else ‘PP’ end end ,
[Req_Ship_Date]=ReqShipDate,
[Subtotal]=REMSUBTO,
[Freight]=FRTAMNT,
[Tax]=TAXAMNT,
[Misc_Charge]=MISCAMNT,
[Discount]=TRDISAMT,
[Total]=DOCAMNT,
[On_Account]=ACCTAMNT,
[Deposits]=DEPRECVD,
[Shipping_Method]=SHIPMTHD,
[Tax_Exempt_1] = TAXEXMT1,
[Tax_Exempt_2] = TAXEXMT2,
[Tax_Registration_Num] = TXRGNNUM,
[Tax_Schedule] = [SOP10100].TAXSCHID,
[Warehouse_Code]=LOCNCODE,
[Sales_Person_ID]=SLPRSNID,
[Sales_Territory]=SALSTERR,
[Payment_Terms]=upper(PYMTRMID),
[Master_Num]=MSTRNUMB,
[Comments]=isnull(CMMTTEXT,”),
[Notes]= isnull(TXTFIELD,”),
[User_Def_Date1]=USRDAT01,
[User_Def_Date2]=USRDAT02,
[User_Def_List1]=USRTAB01,
[User_Def_List2]=USRTAB09,
[User_Def_List3]=USRTAB03,
[User_Def_1]=USERDEF1,
[User_Def_2]=USERDEF2,
[User_Def_3]=USRDEF03,
[User_Def_4]=USRDEF04,
[User_Def_5]=USRDEF05,
[Prev_Sales_Doc_Type]=CASE ORIGTYPE when 1 then ‘QUOTE’ when 2 then ‘ORDER’ when 3 then ‘INVOICE’ when 4 then ‘RETURN’ when 5 then ‘BACKORDER’ else ” end,
[Prev_Sales_Doc_Num]=orignumb,
[Quote_Num]=”,
[Invoice_Num]=”,
[Currency_ID]=CURNCYID,
[Pickticket_Num]=PICTICNU,
[Packingslip_Num]=PCKSLPNO,
[Blind_Ship] = cast(0 as bit),
[Ship_Complete] = cast(case isnull(USRTAB01,’NO’) when ‘yes’ then 1 else 0 end as bit),
[Auto_Calc_Freight] = cast (isnull(SY90000.PropertyValue,0) as bit),
[Currency_Dec]= isnull(isnull( (select DECPLCUR-1 from DYNAMICS..MC40200 as a (nolock) where a.CURNCYID=SOP10100.CURNCYID), (select DECPLCUR-1 from DYNAMICS..MC40200 as a (nolock) join MC40000 as b (nolock) on a.CURNCYID=b.FUNLCURR)),2),
[Created_On] = CREATDDT,
[Created_By] = USER2ENT,
[Email] = cast (isnull(SY90000b.PropertyValue,”) as varchar(400)),
[EnteredBatch] = ”,
[BatchHours] = cast(0 as numeric(19,2)),
[Alert] = ”,
[ReadOnly] = cast(case when (SOP10100.SOPTYPE>4) or (select count(*) from SOP10200 as sd (nolock) where QTYPRBAC+QTYPRINV+QTYPRORD>0 and sd.SOPTYPE=SOP10100.SOPTYPE and sd.SOPNUMBE=SOP10100.SOPNUMBE) >0 then 1 else 0 end as bit),
[Forwardable] = cast(1 as bit),
[Message] = case when SUBTOTAL!=REMSUBTO then ‘One or more line items have been transferred to another document.  The Remaining quantities and amounts are being displayed.’ else ” end,
[Comment_ID] = COMMNTID,
USRDAT01,
USRDAT02,
USRTAB01,
USRTAB09,
USRTAB03,
USERDEF1,
USERDEF2,
USRDEF03,
USRDEF04,
USRDEF05,
SOP10100.DEX_ROW_ID,
UPS_Zone = UPSZONE
from SOP30200 as SOP10100 (nolock)
join spvSalesDocType as sdt (nolock) on sdt.SOPTYPE=SOP10100.SOPTYPE
left join SOP10106 (nolock) on SOP10106.SOPTYPE=SOP10100.SOPTYPE and SOP10106.SOPNUMBE=SOP10100.SOPNUMBE
left join SY03900 (nolock) on SY03900.NOTEINDX=SOP10100.NOTEINDX
left join SY90000 (nolock) on SY90000.ObjectType=’OrderFreightOption’ and SY90000.ObjectID=SOP10100.SOPNUMBE and SY90000.PropertyName=cast(SOP10100.SOPTYPE as varchar(50))
left join SY90000 as SY90000b (nolock) on SY90000b.ObjectType=’SalesDocumentEmail’ and SY90000b.ObjectID=SOP10100.SOPNUMBE and SY90000b.PropertyName=cast(SOP10100.SOPTYPE as varchar(50))
left join (select SOPTYPE, SOPNUMBE, QTYTBAOR = sum(case when SOPTYPE=5 then QUANTITY else QTYTBAOR end), QTYREMAI = sum(QTYREMAI), QTYFULFI = sum(case when sd.NONINVEN=1 or sd.DROPSHIP=1 or i.ITEMTYPE in (4,5,6) then sd.QUANTITY else sd.QTYFULFI end)
from SOP30300 as sd (nolock) left join IV00101 as i (nolock) on i.ITEMNMBR=sd.ITEMNMBR group by SOPTYPE, SOPNUMBE) as d on d.SOPTYPE=SOP10100.SOPTYPE and d.SOPNUMBE=SOP10100.SOPNUMBE
where BACHNUMB!=” and  VOIDSTTS=0

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: