SQL View Joining GL Transactions To MDA

Microsoft Dynamics GPWe 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.

Your Name

Your Email

Suggested Topic

Suggestion Details

Looking for support or consultancy with Microsoft Dynamics GP?

I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.

6 thoughts on “SQL View Joining GL Transactions To MDA

  1. Jim Wilson says:

    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.

    1. Ian Grieve says:

      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

  2. Jim Wilson says:

    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?

    1. Ian Grieve says:

      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

    2. Andrew says:

      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

Leave a Reply to Ian Grieve Cancel reply

Your email address will not be published. Required fields are marked *