I have been using SmartConnect a lot recently, and its an incredibly powerful tool – there is nothing you can’t do with it. So gear up for many posts about it.
Recently we had the requirement to setup automated import of GL Transactions with MDA into GP, where the customer was using Wennsoft Equipment Management Series. As eConnect does not support addition of MDA lines with GL transactions, neither does SmartConnect which is built on top of eConnect. Additionally, Wennsoft has its own MDA tables which were required to be populated, and linked to the GL transactions.
Also, as our main aim was setting up automated integrations, and Integration Manager isn’t really built for automating imports (although possible using Macros – see How to schedule Dynamics GP to automatically log in and run an Integration Manager integration and here Weekly Dynamic: Automating Integrations) – we decided to go for SmartConnect.
SmartConnect allows execution of SQL commands on both document and map inserts, which solved the problem. We wrote SQL code to insert the MDA lines with each GL transaction, and also make the associated imports into the relevant Wennsoft tables. The resulting solution is so simple – that you’d be surprised.
Below are the steps we followed, and the associated SQL scripts (built with the help of Rubal). If you are using eConnect, you would want to refer to the stored procedure below, and use in the GL Insert Post processing routine.
EDIT : In response to this post I recieved the following comment from Martin Olsen
A couple of other things to think about on your blog article:
1. Wennsoft have written some eConnect nodes of their own for use with SC – so likely you may have been able to utilise these.
2. Another option is to turn your scripts in a Stored Proc/Node and import it into SmartConnect using Node Maintenance. Then it acts like every other eConnect node.
3. Third option it to utilise our Node Builder of those people that have not SQL dev skills.
First created a GL Import Integration in Smart Connect, and mapped the required fields.
Add the MDA Code in the Description field with a distribution line
Create a new task in Smart Connect that runs if the document insert succeeds. From here call the Stored procedure that would do the inserts related to MDA. The two stored procedures used are listed below.
Finally with a couple of clicks setup the schedule for the import!
Please use these stored procedures only for your reference, and test them in your test environment.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --[sp_Insert_Equipment_MDA_for JE] 3447, 'EQUIP' CREATE PROC [dbo].[sp_Insert_Equipment_MDA_for_JE] @jrnentry INT , @mdaGroup NVARCHAR(15) AS DECLARE @actindex INT DECLARE @DTA_Index NVARCHAR(25) DECLARE @amount NUMERIC(19, 5) DECLARE @date DATETIME DECLARE @mdaCode NVARCHAR(15) DECLARE @CurrentSeqNum AS INT , @NextSeqNumber AS INT , @fake AS BIT SET @CurrentSeqNum = 0 SET @fake = 1 SET @CurrentSeqNum = 0 SELECT TOP 1 @NextSeqNumber = ( SQNCLINE ) FROM GL10001 WHERE JRNENTRY = @jrnentry AND SQNCLINE > @CurrentSeqNum ORDER BY SQNCLINE ASC SET @CurrentSeqNum = @NextSeqNumber --select @NextSeqNumber, '1' IF ( @NextSeqNumber IS NOT NULL ) BEGIN WHILE ( @fake = 1 ) BEGIN -- Check if mda codes exist and account has mda group assigned SELECT @actindex = actindx , @mdaCode = RTRIM(DSCRIPTN) FROM GL10001 WHERE JRNENTRY = @jrnentry AND SQNCLINE = @CurrentSeqNum --select @actindex as acc, @mdaCode as code IF EXISTS ( SELECT GROUPID FROM DTA00300 WHERE ACTINDX = @actindex AND GROUPID = @mdaGroup ) AND EXISTS ( SELECT * FROM DTA00200 WHERE CODEID = @mdaCode ) BEGIN --If yes,get values and insert MDA SELECT @DTA_Index = REPLACE(DTA_Index, '.', '') + 'N' , @date = TRXDATE FROM GL10000 WHERE JRNENTRY = @jrnentry SELECT @amount = ( DEBITAMT - CRDTAMNT ) FROM GL10001 WHERE JRNENTRY = @jrnentry AND SQNCLINE = @CurrentSeqNum --select @DTA_Index as dt, @amount am EXECUTE [sp_InsertEquipmentMDALines] @jrnentry, @actindex, @DTA_Index, @CurrentSeqNum, @mdaGroup, @amount, @date, @mdaCode END --Get next line SET @NextSeqNumber = NULL SELECT TOP 1 @NextSeqNumber = ( SQNCLINE ) FROM GL10001 WHERE JRNENTRY = @jrnentry AND SQNCLINE > @CurrentSeqNum GROUP BY SQNCLINE ORDER BY SQNCLINE ASC --select @NextSeqNumber, 'next' --if end of transaction, Stop processing IF @NextSeqNumber IS NULL BEGIN --select 'break' BREAK END ELSE BEGIN SET @CurrentSeqNum = @NextSeqNumber SET @actindex = 0 SET @DTA_Index = '' SET @amount = 0 SET @mdaCode = '' END END END
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[sp_InsertEquipmentMDALines] @jrnentry INT , @actindex INT , @DTA_Index NVARCHAR(25) , @SEQNUMBR INT , @mdaGroup NVARCHAR(15) , @amount NUMERIC(19, 5) , @date DATETIME , @mdaCode NVARCHAR(15) AS INSERT INTO [dbo].[DTA10100] ( [DTASERIES] , [DTAREF] , [ACTINDX] , [SEQNUMBR] , [GROUPID] , [DTA_GL_Reference] , [DOCNUMBR] , [RMDTYPAL] , [GROUPAMT] , [JRNENTRY] , [TRXDATE] , [PSTGSTUS] ) VALUES ( 2 , @DTA_Index , @actindex , @SEQNUMBR , @mdaGroup , '' , '' , 0 , @amount , @jrnentry , @date , 1 ) INSERT INTO [dbo].[DTA10200] ( [DTASERIES] , [DTAREF] , [ACTINDX] , [SEQNUMBR] , [GROUPID] , [CODEID] , [DOCNUMBR] , [RMDTYPAL] , [POSTDESC] , [DTAQNTY] , [CODEAMT] , [TRXDATE] ) VALUES ( 2 , @DTA_Index , @actindex , @SEQNUMBR , @mdaGroup , @mdaCode , '' , 0 , '' , 0 , @amount , @date ) -- This part is only required if you are using Wennsoft INSERT INTO dbo.EQSB1010 ( DTASERIES , DTAREF , ACTINDX , SEQNUMBR , SEQUENCE1 , Equipment_MDA_Code , Secondary_MDA_Group , Secondary_MDA_Code , EQS_Cost_Code , POSTDESC , DTA_GL_Reference , CODEAMT , DTAQNTY , Specification_Index ) VALUES ( 2 , -- DTASERIES - smallint @DTA_Index , -- DTAREF - char(25) @actindex , -- ACTINDX - int @SEQNUMBR , -- SEQNUMBR - int 1.00000 , -- SEQUENCE1 - numeric @mdaCode , -- Equipment_MDA_Code - char(15) '' , -- Secondary_MDA_Group - char(15) '' , -- Secondary_MDA_Code - char(15) '' , -- EQS_Cost_Code - char(15) '' , -- POSTDESC - char(51) '' , -- DTA_GL_Reference - char(25) @amount , -- CODEAMT - numeric 0 , -- DTAQNTY - numeric 0 -- Specification_Index - int )