Creating your own Dynamics GP Rolling Column

SmartConnect has an Additional Column type for the Dynamics GP Destination types called GP Rolling Column that is used to get the next transaction number from the Dynamics GP setup tables. This allows for maintaining the existing Dynamics GP numbering sequence for all transactions and prevents the potential for duplicate document numbers.

Not every Dynamics GP transaction type next number process has been created within SmartConnect and there are many 3^rd Party products that may require the same functionality. However, we have the ability to insert records into SmartConnect tables to add our own numbering process to our SmartConnect integrations. If you are not sure of the current available GP Rolling Columns, review this article first. http://www.eonesolutions.com/blog-post/next-dynamics-gp-document-number-smartconnect/

The custom next number functionality is available with SmartConnect 20.15 and newer.

To show how we can accomplish this, we will use the Dynamics GP Inventory Assembly process as an example.

 

Stored Procedure

The first step is to create a stored procedure that retrieves the next document from the Dynamics GP setup table, increments it and puts the new number back into the Dynamics GP setup table. You will want to discuss where the transaction number is stored with the vendor who wrote the module being integrated. Be sure to create the stored procedure in each Dynamics GP Company or an error will occur during the integration where this procedure does not exist.

This SQL Statement gets the next document number from the BOM40100 table and increments it by one, putting the new value back into the BOM40100 table. Then it checks to see if it may have been used already and if so, it will increment the number again. So it does not loop forever, it will check 1000 times to see if it can get a valid number and errors if it can’t find an unused number.

 

— Procedure to get the next Assembly Document

IF EXISTS (

SELECT *

FROM INFORMATION_SCHEMA.ROUTINES

WHERE SPECIFIC_SCHEMA = N’dbo’

AND SPECIFIC_NAME = N’E1_Get_Next_Assembly_Document_Number’

)

DROP PROCEDURE E1_Get_Next_Assembly_Document_Number

GO

CREATE PROCEDURE E1_Get_Next_Assembly_Document_Number

@TYPE SMALLINT, — {series id } Dynamics GP Series/Module ID, if needed

@DOC_TYPE SMALLINT, — {document Id} DocumentTypeNumber from MsGpRollingColumn table

@DOC_ID CHAR(21) = ”,

@INC_DEC TINYINT = 1 — 1 is increment, 0 is decrement

AS

DECLARE @AssemblyDocument VARCHAR(21)

DECLARE @I_tInc_Dec tinyint

DECLARE @O_iErrorState int

DECLARE @Loop int = 0

SET @I_tInc_Dec = @INC_DEC  

WHILE @Loop < 1000

BEGIN

SELECT @AssemblyDocument = TRX_ID FROM BM40100 WHERE SETUPKEY = 1

 

UPDATE BM40100 SET TRX_ID = dbo.E1_fnIncrementDecrement(TRX_ID,0)

WHERE SETUPKEY = 1

— make sure the document doesn’t already exist

IF EXISTS(SELECT 1 FROM BM10200 WHERE TRX_ID = @AssemblyDocument)

BEGIN

SELECT @Loop = @Loop + 1

SELECT @AssemblyDocument = ‘Err’

END

ELSE IF EXISTS(SELECT 1 FROM BM30200 WHERE TRX_ID = @AssemblyDocument)

BEGIN

SELECT @Loop = @Loop + 1

SELECT @AssemblyDocument = ‘Err’

END

ELSE

BEGIN

SET @Loop = 1000

END

END

SELECT @AssemblyDocument AS Number

GO  

GRANT EXECUTE ON E1_Get_Next_Assembly_Document_Number TO DYNGRP

GO

 

SmartConnect Rolling Document

The next step is to insert a record into the MsGPRollingDocument table so it will appear in our GP Rolling Column list.
The fields for this table are as follows

 

SmartConnect Rolling Document Series

If the next document you want to create is not in an existing Series, like Inventory or Financial, you can insert a record into SmartConnect MsGpRollingSeries to create a new Series for use with the Dynamics GP Rolling Column.

 

SmartConnect Variables

The following replacement variables are available to be used as data to pass to your stored procedure, if needed. You can use these variables in the Execute Command section of the MsGpRollingRolling column as they will be replaced with the values from SmartConnect.

 

 

NOTE: Replace [SmartConnect] with the correct SmartConnect System database if it is not the default.

 

USE [SmartConnect]

GO

— If we are using a new custom series, insert into the MsGpRollingSeries first

— insert into MsGpRollingSeries(MsGpRollingSeriesId, SeriesName, SeriesNumber) values(‘Custom Series’,’Custom Series Name’,{Series Number})

— Need to insert a record to create the next document number

insert into MsGpRollingDocument(MsGpRollingDocumentId,DocumentType,DocumentTypeNumber, DocumentTypeIdQuery,ExecuteCommand, MsGpRollingSeriesId)

values(NEWID(),‘Assembly Document’,10,”,‘exec {CompanyId}.dbo.E1_Get_Next_Assembly_Document_Number ”{seriesId}”, ”{documentId}”’,‘Inventory’)

 GO

 

 

Dynamics GP Rolling Column

Once we have created the stored procedure and added our Rolling Column to the database we can add the GP Rolling Column to our mapping.

Open the node mapping.

Click on Additional Columns.

Click on Columns->GP Rolling Column

For my example, we select the Inventory series and then our new Document Type of Assembly Document.

Click Ok and Ok.

Then Map your new GP Rolling Column from the Source Columns to the Transaction Number on the Destination Columns.

 

Now, when we run our integration it will retrieve that next document number each time we send a document to Dynamics GP.