Keep up to date with the latest information about Microsfot Dynamics GP through articles from the Perfect Image and azurecurve blog.
SQL View Joining GL Transactions To MDA
July 19th, 2016 by Ian Grieve
We have a couple of clients using MDA (Multi-dimensional Analysis; the precursor to Analytical Accounting), which I don’t know terribly well. So, when one of them asked for a new SmartList Object to be created which extracts information about General Journals and the related MDA information, I needed to do some exploring of the database to work out the links.
Unfortunately, the links between the GL transactions and MDA are not especially obvious. To verify what I had created I did a search and came across a post from 2011 by Mark Polino which was posting code created by a Jeremy Lowell.
I ended up combining some of the code I had with Jeremy’s code (when I tried just his I was getting duplicate lines) to create the below SQL View. Since writing and giving the view to the client, I’ve spotted a few places where the SQL could be tightened up, but this view has been tested in its current state.
CREATE VIEW uv_AZRCRV_LinkGLtoMDA AS SELECT DISTINCT GLT.JRNENTRY ,GLT.YEAR ,GLT.TRXDATE ,GLT.REFRENCE ,GLT.SOURCDOC ,GLT.DEBITAMT ,GLT.CRDTAMNT ,GLT.ACTINDX ,DTA10100.DTASERIES ,DTA10100.DTAREF ,DTA10100.GROUPID ,DTA10100.DTA_GL_Reference ,DTA10100.GROUPAMT ,DTA10200.CODEID ,DTA10200.POSTDESC ,DTA10200.CODEAMT FROM (SELECT GLT.JRNENTRY ,GLT.YEAR ,GLT.TRXDATE ,GLT.REFRENCE ,GLT.SOURCDOC ,GLT.DEBITAMT ,GLT.CRDTAMNT ,GLT.ACTINDX FROM GL20000 WITH (NOLOCK) UNION ALL SELECT GLT.JRNENTRY ,GLT.YEAR ,GLT.TRXDATE ,GLT.REFRENCE ,GLT.SOURCDOC ,GLT.DEBITAMT ,GLT.CRDTAMNT ,GLT.ACTINDX FROM GL30000 WITH (NOLOCK) ) AS GLT LEFT OUTER JOIN DTA10100 WITH (NOLOCK) ON DTA10100.JRNENTRY = GLT.JRNENTRY AND DTA10100.ACTINDX = GLT.ACTINDX AND (DTA10100.SEQNUMBR = GLT.SEQNUMBR OR DTA10100.SEQNUMBR <> GLT.SEQNUMBR) AND GLT.ORCTRNUM = DTA10100.DOCNUMBR LEFT OUTER JOIN DTA10200 WITH (NOLOCK) ON (DTA10200.DTAREF = DTA10100.DTAREF AND GLT.SEQNUMBR = GLT.OrigSeqNum) OR (DTA10200.DTAREF = DTA10100.DTAREF AND GLT.SEQNUMBR <> GLT.OrigSeqNum) GO GRANT SELECT ON uv_AZRCRV_LinkGLtoMDA TO DYNGRP GO