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 |
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 |
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
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
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
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/
1 comment:
How would you do this with the Next Asset ID in GP2013+ when using IM? I don't want to put the asset id in my source file - I want GP to assign it.
Thanks.
Post a Comment