GL CAATs - Computer Assisted Auditing Techniques for Dynamics GP

Auditors routinely use Computer Assisted Auditing Techniques to review Transactions to assess particular risks.  For businesses who are audited periodically, it is important to be able to support the Audit team, in order to reduce the time and cost of the process.  Here is a query, which provides the typical information required to support Computer Assisted Auditing Techniques of General Ledger transactions:

/* This query's purpose is to pull detailed POSTED GL Transaction data from Dynamics GP for the time period between the Start Date and End Date from the GL20000 (GL OPEN - Posted in Current Year) and GL30000 (GL History - Posted in Prior Years) This approach to the design of the query ensures no matter whether the period(s) covered by this report are open or history, the data is returned. */

DECLARE @StartDate DATETIME, @EndDate DATETIME

Set @StartDate = '2013-01-01' --Sets the Start Date of the Report

Set @EndDate = '2013-01-01' --Sets the End Date of the Report

SELECT GLM.MNACSGMT,GLO.JRNENTRY as JE_Number, GLO.REFRENCE as JE_Description, GLM.ACTDESCR as Line_Item_Description,

GLO.TRXDATE as Entry_Date, GLO.ORPSTDDT as Post_Date, GLO.SOURCDOC as GL_Source_Code, GLO.LASTUSER as 'User', GLO.USWHPSTD as PostingUser,

GLO.ORGNTSRC as Subledger_Source_Code, GLO.ORMSTRNM as Master_Name, GLO.ORDOCNUM as Master_Doc_Number, GLO.ORCRDAMT as Credit_Amount,

GLO.ORDBTAMT as Debit_Amount, GLA.ACTNUMST

FROM GL20000 GLO -- GL Open

join GL00100 GLM -- GL Master Records

on GLO.ACTINDX = GLM.ACTINDX

join GL00105 GLA -- GL Account Details

on GLO.ACTINDX = GLA.ACTINDX

where GLO.TRXDATE >= @StartDate and GLO.TRXDATE <= @EndDate

Union ALL

SELECT GLM.MNACSGMT,GLH.JRNENTRY as JE_Number, GLH.REFRENCE as JE_Description, GLM.ACTDESCR as Line_Item_Description,

GLH.TRXDATE as Entry_Date, GLH.ORPSTDDT as Post_Date, GLH.SOURCDOC as GL_Source_Code, GLH.LASTUSER as 'User', GLH.USWHPSTD as PostingUser,

GLH.ORGNTSRC as Subledger_Source_Code, GLH.ORMSTRNM as Master_Name, GLH.ORDOCNUM as Master_Doc_Number, GLH.ORCRDAMT as Credit_Amount,

GLH.ORDBTAMT as Debit_Amount, GLA.ACTNUMST

FROM GL30000 GLH -- GL History

join GL00100 GLM -- GL Master Records

on GLH.ACTINDX = GLM.ACTINDX

join GL00105 GLA -- GL Account Details

on GLH.ACTINDX = GLA.ACTINDX

where GLH.TRXDATE >= @StartDate and GLH.TRXDATE <= @EndDate