Month: July 2016

I can’t remember why I created this code, but it allows a batch file to be run from an Integration Manager script:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
Set objShell = CreateObject("WScript.Shell")
Set objExec = objShell.Exec("B:\IM.bat")

I’m posting it here so I don’t lose it for when I remember why I write it, and perhaps it may be useful to others.

We have a client who will be loading payables transactions into Microsoft Dynamics GP and need secondary postings created into the General Ledgers of other company databases. The idea is to use the AfterDocument script to create and submit an eConnect document, which is in XML format.

The below code is a small proof of concept which creates an XML file with child and parent nodes and saves it to the T:\:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
Set xmlDoc = CreateObject("Microsoft.XMLDOM")

Set objRoot = xmlDoc.createElement("RootElement")
xmlDoc.appendChild objRoot

Set objRecord = xmlDoc.createElement("SubLevel1")
objRoot.appendChild objRecord

Set objName = xmlDoc.createElement("Element1")
objName.Text = "AAAAAAA"
objRecord.appendChild objName

Set objDate = xmlDoc.createElement("Element2")
objDate.Text = "BBBBBBB"
objRecord.appendChild objDate

Set objIntro = xmlDoc.createProcessingInstruction ("xml","version='1.0'")
xmlDoc.insertBefore objIntro,xmlDoc.childNodes(0)

xmlDoc.Save "T:\Test.xml"

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