Quick Tip: Copying small amounts of data in SQL Server quickly

David Meego - Click for blog homepageLast week, I was working on a Microsoft Dynamics GP update case where we had to re-upgrade one company from the previous service pack to the current service pack.

To allow Dynamics GP Utilities to re-upgrade one company there are 3 things we need to do.

  1. Restore the previous version of the Company database.
  2. Restore the rows from the pre update data into DYNAMICS.dbo.DB_Upgrade table for the company.
  3. Restore the rows from the pre update data into DYNAMICS.dbo.DU000020 table for the company.

Well, the first step of restoring a database is fairly easy and won’t be covered here.

The quick tip I will be covering is how to use the clipboard and cut & paste to copy data inside Microsoft SQL Server Management Studio.


This example is assuming that you have access to both the pre update DYNAMICS and the current DYNAMICS databases from Microsoft SQL Server Management Studio. This can achieved either by connecting to another SQL instance or by restoring the pre update DYNAMICS database backup into another database name.

Note: Before any direct editing of SQL data, it is recommended that you have backups of the database in case anything goes wrong.

The method is the same regardless of table, I will use the DU000020 table just because it has less columns and will be easier to create screenshots for.

In the source database (pre update DYNAMICS), right click on the dbo.DU000020 table and select Edit Top 200 Rows.

Then click the Show SQL Pane button to allow us to view and edit the SQL statement.

Change the SQL statement to add a where clause for the specific data we want to copy and then click the red exclamation mark to execute.

Now that the source data is displayed, click on the first row of the column to the left of the data (where the black right arrow is on the image above) to select the entire first row. The shift click the same button on the last row of the data (above the NULL row) to select the entire data set. Finally right click on the selected area and select Copy.

Now on the target database, locate the DYNAMICS.dbo.DU000020 table and select Edit Top 200 Rows and use the Show SQL Pane to add the where clause to the SQL statement and Execute the update code. Then select the data that you will be replacing using the same method discussed above (click and shift click on the left hand buttons).

This time right click and select Delete. It is important to remove the old data to avoid duplicate key errors when we paste in the new data.

Once the old data has been removed, click on the button to the left of the NULL line where the asterisk (*) is to select the entire line. Right click on the highlighted line and select Paste.

Voila, the data is pasted into the table. The trick that makes this work is selecting the entire NULL row before pasting.

Note: if you need more than 200 records, you can remove the TOP (200) restriction from the SQL select statement. However, be mindful that the clipboard is not the appropriate method to transfer gigabytes of data.

Hope you find this tip useful. It is definitely much simpler than using DTS (Data Transformation Services) or T-SQL to create an INSERT statement.

David

Visit site:
Quick Tip: Copying small amounts of data in SQL Server quickly

February 13, 2012 · David Musgrave · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 414

Microsoft Dynamics Mayhem | Victoria Yudin

Victoria Yudin Ramblings and musings of a Dynamics GP MVP. Home · Resources February 12, 2012 by Victoria Yudin · 0 Comments View all posts by Victoria Yudin…. RSS Feeds. RSS Feed RSS – Posts · RSS Feed RSS – Comments

See the original post:
Microsoft Dynamics Mayhem | Victoria Yudin

February 12, 2012 · Victoria Yudin · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 379

eConnect error – The stored procedure doesn’t exist

Patrick Roth - Click for blog homepageI ran across an odd eConnect issue a while back that I thought I’d share today.

In this case, the customer had recently updated to GP 2010 and updated their application to eConnect 2010 from the previous version that worked fine.

But now under GP 2010, when trying to run their eConnect integration it would fail with the error:

Microsoft.Dynamics.GP.eConnect.eConnectException: The stored procedure ‘taCreatePayrollBatchHeaderInsert’ doesn’t exist.
  at Microsoft.Dynamics.GP.eConnect.ServiceProxy.CreateTransactionEntity(String connectionString, String xml)
  at Microsoft.Dynamics.GP.eConnect.eConnectMethods.EntityImportImplementation(String connectionString, String sXML, Boolean isTransaction)
  at Microsoft.Dynamics.GP.eConnect.eConnectMethods.CreateTransactionEntity(String connectionString, String sXML)
  at DirectDocSenderDotNet.FrmDocSender.btnSend_Click(Object sender, EventArgs e)

Now this was interesting in that it wasn’t a standard business logic error and was a straight SQL exception instead.

Checking SQL, we verifified that indeed the proc DID exist and the DYNGRP did have permissions to it (even though this didn’t appear to be a permissions issue).  The “eConnect” user was assigned to the DYNGRP role.

For kicks, we used DBMaintenance.exe to recreate the Dynamics database procs again – still the same issue despite the fact that the proc was there.

We executed a SQL trace on the server during this process but that seemed normal – except for the error.

We then checked the User settings and the ISV noticed something unusual.

For some reason, the Default Schema for this user for the database was set to “eConnect” instead of “dbo”.

Now this made more sense – since the Default Schema for the database was not dbo, it couldn’t see any objects that were dbo.  We didn’t notice any other issues with this user because it was the eConnect user and not a GP user so there were no Dynamics errors to worry about.

From there, it was a simple matter of setting the user default schema to ‘dbo’ and the application worked correctly from there.

Best regards,
Patrick Roth
Dynamics GP Developer Support

 

 

View the original here:
eConnect error – The stored procedure doesn’t exist

February 10, 2012 · Patrick Roth · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 1,598

SQL view for sales quantities by item by year

Even since I published my view for sales by item by year I started receiving requests for the same type of view showing quantities instead of amounts. There are two ways of doing this, once from inventory and another from sales.  Hard to say which is the best, as I have seen arguments for both, but I prefer to do this from the SOP module. The view below makes a number of assumptions (listed in the view comments in green), and I am hard coding years from 2000 through 2012 as well as adding an overall total column at the end. You can easily change the years or add new ones by following the example in my code.

To see additional Dynamics GP SOP code please take look at this page. For additional GP reporting information and links, check out my GP Reports page.


create view view_Sales_Qty_by_Item_by_Year
as

--***********************************************************************************
--view_Sales_Qty_by_Item_by_Year
--Created Jan 23, 2012 by Victoria Yudin - Flexible Solutions, Inc.
--For updates see http://victoriayudin.com/gp-reports/
--Returns total sales quantities fulfilled (invoices - returns) for each item by year
--Only posted invoices and returns are included
--Quantity is calculated by multiplying by QTYBSUOM column in case other UofM's are
--     used on transations
--Voided transations are excluded
--Item Description is taken from Inventory Item Maintenance for all inventory items
--     and from SOP line items for non-inventory items
--***********************************************************************************

SELECT
D.ITEMNMBR Item_Number, D.Item_Description,
sum(case when year(D.DOCDATE) = 2000 then D.Qty else 0 end) as [2000_Qty],
sum(case when year(D.DOCDATE) = 2001 then D.Qty else 0 end) as [2001_Qty],
sum(case when year(D.DOCDATE) = 2002 then D.Qty else 0 end) as [2002_Qty],
sum(case when year(D.DOCDATE) = 2003 then D.Qty else 0 end) as [2003_Qty],
sum(case when year(D.DOCDATE) = 2004 then D.Qty else 0 end) as [2004_Qty],
sum(case when year(D.DOCDATE) = 2005 then D.Qty else 0 end) as [2005_Qty],
sum(case when year(D.DOCDATE) = 2006 then D.Qty else 0 end) as [2006_Qty],
sum(case when year(D.DOCDATE) = 2007 then D.Qty else 0 end) as [2007_Qty],
sum(case when year(D.DOCDATE) = 2008 then D.Qty else 0 end) as [2008_Qty],
sum(case when year(D.DOCDATE) = 2009 then D.Qty else 0 end) as [2009_Qty],
sum(case when year(D.DOCDATE) = 2010 then D.Qty else 0 end) as [2010_Qty],
sum(case when year(D.DOCDATE) = 2011 then D.Qty else 0 end) as [2011_Qty],
sum(case when year(D.DOCDATE) = 2012 then D.Qty else 0 end) as [2012_Qty],
sum(D.Qty) Total_Qty

FROM
(SELECT SH.DOCDATE, SD.ITEMNMBR,
 coalesce(I.ITEMDESC, SD.ITEMDESC) Item_Description,
 CASE SD.SOPTYPE
     WHEN 3 THEN SD.QTYFULFI*QTYBSUOM
     WHEN 4 THEN SD.QUANTITY*QTYBSUOM*-1
     END Qty
 FROM SOP30200 SH
 INNER JOIN
     SOP30300 SD
     ON SD.SOPNUMBE = SH.SOPNUMBE
     AND SD.SOPTYPE = SH.SOPTYPE
 LEFT OUTER JOIN
     IV00101 I
     ON I.ITEMNMBR = SD.ITEMNMBR
 WHERE SH.VOIDSTTS = 0
     AND SH.SOPTYPE IN (3,4)
     AND SD.ITEMNMBR not like 'XXXXXXXXXXXXXXX%') D

GROUP BY D.ITEMNMBR, D.Item_Description

GO
GRANT SELECT ON view_Sales_Qty_by_Item_by_Year TO DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

Filed under: Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code Tagged: featured, GP Reports code, Sales Order Processing, SQL code

See more here:
SQL view for sales quantities by item by year

January 23, 2012 · Victoria Yudin · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 640

How to configure SQL Profile Tracing using the Support Debugging Tool

David Meego - Click for blog homepageFollowing the release of Build 16 of the Support Debugging Tool, I want to take you through how to configure the SQL Profile Tracing functionality so you can gain the benefits of this awesome feature.

The Support Debugging Tool has always been able to capture a DEXSQL.LOG of the communication between the Dynamics GP Application and the SQL Server, but when a Stored Procedure is called on the SQL Server, we lose visibility of what is actually happening at the SQL Server. Now with SQL Profile Tracing we can see exactly what is happening at the server as well and match this information with other logs we have at the user interface, Dexterity application and SQL Server levels.

This information is available from the PDF User Guide that comes with the Support Debugging Tool in the archive file. The PDF should be installed along with the CNK file so that pressing F1 for help when on a Support Debugging Tool window will open the guide for you.  


Below are step by step instructions to configure the recommended settings for SQL Profile Tracing:

  1. On the SQL Server machine create a folder on a local drive for where the SQL Profile Trace files will be stored while they are being created. Note this local path for later.
     
  2. Share this local folder on the network, so that all Microsoft Dynamics GP users will have Full Control to the folder. Note this network UNC path for later.
     
  3. Create a user (for example: SQLTraceUser) to be used by SQL Profile Tracing system. The user can be a local user on the SQL Server or a domain user, but needs local Administrator rights on the SQL Server machine. It is recommended to set the password to not expire. Note the User ID and password for later.
     
  4. Log into Microsoft Dynamics as ‘sa’ or a user with similar permissions.
     
  5. Open the Support Debugging Tool main window (Microsoft Dynamics GP >> Tools >> Support Debugging Tool).
     
  6. From the Support Debugging Tool main window select Options >> Dex.ini Settings.
     

     
  7. From the main window select Options >> Administrator Settings to open the Administrator Settings window.
     
    NOTE: If the Administrator Settings choice is not available on the menu, then Advanced Mode is not yet enabled. Please enable Advanced Mode using the Enable Debugger Advanced Mode Features from the Debug tab of the Dex.ini Settings window.
     
  8. From the Administrator Settings window, on the General tab, click Edit SQL Profile Trace Settings to open the SQL Profile Trace Settings window.
     

     
  9. On the SQL Profile Trace Settings window, make sure Single User Authentication Mode is selected. In this mode only the single user created earlier will need permissions to create SQL Traces and the permissions for individual users do not need to be changed or elevated.
     

     
  10. Enter the user created previously and press tab. The system will then ask if you want to process the SQL Server Actions to enable the Authentication Mode, click Yes.
     

      
  11. As each step of the SQL Server actions needed to enable the Authentication Mode are completed a desktop alert will be displayed. You will also be asked for the password for the user for the Enable xp_cmdshell proxy account step.  The password is not validated at this time, so please ensure it is entered correctly.
     

     
    NOTE: To see the list of individual steps for enabling or disabling the Authentication Mode, click the Process Single User Mode SQL Server Action or Process Multi User Mode SQL Server Action button. You can select to manually run all of the steps or select individual steps from the list.
     
  12. You can change the Maximum Trace file size and Maximum number of Trace files if desired, or just leave the default values.
     

      
  13. Enter in the Local Path set up previously and press tab. The system will then ask if you want to create the SQL Profile Trace SQL Components, click Yes to create the stored Procedures in the DYNAMICS system database.
     

     
  14. Enter the UNC Network Path set up previously and press tab.
     

     
  15. Make sure the Copy SQL Profile Trace files to Debugger Settings location option is enabled.
     
  16. Click OK to save the settings and close the SQL Profile Trace window.
     
  17. On the Administrator Settings window, on the General tab, enable the Capture SQL Profile Trace option and set the desired Trace Mode (use Small, if unsure). This will enable SQL Profile Tracing for Manual Logging Mode and as the default value for Automatic Debugger Mode.
     

     
  18. Click OK to save the settings and close the Administrator Settings window.

That’s all folks.

Now when logging (both Manual Logging Mode and Automatic Debugger Mode) you have the option to capture a SQL Profile Trace as well as a DEXSQL.LOG, Dexterity Script Profile, Dexterity Script Log and a Macro recording.

David

See more here:
How to configure SQL Profile Tracing using the Support Debugging Tool

January 16, 2012 · David Musgrave · No Comments
Tags: , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 566

Support Debugging Tool Build 16 released

David Meego - Click for blog homepageA week into the New Year and no posts…. what’s happening? Read on to understand where all my spare time has been going….

After over four months of development, testing and documentation, I am thrilled to announce that build 16 of the Support Debugging Tool is now available for download from PartnerSource. This build has over 50 changes and focuses on functionality for logging and debugging issues with Microsoft Dynamics GP, while still having some improvements for the database or application Administrator.

A quick hint about how much is included in this build is that the User Guide PDF has grown from 161 pages to 193 pages.

Below is a summary of the changes made for releases 10.00.0016 and 11.00.0016, I have divided them into logical sections:

Fixes

  • Fixed Security Information Show Resources not displaying when a Security Role not assigned to any users was selected.
  • Fixed Logs, screenshots and table/record dumps being overwritten when new logs started in the same second by adding suffix to filename.
  • Update Trigger handling to allow Restore Value option to work for Text Field datatypes.
  • Fixed Security Privilege errors for non Dictionary Resource Security Objects not being logged when Security Profiler is running in the background.
  • Fixed Manual stopping of non-logging triggers using MBS_Trigger_Stop helper function not working.
  • Fixed Field Physical Name search in Resource Information window to handle Composite and Array fields correctly.
  • Fixed Tables Containing Field Lookup in Resource Explorer to handle Composite and Array fields.
  • Fixed Screenshot not being able to save image files to a UNC pathname. Files now saved to temp and moved to UNC Path.
  • Added check to see if email system is in use when emailing Screenshots as part of processing a trigger.

Enhancements

  • Updated Support Debugging Tool main window to move Logging Options to a sub window and just show current status.


 

  • Added optional display of form details when Security Privileges error is displayed to Administrator Settings.

  • Added Table restricted to Table Trigger Type to Automatic Debugger Mode to provide form restricted database triggers.
  • Added Focus Event with Table Trigger Type to Automatic Debugger Mode to provide access to a specified table buffer from the form.
  • Added Form Menu Trigger Type to Automatic Debugger Mode.
  • Added Minimize Debugger Log Entries for use with Automatic Debugger Mode Non-Logging Triggers to avoid writing to the Debugger Log.
  • Add option to Exclude Selected Users on Automatic Debugger Mode Triggers rather than include selected users.
  • Added option to control which logging modes are restarted when Automatic Debugger Mode trigger fires.
  • Added MBS_Logging_Start and MBS_Logging_Stop helper functions to programattically turn on and off Manual Logging Mode.
  • Updated ScreenShot’s System Status report with Debugger Version and Settings folder, SQL Session SPID, and fixed Data Folder Path.
  • Added HOMEDRIVE and HOMEPATH Environment Variables to ScreenShot’s System Summary Report.
  • Added Physical Memory Status to Screenshot’s System Status Report.
  • Added Database information about System and Company Databases to ScreenShot’s System Status report.
  • Added Dex.ini Setting MouseWheel to the Other tab of the Dex.ini Settings window.
  • Added MBS_Debug_LogAppDetails Dex.ini Setting to log details of the current application, available from Startup tab of Dex.ini Settings window.
  • Added Export to File or Email option to Tables Containing Field Lookup in Resources Explorer.
  • Added Export to File or Email option to Associated Tables Lookup in Resources Explorer.

Performance

  • Added performance enhancements to reading of the debugger.xml setup file using ctree caching files.
  • Improved performance for XML Export, Resource Information and Resource Explorer windows when displaying the number of records in tables.

Usability

  • Extended Automatic Debugger Mode Trigger Name field size from 30 to 60 characters and updated windows and reports to match.
  • Extended Runtime Execute Script Name field size from 30 to 60 characters and updated windows and reports to match.
  • Extended SQL Execute Script Name field size from 30 to 60 characters and updated windows and reports to match.
  • Extended XML Export Profile Name field size from 30 to 60 characters and updated windows and reports to match.
  • Added smart positioning for initial position of Support Debugging Tool main window based on primary screen resolution.
  • Added smart positioning for initial position of Automatic Debugger Mode Status window based on primary screen resolution.
  • Added Window Position Memory to Automatic Debugger Mode Status window using MBS_Debug_WinDebuggerStatus Dex.ini setting.
  • Added separate Auto Open setting to automatically open the Support Debugging Tool main window after login, previously was controled by Debugger Setup Mode setting.
  • Added checks for Logging Paths selection to check folder exists and has write access.
  • Added highlighting of Linked Table on the Associated Table Lookup for a Form in Resource Explorer.
  • Updated Automatic Debugger Mode Trigger Administration and Configuration Export/Import windows to show non-logging triggers with a different icon.

Features

  • Added Macro Recording Facility to Individual Logging, Manual Logging Mode and Automatic Debugger Mode.
  • Added check for MouseWheel=FALSE on version 10.0 and disable Macro Recording if Mouse Scroll Wheel not disabled.
  • Added SQL Profile Tracing Facility to Individual Logging, Manual Logging Mode and Automatic Debugger Mode.
  • Add features to Administrator Settings to configure, create and remove SQL Profile Tracing SQL Components.
 

  • Added Support for SQL Profile Tracing to specify Maximum Trace File Size and Maximum Number of Trace Files.
  • Added Version Control Checking for SQL Profile Tracing Stored Procedures to ensure SQL Tracing is only enabled when latest stored procedures are installed.
  • Added Check for SQL version to ensure that SQL Profile Tracing Events not compatible with SQL Server 2005 are excluded from the stored Procedures.
  • Added SQL Profile Trace SQL Components and permissions cleanup into un-install feature.
  • Added Detection and cleanup of stranded SQL Profile Traces on login.
  • Added SQL Profile Trace List window to display active traces and allow for stopping of stranded traces.

  • Added Trigger Administration Window for quick changes to delete, enable/disable or change start mode of triggers.

  • Added by User, by Database and by Role SQL Server Roles Views to right hand pane of Security Information Window.

 

Diagnostics

  • Reserve Tilde (~) prefix for Trigger IDs, Script IDs and Profile IDs for use by Microsoft Support.
  • Added Automation Functionality for use by Microsoft Support. Added Dex.ini settings MBS_Debug_Automate_File, MBS_Debug_Automate_Script, MBS_Debug_Automate_Status.
  • Added Additional User Confirmation Dialogs for Microsoft Support Diagnostics.

Downloads

Support Debugging Tool for Microsoft Dynamics GP 10.0 Secure Link

Support Debugging Tool for Microsoft Dynamics GP 2010 (v11.0) Secure Link

NOTE: The tool is posted on PartnerSource Secure Link and so can be downloaded by partners. If you are a customer, you will need to obtain the tool through your partner. Any feedback or questions about the tool will be handled via the Microsoft Dynamics GP Community Forum.

Please check out the Support Debugging Tool Portal page, your one-stop-shop for all things relating to the Support Debugging Tool.

Please post your feedback on what you think of this build and what you would like to see in the future.

Thanks to Mariano Gomez, Robert Cavill and especially Sivakumar Venkataraman and Allan Cahill for their efforts in testing all the new features to make sure they worked as desired. Thanks also to Kelly Youells for getting the files and PartnerSource pages updated.

David

Taken from:
Support Debugging Tool Build 16 released

January 5, 2012 · David Musgrave · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 528

SQL view for vendor yearly totals in Dynamics GP

There has been a lot of talk lately about the year end close Payables and Receivables. I feel like I have spent the last week or two justifying my reasoning for not needing to perform the year end close for Payables and Receivables to many of my customers and blog readers. (For more on this, please see my Year end close in Dynamics GP blog post.)

My main reasoning has always been that the only thing accomplished by the year end close is updating the ‘amounts since last close’ year-to-date and last-year totals and since that can be easily (and maybe even better?) accomplished by a custom report, why waste time on the year end close for these modules in GP? However, where is this custom report?

For Payables – you now have 2 choices.  I previously published this view that results in one row per vendor per year with the columns being the different possible totals GP tracks for vendors. This is often useful for comparing year to year information for one vendor at a time.

However, many people have asked to see this in columns representing years, so they can see all the vendor totals at the same time for all the years. That’s what you have below. I included separate columns for amounts billed and paid – please take a look at the notes (in green) for more details on the logic. The code below will give you 2007 through 2012 calendar years as well as ‘life-to-date’ totals. You can add or remove years as needed following my example.

I will be posting a similar view for Receivables in the next few days, so keep an eye out. For more Dynamics GP Payables code, take a look at my Payables SQL Views page. Or check out my GP Reports page for views in other modules and additional report writing and coding tips.

CREATE VIEW view_Vendor_Totals_by_Year
AS

-- ****************************************************************
-- Created Jan 4, 2012 by Victoria Yudin - Flexible Solutions, Inc.
-- For updates please check http://victoriayudin.com/gp-reports/
-- Includes all posted payables transactions in GP
-- Document dates and calendar years are used for groupings
-- Voided transactions are excluded
-- Billed amount adds Invoices, Finance Charges and Misc Charges
--      and subtracts Credit Memos and Returns
-- Only fucntional currency is shown
-- Note that Returns are assumed as 'on account', if this is not
--      typically the case, Returns should be excluded
-- ****************************************************************

SELECT
P.VENDORID Vendor ID,
V.VENDNAME Vendor Name,
sum(case when year(P.DOCDATE) = 2012 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) 2012 Billed,
sum(case when year(P.DOCDATE) = 2012 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) 2012 Paid,
sum(case when year(P.DOCDATE) = 2011 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) 2011 Billed,
sum(case when year(P.DOCDATE) = 2011 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) 2011 Paid,
sum(case when year(P.DOCDATE) = 2010 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) 2010 Billed,
sum(case when year(P.DOCDATE) = 2010 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) 2010 Paid,
sum(case when year(P.DOCDATE) = 2009 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) 2009 Billed,
sum(case when year(P.DOCDATE) = 2009 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) 2009 Paid,
sum(case when year(P.DOCDATE) = 2008 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) 2008 Billed,
sum(case when year(P.DOCDATE) = 2008 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) 2008 Paid,
sum(case when year(P.DOCDATE) = 2007 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) 2007 Billed,
sum(case when year(P.DOCDATE) = 2007 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) 2007 Paid,
sum(case when P.DOCTYPE < 6 then P.DOCAMNT else 0 end) Life Billed,
sum(case when P.DOCTYPE = 6 then P.DOCAMNT else 0 end) Life Paid

FROM --all posted payables transactions, exclude voids
 (SELECT VENDORID, DOCTYPE, DOCDATE, PSTGDATE,
 case when DOCTYPE in (4,5) then DOCAMNT *-1 else DOCAMNT end DOCAMNT
 FROM PM20000
 WHERE VOIDED = 0
 UNION ALL
 SELECT VENDORID, DOCTYPE, DOCDATE, PSTGDATE,
 case when DOCTYPE in (4,5) then DOCAMNT *-1 else DOCAMNT end DOCAMNT
 FROM PM30200
 WHERE VOIDED = 0) P

INNER JOIN -- vendor master
 PM00200 V
 ON V.VENDORID = P.VENDORID

GROUP BY P.VENDORID, V.VENDNAME

GO
GRANT SELECT ON view_Vendor_Totals_by_Year TO DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

Filed under: Dynamics GP, GP Reports code, GP SQL scripts, Payables SQL code Tagged: Dynamics GP, featured, GP Reports code, GP SQL view, Payables, SQL code, year end close

Read More:
SQL view for vendor yearly totals in Dynamics GP

January 4, 2012 · Victoria Yudin · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 708