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
)
