Dynamics GP - SQL View - All Posted and Unposted GL Transactions Detail and Summary by period and account

See Original view here
http://victoriayudin.com/2011/04/27/sql-view-for-all-gl-transactions-in-dynamics-gp/


--All GL Details
----------------------------------------------------------------------------------------------------------
SELECT     GL.Trx_Status, GL.TRXDATE AS Trx_Date, GL.JRNENTRY AS Journal_Entry, GM.ACTNUMST AS Account_Number,
                      GA.ACTDESCR AS Account_Description, GL.DEBITAMT AS Debit_Amount, GL.CRDTAMNT AS Credit_Amount, GL.REFRENCE AS Reference,
                      GL.SOURCDOC AS Source_Document, GL.ORTRXSRC AS Originating_TRX_Source, GL.ORMSTRID AS Originating_Master_ID,
                      GL.ORMSTRNM AS Originating_Master_Name, GL.ORDOCNUM AS Originating_Doc_Number, GL.CURNCYID AS Currency_ID,
                      GL.LASTUSER AS Last_User, GL.USWHPSTD AS User_Who_Posted, GL.OPENYEAR AS GLYear, GL.PERIODID AS GLPeriod
FROM         (SELECT     ACTINDX, TRXDATE, SOURCDOC, JRNENTRY, ORTRXSRC, REFRENCE, ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT,
                                              CURNCYID, 'Open' AS Trx_Status, LASTUSER, USWHPSTD, OPENYEAR, PERIODID
                       FROM          dbo.GL20000
                       WHERE      (SOURCDOC NOT IN ('BBF', 'P/L')) AND (VOIDED = 0)
                       UNION ALL
                       SELECT     ACTINDX, TRXDATE, SOURCDOC, JRNENTRY, ORTRXSRC, REFRENCE, ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT,
                                             CURNCYID, 'History' AS Trx_Status, LASTUSER, USWHPSTD, HSTYEAR, PERIODID
                       FROM         dbo.GL30000
                       WHERE     (SOURCDOC NOT IN ('BBF', 'P/L')) AND (VOIDED = 0)
                       UNION ALL
                       SELECT     GD.ACTINDX, GH.TRXDATE, GH.SOURCDOC, GH.JRNENTRY, GH.ORTRXSRC, GH.REFRENCE, GD.ORDOCNUM, GD.ORMSTRID,
                                             GD.ORMSTRNM, GD.DEBITAMT, GD.CRDTAMNT, GH.CURNCYID, 'Work' AS Trx_Status, GH.LASTUSER, GH.USWHPSTD, GH.OPENYEAR,
                                             GH.PERIODID
                       FROM         dbo.GL10000 AS GH INNER JOIN
                                             dbo.GL10001 AS GD ON GH.JRNENTRY = GD.JRNENTRY
                       WHERE     (GH.VOIDED = 0)) AS GL INNER JOIN
                      dbo.GL00105 AS GM ON GL.ACTINDX = GM.ACTINDX INNER JOIN
                      dbo.GL00100 AS GA ON GL.ACTINDX = GA.ACTINDX

--All GL Summary
---------------------------------------------------------------------------------------------------------------------
SELECT     GLYear, GLPeriod, Account_Number, Account_Description, SUM(Debit_Amount) AS Debit, SUM(Credit_Amount) AS Credit,
                      SUM(Debit_Amount - Credit_Amount) AS Total
FROM         dbo.BI_GL_Trx
GROUP BY Account_Number, Account_Description, GLYear, GLPeriod