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
This view is using another view called spvSalesDocType, i cant seem to find that view anywhere.
Sorry this is a Salespad view that I forgot to add to the website. Create the view using this:
/****** Object: View [dbo].[spvSalesDocType] Script Date: 04/15/2013 08:38:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [dbo].[spvSalesDocType]
AS
select SOPTYPE=1, Sales_Doc_Type=’QUOTE’
union
select SOPTYPE=2, Sales_Doc_Type=’ORDER’
union
select SOPTYPE=3, Sales_Doc_Type=’INVOICE’
union
select SOPTYPE=4, Sales_Doc_Type=’RETURN’
union
select SOPTYPE=5, Sales_Doc_Type=’BACKORDER’
union
select SOPTYPE=6, Sales_Doc_Type=’FULFILLMENTORDER’
–select SOPTYPE=6, Sales_Doc_Type=’INVOICE’
GO