Active Accounts in Dynamics GP with no transactions

So the other day I was asked by my controller if I could provide a list of accounts in Dynamics GP that where active but did not have any transactions against the account.  So the first thing I did was open GP to see if this was something that I could produce in SmartList which was a no go.  I then browsed through some reports in GP but nothing gave me the information I was looking for.  So I did what any reasonable person would do and wrote a SQL script that will pull this information back.  Below is the code I will put a link to each to the table definitions.

SELECT ACTINDX, ACTNUMST FROM GL00105
WHERE ACTINDX NOT IN
(SELECT DISTINCT ACTINDX FROM GL20000)
AND ACTINDX NOT IN
(SELECT ACTINDX FROM GL00100 WHERE ACTIVE=0)

 

No alternatively if you wanted to see this information for accounts that are inactive it would look like this. You simply set the where clause to read ACTIVE=1.

SELECT ACTINDX, ACTNUMST FROM GL00105
WHERE ACTINDX NOT IN
(SELECT DISTINCT ACTINDX FROM GL20000)
AND ACTINDX NOT IN
(SELECT ACTINDX FROM GL00100 WHERE ACTIVE=1)

  1. GL00105 – Link
  2. GL20000 – Link
  3. GL00100 – Link

 

I also want to take a moment and say what an absolutely amazing tool Dynamics GP Table Reference is.  You may find their website by browsing out to https://www.gptablereference.com/

jcox@hexoma.com

Add a Comment

Your email address will not be published. Required fields are marked *