Sunday, December 20, 2009

Hybrid - SOP Batch Splitting

Two weeks ago, I began working on a Microsoft CRM to Microsoft Dynamics GP integration project. Sales orders initiated in CRM and are transferred to GP once they are submitted in CRM. Once the orders got to GP they must be placed on automatic hold for document verification. However, once the hold was removed, the client wanted to be able to transfer all verified orders in bulk... so far good! The problem though was, when orders were transferred to invoices, the invoices remained mixed with the orders that did not get transferred in the same batch. This presented a control issue for the client as the staff performing the documentation is not the same staff transferring the orders.

Last year I posted the article Moving SOP Transactions from One Batch to Another. This article demonstrated a SQL script that would allow a database administrator to move SOP documents from one batch to another. The script in that article forms the basis for this customization. However, to make it more user friendly, I decided to provide the client with a way of running it from the Microsoft Dynamics GP interface by adding a Split button with Modifier to the SOP Batch Entry window. The following is a modified version of the SOP Batch Entry window with the Split button.


The Split button in turn, prompts the user for a new batch that will host the invoices. Upon entering the new batch number, the VBA script instantiates the UserInfo object to create an ADO connection. The connection executes a stored procedure to move the invoices into the batch entered by the user.


While the customization has been designed specifically to move invoices into a new batch, it can be adapted to move same SOP document types out of a batch containing other SOP document types.

Downloads

SOP Split Batch - Click here to download package file and SQL stored procedure.

Until next post!

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

9 comments:

reccon10 said...

Where can I locate the stored procedure "pr_SplitSOPBatch" to complete the execution of the SOP Batch Split freebie?

Mariano Gomez said...

Reccon10,

Thanks for following up! Please replace pr_SplitSOPBatch with sopSplitBatch, which is the actual stored proc name that's embedded in the zip file.

I will update the code accordingly and sorry for the inconvenience.

MG.-

Naz said...

This seems useful and we would like to try it. Is the procedure backward compatible with GP v9 SP4?

Mariano Gomez said...

Naz,
It certainly is backward compatible, but I suggest you try in a test environment first.

MG.-
Mariano Gomez, MVP

Naz said...

Thanks for the response. We'll test as guided.

Flippant said...

Hi MG-
Trying to run the create procedure .sql on GP9 SP4, against the company DB, it looks like some columns don't exist in this version. Is it ok to take these out totally?

Msg 207, Level 16, State 1, Procedure sopSplitBatch, Line 164
Invalid column name 'Workflow_Approval_Status'.
Msg 207, Level 16, State 1, Procedure sopSplitBatch, Line 165
Invalid column name 'Workflow_Priority'.
Msg 207, Level 16, State 1, Procedure sopSplitBatch, Line 166
Invalid column name 'TIME1'.
Msg 207, Level 16, State 1, Procedure sopSplitBatch, Line 112
Invalid column name 'Workflow_Approval_Status'.
Msg 207, Level 16, State 1, Procedure sopSplitBatch, Line 113
Invalid column name 'Workflow_Priority'.
Msg 207, Level 16, State 1, Procedure sopSplitBatch, Line 114
Invalid column name 'TIME1'.
Msg 15151, Level 16, State 1, Line 4
Cannot find the object 'sopSplitBatch', because it does not exist or you do not have permission.

Mariano Gomez said...

@Flippant: it should be Ok to remove the columns that are erroring out. Keep in mind that this feature was written for version 10 and these columns were not present in v9.

Also be sure to look at the second comment above on the stored proc name.

Flippant said...

Thanks MG.
I took out the columns and ran the script successfully.

By "replace pr_SplitSOPBatch with sopSplitBatch, which is the actual stored proc name that's embedded in the zip file." - you are referring to the VBA call behind the 'split' button, correct? the line where it says '.CommandText = "EXEC pr_SplitSOPBatch ...'

I ask because i am not sure why, upon clicking the Split button, VBA editor comes up with Split - BeforeUserChanged -
the first line with 'Dim oCn As New ADODB.Connection' is highlighted and the error mesage says 'Compile Error: user defined type not defined'

is this related at all or another gp version difference thing?

Mariano Gomez said...

@Flippant: the reason you are getting this message is because you have not marked the Microsoft ActiveX Data Object 2.6 (or above) as a reference in your project. Go to Tools | References to complete this action.

Correct on the VBA action behind the Split button.