Archive | Receivables SQL code RSS feed for this section

SQL stored procedure for all open AR transactions by customer name

November 24, 2014 by Victoria Yudin 7 Comments

A little variation on my Unapplied AR Transactions view, this SQL stored procedure will return all open (unapplied) receivables transactions for customers with a name containing what is supplied as the parameter. In case you’re wondering why I am all of a sudden posting a stored procedure, I am going to follow this up later […]

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

Identify what modules your Dynamics GP sales data originates in

April 29, 2014 by Victoria Yudin 1 Comment

I have posted a lot of queries for getting sales totals from the Sales Order Processing module. However, many companies also enter transactions directly in the RM (Receivables Management) module. In that case, if you need to see the data from both of these sources, you may need to take a slightly different approach to […]

Continue reading →
Dynamics GP, Receivables SQL code, SOP SQL code
, , ,

SQL script for comparing voids between RM and SOP in Dynamics GP

April 28, 2014 by Victoria Yudin 1 Comment

How do we find transactions that were voided in the Receivables Management (RM) module, but not in Sales Order Processing (SOP)? This question has come up few a times on newsgroups and also with our customers. Most recently there was a question on the Dynamics GP Community Forum about this. The script below is a slightly cleaned […]

Continue reading →
Dynamics GP, Receivables SQL code, 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 commissions details in Dynamics GP

July 19, 2012 by Victoria Yudin 14 Comments

Commissions are typically difficult to create generic reports for, as many companies have unique commission structures. We find that out-of-the-box functionality in GP does not really work for most companies and often the commissions are recorded in ways other than what was intended. That said, if you find that you are using the commissions functionality […]

Continue reading →
Dynamics GP, GP Reports code, GP SQL scripts, Receivables 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
, , , , ,

Customer shipping addresses in Dynamics GP

February 18, 2011 by Victoria Yudin 14 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
, , , ,

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