Tag Archives: SQL code

SQL view to show yearly totals for Dynamics GP Vendors

July 15, 2010 by Victoria Yudin 15 Comments

Below is a view that will show yearly totals for your Dynamics GP Vendors. It’s something we have used internally for a while, but I have recently gotten a few requests for it, so I cleaned it up and am sharing it. This will show yearly totals for calendar years. If you want to use fiscal years […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, Payables SQL code
, , ,

Updates to SQL view to show all GL distributions for AP transactions

June 25, 2010 by Victoria Yudin 9 Comments

I have made a number of updates to my SQL view to show all GL distributions for AP transactions since I first published it. Some of these were in response to comments asking for additional fields, others were added when I came across new data to test with. Rather than publish another post with the latest revisions, I have updated the […]

Continue reading →
Dynamics GP, GL SQL code, GP Reports code, GP SQL scripts, Payables SQL code
, , , ,

Awesome resources available from Mark Polino

June 17, 2010 by Victoria Yudin 0 Comments

Mark Polino, one of the very first Dynamics GP bloggers and someone who has personally inspired me often with his writing, has done some spring cleaning on his blog. There is a ton of great content, but I would like to point out these two great resources: SQL Scripts – one location with descriptions and links for many […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts
, , , ,

How to find all SQL tables with a column name

April 23, 2010 by Victoria Yudin 8 Comments

Often I need to find all the tables in Dynamics GP that have a particular column or search for all columns where I only have a partial column name. Over the years I have seen lots of different code to accomplish this, so this is nothing new. But I am asked about this enough that […]

Continue reading →
GP Reports code, SQL coding, SQL Server
,

SQL view for Fixed Assets depreciation in Dynamics GP

April 7, 2010 by Victoria Yudin 21 Comments

Below is a view I have put together to get the monthly Fixed Assets depreciation amounts in Dynamics GP. For more detail on the Fixed Assets module, please visit my Fixed Assets Tables page. If you’re looking for more SQL code, you can find it on my GP Reports page. ~~~~~ CREATE VIEW view_FA_Depreciation AS /******************************************************************* view_FA_Depreciation Created on […]

Continue reading →
Dynamics GP, Fixed Assets SQL code, GP Reports code, GP SQL scripts
, , ,

There’s a new blog in town

March 24, 2010 by Victoria Yudin 5 Comments

Ron Wilson, who you may have seen posting on various Dynamics GP forums and newsgroups, has recently started a blog called Real Life Dynamics User. Ron has been busy posting tips and tricks, sharing his experiences with Dynamics GP and posting useful SQL scripts and views. Keep up the great work, Ron! For the Office Space fans out there, […]

Continue reading →
Dynamics GP, GP Reports code, Miscellaneous
, ,

SQL view with AR apply detail

February 15, 2010 by Victoria Yudin 92 Comments

This has been a hot topic in the newsgroups lately and several people have asked me if I have any code for Receivables (AR) apply information in Dynamics GP. Below is a view that should help you get started if you’re building a report to show AR apply information in Dynamics GP. For more views like this, check out […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, Receivables SQL code
, , ,

SQL view with all GL distributions for AR transactions

February 10, 2010 by Victoria Yudin 48 Comments

As a corollary to my SQL view with all posted Receivables transactions, below is a view that will add all the General Ledger distributions to the AR transactions. I made a few changes to the original AR transactions view, primarily to remove some columns that are typically not needed and add underscores to the column names so […]

Continue reading →
Dynamics GP, GL SQL code, GP Reports code, GP SQL scripts, Receivables SQL code
, , , ,

SQL view for inventory items and dates

December 4, 2009 by Victoria Yudin 45 Comments

This view is a little bit of a twist on my previous SQL view for inventory quantities on hand. The results of this view will give you a list of your Microsoft Dynamics GP inventory items, current quantities and the last sales and purchase dates along with the vendor. For other SQL views on Dynamics GP data, please visit […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, Inventory SQL code
, , ,

SQL view for Payables payment apply detail in GP

October 22, 2009 by Victoria Yudin 108 Comments

I have published SQL views that return all posted Payables payments in Dynamics GP and Payables apply information to help identify what payments (or credits) paid off an invoice, however another frequent request is for a list of payments and their apply information.  Below is a view that returns a list of all posted payments with details on how […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, Payables SQL code
, , ,

SQL view for all posted Payables transactions in Dynamics GP

October 12, 2009 by Victoria Yudin 37 Comments

Payables Transactions have been a popular request for reports lately. Below is a view that lists all posted Payables transactions in functional currency. Voided transactions are not excluded. For other SQL views on Dynamics GP data, please visit my GP Reports page. ~~~~~ CREATE VIEW view_PM_Transactions AS /*************************************************************** view_PM_Transactions Created Oct 12 2009 by Victoria Yudin – Flexible […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, Payables SQL code
, , ,

SQL view for all posted Payables payments in Dynamics GP

September 11, 2009 by Victoria Yudin 13 Comments

This view is in response to more than a few inquiries I have had for a vendor check report. This view brings in all payments, not just checks, so if only checks are needed, add the following to the end of the WHERE clause: AND P.PYENTTYP = 0 For other SQL code, please visit my […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, Payables SQL code
, , ,

SQL view for all unapplied Receivables transactions in Dynamics GP

September 5, 2009 by Victoria Yudin 29 Comments

Here is a SQL view that will return all unapplied Receivables transactions in Dynamics GP. This will calculate how many days overdue unapplied transaction are. If something is not overdue, or if it is a credit transaction (payment, credit or return) the Days_Past_Due will be zero. For more SQL code like this, please visit my GP Reports and […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, Receivables SQL code
, , ,

SQL view for inventory quantities on hand

June 18, 2009 by Victoria Yudin 27 Comments

I haven’t had much time to blog lately as we have added functionality to our GP Reports Viewer product that allows seamless replacement of SOP reports in Dynamics GP and the amount of interest has been overwhelming.  However, I just needed to create a new SmartList to show current inventory quantities on hand with their costs and I […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, Inventory SQL code
, , ,

SQL view with all SOP line items in Dynamics GP

May 17, 2009 by Victoria Yudin 72 Comments

Here is a script that I have found useful on numerous occasions – it returns the lines items for all SOP (Sales Order Processing) transactions, both posted and unposted. This view will work with either SmartList Builder or Crystal Reports. For more scripts like this, take a look at my GP Reports page. ~~~~~  CREATE […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code
, , ,

SQL view with all posted Receivables transactions

April 24, 2009 by Victoria Yudin 30 Comments

Below is code to create a SQL view that returns all the posted Receivables transactions in Dynamics GP. I have had an abbreviated version of this view on my GP Reports page for a while, but I cleaned it up a bit and gave it some more friendly column names. I also added values for the commonly used […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, Receivables SQL code
, , ,

How to use a SQL view in SmartList Builder

April 20, 2009 by Victoria Yudin 43 Comments

Something I have been asked more than a few times is how to use a SQL view in SmartList Builder to create a new SmartList.  I am going to walk through this step by step using Dynamics GP 10.0 SP 3 and SQL Server 2005 SP 3:   STEP 1  –  create SQL view To start […]

Continue reading →
Dynamics GP, GP SQL scripts, SmartList Builder
, , , , ,

SQL view with security resource details for Dynamics GP

March 20, 2009 by Victoria Yudin 38 Comments

As a corollary to my view showing the Dynamics GP security roles and tasks assigned to users, I have created another view that adds the security resource details. Thank you very much to Tim Foster for pointing me to a newsgroup post by David Musgrave detailing how to populate the SY09400 table in SQL which holds […]

Continue reading →
Dynamics GP, GP 10.0, GP 2010, GP Reports code, GP SQL scripts, System/Setup SQL code
, , , , , ,
Newer Entries
Older Entries