Extract Journal Entries for Auditors using SQL Query

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

Popular posts from this blog

WHERE TO GO IN ARIZONA

Image
DynamicsCon Live 2023 THE SCOTTSDALE PLAZA RESORT & VILLAS 7200 N SCOTTSDALE RD, SCOTTSDALE, ARIZONA, 85253, USA DynamicsCon LIVE, Hosted by Dynamics User Group returns May 22nd-25th in sunny Scottsdale, AZ! We’re returning with twice the content and breakout room space! Over 115 sessions will be happening over the course of 3 days, with a welcome reception to kick off and reunite the DUG community the evening of Monday, May 22nd. Dynamics User Group (DUG) Arizona would like to welcome you!  The Scottsdale Plaza Resort & Villas is located just minutes away from the restaurants, shops, and art galleries of Old Town Scottsdale listed below . Also, there are many places to explore if you are staying few more days after the conference. With a dazzling array of amazing sights, Arizona’s desert climate is home to some astounding geological formations and canyons that are well worth exploring. The Grand Canyon, for example, is one of the United States’ most famous sights, while the i
Read more

GP Guru Level Up: Episode 3 - How to bring your out of the box Aged Trial Balance into Smartlist

Image
 We had a great session at our Dynamics User Group Virtual with our guest speaker Marion Palaming discussing techniques in using SQL Views to create  PM and RM Aged Trial Balance into Smartlist. Here are the SQL Views used in the presentation: Disclaimer: did some minor updates from Victoria Yudin's SQL Views RECEIVABLES MODULE USE ['yourDB] GO /****** Object:  View [dbo].[RM_SMAging_CurrentDate]    Script Date: 4/21/2023 10:54:48 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[RM_SMAging_CurrentDate] AS SELECT CM.CUSTNMBR AS Customer_ID, CM.CUSTNAME AS Customer_Name, CM.PYMTRMID AS Customer_Terms, CM.CUSTCLAS AS Customer_Class, CM.PRCLEVEL AS Price_Level, RM.CURNCYID,               CASE RM.RMDTYPAL WHEN 1 THEN 'Sale / Invoice' WHEN 3 THEN 'Debit Memo' WHEN 4 THEN 'Finance Charge' WHEN 5 THEN 'Service Repair' WHEN 6 THEN 'Warranty' WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN &
Read more

GP Guru Level Up: Episode 2 - How to use the Replicator to create a macro with John Arnold

Image
 In continuation of my previous blog in  Episode 1  about using a macro to process NSF, I learned that there's an easiest way to create a macro using the  Replicator and it's   free for everyone!  This is developed by John Arnold , Senior Software Engineer from US Digital. Preparation steps: Go to John's website:  Rnoldz.com/ Click on Replicator >> Download the Replicator Excel File Right click on the file >>  Mark the checkbox to Unblock Security Open the Replicator File Ready your NSF macro Ready your data in Excel Here is the NSF macro template for future use: # DEXVERSION=18.00.0021.000 2 2 CheckActiveWin dictionary 'default'  form 'RM_Misc_Transaction' window 'RM_Misc_Transaction'    TypeTo field 'Customer Number' ,  '09728003SA'   MoveTo field 'Document Type' item 1  # 'Sales / Invoices'    ClickHit field 'Document Type' item 7  # 'Payments'    MoveTo field '(L) Document Number
Read more