Victoria Yudin

SQL view for current Receivables aging in Dynamics GP

January 25, 2012 by Victoria Yudin 8 Comments

I have created a variation on my view that shows all unapplied Receivables transactions to show customer aging in buckets. This is only looking at functional currency and will return data in summary, meaning one row per customer with a balance. I am hard-coding the aging using the default aging setup installed with GP, which is aging by [...]

featured, GP Reports code, GP SQL view, Receivables, SQL code

SQL view for customer yearly totals in Dynamics GP

January 9, 2012 by Victoria Yudin 3 Comments

As a follow up to my SQL view for vendor yearly totals, here is something similar for customers. I have combined sales less credits/returns in one column, please take a look at the notes above the code (on lines 5 through 16 below) to see more details about the logic I am using. The code [...]

Dynamics GP, GP Reports code, GP SQL view, Receivables, SQL code, year end close

SQL view for vendor yearly totals in Dynamics GP

January 4, 2012 by Victoria Yudin 6 Comments

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, [...]

Dynamics GP, GP Reports code, GP SQL view, Payables, SQL code, year end close

SQL view to show monthly totals for Dynamics GP Vendors

December 9, 2011 by Victoria Yudin 6 Comments

In response to a reader request, I have created this new view based on my Vendor Yearly Totals view that will show monthly totals for your Dynamics GP Vendors for all years. This view will show both the numbers and names of the months and assumes that you have 12 periods corresponding to the calendar [...]

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

SQL view for bank deposits and receipts in Dynamics GP

October 6, 2011 by Victoria Yudin 5 Comments

About a year ago I published a view for a Checkbook Register and have received some follow up requests asking for a way to show the receipt details. Primarily this request seems to come from the need to see the difference in dates between the receipts and the deposits for tracking down possible bank reconciliation issues. I didn’t want to [...]

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

SQL view for current Payables aging in Dynamics GP

September 30, 2011 by Victoria Yudin 25 Comments

Over the past few years I have had several requests for a summary current Payables aging report that can be easily exported into Excel. Yes, you can play with the Report Writer aging report to take out the headers and make it export to Excel, but sometimes there are other reasons for wanting a report outside [...]

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

SQL view for last sale date of item

March 31, 2011 by Victoria Yudin 11 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 [...]

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 7 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 /******************************************************************* [...]

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

Customer shipping addresses in Dynamics GP

February 18, 2011 by Victoria Yudin 1 Comment

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 [...]

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 4 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 [...]

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

SQL view for Dynamics GP Checkbook Register

October 14, 2010 by Victoria Yudin 21 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 [...]

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 9 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 [...]

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 25 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 [...]

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

SQL view for current Receivables aging in Dynamics GP

SQL view for customer yearly totals in Dynamics GP

SQL view for vendor yearly totals in Dynamics GP

SQL view to show monthly totals for Dynamics GP Vendors

SQL view for bank deposits and receipts in Dynamics GP

SQL view for current Payables aging in Dynamics GP

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

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

Search this blog:

My Book

Email Subscription

GP Reports Viewer Newsletter

RSS Feeds

Links

Forums

Blogroll

Pages

Categories

Hits since 9.17.2008

Archives

Follow “Victoria Yudin”