Skip to content

We Have A New Dynamics Community Version


The folks over at http://community.dynamics.com have been working on an updated version of the website for quite some time now.  I was able to get a sneak peek at Convergence 2010 this year and I am happy to say that it has finally been released. 

I am sure there will be some bugs to work out (*what happened to my posts count Nick*) but all in all, I think this will be a big step forward in helping make the community easier and more accessible.

Great work guys!

https://community.dynamics.com/b/communityteam/archive/2010/10/06/welcome-to-microsoft-dynamics-community-2-0.aspx

SQL Script – Creating Price Level Based On Existing Price Level


In the current economic times, collections have become a real burden for our company.  We are fortunate to have very tight credit control but there are still the customers who always take FOREVER to collect from.  We have recently decided to start charging these customers a 10% ‘premium’, over what they would normally pay, to cover our collections cost that we assume we are going to incur.  The method we chose to take was to:

- Create a new Price Level for these customers

- Copy the normal Price Level to this new Price Level

- Add 10% to the new Price Level

- Change the customer’s Price Level from the old to the new 

I figured with Price List Utilities (PLU), this would be a no brainer…I was quite wrong.  The creation of the new Price Level is easy using PLU, but copying another price level and adding the 10% was where I ran into the problem.  It just didn’t seem like it could be done using PLU, so I went the SQL route.

This script assumes you already have a new Price Level, let’s call that PREMIUM, created for your items that you want to charge the premium on and that the percentage is set to 0% for the PREMIUM Price Level (which means you would be selling the item at cost).  Let’s call the Price Level we are going to copy from the STANDARD Price Level.

Example:

Widget A has an STANDARD Price Level of 30% for STANDARD Price Level customers.  We want to create a PREMIUM Price Level and copy the 30% + 10% (total of 40%) to the PREMIUM Price Level and charge that 40% to customers set to the PREMIUM Price Level.

Once you have the PREMIUM Price Level created and assigned to your items at 0% Mark Up/Margin then we are ready to run the script.  After running the script, update your customer’s Price Level to PREMIUM in GP and you are now charging a premium to those customers.

***Warning***

This script has code that will update data in the Dynamics GP database.  While this is not “illegal” it may or may not be supported by Microsoft.  I have not had any issues with this in our environment, but make sure you test this in your non production environment first, before deploying it to your production environment.

***End Warning***

The USE [TRAIN] tells the script to only execute on my database named TRAIN.  You will need to modify the [TRAIN] to be whatever your database name is.

I would recommend running this in your test environment first…

The SET @premium and SET @standard statements will allow you to replace PREMIUM and STANDARD Price Levels with the names of the Price Levels you actually use. 

The SET @percent allows you to define the premium precentage that you want to add to the customer. 

Just replace the text between the ‘ ‘ and this script will work for your company.

USE [TRAIN]

DECLARE @premium VARCHAR(15)
DECLARE @standard VARCHAR(15)
DECLARE @percent VARCHAR(2)

SET @premium=’SGOLF’
SET @standard=’GOLF’
SET @percent=’10’

update a
set a.uomprice = b.uomprice + @percent
from iv00108 a
inner join iv00108 b on a.itemnmbr = b.itemnmbr
where a.prclevel = @premium and b.prclevel = @standard

SQL View – Customers


The following view gives you customer information from the RM00101 and RM00103 (A/R table) tables.  It also includes any Notes for the customer. 

*Thanks to Rockfield who gave me the idea of adding the Notes field.

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

You can name the view whatever you want by change the [tspvCustomer] 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].[tspvCustomer]    Script Date: 06/23/2010 16:54:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE view [dbo].[tspvCustomer]
AS
select
    [Customer_Num]=RM00101.CUSTNMBR,
    [Customer_Name]=CUSTNAME,
    [Customer_Class]=CUSTCLAS,
    [Corporate_Customer_Num]=CAST(CPRCSTNM AS VARCHAR(25)),
    [Short_Name]=SHRTNAME,
    [Statement_Name]=STMTNAME,
    [Primary_Addr_Code]=ADRSCODE,
    [Primary_Bill_To_Addr_Code]=PRBTADCD,
    [Primary_Ship_To_Addr_Code]=PRSTADCD,
    [Statement_To_Addr_Code]=STADDRCD,
    [Sales_Person_ID]=SLPRSNID,
    [Sales_Territory]=SALSTERR,
    [Payment_Terms]=upper(PYMTRMID),
    [Shipping_Method]=SHIPMTHD,
    [Tax_Schedule]=taxschid,
    [Address1]=address1,
    [Address2]=address2,
    [Address3]=address3,
    [City]=city,
    [State]=state,
    [Zip]=zip,
    [Phone1]=phone1,
    [Phone2]=phone2,
    [Phone3]=phone3,
    [Fax]=fax,
    [Price_Level]= isnull(case when PRCLEVEL!=” then PRCLEVEL else (select PRCLEVEL from RM40101 where SETUPKEY=1) end,”),
    [User_Def_1]=USERDEF1,
    [User_Def_2]=USERDEF2,
    [Tax_Exempt_1] = TAXEXMT1,
    [Tax_Exempt_2] = TAXEXMT2,
    [Tax_Registration_Num] = TXRGNNUM,
    [Comment_1] = COMMENT1,
    [Comment_2] = COMMENT2,
    [IntegrationSource] = INTEGRATIONSOURCE,
    [Inactive] = cast(INACTIVE as bit),
    [On_Hold]=cast(HOLD as bit),
    [Customer_Note]=ISNULL(TXTFIELD, ”),
    [Currency_ID]= isnull(isnull(custCurr.CURNCYID, funcCurr.CURNCYID),”),
    [Currency_Dec]= isnull(isnull(custCurr.DECPLCUR, funcCurr.DECPLCUR),3) – 1,
    [Credit_Limit_Type]=case when crlmttyp=’0′ then ‘No Credit’ when crlmttyp=’1′ then ‘Unlimited’ when crlmttyp=’2′ then ‘Amount’ end,
    [Credit_Limit_Amnt]=crlmtamt,
    Last_Aged=LASTAGED,
    Balance=CUSTBLNC,
    Unapplied_Amount = (select sum(curtrxam) from RM20101 as ar (nolock) where ar.CUSTNMBR=RM00101.CUSTNMBR and RMDTYPAL=9 and VOIDSTTS=0 and curtrxam>0),
    Customer_Credit_Limit=CRLMTAMT,
    Last_Pay_Date=LASTPYDT,
    Last_Pay_Amt=LPYMTAMT,
    Last_Invoice_Date=LSTTRXDT,
    Last_Invoice_Amt=LSTTRXAM, 
    Last_Stmt_Date=LASTSTDT,
    Last_Stmt_Amt=LSTSTAMT,
    Life_Avg_Days=AVDTPLIF,
    Year_Avg_Days=AVGDTPYR,
    [Ship_Complete] = Cast(SHIPCOMPLETE as bit),
    USERDEF1, USERDEF2,
    [Created_Date]=creatddt,
    [Modified_Date]=modifdt,
    [Notes]=TXTFIELD
from RM00101 (nolock)
    left join SY03900 (nolock) on SY03900.NOTEINDX=RM00101.NOTEINDX
    left join RM00103 as cs (nolock) on cs.CUSTNMBR=RM00101.CUSTNMBR
    left join DYNAMICS..MC40200 as custCurr (nolock) on custCurr.CURNCYID=RM00101.CURNCYID
    left join MC40000 as currSetup (nolock) on 1=1
    left join DYNAMICS..MC40200 as funcCurr (nolock) on funcCurr.CURNCYID=currSetup.FUNLCURR

SQL Script – Default Item Class GL Accounts


Have you ever need to take a look at what your Item Class General Ledger accounts are, but didn’t want to go through them one at a time?  Here is a helpful little script that will allow you to see all Item Classes and what GL accounts are populated in the “Item Class Account Setup” screen.

It also needs noting that this view will only return 3 account segments (actnumbr_1, actnumbr_2, and actnumbr_3).  If you want or need to add more than the first 3 account segments, then you will need to add them here:

ltrim(rtrim(actnumbr_1)) + ‘-‘ + ltrim(rtrim(actnumbr_2)) + ‘-‘ + ltrim(rtrim(actnumbr_3)) ***add account here*** as Account_Num,

by adding + ‘-‘ + ltrim(rtrim(actnumbr_x)) for each account segment you wish to add in the ***add account here*** section above.

It should also be noted that the Sort column can be ignored.  It is only for sorting the idx column by the default sort in the Item Class Account Setup screen. 

This script looks a little different than most because it does not start with SELECT *….  Please copy and paste it into SQL Server Management Studio and execute on your main company database like normal (including the ; at the beginning).

;
WITH    CTE
          AS ( SELECT   itmclscd,
                        itmclsdc,
                        account,
                        idx
               FROM     iv40400 pvt UNPIVOT ( account FOR idx IN ( ivivindx , ivivofix , ivcogsix , ivslsidx , ivsldsix , ivslrnix , ivinusix , ivinsvix , ivdmgidx , ivvaridx , dpshpidx , purpvidx , uppvidx , ivretidx , asmvridx ) ) as unpvt)
    SELECT  [Item_Class]=a.itmclscd,
            [Class_Description]=a.itmclsdc,
            CASE WHEN a.idx = ‘ivivindx’ THEN ‘Inventory on Hand’
                 WHEN a.idx = ‘ivivofix’ THEN ‘Inventory Offset’
                 WHEN a.idx = ‘ivcogsix’ THEN ‘Cost of Good Sold’
                 WHEN a.idx = ‘ivslsidx’ THEN ‘Sales’
                 WHEN a.idx = ‘ivsldsix’ THEN ‘Markdowns’
                 WHEN a.idx = ‘ivslrnix’ THEN ‘Sales Returns’
                 WHEN a.idx = ‘ivinusix’ THEN ‘In Use’
                 WHEN a.idx = ‘ivinsvix’ THEN ‘In Service’
                 WHEN a.idx = ‘ivdmgidx’ THEN ‘Damaged’
                 WHEN a.idx = ‘ivvaridx’ THEN ‘Variance’
                 WHEN a.idx = ‘dpshpidx’ THEN ‘Drop Ship Items’
                 WHEN a.idx = ‘purpvidx’ THEN ‘Purchase Price Variance’
                 WHEN a.idx = ‘uppvidx’ THEN ‘Unrealized Purchase Price Var’
                 WHEN a.idx = ‘ivretidx’ THEN ‘Inventory Returns’
                 WHEN a.idx = ‘asmvridx’ THEN ‘Assembly Variance’
            END AS idx,
            CASE WHEN a.idx = ‘ivivindx’ THEN ‘a’
                 WHEN a.idx = ‘ivivofix’ THEN ‘b’
                 WHEN a.idx = ‘ivcogsix’ THEN ‘c’
                 WHEN a.idx = ‘ivslsidx’ THEN ‘d’
                 WHEN a.idx = ‘ivsldsix’ THEN ‘e’
                 WHEN a.idx = ‘ivslrnix’ THEN ‘f’
                 WHEN a.idx = ‘ivinusix’ THEN ‘g’
                 WHEN a.idx = ‘ivinsvix’ THEN ‘h’
                 WHEN a.idx = ‘ivdmgidx’ THEN ‘i’
                 WHEN a.idx = ‘ivvaridx’ THEN ‘j’
                 WHEN a.idx = ‘dpshpidx’ THEN ‘k’
                 WHEN a.idx = ‘purpvidx’ THEN ‘l’
                 WHEN a.idx = ‘uppvidx’ THEN ‘m’
                 WHEN a.idx = ‘ivretidx’ THEN ‘n’
                 WHEN a.idx = ‘asmvridx’ THEN ‘o’
            END AS sort,
            ( LTRIM(RTRIM(b.actnumbr_1)) + ‘-‘ + LTRIM(RTRIM(b.actnumbr_2))
            + ‘-‘ + LTRIM(RTRIM(b.actnumbr_3)) ) AS Account_Num,
            [Account_Description]=b.actdescr,
            [Notes]=c.txtfield
    FROM    CTE a
    LEFT JOIN gl00100 b ON a.account = b.actindx
    LEFT JOIN tspvGPNotes c ON b.noteindx = c.noteindx
    ORDER BY itmclscd,
            sort

SQL Views – Active Chart of Accounts


The following view returns all the active accounts in your General Ledger Chart of Accounts.  It also combines the full account number and account description.

It also needs noting that this view will only return 3 account segments (actnumbr_1, actnumbr_2, and actnumbr_3).  If you want or need to add more than the first 3 account segments, then you will need to add them here:

ltrim(rtrim(actnumbr_1)) + ‘-‘ + ltrim(rtrim(actnumbr_2)) + ‘-‘ + ltrim(rtrim(actnumbr_3)) ***add account here*** as Full_Account_Num,

by adding + ‘-‘ + ltrim(rtrim(actnumbr_x)) for each account segment you wish to add in the ***add account here*** section above.

You can name the view whatever you want by changing the [tspvActiveChartofAccounts] 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].[tspvActiveChartofAccounts]    Script Date: 06/17/2010 15:32:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[tspvActiveChartofAccounts]
AS 
select  creatddt as Created,
        modifdt as Last_Modified,
        ltrim(rtrim(actnumbr_1)) + ‘-‘ + ltrim(rtrim(actnumbr_2)) + ‘-‘
        + ltrim(rtrim(actnumbr_3)) as Full_Account_Num,
        actdescr as Account_Description,
        case when accttype = ‘1’ then ‘Posting Account’
             when accttype = ‘2’ then ‘Unit Account’
             when accttype = ‘3’ then ‘Posting Allocation Account’
             when accttype = ‘4’ then ‘Unit Allocation Account’
        end as Account_Type,
        case when pstngtyp = ‘0’ then ‘Balance Sheet’
             when pstngtyp = ‘1’ then ‘Profit and Loss’
        end as Posting_Type,
        case when fxdorvar = ‘0’ then ‘Fixed Allocation’
             when fxdorvar = ‘1’ then ‘Varialbe Allocation’
        end as Allocation_Type,
        txtfield as Notes,
        date1 as Note_Date
from    gl00100 a
left join sy03900 b on a.noteindx = b.noteindx
where   active = ‘1’

SQL Scripts – Clear Users and Activity Locks


Often times, when needing to perform maintenance in GP, your users will be logged out but GP still thinks they have documents open, batches they are working in, etc.  This script will clear out off the user activity locks as well as their logins.

***This will remove ALL users from being logged in.  Please make sure that users are indeed logged out before running this script.

Use DYNAMICS
Delete SY00800
Delete SY00801
Delete ACTIVITY

Use TEMPDB
Delete DEX_LOCK
Delete DEX_SESSION

Updated (6/23/2010) – David Musgrave has pointed out a better way to do this.  You can actually run this version while users are logged in and not affect their login status.  Check it out:  http://blogs.msdn.com/b/developingfordynamicsgp/archive/2008/12/03/releasing-stuck-batches-and-transactions-without-exiting-all-users.aspx

Thanks David!

SQL View – GP Notes


The following view just pulls the raw table data out of the SY03900 (notes) table and gives it a friendly name.  It just helps me when I can’t remember what table the notes are stored in :).

You can name the view whatever you want by changing the [tspvGPNotes] 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].[tspvGPNotes]    Script Date: 06/17/2010 15:11:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[tspvGPNotes1]
AS
SELECT * FROM sy03900

From The Community #7 – GP and VPN Connections


A question in the Dynamics GP forums was posted on the following issue:

We have a remote location which is currently accessing GP through terminal services.  We would like to have a more secure connection using a VPN connection.  Our partner has indicated that there may be issues with a VPN connection.  Has anyone tried it?  Is it working?  Is it supported?

Any help would be greatly appreciated.”

https://community.dynamics.com/forums/p/35405/61942.aspx

While it is not impossible to transmit data from a remote GP client to a headquartered GP server over a VPN connection, it just simply is not feasible.  The amount of data that is transmitted back and forth from a client to the server in GP is too great to expect any type of acceptable performance over a VPN connection (unless you are one of the lucky few to be chosen for Google’s Fiber).  The best scenario, and one that I practice in our business, is to have a secured VPN connection and run Remote Desktop (ala Terminal Server) over the VPN connection to a GP client located on the same network as the GP server.

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

SQL View – Sales Documents (History)


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

Follow

Get every new post delivered to your Inbox.

Join 28 other followers