Reconciling SOP Batches

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:

dbo.spReconcileSOPBatches

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
--

CREATE PROCEDURE dbo.spReconcileSOPBatches AS

WITH SOP_BACHES (BACHNUMB, NUMOFTRX, BCHTOTAL) AS (
 SELECT BACHNUMB, ISNULL(COUNT(SOPNUMBE), 0) AS NUMOFTRX, ISNULL(SUM(DOCAMNT), 0) 
    AS BCHTOTAL
 FROM SOP10100
 GROUP BY BACHNUMB
)
UPDATE A SET A.NUMOFTRX = B.NUMOFTRX, A.BCHTOTAL = B.BCHTOTAL
FROM SY00500 A INNER JOIN SOP_BACHES B ON (A.BACHNUMB = B.BACHNUMB)
WHERE (A.SERIES = 3) AND (A.BCHSOURC = 'Sales Entry');
GO

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):

GetConnectionInfo()

' Created by Mariano Gomez, MVP
' This code is licensed under the Creative Commons 
' Attribution-NonCommercial-ShareAlike 3.0 Unported License.
' http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
'
Dim cn As New ADODB.connection
Dim rst As New ADODB.recordset
Dim cmd As New ADODB.Command

Private Sub GetConnectionInfo()

  Set cn = UserInfoGet.CreateADOConnection
  cn.DefaultDatabase = UserInfoGet.IntercompanyID
  cmd.ActiveConnection = cn
End Sub

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.

Startup

{ Created by Mariano Gomez, MVP     }
{ This code is licensed under the Creative Commons   }
{ Attribution-NonCommercial-ShareAlike 3.0 Unported License. }
{ http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode }
{        }
{ global procedure: Startup     }

local integer l_result;
l_result = Trigger_RegisterFocus(anonymous('Process Button' of windowSOP_Reconcile of form SOP_Reconcile), TRIGGER_FOCUS_CHANGE, TRIGGER_BEFORE_ORIGINAL, script ReconcileSOPBatches);

if l_result > SY_NOERR then
  warning "Trigger registration failed.";
end if;

ReconcileSOPBatches

{ Created by Mariano Gomez, MVP     }
{ This code is licensed under the Creative Commons   }
{ Attribution-NonCommercial-ShareAlike 3.0 Unported License. }
{ http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode }
{        }
{ global procedure: ReconcileSOPBatches }
local long status;
call spReconcileSOPBatches, status;

spReconcileSOPBatches

{ Created by Mariano Gomez, MVP     }
{ This code is licensed under the Creative Commons   }
{ Attribution-NonCommercial-ShareAlike 3.0 Unported License. }
{ http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode }
{        }
{ global prototype procedure: spReconcileSOPBatches}
sproc returns long Ret_Code;

local long timedelay;
local integer i, n_loopcount;

if 'Reconcile Batches' of window SOP_Reconcile of form SOP_Reconcile = 1 then
  try
    call sproc "spReconcileSOPBatches", Ret_Code;
  catch [EXCEPTION_CLASS_DB_DEADLOCK]
    if i <= n_loopcount then      
      timedelay = Timer_Sleep(200);
      increment i;
      restart try;
    else
      exit try;
    end if;
  else
    exit try;
  end try;
end if; 

I would like to hear from you. Please leave your comments!

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/


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.

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
--
CREATE PROCEDURE dbo.spReconcileSOPBatches AS
DECLARE @bachnumb char(25), @numtrx int, @batchamount numeric(19,5)
DECLARE c_sop CURSOR FOR
  SELECT BACHNUMB FROM SY00500
  WHERE (SERIES = 3) AND (BCHSOURC = 'Sales Entry')

OPEN c_sop
FETCH NEXT FROM c_sop INTO @bachnumb

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @numtrx = ISNULL(COUNT(SOPNUMBE),0), @batchamount = ISNULL(SUM(DOCAMNT),0)
  FROM SOP10100
  WHERE BACHNUMB = @bachnumb

  UPDATE SY00500 SET NUMOFTRX = @numtrx, BCHTOTAL = @batchamount
  WHERE BACHNUMB = @bachnumb

  FETCH NEXT FROM c_sop INTO @bachnumb
END
CLOSE c_sop
DEALLOCATE c_sop
GO