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.OPENYEAR AS YEAR ,GLT.TRXDATE ,GLT.REFRENCE ,GLT.SOURCDOC ,GLT.DEBITAMT ,GLT.CRDTAMNT ,GLT.ACTINDX ,GLT.SEQNUMBR ,GLT.OrigSeqNum ,GLT.ORCTRNUM FROM GL20000 AS GLT WITH (NOLOCK) UNION ALL SELECT GLT.JRNENTRY ,GLT.HSTYEAR AS YEAR ,GLT.TRXDATE ,GLT.REFRENCE ,GLT.SOURCDOC ,GLT.DEBITAMT ,GLT.CRDTAMNT ,GLT.ACTINDX ,GLT.SEQNUMBR ,GLT.OrigSeqNum ,GLT.ORCTRNUM FROM GL30000 AS GLT 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