Integration Manager: Integrating journal entries with Analytical Accounting Information

My good friend, David Musgrave, somehow manages to get me involved in interesting topics circulating in his inbox. Just recently, he came across a fairly long thread among his peers, needing to work out some Integration Manager issues for journal entries with Analytical Accounting information. David was kind enough to involve me, as I had posted an answer on the newsgroups a long time ago on this same issue.

If you are one of the fervourous Integration Manager fans out there and have had to work on integrating journal entries with Analytical Accounting information, you may know this is only possible with the eConnect Adapter, not the Standard Adapter.

The eConnect Adapter was introduced with Integration Manager version 10, and replaces the old SQL Optimized Adapter available in prior versions of Integration Manager. The eConnect Adapter in turn, leverages eConnect components to deliver a robust transactional environment for high volume integrations using ADO.NET to access Microsoft Dynamics GP company databases.

eConnect Adapter – Journal Entry# field with Use Default rule value

However, the eConnect Adapter, though, while it provides a Use Default rule value for the Journal Entry# field, this setting causes the integration to fail, as eConnect (the component) requires a value to be supplied, this is, the actual journal number.

Of course the question now is, how do you retrieve the next journal number from your company database to supply this value to the Journal Entry# field to allow the integration to be successful and thereby, preventing you from having to manually reserve ? The answer is, scripting, of the VBScript type.

You can add VBScript code to the Before Document event script in Integration Manager to retrieve the next journal number from your company database, as follows:

' Created by: Mariano Gomez, MVP
' This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.5 Generic license.
Option Explicit

Const adCmdStoredProc = 4
Const adParamInput = 1
Const adParamOutput = 2
Const adParamInputOutput = 3
Const adInteger = 3
Const adVarchar = 200
Const adBoolean = 11
Const adChar = 129
Const adDate = 7
Const adNumeric = 131

Dim SqlStmt
Dim objConnection, objCommand, NextJournal

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")

objConnection.Open _
    "Provider=SQLNCLI10;Server=MGB001\GP11;Database=TWO; Trusted_Connection=yes;" 

With objCommand
 .ActiveConnection = objConnection
 .CommandType = adCmdStoredProc
 .CommandText = "glGetNextJEWrapper" 'our wrapper stored proc

 .Parameters.Append .CreateParameter ("@IO_iOUTJournalEntry", adInteger, adParamOutput, 4)
 .Parameters.Append .CreateParameter ("@O_iErrorState", adInteger, adParamOutput, 4)

 .Execute
 NextJournal = objCommand.Parameters("@IO_iOUTJournalEntry").Value
End With

SetVariable "gblJounal", NextJournal

Set objCommand = Nothing
Set objConnection = Nothing

The above code calls the stored procedure dbo.glGetNextNumberWrapper, which leverages the existing Microsoft Dynamics GP’s dbo.glGetNextJournalEntry stored procedure to retrieve the next journal number, stored in the dbo.GL40100 (General Ledger Setup) table. As this is a call to a standard Microsoft Dynamics GP stored procedure, we are avoiding the use of custom code to retrieve the journal number and increment the value at the same time.

It is also necessary to note that the above code uses a Trusted Connection to connect to the company database. You can change the connection string as you see fit, just keep in mind that if you are going to use a SQL login, it cannot be a Microsoft Dynamics GP user login as the password for these logins are encrypted on SQL Server.

The following is the code for the dbo.glGetNextNumberWrapper stored procedure called by the Before Document script:

IF OBJECT_ID('dbo.glGetNextJEWrapper') IS NOT NULL
 DROP PROCEDURE glGetNextJEWrapper;
GO
CREATE PROCEDURE glGetNextJEWrapper
 @IO_iOUTJournalEntry int OUTPUT,
 @O_iErrorState int OUTPUT
AS
DECLARE @l_tINCheckWORKFiles tinyint = 1, @I_iSQLSessionID int = USER_SID(), @O_tOUTOK tinyint;

IF @IO_iOUTJournalEntry IS NULL
 SET @IO_iOUTJournalEntry = 0

EXECUTE glGetNextJournalEntry
   @l_tINCheckWORKFiles
  ,@I_iSQLSessionID
  ,@IO_iOUTJournalEntry OUTPUT
  ,@O_tOUTOK OUTPUT
  ,@O_iErrorState OUTPUT
GO
GRANT EXECUTE ON glGetNextJEWrapper TO DYNGRP;

For more information on connection strings, visit http://www.connectionstrings.com/. Also, check the following article over at Developing for Dynamics GP on why does Microsoft Dynamics GP encrypts passwords.

Once the Before Document event script is implemented, you can then add a small field script to the Journal Number# field to retrieve the value stored in the gblJournal Integration Manager global variable, as follows:

' Created by: Mariano Gomez, MVP
' This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.5 Generic license.
CurrentField.Value = GetVariable("gblJournal")

Integration Manager has great import capabilities when combined with the power of scripting and when you have a clear understanding of the underlaying technologies that support it.

Please enter your comments on this article or any methods you have used in the past to overcome similar issues.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Advertisements

6 Responses to Integration Manager: Integrating journal entries with Analytical Accounting Information

  1. >Hi MarianoGreat post that many users will find helpful.Davidhttp://blogs.msdn.com/DevelopingForDynamicsGP/

  2. Nacho says:

    >Hi Mariano,If I´m using Integration Manager 9.0 (for GP 9.0), how can I integrate AA information in GL transactions; because the e-Connect Adapter does not exists in IM 9.0Thanks in advance!Ignacio

  3. Jason & I says:

    >Mariano,This is a great post. I am currently trying to implement it at a client but am receiving"Object reference not set to an instance" for the JournalEntry# script. Can you please advise?Thanks so much,Stacy

  4. Brendon says:

    >Thanks for the post. I am getting an error when I run the integration "DOC 1 ERROR: Error Executing Script 'GLTransaction.Journal Entry#' Line 1: – Object reference not set to an instance of an object.".Any assistance would be appreciated.Thanks,Brendon

  5. Rogelio Carrillo says:

    Mariano,

    Does Analytical Accounting works with Integration Manager or eConnect when importing sales transactions? I need to create an integration for a customer to import invoices and use the AA dimensions when importing but I’m not sure if this can be done with IM or eConnect

    Thanks

    Roger

  6. Jessica L says:

    This code is awesome. I had to use it for SQL Server 2005 – so I was able to make some modifications – here's what I did to make it work on 2005.First, replace the string with the SQL Server 9.0 driver:Replace the line "Provider=SQLNCLI10;Server=MGB001\GP11;Database=TWO; Trusted_Connection=yes;" with "Provider=SQLNCLI;Server=MGB001\GP11;Database=TWO; Trusted_Connection=yes;" Then I had to modify the stored proc a little bit:IF OBJECT_ID('dbo.glGetNextJEWrapper') IS NOT NULL DROP PROCEDURE glGetNextJEWrapper;GOCREATE PROCEDURE glGetNextJEWrapper @IO_iOUTJournalEntry int OUTPUT, @O_iErrorState int OUTPUTASDECLARE @l_tINCheckWORKFiles tinyint declare @I_iSQLSessionID int declare @O_tOUTOK tinyint;IF @IO_iOUTJournalEntry IS NULL SET @IO_iOUTJournalEntry = 0set @l_tINCheckWORKFiles = 1set @I_iSQLSessionID = USER_SID()EXECUTE glGetNextJournalEntry @l_tINCheckWORKFiles ,@I_iSQLSessionID ,@IO_iOUTJournalEntry OUTPUT ,@O_tOUTOK OUTPUT ,@O_iErrorState OUTPUTGOGRANT EXECUTE ON glGetNextJEWrapper TO DYNGRP;There is another code that a Microsoft technician passed on to us. You can use this code in the "Use Script" section of the Journal Entry# mapping. However, it only works for one journal entry at a time. Not sure if it's of much use, as I'm replacing it with the code above because of the single journal entry limitation.Thank you so much, Mariano. You're a celebrity in our office. If you're ever in Utah, stop by!Jessica, Spring2 Technologies

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: