Tag Archives: GP SQL view

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 all posted GL transactions in Dynamics GP

August 11, 2009 by Victoria Yudin 123 Comments

Below is a SQL script to create a view showing all posted General Ledger transactions in Dynamics GP. This has the common columns asked for on reports, but you can certainly add your own as needed. For additional SQL code, please visit my General Ledger SQL views page or the GP Reports page. A few notes on this view: Year-end […]

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

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

SQL view to show security roles and tasks in Dynamics GP

March 12, 2009 by Victoria Yudin 25 Comments

Dynamics GP 10.0 has brought about a drastic change in GP security. Not only is the security pessimistic now, so by default no permissions are granted, but setting up and administering security is quite different from what many of us are used to after working with previous versions of GP for many years. And, of course, reporting on […]

Continue reading →
Crystal Reports, Dynamics GP, GP 10.0, GP 2010, GP Reports code, GP SQL scripts, System/Setup SQL code
, , , , , , ,

SQL view to show items with SOP POP link

February 4, 2009 by Victoria Yudin 15 Comments

Here is a view to show all Sales Order Processing line items with the SOP-POP link in Dynamics GP.  You can use this with either SmartList Builder Crystal Reports, as I have made sure not to put any spaces in the column names.  I maintain a list of all the SQL code I’ve published on […]

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

SQL view to show customer pricing

January 7, 2009 by Victoria Yudin 18 Comments

Here is a view to show customer specific item pricing in Dynamics GP when using standard pricing.  This will work either with SmartList Builder or Crystal Reports as I have made sure not to put any spaces in the names.  If you are using SmartList Builder this means you may want to go down the […]

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

SQL view for Payables GL distributions in Dynamics GP

November 28, 2008 by Victoria Yudin 174 Comments

Here is a view that will return the General Ledger distributions for all posted or unposted payables transactions.  Unposted is also called ‘work’ and posted includes any open or historical transactions.  If you’re going to use this with SmartList Buidler, make sure to include the section at the end to grant permissions to DYNGRP. Disclaimer: I […]

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

SQL view for Crystal Reports to show open AP invoices from multiple companies in GP

November 10, 2008 by Victoria Yudin 0 Comments

In my experience Crystal Reports really doesn’t like it when you have spaces in your field names. I’ve gotten a request for a ‘Crystal’ version of the Multicompany Open AP view I posted up a few days ago, so here it is. Please follow the in-line directions in green to change the generic company names […]

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

SQL view to show open AP invoices from multiple companies in GP

November 8, 2008 by Victoria Yudin 0 Comments

If you have multiple companies in GP and want to create a quick view to show a combined list of all the open AP invoices, you can use the example below.  I am not doing anything fancy here, just hard coding the company names and database ID’s.  In my experience this is a set list for most […]

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

SQL view to show AP apply information in GP

October 22, 2008 by Victoria Yudin 108 Comments

Here is a corollary to the view I posted last week. The view below will show you what payments/credit memos were applied to payables transactions in GP. If you have SmartList Builder you can easily incorporate this SQL view into a new SmartList.  [Update: You can also use this in Crystal with the changes I made on […]

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

SQL view to show what check paid a Purchase Order

October 17, 2008 by Victoria Yudin 14 Comments

There is no easy way in Dynamics GP to see what check or credit memo was applied to a particular Purchase Order. Here is a SQL view that can be used in SmartList Builder or any other reporting tool you choose to see this information. For other SQL scripts please visit my GP Reports Page. ~~~~~ […]

Continue reading →
GP Reports code, GP SQL scripts, Payables SQL code
, , , ,
Newer Entries