Getting the Next Voucher Number for a Payables Transaction Integration

September 7, 2011

At the beginning of the year, I wrote a 2-part series on retrieving document numbers assigned by Microsoft Dynamics GP when a field rule is set to Use Default in Integration Manager, see:

IM – Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager – Part 1

IM – Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager – Part 2

Back then, I was addressing an issue faced by many working with integrations that require you to pass back some value to a source system and the complexities involved in retrieving those values, especially when the field rule is set to Use Default, this is, Microsoft Dynamics GP is assigning the field value, not the integration (via source file or otherwise).

Today, I am looking at a slightly different issue, nonetheless, related.

In this occasion, the client wanted to retrieve the next voucher number before hand for a payables transaction integration and assign it to the voucher number field, but did not want to have to write their own script. In essence, they wanted to leverage whatever mechanism built already in Microsoft Dynamics GP’s business logic to get the next voucher number.


Payables Setup Options – Next Voucher Number field

 Indeed, writing their own code would involve retrieving the field value and incrementing the numeric part of the string. This sometimes can be a nightmare, especially when having to deal with record collisions and users accessing the system while the integration was running.

In doing some additional reading, I realized that eConnect already included this method, so all I had to do was find the SQL Server stored procedure to get the next voucher number. That stored procedure is conveniently named taGetPMNextVoucherNumber. One down, 2 more to go!

The second challenge with eConnect stored procedures is to determine the parameters that need to be passed in, but all eConnect stored procedures are created with encryption, so editing them was not an option. However, I remembered that in SQL Server Management Studio, you have the ability to execute a stored procedure from the Management Studio UI and that this would in effect display a window with the parameters, furthermore detailing data types and whether they are input or output type parameters.

Execute Stored Procedure option (Right-click)

Execute Procedure window

The good thing about this window is you can enter values for your input parameters and click on OK, and SQL Server will automatically generate a template for executing the stored procedure, with variable declarations, types, and all. The construct looks something like this:

USE [TWO]
GO

DECLARE @return_value int,
@O_vCNTRLNUM varchar(21),
@O_iErrorState int

EXEC @return_value = [dbo].[taGetPMNextVoucherNumber]
@O_vCNTRLNUM = @O_vCNTRLNUM OUTPUT,
@I_sCNTRLTYP = 0,
@O_iErrorState = @O_iErrorState OUTPUT

SELECT @O_vCNTRLNUM as N'@O_vCNTRLNUM',
@O_iErrorState as N'@O_iErrorState'

SELECT 'Return Value' = @return_value
GO

This was fantastic, because now I did not have to struggle with understanding what needed to be passed in. It so happens that the control type parameter, @I_sCNTRLTYP, requires a zero to retrieve the next voucher number. In essence, I played with the parameter value and compared to what I was seeing in the GP interface (above), so here are the parameter values accepted for control type:

0 – Next Voucher Number
1 – Next Payment Number
2 – Next Alignment Number

Two down, 1 more to go.

Finally, the rest is putting the scripts together in Integration Manager to call the stored procedure.

As a best practice,  I tend to make the connections to the database persistent throughout the integration. This assures me that connections are only opened once, and closed at the end of the integration, improving the overall performance of the integration and reducing the points of failure. So, as you can imagine, a before document or a field script aren’t the places to open and close connections, as these events occur over and over, based on the number of records being integrated.

I typically open the connection in the Before Integration event script, so this is what this script looks like:

' Created by Mariano Gomez, MVP
' This code is licensed under the Creative Commons
' Attribution-NonCommercial-ShareAlike 2.5 Generic license.
'
' Persisting ADO connection

Const adUseClient = 3
Dim oCn

Set oCn = CreateObject("ADODB.Connection")
With oCn
.ConnectionString = "database=" & GPConnection.GPConnIntercompanyID
.CursorLocation = adUseClient
End With

GPConnection.Open(oCn)
SetVariable "gblConn", oCn

Note that the connection object is stored in a global variable, gblConn, using the SetVariable statement in Integration Manager.

Once we have the connection piece sorted out, now we can focus on adding code to the Voucher Number field script to call the eConnect stored procedure, as follows:

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

' Prepare the SQL statement and retrieve the next voucher number
Const adCmdStoredProc = 4
Const adVarchar = 200
Const adInteger = 3
Const adParamInput = 1
Const adParamOutput = 2
Const PMVoucher = 0

Set oCmd = CreateObject("ADODB.Command")
With oCmd
.ActiveConnection = GetVariable("gblConn")
.CommandType = adCmdStoredProc
.CommandText = "taGetPMNextVoucherNumber" 'the eConnect stored proc

.Parameters.Append .CreateParameter ("@O_vCNTRLNUM", adVarchar, adParamOutput, 25)
.Parameters.Append .CreateParameter ("@I_sCNTRLTYP", adInteger, adParamInput)
.Parameters.Append .CreateParameter ("@O_iErrorState", adInteger, adParamOutput, 4)

oCmd.Parameters("@I_sCNTRLTYP").Value = PMVoucher
.Execute
NextVoucher = oCmd.Parameters("@O_vCNTRLNUM").Value
CurrentField.Value = NextVoucher
'MsgBox NextVoucher
End With

Set oCmd = Nothing

Note how in this occasion, we are using the GetVariable function to retrieve a pointer to the connection object stored in the global variable. We then access the Parameters object to add the different parameters and set the value for the input parameter to the stored procedure.

When this script is executed within the context of the integration, it effectively returns the next voucher number for the transaction being integrated, from which you can proceed to update this information in your source system, if needed.

Note that by using standard Microsoft Dynamics GP business logic, your integration can now be supported if you need to open a support incident. Indeed another method for retrieving a document number for your transaction.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Advertisements

"Object Reference Not Set" error when running Integration Manager with eConnect Adapter

August 30, 2011

I have seen a number of forum posts around this subject and have even received a few calls for help in troubleshooting the issue. In the occassions I have assisted someone, I have noticed that most of the time the developer or consultant was using an event or field script of some kind, which almost always attempts to get some information from Microsoft Dynamics GP.

So, in an attempt to reproduce the problem, I have recreated the following VBScript based on a recent case:

' Created by Mariano Gomez, MVP

' This code is licensed under the Creative Commons
' Attribution-NonCommercial-ShareAlike 2.5 Generic license.

Dim objConn, objRec, cmd, sJE


set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "database=" & GPConnection.GPConnInterCompanyID
GPConnection.Open(objConn)


Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = objConn

cmdString = "SELECT NJRNLENT FROM GL40000;"
Set objRec = objConn.Execute(cmdString)

if Not objRec.Bof and Not objRec.Eof then
objRec.MoveFirst
CurrentField = objrec.fields(0).value
end if

'Close recordset when finished
Call objRec.Close

'Close connection when finished
Call objConn.Close

Set cmd = Nothing
Set objConn = Nothing

NOTE: This script purposefully contains errors and does not follow best practices. It was recreated to illustrate the issue on the subject.

In summary, the above script was added by the consultant to retrieve the next journal number for a GL Transaction integration with the eConnect Adapter. The consultant reported the script working on and off on the server and not working on the workstations. However, in each case the error reported by Integration Manager is as follows:

Opening source query...

Establishing source record count...
Beginning integration...

DOC 1 ERROR: Error Executing Script 'GLTransaction.Journal Entry#' Line 9: -
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Integration Failed
Integration Results
1 documents were read from the source query.
1 documents were attempted:
0 integrated without warnings.
0 integrated with warnings.
1 failed to integrate.

The error indicates the is a problem with the data source name not being found, which leads to an object reference problem when the connection is attempted. But why would this code work on the server at times and not work on the client? Then it hit me!

The GPConnection object retrieves the connection and login information for the user currently signed on to Microsoft Dynamics GP… and therein lies the issue! The GPConnection object actually requires the Microsoft Dynamics GP user interface to be active for the object to retrieve the connection information, which is typically not the case for eConnect Adapter-based integrations.

As a side note, the times the integration did work, the user interface HAD to be active, but this was not apparent to the consultant.

So, how can we adjust this integration to follow best practices and work without the Microsoft Dynamics GP user interface having to be active?

The answer is relatively simple. The above code will need to switch out the way it obtains the connection string for an actual (as in hardcoded) connection string.

'

objConn.ConnectionString = "Provider=SQLNCLI10;Server=yourSQLServerName;_
Database=YourCompanyDB; Trusted_Connection=yes;" 

Because the script uses a trusted connection to the database (a best practice), it is advisable that proper permissions be granted to the user’s domain account on SQL Server in order for the integration to be successful. The domain account will also need to be added to the DYNGRP role. What many customers have done is created specific domain accounts to execute eConnect integrations under a trusted connection. This further limits the exposure to security breaches.

For a final look at a technique to implement the above script, see the following article on this site:

Integration Manager: Integrating journal entries with Analytical Accounting Information

Hope you found this post useful.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/


IM – Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager – Part 2

January 9, 2011

Part 2 of 2 – Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager.


In the previous installment, I explained the technique that I otherwise use to relate source systems records with imported records in Microsoft Dynamics GP for which a Use Default field rule has been set for the key field. This article demonstrates the key event scripts needed to obtain the record.

Before Integration event script

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

' Secure a connection against the company database we will be running
' the integration against.

Const adUseClient = 3

set oCn = CreateObject("ADODB.Connection")
oCn.ConnectionString = "database=" & GPConnection.GPConnIntercompanyID
oCn.CursorLocation = adUseClient
GPConnection.Open(oCn)

' Make sure the connection is valid
If (oCn.Status = 1) Then
' Setup global connection variables
SetVariable "gblConn", oCn
SetVariable "gblInterID", GPConnection.GPConnIntercompanyID
Else
CancelIntegration
End If

The Before Integration event script will allow us to secure a connection against the company database we will be running our integration against. By opening the connection in this event script, we will avoid having to open a connection for each transaction being integrated, further on, creating any loss of performance in the process. It is important to highlight that we need to save the successful connection to a global variable, to be able to use it in other event scripts. In this case, we will assign the oCn connection variable to a global variable, gblConn, using the SetVariable statement.

Once Integration Manager has integrated the document, we will use the After Document event script to retrieve the record integrated. At this stage, we are assuming that the mapping of source fields to destination fields provisions a user-defined field or description field for the source key field. In the past, I have also used note fields to store these key fields when it has not been feasible to use a standard Microsoft Dynamics GP field.

Note: the After Document event script will only execute upon success of the document being integrated. If Integration Manager is unsuccessful integrating the document, the Document Error script will execute instead. This event can be used to report failure to the source system, which may facilitate new attempts to integrate from the source system by reporting different event statuses.

After Document event script

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

' Prepare the SQL statement and retrieve the assigned Sales Transaction number
Set oCmd = CreateObject("ADODB.Command")
With oCmd
.ActiveConnection = GetVariable("gblConn")
.CommandType = adCmdText

.CommandText = "SELECT SOPNUMBE FROM SOP10106 WHERE USERDEF05 = '" & SourceFields("mySourceQry.KeyField") & "'"
Set oRst = .Execute

If Not oRst.EOF Then
SopNumber = oRst!SOPNUMBE
End If
oRst.Close
End With

' From here on you can open a connection to your source system and update the
' some status flag and the column provisioned to track the GP document number

I hope you find this technique useful. Of course, this is a technique I have been using over the years. I would like to find out from you what methods you have used to accomplish the same.

Until next post!

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


IM – How to group Integration Manager transactions based on transaction date

February 9, 2010

Just recently, I fielded a question where the user wanted to group a number of customer invoice transactions in a file based on the invoice date. The file happened to contain invoices downloaded from the billing system in a date range, for example 02/01/2010 – 02/05/2010. They wanted the resulting transaction batches in GP to be something like,

AR02012010
AR02022010
AR02032010
AR02042010
AR02052010

This would facilitate analysis by day and to make sure control totals matched those created on a daily basis.


The solution involves adding a field script to the batch ID field which in turn reads the transaction date field from the source query and forms the new batch ID with that information:

Very simple, but useful script. Keep in mind that you must enable the option to Add Missing batches for the integration as shown below:

When the integration is executed, it will create as many AR batches in Microsoft Dynamics GP as unique transaction dates there are in the file.

Until next post!

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


IM – How to filter source query records dynamically

January 16, 2010

Ever wanted to give users the ability to limit source records dynamically? How about being able to filter records from in a source query by a date range? Well, I encoutered this situation working on my current project.

Out of the box, Integration Manager offers the ability to retrieve records and set static filters to source records. However, in many cases users may want to dynamically (at runtime) establish a date range or any other range parameter for the records, then have these ranges applied to the source query.

This is possible by using some old fashioned VBScript with Integration Manager. Consider the following records:


DOCUMENT_DATE JOURNAL_NUMBER ACCOUNT_NUMBER DEBIT_AMOUNT CREDIT_AMOUNT
12/01/2009 100 000-1200-00 100.00 0.00
12/01/2009 100 000-6620-00 0.00 100.00
12/15/2009 200 000-1201-00 22.50 0.00
12/15/2009 200 000-6630-00 0.00 22.50
01/06/2010 300 000-1200-00 120.00 0.00
01/06/2010 300 000-6620-00 0.00 120.00
01/20/2010 400 000-1201-00 52.50 0.00
01/20/2010 400 000-6630-00 0.00 52.50

In order to make our integration interactive, we must first prompt the user to enter the date restriction in the format required to filter the data:

Before Integration


Dim startDate, endDate

Do
startDate = InputBox("Enter the start date for your transactions (mm/dd/yyyy).")
If Not IsDate(startDate) Then
MsgBox "Invalid date format, please try again."
End If
Loop Until IsDate(startDate)

Do
endDate = InputBox("Enter the end date for your transactions (mm/dd/yyyy).")
If Not IsDate(endDate) Then
MsgBox "Invalid date format, please try again."
End If
Loop Until IsDate(endDate)

If CDate(startDate) > CDate(endDate) Then
MsgBox("The start date must be greater than the end date. Integration will end now.")
CancelIntegration
End If

' Store the user input in global variables that can be retrieved later on
SetVariable "gblStartDate", startDate
SetVariable "gblEndDate", endDate

Now we can apply the user’s input as restrictions to our source query data by invoking the AdditionalCriteria property of the Query object.

Before Query


Query.AdditionalCriteria = "DOCUMENT_DATE >= '" & CDate(GetVariable("gblStartDate")) & "' AND DOCUMENT_DATE <= '" & CDate(GetVariable("gblEndDate")) & "'"

Note that the Query.AdditionalCriteria will only work on source queries that use a Text or Simple ODBC DSN. The AdditionalCriteria property will not work on Advanced ODBC queries.

Until next post!

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


Color coding Integration Manager scripts

August 12, 2009

Ok, so this is not currently possible, but won’t it be great if it were? :-))

This question came up in the Dynamics GP Developers’ newsgroup just a couple days aback and while the developer was sure of seing this at one of his clients, I assured him that Integration Manager did not have the capability of color coding the script, which is very standard stuff in other script editors and development environments, take for example Dynamics GP VBA code editor.

The above image is simulated and not an actual capability of Integration Manager

Nonetheless, I explained to the developer that I use other tools like VbsEdit to provide script color coding and some debugging capabilities. Except for native Integration Manager objects, VbsEdit is capable of color coding keywords, numbers, strings, etc., and provide line numbering which facilitates code readability and troubleshooting. Did I mention the ability to insert code breakpoints and the more than 200 sample scripts provided?

So the next time you are struggling with VBScripting within Integration Manager, consider giving VbsEdit a shot. You may also want to look into Notepad++ which offers similar features.

Finally as I may be wrong and since nothing is set in stone, if you have been able to enable script color coding in Integration Manager’s Script Editor please post back with how you did it.

Until next post,

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


How to import the Vendor 1099 Box with Integration Manager

May 14, 2009

Business Situation

A few days aback I addressed an issue where the user needed to set the 1099 Box on the vendor import with Integration Manager. To my surprise, while there is a field to map the 1099 Type, there is no 1099 Box field in the Options collection in the Vendor import in IM. To make matters a bit more interesting, the user needed to have the 1099 Box filled based on some settings in the source system.

Solution

The 1099 Box is not accessible via Integration Manager, however we can create a small SQL Server stored procedure in the company database that will accept the vendor ID and the 1099 Box as parameters. The stored procedure in turn will update the vendor record with the value submitted.

dbo.uspUpdateVendor1099Box



-- Created by Mariano Gomez, MVP

IF OBJECT_ID ( N'dbo.uspUpdateVendor1099Box', N'P' ) IS NOT NULL
DROP PROCEDURE uspUpdateVendor1099Box;
GO

CREATE PROCEDURE dbo.uspUpdateVendor1099Box
@VENDORID CHAR(21) = NULL
,@TEN99BOXNUMBER SMALLINT = NULL
,@RET_VAL INT OUTPUT
AS

SET @RET_VAL = 0

IF (@VENDORID IS NULL) OR (@TEN99BOXNUMBER IS NULL)
BEGIN
SELECT @RET_VAL = 0;
RETURN
END

BEGIN TRY
UPDATE PM00200 SET TEN99BOXNUMBER = @TEN99BOXNUMBER WHERE VENDORID =
@VENDORID
END TRY
BEGIN CATCH
SELECT @RET_VAL = ERROR_NUMBER()
END CATCH;
GO

GRANT EXECUTE ON dbo.uspUpdateVendor1099Box TO DYNGRP
GO

Now, in the Vendor integration, we can open the After Document event script and add the following VBScript code:

After Document



' Created by Mariano Gomez, MVP
Const adUseClient = 3
Const adCmdStoredProc = 4
Const adSmallInt = 2
Const adInteger = 3
Const adChar = 129
Const adParamInput = 1
Const adExecuteNoRecords = 128

Dim oCn
Dim oCmd
Dim pVendorID, pTen99BoxNumber, pRetVal

set oCn = CreateObject("ADODB.Connection")
oCn.ConnectionString = "database=" & GPConnection.GPConnIntercompanyID
oCn.CursorLocation = adUseClient
GPConnection.Open(oCn)

With oCmd
.ActiveConnection = oCn
.CommandType = adCmdStoredProc or adExecuteNoRecords
.CommandText = "dbo.uspUpdateVendor1099Box"


'Set parameters
set pVendorID = .CreateParameter("VENDORID", adChar, adParamInput, 21, _
GetVariable("gVendorID"))
.Parameter.Append pVendorID

set pTen99BoxNumber = .CreateParameter("TEN99BOXNUMBER", adSmallInt, _
adParamInput, 8, GetVariable("gBoxNumber"))
.Parameter.Append pTen99BoxNumber

set pRetVal = .CreateParameter("RET_VAL", adInteger, adParamOutput)
.Parameter.Append pRetVal

.Execute
End With

If pRetVal.Value 0 Then
' Error handling here, you can choose to write to IM's log
End If

Since I am making use of global variables, gVendorID and gBoxNumber, we will need to capture both of these values in the vendor ID field script — we will no longer map the vendor ID since it’s necessary to use a field script.

Vendor ID field script



' Created by Mariano Gomez, MVP
SetVariable "gVendorID", SourceFields("someSource.Vendor")
SetVariable "gBoxNumber", SourceFields("someSource.Ten99Box")
CurrentField.Value = SourceFields("someSource.Vendor")

This conclude our integration! Don’t get discourage if you don’t see a specific field mapping in IM. With little creativity and the right mindset you can always come around with a solution that is worth showcasing.

Until next post!

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