Tag Archives: GP SQL view

SQL view for unposted checks in Dynamics GP

August 29, 2017 by Victoria Yudin 11 Comments

I have been asked more than a few times for a script that shows uposted checks and their apply detail. This can be helpful to replace the Check Batch Edit List report, which is universally hated by all GP users I have spoken to. create view view_unposted_check_batch as — ~ ~ ~ ~ ~ ~ […]

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

SQL view for total payments to 1099 vendors in the prior year

January 20, 2015 by Victoria Yudin 8 Comments

We received several requests just yesterday for a listing of total vendor payments in the prior calendar year. Below is a view that will show the total payments in the prior year for all 1099 vendors. This is not necessarily meant to match the report that comes out from the Print 1099 window, rather this is total payments for […]

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

SQL view for easier General Ledger transaction searches in Dynamics GP

September 4, 2014 by Victoria Yudin 10 Comments

Just about every Dynamics GP customer I have worked with, at one time or another, has had occasion to search the General Ledger for a particular dollar amount. Usually, I use the Account Transactions SmartList for this and search for Debit Amount = XXX or Credit Amount = XXX (using the Match 1 or More search […]

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

SQL view for Dynamics GP vendors with last activity date

July 17, 2014 by Victoria Yudin 13 Comments

I have seen many requests recently for a way to see what Dynamics GP vendors have had no recent activity. Since ‘recent’ can mean different things, I thought I would put together a view that shows the last activity date for each vendor. Once you have this you can easily filter for whatever date range you […]

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

SQL view for Dynamics GP open year GL trial balance with month end balances

July 9, 2014 by Victoria Yudin 30 Comments

Based on a reader’s request, I have created a variation on my SQL view for Dynamics GP open year GL trial balance. This version will show you the month end balances instead of the net change for each month. Here is a sample of the results (please click on the image to see it bigger): […]

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

SQL view for Dynamics GP open year GL trial balance

March 17, 2014 by Victoria Yudin 29 Comments

General Ledger trial balances have been a popular request lately. The view below will return a General Ledger trial balance showing the monthly net change for the first open year in your Dynamics GP. I’ve hard-coded month names to be the calendar year, if you are on a different fiscal year, you will need to […]

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

SQL view for SOP sales by customer by month

June 7, 2013 by Victoria Yudin 49 Comments

Someone asked me for code to get monthly SOP sales and I could have sworn I already had that. But I searched and didn’t come up with anything, so here is a new view to show SOP sales (the total of SOP invoices less returns) by month. As usual, I am making a few assumptions (listed […]

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

SQL view for current Receivables aging detail in Dynamics GP

May 1, 2013 by Victoria Yudin 54 Comments

Another reader request brings us this new script. Below is a view for current Receivables aging in detail. This code is only looking at functional currency and will return one row per open (unpaid) receivables transaction. I am hard-coding the aging using the following buckets and aging by due date: Current 0 to 30 Days 31 to […]

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

SQL view for current Payables aging detail in Dynamics GP

April 24, 2013 by Victoria Yudin 35 Comments

I have received a few requests for a detailed version my Current Payables Aging Summary view. Here it is. This code is only looking at functional currency and will return one row per open (unpaid) payables transaction. I am hard-coding the aging using the default aging setup installed with GP, which is aging by due date and using the […]

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

Dynamics GP SOP line items with serial numbers and comments

June 4, 2012 by Victoria Yudin 17 Comments

Based on some reader requests, this new view is a combination of  my All SOP Line Items view and the recently posted script showing how to get a concatenated list of serial numbers in SQL. This new view will return all posted (history) or unposted (open) Sales Order Processing line items with the associated serial numbers (or lot numbers). […]

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

SQL view for sales quantities by customer by item by year

March 9, 2012 by Victoria Yudin 12 Comments

Seems like no many how many variations of sales reports there are, people will always want more. I recently had a request for something similar to my view for sales quantities by item by year, but also adding in the customer.  Below is code for a view to accomplish this. This view makes a number of […]

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

SQL view for current Receivables aging in Dynamics GP

January 25, 2012 by Victoria Yudin 71 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 […]

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

SQL view for customer yearly totals in Dynamics GP

January 9, 2012 by Victoria Yudin 35 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 […]

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

SQL view for vendor yearly totals in Dynamics GP

January 4, 2012 by Victoria Yudin 20 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, […]

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

SQL view to show monthly totals for Dynamics GP Vendors

December 9, 2011 by Victoria Yudin 19 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 […]

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

SQL view for bank deposits and receipts in Dynamics GP

October 6, 2011 by Victoria Yudin 11 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 […]

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

SQL view for current Payables aging in Dynamics GP

September 30, 2011 by Victoria Yudin 51 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 […]

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

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
, , ,
Older Entries