Hi and welcome to another edition of The Dynamics GP Blogster! Someone, please explain something to me? Why is it that after 20 years of existance, we still don't have a Reconcile SOP Batches utility in Dynamics GP?
Well, I figured I would try to solve this puzzle by creating my own SQL server stored procedure and provide a few ideas on expanding the Remove-Reconcile Sales Documents window (Microsoft Dynamics GP | Tools | Utilities | Sales | Reconcile-Remove Sales Documents) to execute the stored procedure, all seamlessly integrated!
First the stored procedure:
This stored procedure should be created against each company database. Once created, you must run the GRANT.SQL utility script against each company database to make sure each user in the DYNGRP database role gains execution rights to it.
Ok, now that we have the stored procedure out of the way, lets focus on ways to improve our already existing UI to perform the task at hand:
1. You could add the form to Modifier, add a push button control, then add the form to VBA and program the push button control to execute the stored procedure (complexity level: beginners, required knowledge ADODB, SQLOLEDB, DYNAMICS GP VBA object model). This is what the code may look like (at least to read the connection information, since the rest is history):
2. You could use Dexterity to add a checkbox beneath the radio button group and set an event trigger on the Process button to fire up before or after the GP process is completed (complexity level: advanced, required knowledge SanScript). The trigger will need to be registered prior to being able to use it.
I would like to hear from you. Please leave your comments!
Until next post!
Mariano Gomez, MVP
UPDATES TO THIS ARTICLE:
12/15/2008 - Replaced cursor (below) for set-based approach (above). The set-based approach improves the stored procedure's performance and yields a better query plan that the cursor.