Skip to content

SQL View – Sales Line Items

April 8, 2010

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

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

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

CREATE view [dbo].[tspvSalesLineItem]
AS
select 
    [Sales_Doc_Num] = [sd].SOPNUMBE,
    [Line_Num] = [sd].LNITMSEQ,
    [Component_Seq_Num] = [sd].CMPNTSEQ,
    [Source]= ‘Open’,
    [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] = case sd.SOPTYPE when 4 then [sd].QUANTITY else [sd].QTYREMAI end,
    [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] = [sd].QTYFULFI,
    [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’ else ‘N/A’ 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 when i.ITEMTYPE=3 then ‘None (Kit)’ else case isnull(i.ITMTRKOP,1) when 1 then ‘None’ when 2 then ‘Serial Numbers’ when 3 then ‘Lot Numbers’ else ” end 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 = cast(isnull(pd.UNITCOST/pl.QTYBSUOM, sd0.UNITCOST) as numeric(19,5)),
            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 SOP10200 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 SOP10100 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

About these ads

From → Dynamics GP

12 Comments
  1. Paul Dyer permalink

    Is there a way I could download these scripts? I seem to be having formating issues copying from the HTML on here…

  2. Ron permalink

    Paul,

    What are you using to paste into? The copy paste works fine for me when I paste into SQL Server Management Studio. Are you getting any errors?

    Ron

    • Paul permalink

      I’m cutting and pasting into SQL Management Studio 2005… tons of character issues. Some I’ve been able to fix, but this one is giving a ton of issues.

      Fixed the basing problems with ‘ ‘ and – causing issues when you cut and paste, but still having issues.

      Here’s the errors from this script raw:

      Msg 102, Level 15, State 1, Procedure tspvSalesLineItem, Line 8
      Incorrect syntax near ‘‘’.
      Msg 156, Level 15, State 1, Procedure tspvSalesLineItem, Line 18
      Incorrect syntax near the keyword ‘case’.
      Msg 102, Level 15, State 1, Procedure tspvSalesLineItem, Line 18
      Incorrect syntax near ‘,’.
      Msg 102, Level 15, State 1, Procedure tspvSalesLineItem, Line 18
      Incorrect syntax near ‘)’.
      Msg 156, Level 15, State 1, Procedure tspvSalesLineItem, Line 22
      Incorrect syntax near the keyword ‘case’.
      Msg 102, Level 15, State 1, Procedure tspvSalesLineItem, Line 23
      Incorrect syntax near ‘,’.
      Msg 102, Level 15, State 1, Procedure tspvSalesLineItem, Line 23
      Incorrect syntax near ‘)’.
      Msg 102, Level 15, State 1, Procedure tspvSalesLineItem, Line 24
      Incorrect syntax near ‘,’.
      Msg 102, Level 15, State 1, Procedure tspvSalesLineItem, Line 24
      Incorrect syntax near ‘)’.
      Msg 102, Level 15, State 1, Procedure tspvSalesLineItem, Line 80
      Incorrect syntax near ‘—’.
      Msg 156, Level 15, State 1, Procedure tspvSalesLineItem, Line 92
      Incorrect syntax near the keyword ‘as’.

  3. Paul permalink

    Here’s a pointer to posting Code in WordPress.

    http://codex.wordpress.org/Writing_Code_in_Your_Posts

    I think wordpress is reformating the code.

  4. Ron permalink

    Paul,

    Did that resolve your issue? I am sorry it took me so long to respond. We have 3 folks in my department out this week and I was traveling all last week. It has been extremely hectic and I have let the blog suffer because of it. Let me know if this fixed your issues.

    Ron

  5. Hello! Quick question that’s totally off topic.
    Do youu know how to make your site mobile friendly? My website looks
    weird when browsing from my iphone. I’m trying to find a theme or plugin
    that might be able to fix this problem. If you have any recommendations, please share.
    Appreciate it!

  6. Very soon this web site will be amous among all blogging
    users,due to it’s pleasant articles or reviews

Trackbacks & Pingbacks

  1. SQL Scripts – SOP to POP Margin Analysis « Real Life Dynamics User
  2. SQL View – Sales Documents - Real Life Dynamics User (RLDU)
  3. SQL View – Orders Needing Purchase « Real Life Dynamics User
  4. SQL Script – Orders Needing Purchase « Real Life Dynamics User
  5. SQL Script – Orders Needing Purchase - Real Life Dynamics User (RLDU) - GP Technical Blogs - Microsoft Dynamics Community

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

%d bloggers like this: