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 transactions between the specified years.
/*
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).
*/
DECLARE @StartYear AS INTEGER = 2018
DECLARE @EndYear AS INTEGER = 2023
SELECT
['Year-to-Date Transaction Open'].OPENYEAR AS 'Year'
,['Year-to-Date Transaction Open'].JRNENTRY AS 'Journal No'
,['Year-to-Date Transaction Open'].SOURCDOC AS 'Source Doc'
,['Year-to-Date Transaction Open'].REFRENCE AS 'Reference'
,['Year-to-Date Transaction Open'].DSCRIPTN AS 'Description'
,['Year-to-Date Transaction Open'].TRXDATE AS 'Trx Date'
,['Year-to-Date Transaction Open'].TRXSORCE AS 'Trx Source'
,['Account Index Master'].ACTNUMST AS 'Account Number'
,['Year-to-Date Transaction Open'].LSTDTEDT AS 'Last Edited'
,['Year-to-Date Transaction Open'].USWHPSTD AS 'User ID'
,CASE
WHEN ['Year-to-Date Transaction Open'].SERIES = 1 THEN 'All'
WHEN ['Year-to-Date Transaction Open'].SERIES = 2 THEN 'Financial'
WHEN ['Year-to-Date Transaction Open'].SERIES = 3 THEN 'Sales'
WHEN ['Year-to-Date Transaction Open'].SERIES = 4 THEN 'Purchasing'
WHEN ['Year-to-Date Transaction Open'].SERIES = 5 THEN 'Inventory'
WHEN ['Year-to-Date Transaction Open'].SERIES = 6 THEN 'Payroll - USA'
WHEN ['Year-to-Date Transaction Open'].SERIES = 7 THEN 'Project'
WHEN ['Year-to-Date Transaction Open'].SERIES = 8 THEN ''
WHEN ['Year-to-Date Transaction Open'].SERIES = 9 THEN ''
ELSE
'3rd Party'
END AS 'Series'
,['Year-to-Date Transaction Open'].SEQNUMBR / 16384 AS 'Sequence No'
,['Year-to-Date Transaction Open'].PERIODID AS 'Period ID'
,['Year-to-Date Transaction Open'].CRDTAMNT AS 'Credit Amount'
,['Year-to-Date Transaction Open'].DEBITAMT AS 'Debit Amount'
,['Year-to-Date Transaction Open'].DOCDATE AS 'Document Date'
,['Year-to-Date Transaction Open'].VOIDED AS 'Voided'
FROM
GL20000 AS ['Year-to-Date Transaction Open'] --Year-to-Date Transaction Open (GL20000)
INNER JOIN
GL00105 AS ['Account Index Master'] --Account Index Master (GL00105)
ON
['Account Index Master'].ACTINDX = ['Year-to-Date Transaction Open'].ACTINDX
WHERE
OPENYEAR BETWEEN @StartYear AND @EndYear
UNION ALL
SELECT
['Account Transaction History'].HSTYEAR AS 'Year'
,['Account Transaction History'].JRNENTRY AS 'Journal No'
,['Account Transaction History'].SOURCDOC AS 'Source Doc'
,['Account Transaction History'].REFRENCE AS 'Reference'
,['Account Transaction History'].DSCRIPTN AS 'Description'
,['Account Transaction History'].TRXDATE AS 'Trx Date'
,['Account Transaction History'].TRXSORCE AS 'Trx Source'
,['Account Index Master'].ACTNUMST AS 'Account Number'
,['Account Transaction History'].LSTDTEDT AS 'Last Edited'
,['Account Transaction History'].USWHPSTD AS 'User ID'
,CASE
WHEN ['Account Transaction History'].SERIES = 1 THEN 'All'
WHEN ['Account Transaction History'].SERIES = 2 THEN 'Financial'
WHEN ['Account Transaction History'].SERIES = 3 THEN 'Sales'
WHEN ['Account Transaction History'].SERIES = 4 THEN 'Purchasing'
WHEN ['Account Transaction History'].SERIES = 5 THEN 'Inventory'
WHEN ['Account Transaction History'].SERIES = 6 THEN 'Payroll - USA'
WHEN ['Account Transaction History'].SERIES = 7 THEN 'Project'
WHEN ['Account Transaction History'].SERIES = 8 THEN ''
WHEN ['Account Transaction History'].SERIES = 9 THEN ''
ELSE
'3rd Party'
END AS 'Series'
,['Account Transaction History'].SEQNUMBR / 16384 AS 'Sequence No'
,['Account Transaction History'].PERIODID AS 'Period ID'
,['Account Transaction History'].CRDTAMNT AS 'Credit Amount'
,['Account Transaction History'].DEBITAMT AS 'Debit Amount'
,['Account Transaction History'].DOCDATE AS 'Document Date'
,['Account Transaction History'].VOIDED AS 'Voided'
FROM
GL30000 AS ['Account Transaction History'] --Account Transaction History (GL30000)
INNER JOIN
GL00105 AS ['Account Index Master']
ON
['Account Index Master'].ACTINDX = ['Account Transaction History'].ACTINDX
WHERE
HSTYEAR BETWEEN @StartYear AND @EndYear
Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
Looking for support or consultancy with Microsoft Dynamics GP?
I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.