SQL view for all GL transactions in Dynamics GP

[as_radwan]

I have been asked a few times now to add unposted General Ledger transactions to my Posted GL Transactions view. Here it is. This view also excludes voided transactions and introduces new columns for transaction status and source doc.

To see more information about GL tables in Dynamics GP, visit my GL Tables page. For more Dynamics GP SQL code, take a look at my GP Reports page.

~~~~~

CREATE VIEW view_GL_Trx
AS


/******************************************************************
view_GL_Trx
Created Apr 27, 2011 by Victoria Yudin - Flexible Solutions, Inc.
For updates see http://victoriayudin.com/gp-reports/
- Returns all lines for all GL transactions
- Excludes year-end closing entries
- Excludes voided transactions
- Returns Functional amounts only

Tables used: GL10000
- Work Trx header GL10001
- Work Trx detail GL20000
- Open Year Trx GL30000
- Historical Trx GL00100
- Account Master GL00105
- Account Index Master
******************************************************************/

SELECT
Trx_Status,
TRXDATE Trx_Date,
JRNENTRY Journal_Entry,
ACTNUMST Account_Number,
ACTDESCR Account_Description,
DEBITAMT Debit_Amount,
CRDTAMNT Credit_Amount,
REFRENCE Reference,
SOURCDOC Source_Document,
ORTRXSRC Originating_TRX_Source,
ORMSTRID Originating_Master_ID,
ORMSTRNM Originating_Master_Name,
ORDOCNUM Originating_Doc_Number,
CURNCYID Currency_ID

FROM
(SELECT ACTINDX, TRXDATE, SOURCDOC, JRNENTRY, ORTRXSRC, REFRENCE,
     ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT, CURNCYID,
     Trx_Status = 'Open'
FROM 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,
     Trx_Status = 'History'
FROM 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, Trx_Status = 'Work'
FROM GL10000 GH
   INNER JOIN GL10001 GD
   ON GH.JRNENTRY = GD.JRNENTRY
     WHERE VOIDED = 0) GL

INNER JOIN GL00105 GM
ON GL.ACTINDX = GM.ACTINDX

INNER JOIN GL00100 GA
ON GL.ACTINDX = GA.ACTINDX

/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/ 
GO
GRANT SELECT ON view_GL_Trx TO DYNGRP

~~~~~

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone

BY VICTORIA YUDIN COMMENTS OFF
http://victoriayudin.com/2011/04/27/...n-dynamics-gp/