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
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
Hi Ian,
Ran the above script but receiving the following error:
Msg 4104, Level 16, State 1, Procedure uv_AZRCRV_LinkGLtoMDA, Line 20
The multi-part identifier “GLT.JRNENTRY” could not be bound.
…
Msg 207, Level 16, State 1, Procedure uv_AZRCRV_LinkGLtoMDA, Line 61
Invalid column name ‘OrigSeqNum’.
Msg 15151, Level 16, State 1, Line 1
Cannot find the object ‘uv_AZRCRV_LinkGLtoMDA’, because it does not exist or you do not have permission.
Do you have any suggestion to fix this error?
Jim Wilson
Triad Associates, Inc.
Hi Jim,
I’ve updated the script above to resolve the error. Not sure how the errors had made it into the posted script, but it should be working now.
Ian
Hi Ian,
Thanks for the script update, I tried your script the other day and received no error while creating the SQL view, but after that I can see a lot of duplicates in the Smartlist. Do you know any reason? Maybe the UNION you used in your script, you can search any journal entry number in Smartlist and see the duplicate. Have you tested your script? I added vendor id and vendor name in to your script, do you think that will cause the duplicate?
Jim,
I have a client using the script and they don’t have duplicates.
The union is there to bring through both Open and History transactions.
Is there any chance I could see the data and run the script with a view to updating it to work? Happy to sign an NDA if required.
Ian
Could someone maybe explain the point of the following condition. It seems nonsensical to me:
(DTA10100.SEQNUMBR = GLT.SEQNUMBR OR DTA10100.SEQNUMBR GLT.SEQNUMBR)
I also got duplicates, but the way to resolve is to use the following:
LEFT OUTER JOIN
DTA10100 WITH (NOLOCK)
ON
DTA10100.JRNENTRY = GLT.JRNENTRY
AND
DTA10100.ACTINDX = GLT.ACTINDX
AND
DTA10100.SEQNUMBR = GLT.OrigSeqNum — link on original sequence number
AND
GLT.ORCTRNUM = DTA10100.DOCNUMBR
LEFT OUTER JOIN
DTA10200 WITH (NOLOCK)
ON
DTA10200.DTAREF = DTA10100.DTAREF
AND
DTA10100.SEQNUMBR = DTA10200.SEQNUMBR — link DTA together on sequence number