Victoria Yudin

SQL view for last sale date of item

March 31, 2011 by Victoria Yudin 35 Comments

An interesting question came up in response to my last SQL view showing the last sale by customer and item. How do you show the latest sale of an item and who the customer was for that particular sale? This is a bit more difficult, as you have to make two passes through the data, first […]

Continue reading →

Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code

GP Reports code, GP SQL view, Sales Order Processing, SQL code

SQL view for last sale by customer and item

March 29, 2011 by Victoria Yudin 18 Comments

When was the last time a particular customer bought a particular item? Here is a little view that will tell you. For other Dynamics GP views and reporting tips, take a look at my GP Reports page. Or check out the SOP Tables page for more details about reporting on SOP data. ~~~~~ CREATE VIEW view_Last_Sale_By_Customer_Item AS /******************************************************************* […]

Continue reading →

Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code

GP Reports code, GP SQL view, Sales Order Processing, SQL code

Customer shipping addresses in Dynamics GP

February 18, 2011 by Victoria Yudin 16 Comments

Need to see a list of all the shipping addresses for your Dynamics GP customers? You can use the view below which pulls out the ship to address and the associated tax schedule, shipping method , salesperson, etc. To see other SQL views for Dynamics GP receivables data, take a look at the Receivables SQL Views page. For […]

Continue reading →

Dynamics GP, GP Reports code, GP SQL scripts, Receivables SQL code

Dynamics GP, GP Reports code, GP SQL view, Receivables, SQL code

Yearly Fixed Assets depreciation totals in Dynamics GP

February 15, 2011 by Victoria Yudin 13 Comments

Around this time of year we get a lot of questions about closing the year for the Dynamics GP Fixed Assets module, since you cannot run January depreciation until you close the prior year. And right after we start getting requests for reports of prior year depreciation. Here is a view that shows year to date, last year […]

Continue reading →

Dynamics GP, Fixed Assets SQL code, GP Reports code, GP SQL scripts

Dynamics GP, Fixed Assets, GP Reports code, GP SQL view, SQL code

Dynamics GP inventory by location with dates and sites

January 29, 2011 by Victoria Yudin 13 Comments

In response to a request from a reader, I have created a new version of the Inventory with Dates script to show the same information by Site ID. I also added a Quantity on Order column. You can see more Dynamics GP Inventory scripts here. Or visit my GP Reports page for links to additional GP […]

Continue reading →

Dynamics GP, GP Reports code, GP SQL scripts, Inventory SQL code

Dynamics GP, GP Reports code, Inventory, SQL code

Change GP fiscal period names using SQL Server

January 27, 2011 by Victoria Yudin 15 Comments

Every time I create a new fiscal year in Dynamics GP or show customers how to do it, I get a little frustrated that I have to manually rename all the periods. Some users may not care that instead of January GP displays Period 1, but I don’t think that is very user friendly. So, for more years […]

Continue reading →

Dynamics GP, GP 10.0, GP 2010, GP 2013, GP 8.0, GP 9.0, GP SQL scripts, System/Setup SQL code

Dynamics GP, SQL code

SQL view for Dynamics GP Checkbook Register

October 14, 2010 by Victoria Yudin 30 Comments

The view below will return all Checkbook transactions for Dynamics GP. It does not show details for the deposits because I wanted to keep this to one line per transaction, as it appears on the Checkbook Register in GP. To add deposit detail to you report, you can join table CM20300 on CM20200.CMTrxNum = CM20300.depositnumber. You […]

Continue reading →

Bank Rec SQL code, Dynamics GP, GP Reports code, GP SQL scripts

Bank Reconciliation, GP Reports code, GP SQL view, SQL code

SQL view for Payables apply detail and GL distributions in Dynamics GP

September 23, 2010 by Victoria Yudin 37 Comments

Who doesn’t need yet another view for Payables transactions in Dynamics GP? 🙂 The view below is a combination of my Payment Apply Detail and GL Distributions for AP Transactions views. It lists all Payments, then shows the transactions they were applied to and the GL distributions of those applied to transactions. You can find other Dynamics GP Payables […]

Continue reading →

Dynamics GP, GP Reports code, GP SQL scripts, Payables SQL code

GP Reports code, GP SQL view, Payables, SQL code

SQL view for Payables invoices originating from POP in Dynamics GP

September 1, 2010 by Victoria Yudin 40 Comments

This Dynamics GP SQL view originated from a request on the Dynamics GP customer forum, but is also something that I can see being useful in a variety of situations. It returns all posted Payables invoices that came from the Purchase Order Processing module with details of the items that were received on each invoice as well […]

Continue reading →

Dynamics GP, GL SQL code, GP Reports code, GP SQL scripts, Payables SQL code, POP SQL code

General Ledger, GP Reports code, GP SQL view, Payables, Purchase Order Processing, SQL code

SQL view for asset account numbers in Dynamics GP

August 10, 2010 by Victoria Yudin 4 Comments

Want to see all the account numbers set up for all your Fixed Assets? You can do this in SmartList, but SmartList is sometimes limiting when you want to do complex searches. Here is a view that will return all the account numbers assigned for each asset in Dynamics GP. ~~~~~ CREATE VIEW view_Asset_Accounts AS /******************************************************************* view_Asset_Accounts […]

Continue reading →

Dynamics GP, Fixed Assets SQL code, GP Reports code, GP SQL scripts

Fixed Assets, GP Reports code, GP SQL view, SQL code

SQL view for Inventory Price Levels in Dynamics GP

August 9, 2010 by Victoria Yudin 16 Comments

When you are creating prices for different price levels in Dynamics GP, there is no easy way to see these, or to see all the prices for a particular price level. Below is a view that can help with this. ~~~~~ CREATE VIEW view_Inventory_Price_Levels AS /******************************************************************* view_Inventory_Price_Levels Created on Aug 9, 2010 by Victoria Yudin – […]

Continue reading →

Dynamics GP, GP Reports code, GP SQL scripts, Inventory SQL code

GP Reports code, GP SQL view, Inventory, 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

GP Reports code, GP SQL view, 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

General Ledger, GP Reports code, GP SQL view, 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

Dynamics GP, GP Reports code, GP SQL view, GP table information, SQL code

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

Fixed Assets, GP Reports code, GP SQL view, SQL code

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

GP Reports code, GP SQL view, 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

General Ledger, GP Reports code, GP SQL view, 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

Dynamics GP, GP Reports code, Inventory, SQL code

	Victoria Yudin on Sales Transaction Entry GL dis…
	Matthew on Sales Transaction Entry GL dis…
	Victoria Yudin on SQL view to show AP apply info…
	Kevin Mackol on SQL view to show AP apply info…
	Victoria Yudin on Company/System Tables

SQL view for last sale date of item

SQL view for last sale by customer and item

Customer shipping addresses in Dynamics GP

Yearly Fixed Assets depreciation totals in Dynamics GP

Dynamics GP inventory by location with dates and sites

Change GP fiscal period names using SQL Server

SQL view for Dynamics GP Checkbook Register

SQL view for Payables apply detail and GL distributions in Dynamics GP

SQL view for Payables invoices originating from POP in Dynamics GP

SQL view for asset account numbers in Dynamics GP

SQL view for Inventory Price Levels in Dynamics GP

SQL view to show yearly totals for Dynamics GP Vendors

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

Awesome resources available from Mark Polino

SQL view for Fixed Assets depreciation in Dynamics GP

SQL view with AR apply detail

SQL view with all GL distributions for AR transactions

SQL view for inventory items and dates

Search this blog:

My Last Book

My Previous Book

Email Subscription

GP Reports Viewer Newsletter

RSS Feeds

Links

Forums

Blogroll

Pages

Most Clicked Links

Recent Blog Comments

Categories

Hits since 9.17.2008

Archives