-->

Tuesday, June 19, 2012

Decisions 2012 : Dynamics GP Web Client Resources

That you to everybody who attended the session at Decisions 2012 - Dynamics GP Web Client: What You Need to Know Now.

Thanks for David Musgrave, Kevin, Jay from Microsoft, and GPUG for sharing this information.

You can download the presentation from - https://www.box.com/s/c6f3deb0cac5d7f0e67a 

Web Client Information

GP 2013 Launch Portal

System Requirements for GP 2013

Developer Insights from Microsoft about GP 2013

Copy Paste JEs from Excel to Dynamics GP

Mohammad R. Daoud has built a fantastic utility (based on a suggestion by MVP Mark Polino)

His utility allows you to copy a JE from excel directly into the JE window in Dynamics GP, how cool is that ?!

Mail him for a copy

In the past Rubal had built a utility requested by one of our customers that allowed you paste JE’s with AA information into a custom JE entry window. However, having something that works with the default JE window is fantastic!

Also if you dig excel for data entry with Dynamics GP – you should check out – the excel data entry templates that ship with SmartConnect. SmartConnect has templates for GL, PM, PO, SOP, Inventory – and with excellent VBA validation, and SQL connection to check if an account/item exists and so on.

image

Wednesday, June 13, 2012

GP 2013 Training

GP 2013 is a major new release, especially with the game changing web client. If you are looking to get up to speed – please bookmark Sept 11 to 14 on your calendar (Fargo) and October 16th in Seattle, and check out the details of the session at -

GP Technical Airlift » An event is designed to deliver advanced technical skills around Microsoft Dynamics GP 2013 for both consultants and developers.

Microsoft Dynamics GP2013 Partner Training Rundown - Inside Microsoft Dynamics GP - Site Home - MSDN Blogs (Pam)

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
                   
            )
     
 

Saturday, June 9, 2012

Steps to disable third-party products or temporarily disable additional products in Dynamics GP

In the last couple of weeks I have seen a couple of GP consultants / advanced users try and disable products the long way, manually editing the dynamics.set file - Steps to disable third-party products or temporarily disable additional products in the Dynamics.set file in Microsoft Dynamics GP

Although this works – there is a much easy way to do this! Use the Dictionary control window in Support debugging tool. I personally think, it takes letter time to install Support debugging tool, then to do this manually – plus you don’t have to worry about making mistakes. SDT also takes a backup of the Dynamics.set file automatically. 

The Dictionary Control window, allows dictionaries to have their triggers and alternate or modified windows to be disabled temporarily or until re-enabled.

 

image

image

This can be used for troubleshooting issues without having to manually edit the Dynamics.set launch file and restart the application. Here is an example from David Musgrave’s blog.

Check out a lot more posts about the support debugging tool here - GPWindow.com - Dynamics GP- TOOLS FOR GP > SUPPORT DEBUGGING TOOL