Twice a year, I receive a request to extract Journal Entries for Auditors. It could have been done using the Smartlist in Dynamics GP but my End Users didn't want to use it because it is time consuming on their end. We extract into two sets: January to August and then October to December once they are done with closing their books.
Until today, I'm still using the SQL scripts below and extract the data into Excel.
OPEN YEAR
select A.OPENYEAR
'Year',
TRXDATE 'Trx_Date',
ORPSTDDT 'Originating_Posted_Date',
A.JRNENTRY 'Journal_Entry',
A.RCTRXSEQ 'Recurring_Trx_Sequence',
ORTRXSRC
'Originating_TRX_Source',
A.SOURCDOC 'Source
Document',
A.REFRENCE
'Reference',
ORMSTRID 'Originating_Master_ID',
ORMSTRNM
'Originating_Master_Name',
ORDOCNUM 'Originating_Doc_Number',
DEBITAMT 'Debit_Amount',
CRDTAMNT 'Credit_Amount',
(DEBITAMT-CRDTAMNT) 'Debit-Credit',
isnull((select ACTNUMST from GL00105 where
ACTINDX=A.ACTINDX),'') AccountNumber,
isnull((select ACTDESCR from GL00100 where
ACTINDX=A.ACTINDX),'') AccountDescription,
USWHPSTD 'User_Who_Posted',
CURNCYID 'Currency_ID',
A.ACTINDX,
LASTUSER 'Last User',
LSTDTEDT 'Last Date Edited',
A.DEX_ROW_ID
from GL20000 A (nolock)
where A.TRXDATE between '10/01/2022' and '12/31/2022'
order by A.DEX_ROW_ID
CLOSED YEAR
SELECT YEAR1 Trx_Year,
TRXDATE Trx_Date,
ORPSTDDT Originating_Posted_Date,
JRNENTRY Journal_Entry,
ORTRXSRC Originating_TRX_Source,
REFRENCE Reference,
ORMSTRID Originating_Master_ID,
ORMSTRNM Originating_Master_Name,
ORDOCNUM Originating_Doc_Number,
DEBITAMT Debit_Amount,
CRDTAMNT Credit_Amount,
ACTNUMST Account_Number,
ACTDESCR Account_Description
,S.Debits Total_Debits
,USWHPSTD User_Who_Posted
,CURNCYID Currency_ID
,GL.ACTINDX
FROM
(SELECT ACTINDX, HSTYEAR YEAR1, TRXDATE,ORPSTDDT,
JRNENTRY, ORTRXSRC, REFRENCE,
ORDOCNUM, ORMSTRID, ORMSTRNM,
DEBITAMT, CRDTAMNT,CURNCYID,USWHPSTD
FROM GL30000
WHERE SOURCDOC not in ('BBF','P/L')AND HSTYEAR = '2021') GL
INNER JOIN GL00105 GM
ON GL.ACTINDX = GM.ACTINDX
INNER JOIN GL00100 GA
ON GL.ACTINDX = GA.ACTINDX
LEFT OUTER JOIN
(SELECT JRNENTRY JE, TRXDATE TrxD, sum(DEBITAMT) Debits
FROM GL30000
GROUP BY JRNENTRY, TRXDATE) S
ON GL.JRNENTRY = S.JE
AND GL.TRXDATE = S.TrxD
where GL.TRXDATE between '10/01/2021' and '12/31/2021'
order by GA.DEX_ROW_ID
At one point, we used MEM and this is how I extract the Journal Entries.
OPEN YEAR with Multi-Entity Management (MEM)
SELECT GL.OPENYEAR,
GL.SOURCDOC Series,
TRXDATE Trx_Date,
ORPSTDDT Originating_Posted_Date,
GL.JRNENTRY Journal_Entry,
ORTRXSRC Originating_TRX_Source,
GL.REFRENCE Reference,
ORMSTRID Originating_Master_ID,
ORMSTRNM Originating_Master_Name,
ORDOCNUM Originating_Doc_Number,
DEBITAMT Debit_Amount,
CRDTAMNT Credit_Amount,
ACTNUMST Account_Number,
ACTDESCR Account_Description,
S.Debits Total_Debits,
USWHPSTD User_Who_Posted,
CURNCYID Currency_ID,
GL.ACTINDX,
GC.BSSI_Facility_ID Company_ID
FROM
(SELECT ACTINDX, SOURCDOC, OPENYEAR, TRXDATE,ORPSTDDT,
JRNENTRY, ORTRXSRC, REFRENCE,
ORDOCNUM, ORMSTRID, ORMSTRNM,
DEBITAMT, CRDTAMNT,CURNCYID,USWHPSTD
FROM GL20000
WHERE SOURCDOC not in ('BBF','P/L')
and OPENYEAR = '2021'
AND TRXDATE BETWEEN '1/1/21' AND '1/31/21'
) GL
INNER JOIN GL00105 GM
ON GL.ACTINDX = GM.ACTINDX
INNER JOIN GL00100 GA
ON GL.ACTINDX = GA.ACTINDX
INNER JOIN B3940200 GC
ON GL.JRNENTRY = GC.JRNENTRY
LEFT OUTER JOIN
(SELECT JRNENTRY JE, TRXDATE TrxD, sum(DEBITAMT) Debits
FROM GL20000
WHERE OPENYEAR = '2021'
AND TRXDATE BETWEEN '1/1/21' AND '1/31/21'
GROUP BY JRNENTRY, TRXDATE) S
ON GL.JRNENTRY = S.JE
AND GL.TRXDATE = S.TrxD
CLOSED YEAR with Multi-Entity Management (MEM)
select A.HSTYEAR 'Year',
TRXDATE 'Trx_Date',
ORPSTDDT 'Originating_Posted_Date',
A.JRNENTRY 'Journal_Entry',
A.RCTRXSEQ 'Recurring_Trx_Sequence',
ORTRXSRC 'Originating_TRX_Source',
A.SOURCDOC 'Source Document',
A.REFRENCE 'Reference',
ORMSTRID 'Originating_Master_ID',
ORMSTRNM 'Originating_Master_Name',
ORDOCNUM 'Originating_Doc_Number',
DEBITAMT 'Debit_Amount',
CRDTAMNT 'Credit_Amount',
(DEBITAMT-CRDTAMNT) 'Debit-Credit',
isnull((select ACTNUMST from GL00105 where ACTINDX=A.ACTINDX),'') AccountNumber,
isnull((select ACTDESCR from GL00100 where ACTINDX=A.ACTINDX),'') AccountDescription,
USWHPSTD 'User_Who_Posted',
CURNCYID 'Currency_ID',
A.ACTINDX,
LASTUSER 'Last User',
LSTDTEDT 'Last Date Edited',
isnull((select top 1 BSSI_Facility_ID from B3930000 where JRNENTRY=A.JRNENTRY),'') Company,
A.DEX_ROW_ID
FROM GL30000 A (NOLOCK)
WHERE JRNENTRY NOT IN (SELECT JRNENTRY FROM B3930000)
AND JRNENTRY IN (SELECT JRNENTRY FROM B3930000 WHERE BSSI_Facility_ID='100')
order by A.DEX_ROW_ID
Comments
Post a Comment