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
)
