Thursday, November 4, 2010

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 "gblJournal", 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
IntellPartners, LLC
http://www.IntellPartners.com/

Edits:
05/25/2011 - fixed IM global variable name as it was not matching between the Before Document script and the Journal Number Field Script, causing nothing to be assigned to the field and erroring out the integration.

12 comments:

David Musgrave said...

Hi Mariano

Great post that many users will find helpful.

David
http://blogs.msdn.com/DevelopingForDynamicsGP/

Nacho said...

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.0

Thanks in advance!

Ignacio

Jason & I said...

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

Brendon said...

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

Jessica L said...

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;
GO
CREATE PROCEDURE glGetNextJEWrapper
@IO_iOUTJournalEntry int OUTPUT,
@O_iErrorState int OUTPUT
AS

DECLARE @l_tINCheckWORKFiles tinyint
declare @I_iSQLSessionID int
declare @O_tOUTOK tinyint;

IF @IO_iOUTJournalEntry IS NULL
SET @IO_iOUTJournalEntry = 0
set @l_tINCheckWORKFiles = 1
set @I_iSQLSessionID = USER_SID()


EXECUTE glGetNextJournalEntry
@l_tINCheckWORKFiles
,@I_iSQLSessionID
,@IO_iOUTJournalEntry OUTPUT
,@O_tOUTOK OUTPUT
,@O_iErrorState OUTPUT
GO
GRANT 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

Anonymous said...

I am using IM 10 SP5 and trying to upload JV with AA data but getting below mentioned error. Please advice

DOC 1 ERROR: Microsoft.Dynamics.GP.eConnect: Number = 9466 Stored Procedure taAnalyticsDistribution : Description = Code is setup as Fixed, cannot be updated

Anonymous said...

Hi this is the error that am I am getting, once I run the integration:

Object reference not set to an instance of an object. error compling script 'Before Document' Line 1: - Expected statement
Expected statement

Peter Muchira.

Mariano Gomez said...

@Peter:

Take a look at http://dynamicsgpblogster.blogspot.com/2011/08/object-reference-not-set-error-when.html

Anonymous said...

Hi Mariano,
Interesting article.
I have a query that you may be able to assist me with.
I would like to integrate
i. GL transaction with analytical information as well as multicurrency details. Is this possible using econnect ?

Thanks
@GP USER

Mariano Gomez said...

@GP User,

Yes, it's possible. Follow the eConnect Programmer's Guide for XML nodes information.

MG.-
Mariano Gomez

Heather Roggeveen said...

Hi Mariano

I am working through integrating into SOP with AA using Integration Manager. Can the same theory be used for getting the next SOP number. I am in no way technical so wondering if some coding existed out there already for this.

Cheers

Heather

Mariano Gomez said...

Heather,

The same technique could be used, but I recommend you explore a tool like SmartConnect instead of IM as SmartConenct exposes all the eConnect nodes, including the ones to support Analytical Accounting.