Skip to content

SQL View – Sales Documents

April 7, 2010

The following view returns open sales order header information.  Combine this with open sales line items (to be published 4/8/2010) to get a full view of a sales order.

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

You can name the view whatever you want by change the [tspvSalesDocument] 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].[tspvSalesDocument]    Script Date: 04/06/2010 10:56:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE view [dbo].[tspvSalesDocument] AS
select
    [Sales_Doc_Type]=dbo.SOP10100.SOPTYPE,
    [Sales_Doc_Num]=SOP10100.SOPNUMBE,
    [Sales_Doc_ID]=DOCID,
    [Original_Num]=ORIGNUMB,
    [Doc_Date]=DOCDATE,
    [Actual_Ship_Date]=ACTLSHIP,
    [Fulfillment_Date]=FUFILDAT,
    [Source]=cast(‘Open’ 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 WHEN QTYREMAI – QTYFULFI = 0 THEN ‘FP’
                                        WHEN QTYFULFI > 0 THEN ‘PP’
                                        ELSE ”
                                   END
                   END + CASE WHEN (SELECT
                                        COUNT(*)
                                    FROM
                                        SOP10107 AS t (NOLOCK)
                                    WHERE
                                        t.SOPTYPE = SOP10100.SOPTYPE
                                        AND t.SOPNUMBE = SOP10100.SOPNUMBE
                                   ) > 0 THEN ‘TRK’
                              ELSE ”
                         END,
    [Req_Ship_Date]=ReqShipDate,
    [Subtotal]=REMSUBTO,
    [Freight]=FRTAMNT,
    [Tax]=TAXAMNT,
    [Misc_Charge]=MISCAMNT,
    [Discount]=TRDISAMT,
    [Total]=case when SOP10100.SOPTYPE=4 then REMSUBTO+FRTAMNT+TAXAMNT+MISCAMNT-TRDISAMT else REMSUBTO+FRTAMNT+TAXAMNT+MISCAMNT-TRDISAMT end,
    [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]=cast(” as varchar(25)),
    [Invoice_Num]=cast(” as varchar(25)),
    [Currency_ID]=CURNCYID,
    [Pickticket_Num]=PICTICNU,
    [Packingslip_Num]=PCKSLPNO,
    [Blind_Ship] = cast(0 as bit),
    [Ship_Complete] = cast(SHIPCOMPLETE 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 join MC40000 as b on a.CURNCYID=b.FUNLCURR)),2),
    [Created_On] = CREATDDT,
    [Created_By] = USER2ENT,
    [Email] = cast (isnull(SY90000b.PropertyValue,”) as varchar(400)),
    [EnteredBatch] = batchTimeStamp.PropertyValue,
    [BatchHours] = cast(cast(datediff(mi, batchTimeStamp.PropertyValue, getdate()) as numeric(19,2))/60 as numeric(19,2)),
    [ReadOnly] = cast
                    (case when
                        (SOP10100.SOPTYPE>4) –Backorders are unsupported
                        or (select count(*) from SOP10200 as sd (nolock) where QTYPRBAC+QTYPRINV+QTYPRORD+QTYDMGED+QTYRTRND+QTYINUSE+QTYINSVC>0 and sd.SOPTYPE=SOP10100.SOPTYPE and sd.SOPNUMBE=SOP10100.SOPNUMBE) > 0
                    then
                        1
                    else
                        0
                    end
                as bit),
    [Forwardable] = cast(1 as bit),
    [Comment_ID] = COMMNTID,
    USRDAT01,
    USRDAT02,
    USRTAB01,
    USRTAB09,
    USRTAB03,
    USERDEF1,
    USERDEF2,
    USRDEF03,
    USRDEF04,
    USRDEF05
from SOP10100 (nolock)
    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 SOP10200 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
    left join SY90000 as batchTimeStamp (nolock) on batchTimeStamp.ObjectType=’SalesDocBatchStamp’
        and batchTimeStamp.ObjectID=cast (SOP10100.SOPTYPE as char(2)) + SOP10100.SOPNUMBE
        and batchTimeStamp.PropertyName=SOP10100.BACHNUMB
where BACHNUMB!=” and  VOIDSTTS=0
/*-where*/ /*<where>*/

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

%d bloggers like this: