SQL view for all posted GL transactions in Dynamics GP

Below is a SQL script to create a view showing all posted General Ledger transactions in Dynamics GP. This has the common columns asked for on reports, but you can certainly add your own as needed. For additional SQL code, please visit my GP Reports page. A few notes on this view:

• Year-end close transactions are excluded
• Only functional amounts are brought in
• Unposted transactions will not be shown

~~~~~

CREATE VIEW view_Posted_GL_Trx
AS

/*******************************************************************
view_Posted_GL_Trx
Created Aug 11, 2009 by Victoria Yudin - Flexible Solutions, Inc.
For updates see http://victoriayudin.com/gp-reports/
- Returns all lines for posted GL transactions
- Excludes year-end closing entries
- Returns Functional amounts only
GL20000 - Open Year Trx
GL30000 - Historical Trx
GL00100 - Account Master
GL00105 - Account Index Master
*******************************************************************/

SELECT	YEAR1 Trx_Year,
	TRXDATE Trx_Date,
	JRNENTRY Journal_Entry,
	ORTRXSRC Originating_TRX_Source,
	REFRENCE Reference,
	ORMSTRID Originating_Master_ID,
	ORMSTRNM Originating_Master_Name,
	ORDOCNUM Originating_Doc_Number,
	DEBITAMT Debit_Amount,
	CRDTAMNT Credit_Amount,
	ACTNUMST Account_Number,
	ACTDESCR Account_Description,
	CURNCYID Currency_ID
FROM
(SELECT ACTINDX, OPENYEAR YEAR1, TRXDATE,
	JRNENTRY, ORTRXSRC, REFRENCE,
	ORDOCNUM, ORMSTRID, ORMSTRNM,
	DEBITAMT, CRDTAMNT, CURNCYID
 FROM	GL20000
 WHERE	SOURCDOC not in ('BBF','P/L')
 UNION ALL
 SELECT ACTINDX, HSTYEAR YEAR1, TRXDATE,
	JRNENTRY, ORTRXSRC, REFRENCE,
	ORDOCNUM, ORMSTRID, ORMSTRNM,
	DEBITAMT, CRDTAMNT,CURNCYID
 FROM	GL30000
 WHERE   SOURCDOC not in ('BBF','P/L')) 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_Posted_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

Posted in Crystal Reports, Dynamics GP, GP Reports, GP SQL scripts, SmartList Builder Tagged: Crystal Reports, Dynamics GP, General Ledger, GP Reports, GP SQL view, SmartList Builder, SQL code