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