SQL Scripts for Microsoft Dynamics GP: List GL Accounts With Notes

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 General Ledger Accounts with notes and which have been used on posted GL transactions.

/*
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  
		ACTNUMST AS Account_Num
        ,ACTDESCR AS Account_Description
		,CASE WHEN ACCTTYPE = '1' THEN 'Posting Account' 
             WHEN ACCTTYPE = '2' THEN 'Unit Account' 
        END AS Account_Type
		,CASE WHEN PSTNGTYP = '0' THEN 'Balance Sheet' 
             WHEN PSTNGTYP = '1' THEN 'Profit and Loss' 
        END AS Posting_Type
		,ACCATDSC AS Account_Category
		,CREATDDT AS Created
        ,MODIFDT AS Last_Modified
		,TXTFIELD AS Notes
        ,DATE1 AS Note_Date 
FROM
	GL00100 AS ['Account Master'] --Breakdown Account Master (GL00100)
INNER JOIN
	GL00105 AS ['Account Index Master'] --Account Index Master (GL00105)
		ON
			['Account Index Master'].ACTINDX = ['Account Master'].ACTINDX
INNER JOIN
	GL00102 --Account Category Master (GL00102)
		ON
			['Account Master'].ACCATNUM = GL00102.ACCATNUM
LEFT JOIN
	SY03900 --Record Notes Master (SY03900)
		ON
			['Account Master'].NOTEINDX = SY03900.NOTEINDX
WHERE
	EXISTS
		(
			SELECT
				ACTINDX 
			FROM
				GL20000 as ['Year-to-Date Transaction Open'] --Year-to-Date Transaction Open (GL20000)
			WHERE
				['Year-to-Date Transaction Open']ACTINDX = ['Account Master'].ACTINDX
            UNION ALL
				SELECT
					ACTINDX
				FROM
					GL30000 AS ['Account Transaction History'] --Account Transaction History (GL30000)
				WHERE
					['Account Transaction History'].ACTINDX = ['Account Master'].ACTINDX
		)