SQL View – Sales Line Items (History)

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

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

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

CREATE view [dbo].[tspvSalesLineItemHistory]
AS
select 
    [Sales_Doc_Type] = sdt.Sales_Doc_Type,
    [Sales_Doc_Num] = [sd].SOPNUMBE,
    [Line_Num] = [sd].LNITMSEQ,
    [Component_Seq_Num] = [sd].CMPNTSEQ,
    [Source]= ‘History’,
    [Item_Number] = [sd].ITEMNMBR,
    [Item_Description] = [sd].ITEMDESC,
    [Is_Non_Inventory] = cast([sd].NONINVEN as bit),
    [Is_Dropship] = cast([sd].DROPSHIP as bit),
    [UOfM_Schedule] = isnull(i.UOMSCHDL, ”),   
    [Unit_Of_Measure] = [sd].UOFM,
    [Warehouse_Code] = [sd].LOCNCODE,
    [Unit_Cost] = [sd].UNITCOST,
    [Expected_Unit_Cost] = Expected_Unit_Cost,
    [Extended_Cost] = Round(((case sd.SOPTYPE when 4 then [sd].QUANTITY else [sd].QTYREMAI end)*[sd].UNITCOST),isnull(isnull( (select DECPLCUR-1 from DYNAMICS..MC40200 as a (nolock) where a.CURRNIDX=sd.CURRNIDX), (select DECPLCUR-1 from DYNAMICS..MC40200 as a join MC40000 as b on a.CURNCYID=b.FUNLCURR)),2)),
    [Unit_Price] = [sd].UNITPRCE,
    [Extended_Price] =
        Round(
        ((case sd.SOPTYPE when 4 then [sd].QUANTITY else [sd].QTYREMAI end) * (sd.UNITPRCE – sd.MRKDNAMT)) ,
        isnull(isnull( (select DECPLCUR-1 from DYNAMICS..MC40200 as a (nolock) where a.CURRNIDX=sd.CURRNIDX), (select DECPLCUR-1 from DYNAMICS..MC40200 as a join MC40000 as b on a.CURNCYID=b.FUNLCURR)),2)),
    [Currency_Dec]= isnull(isnull( (select DECPLCUR-1 from DYNAMICS..MC40200 as a (nolock) where a.CURRNIDX=sd.CURRNIDX), (select DECPLCUR-1 from DYNAMICS..MC40200 as a join MC40000 as b on a.CURNCYID=b.FUNLCURR)),2),
    [Markdown_Amount] = cast(case when MRKDNTYP=0 then 0 else [sd].MRKDNAMT end as numeric(19,5)),
    [Markdown_Pct] = case when MRKDNTYP=0 then sd.MRKDNPCT else 0 end,
    [Item_Tax_Schedule] = [sd].ITMTSHID,
    [Quantity] = [sd].QUANTITY,
    [Qty_Allocated] = [sd].ATYALLOC,
    [Qty_Remaining] = [sd].QTYREMAI,
    [Qty_Canceled] = [sd].QTYCANCE,
    [Qty_Backordered] = [sd].QTYTBAOR,
    [Qty_SentTo_Backordered] = [sd].QTYPRBAC,
    [Qty_ToPick] = case when i.ITEMTYPE in (4,5,6) then 0 else ([sd].QTYREMAI – [sd].QTYTBAOR – [sd].QTYFULFI) end,
    [Qty_Fulfilled] = case when i.ITEMTYPE in (5,6) then [sd].QUANTITY else [sd].QTYFULFI end,
    [Qty_Selected] = [sd].QTYSLCTD,
    [Qty_Avail] = isnull((inv.QTYONHND – inv.ATYALLOC), 0),
    [Qty_To_Invoice] = sd.QTYTOINV,
    [Fulfillment_Date] = [sd].FUFILDAT,
    [Actual_Ship_Date] = [sd].ACTLSHIP,
    [Shipping_Method] = [sd].SHIPMTHD,
    [Tax_Schedule] = [sd].TAXSCHID,
    [Sales_Territory] = [sd].SALSTERR,
    [Sales_Person_ID] = [sd].SLPRSNID,
    [Price_Level] = [sd].PRCLEVEL,
    [Purchasing_Status] = case sd.PURCHSTAT when 1 then ‘None’ when 2 then ‘Needs Purchase’ when 3 then ‘Purchased’ when 4 then ‘Partially Received’ when 5 then ‘Fully Received’ end,
    [Item_Qty_Dec] = cast([sd].DECPLQTY-1 as smallint),
    [Item_Curr_Dec] = cast([sd].DECPLCUR-1 as smallint),
    [Custom_Group] = [sd].[ITEMCODE],
    [Tax_Amount] = [sd].[TAXAMNT],
    [Req_Ship_Date] = [sd].[ReqShipDate],
    [Item_Tracking] = case isnull(i.ITMTRKOP,1) when 1 then ‘None’ when 2 then ‘Serial Numbers’ when 3 then ‘Lot Numbers’ else ” end,
    [ReadOnly] = cast(case when QTYPRBAC+QTYPRINV+QTYPRORD>0 then 1 else 0 end as bit),
    [Master_Num]=sh.MSTRNUMB,
    Ship_To_Address_Code = sd.PRSTADCD,
    Ship_To_Name = sd.ShipToName,
    Contact_Person = sd.CNTCPRSN,
    Address_Line_1 = sd.ADDRESS1,
    Address_Line_2 = sd.ADDRESS2,
    Address_Line_3 = sd.ADDRESS3,
    City = sd.CITY,
    State = sd.STATE,
    Zip = sd.ZIPCODE,
    Country = sd.COUNTRY,
    Phone_1 = sd.PHONE1,
    Phone_2 = sd.PHONE2,
    Phone_3 = sd.PHONE3,
    Fax = sd.FAXNUMBR,
    [Comment] = isnull([SOP10202].[CMMTTEXT],”)
    from
        — sd table:
        (select sd1.*,
        Curr_Margin_Amt = Curr_Extended_Price-Curr_Extended_Cost,
        Curr_Margin_Pct = case when Curr_Extended_Price=0 then 0 else (Curr_Extended_Price-Curr_Extended_Cost)/Curr_Extended_Price*100 end,
        Expected_Margin_Amt = Curr_Extended_Price-Expected_Extended_Cost,
        Expected_Margin_Pct = case when Curr_Extended_Price=0 then 0 else (Curr_Extended_Price-Expected_Extended_Cost)/Curr_Extended_Price*100 end
        from
            — sd1 table:
            (select sd0.*,
            Curr_Quantity = case sd0.SOPTYPE when 4 then sd0.QUANTITY else sd0.QTYREMAI end,
            Curr_Extended_Cost = Round((case sd0.SOPTYPE when 4 then sd0.QUANTITY else sd0.QTYREMAI end)*sd0.UNITCOST, isnull(sopCurr.DECPLCUR, sysCurr.DECPLCUR)-1),
            Curr_Extended_Price = Round((case sd0.SOPTYPE when 4 then sd0.QUANTITY else sd0.QTYREMAI end)*sd0.UNITPRCE, isnull(sopCurr.DECPLCUR, sysCurr.DECPLCUR)-1),
            Expected_Unit_Cost = isnull(pd.UNITCOST/pl.QTYBSUOM, sd0.UNITCOST),
            Expected_Extended_Cost = Round((case sd0.SOPTYPE when 4 then sd0.QUANTITY else sd0.QTYREMAI end)*isnull(pd.UNITCOST/pl.QTYBSUOM, sd0.UNITCOST), isnull(sopCurr.DECPLCUR, sysCurr.DECPLCUR)-1)
            from SOP30300 as sd0 (nolock)
            left join SOP60100 as pl (nolock) on sd0.SOPTYPE=pl.SOPTYPE and sd0.SOPNUMBE=pl.SOPNUMBE and sd0.LNITMSEQ=pl.LNITMSEQ and sd0.CMPNTSEQ=pl.CMPNTSEQ
            left join POP10110 as pd (nolock) on pd.PONUMBER=pl.PONUMBER and pd.ORD=pl.ORD
            left join DYNAMICS..MC40200 as sopCurr (nolock) on sopCurr.CURRNIDX=sd0.CURRNIDX
            left join (select DECPLCUR = isnull((select top 1 DECPLCUR-1 from DYNAMICS..MC40200 as a (nolock) join MC40000 as b (nolock) on a.CURNCYID=b.FUNLCURR),2)) as sysCurr on 1=1
            ) as sd1
        ) as sd
        — other joined tables:
        join spvSalesDocType as sdt (nolock) on sdt.SOPTYPE=sd.SOPTYPE
        join SOP30200 as sh on sh.SOPTYPE=sd.SOPTYPE and sh.SOPNUMBE=sd.SOPNUMBE
        left join SOP10202 (nolock) on sd.SOPTYPE=SOP10202.SOPTYPE
            and sd.SOPNUMBE=SOP10202.SOPNUMBE
            and sd.LNITMSEQ=SOP10202.LNITMSEQ
            and sd.CMPNTSEQ=SOP10202.CMPNTSEQ
        left join IV00101 as i (nolock) on i.ITEMNMBR=sd.ITEMNMBR and sd.NONINVEN=0
        left join IV00102 as inv (nolock) on inv.ITEMNMBR=sd.ITEMNMBR and inv.LOCNCODE=sd.LOCNCODE