SQL View for Microsoft Dynamics GP General Ledger Accounts

Microsoft Dynamics GP - SQL View for GL Account Review

Hello everyone!

It’s been far too long since my last blog post. I have lots of updates but I wanted to just get to the good stuff first.

I will be sharing a tip at the 2022 Community Summit and I wanted to give you all access to a SQL View that I found and improved. I had a customer with thousands and thousands of accounts that were setup when they first setup GP. Their partner took every possible combo of each segment value and created accounts. This created Balance Sheet accounts for each subdivision/site. Many accounts were never used. They had no easy way to find them! I found a SQL View on the Microsoft Communities site. I added some additional fields and it was perfect!

I have a contest and a survey to help me figure out what I should focus on next. Here’s a link to the survey: Training Dynamo Survey

To learn how to load SQL Views and use them with SmartList Designer or SmartList Builder, visit and subscribe to my YouTube Channel: youtube.com/c/trainingdynamo

--The SQL Script below started here  https://community.dynamics.com/gp/f/microsoft-dynamics-gp-forum/144447/gl-account-numbers--cleanup-of-accounts-not-used
--Amber Bell from Training Dynamo added the "Created Date" and "Account Type" and turned into a SQL View


CREATE VIEW _GLAccountLastUsed
AS


SELECT  B.ACTNUMST 'Account Number' ,
        A.ACTDESCR 'Account Description' ,
	 A.CREATDDT 'Created Date',
	 A.ACTINDX 'Account Index',
	  A.ACCTTYPE 'Account Type',
	  A.ACTIVE 'Active',
        CASE WHEN ISNULL(D.TRX_Date, 0) < A.CREATDDT THEN 'Yes'
             ELSE ''
        END AS NeverUsed ,
        ISNULL(D.TRX_Date, 0) AS 'Last Used' ,
        DATEDIFF(YY, ISNULL(D.TRX_Date, 0), GETDATE()) 'Not Used Since (In Years)' ,
        DATEDIFF(MM, ISNULL(D.TRX_Date, 0), GETDATE()) 'Not Used Since (In Months)' ,
        DATEDIFF(DD, ISNULL(D.TRX_Date, 0), GETDATE()) 'Not Used Since (In Days)'
FROM    dbo.GL00100 AS A
        LEFT OUTER JOIN dbo.GL00105 AS B ON A.ACTINDX = B.ACTINDX
        LEFT OUTER JOIN ( SELECT    ACTINDX ,
                                    MAX(TRX_Date) TRX_Date
                          FROM      ( SELECT    ACTINDX ,
                                                MAX(TRXDATE) AS TRX_Date
                                      FROM      dbo.GL20000
                                      GROUP BY  ACTINDX
                                      UNION ALL
                                      SELECT    ACTINDX ,
                                                MAX(TRXDATE) AS TRX_Date
                                      FROM      dbo.GL30000
                                      GROUP BY  ACTINDX
                                    ) AS C
                          GROUP BY  C.ACTINDX
                        ) AS D ON B.ACTINDX = D.ACTINDX

GO
GRANT SELECT ON _GLAccountLastUsed TO DYNGRP