-->

Sunday, June 10, 2012

Importing MDA with SmartConnect/eConnect

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.

 

image

Add the MDA Code in the Description field with a distribution line

image

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.

 

image

Finally with a couple of clicks setup the schedule for the import!

image

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
                   
            )
     
 

1 comment:

JackNeal said...

I am getting "Error converting data type nvarcahr to int" in SmartConnect when I follow your instructions above.

Any idea what could be causing this?

Thank you!