Getting the Next Voucher Number for a Payables Transaction Integration

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/