SQL Scripts for Microsoft Dynamics GP: List General Ledger Transactions (Excluding Year End Journals)

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script returns a list of posted General Ledger journals, excluding the BBF and P/L journals created by the year end closing process.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/

SELECT
	YEAR1 AS Trx_Year,
	TRXDATE AS Trx_Date,
	JRNENTRY AS Journal_Entry,
	ORTRXSRC AS Originating_TRX_Source,
	REFRENCE AS Reference,
	ORMSTRID AS Originating_Master_ID,
	ORMSTRNM AS Originating_Master_Name,
	ORDOCNUM AS Originating_Doc_Number,
	DEBITAMT AS Debit_Amount,
	CRDTAMNT AS Credit_Amount,
	ORDBTAMT AS Originating_Debit_Amt,
	ORCRDAMT AS Originating_Credit_Amt,
	ACTNUMST AS Account_Number,
	ACTDESCR AS Account_Description,
	ACCATDSC AS Account_Category,
	CURNCYID AS Currency_ID,
	XCHGRATE AS Exchange_Rate,
	EXCHDATE AS Exchange_Date,
	USWHPSTD AS User_Who_Posted
FROM
	(
		SELECT 
			ACTINDX, 
			OPENYEAR YEAR1, 
			TRXDATE, 
			JRNENTRY, 
			ORTRXSRC,
			REFRENCE, 
			ORDOCNUM, 
			ORMSTRID, 
			ORMSTRNM, 
			DEBITAMT,
			CRDTAMNT,
			ORDBTAMT, 
			ORCRDAMT, 
			CURNCYID, 
			XCHGRATE, 
			EXCHDATE, 
			USWHPSTD
		FROM
			GL20000 --Year-to-Date Transaction Open (GL20000)
		WHERE
			SOURCDOC NOT IN ('BBF','P/L')
		AND
			VOIDED = 0
		UNION ALL
			SELECT 
				ACTINDX, 
				HSTYEAR YEAR1, 
				TRXDATE, 
				JRNENTRY, 
				ORTRXSRC,
				REFRENCE, 
				ORDOCNUM, 
				ORMSTRID, 
				ORMSTRNM, 
				DEBITAMT,
				CRDTAMNT,
				ORDBTAMT, 
				ORCRDAMT, 
				CURNCYID, 
				XCHGRATE, 
				EXCHDATE, 
				USWHPSTD
			FROM
				GL30000 --Account Transaction History (GL30000)
			WHERE
				SOURCDOC NOT IN ('BBF','P/L')
			AND
				VOIDED = 0
	) ['Posted GL Transactions']
INNER JOIN
	GL00105 AS ['Account Index Master'] --Account Index Master (GL00105)
		ON
			['Posted GL Transactions'].ACTINDX = ['Account Index Master'].ACTINDX
INNER JOIN
	GL00100 AS ['Account Master'] --Breakdown Account Master (GL00100)
		ON
			['Posted GL Transactions'].ACTINDX = ['Account Master'].ACTINDX
INNER JOIN
	GL00102  AS ['Account Category Master'] --Account Category Master (GL00102)
		ON
			['Account Master'].ACCATNUM = ['Account Category Master'].ACCATNUM