The Dynamics GP general ledger is stored in 2 database tables: GL20000 contains the transaction history for any year that is open (typically just the current year), while GL30000 contains the transaction history for any years that are closed or historical.
To query just the open years only (i.e. the current year):
SQL Code
dbo.GL20000 INNER JOIN dbo.GL00105 ON dbo.GL20000.ACTINDX = dbo.GL00105.ACTINDX INNER JOIN dbo.GL00100 ON dbo.GL00105.ACTINDX = dbo.GL00100.ACTINDX INNER JOIN dbo.GL00102 ON dbo.GL00100.ACCATNUM = dbo.GL00102.ACCATNUM INNER JOIN dbo.SY40101 ON dbo.GL20000.OPENYEAR = dbo.SY40101.YEAR1 LEFT OUTER JOIN(SELECT YEAR1, PERIODID, PERNAME FROM dbo.SY40100 GROUP BY YEAR1, PERIODID, PERNAME) AS P ON dbo.GL20000.PERIODID = P.PERIODID AND dbo.GL20000.OPENYEAR = P.YEAR1
WHERE (dbo.GL00100.PSTNGTYP = 1)
ORDER BY AccountCategory, dbo.GL00105.ACTNUMST
FROM
TOP (100) PERCENT CONVERT(CHAR(10), dbo.GL20000.TRXDATE, 101) AS TransactionDate, dbo.GL00105.ACTNUMST, dbo.GL00100.ACTDESCR, dbo.GL20000.DEBITAMT, dbo.GL20000.CRDTAMNT, dbo.GL20000.OPENYEAR, dbo.GL20000.PERIODID, MONTH(dbo.GL20000.TRXDATE) AS Month, dbo.GL20000.DEBITAMT - dbo.GL20000.CRDTAMNT AS NetAmount, dbo.GL00102.ACCATNUM AS AccountCategory, dbo.GL00102.ACCATDSC AS AccountCategoryDescription, P.PERNAME, CONVERT(CHAR(10), dbo.SY40101.FSTFSCDY, 101) AS FirstDayOfYear, CONVERT(CHAR(10), dbo.SY40101.LSTFSCDY, 101) AS LastDayOfYear, dbo.GL20000.REFRENCE, dbo.GL20000.DSCRIPTN, dbo.GL20000.ORMSTRID AS VendorOrCustomerID, dbo.GL20000.ORMSTRNM AS VendorOrCustomerName, dbo.GL20000.ORDOCNUM AS SubledgerDocumentNumber, dbo.GL20000.SERIES, dbo.GL20000.USWHPSTD, dbo.GL20000.JRNENTRY
SELECT
view vwGeneralLedgerDetail_CurrentYear as
Create