How to roll down SOP's Requested Ship Date changes to line items and purchase orders - Part II

In the previous installment, you saw how to make changes to the Requested Ship Date roll down to the line items and linked purchase order documents via a SQL Server trigger.

This article will now show a VBA method combined with a SQL Server stored procedure call. The advantage of using VBA is to allow more interaction with the end-user and control the flow a bit more.

The following steps will get us there:

1) Add the Sales Transaction Entry window to VBA. Open the Sales Transaction Entry window and press CTRL + F11 on your keyboard.

2) Add the Document Type drop-down list, Document Number, and Customer ID fields to VBA. Press Shift + F11 on your keyboard to activate the visual cue. Once active, click on both fields. Press Shift + F11 to deactivate the visual cue.

3) Add the Sales Date Entry window to VBA. Click on the expansion button next to the Date field to open the Sales Date Entry window and press CTRL + F11 on your keyboard.

4) Add the Requested Ship Date field to VBA. Press Shift + F11 on your keyboard to activate the visual cue. Once active, click on the Requested Ship Date field. Press Shift + F11 to deactivate the visual cue.

5) Open the Visual Basic Editor. Press ALT + F11 on your keyboard. Locate the Microsoft Dynamics GP project in the Project Explorer window to the left of the screen. Expand to locate the SalesDateEntry (Window) object. Double-click to open the code editor.

6) Copy and paste the following code in the Editor:

'----------------------------------------------------------------------
' Created by Mariano Gomez
' Maximum Global Business, LLC - http://www.maximumglobalbusiness.com
' (C) 2008
' This code is provided "AS IS" with no warranties expressed or implied
'----------------------------------------------------------------------
Option Explicit

Private Sub RequestedShipDate_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
    Dim result As Integer
    Dim SOPNumber As String
    Dim SOPType As Integer

    Dim objConn As New ADODB.Connection
    Dim objCmd As New ADODB.Command
    Dim objRS As New ADODB.Recordset

    If Not (SalesTransactionEntry.DocumentNo.Empty) And Not (SalesTransactionEntry.CustomerID.Empty) Then
        ' Check to see if there are any line items entered for this transaction, otherwise

        Set objConn = UserInfoGet.CreateADOConnection()
       
        With objConn
           .CursorLocation = adUseClient
           .DefaultDatabase = UserInfoGet.IntercompanyID
        End With
       
        With objCmd
            .ActiveConnection = objConn
            .CommandType = adCmdText
            .CommandText = "SELECT * FROM SOP10100 WHERE SOPNUMBE = '" & SOPNumber & "' AND SOPTYPE = " & CStr(SOPType)
            Set objRS = .Execute
        End With
   
        If objRS.RecordCount > 0 Then
            ' We have some records to process in the SOP_Line_WORK
       
            result = MsgBox("Do you want to roll down changes to line items and linked POs?", vbYesNo, "Roll down Requested Ship Date")
            If result = vbYes Then
                SOPNumber = SalesTransactionEntry.DocumentNo
           
                ' Since the SOP Types in the DDL are not equivalent to the actual SOP Types stored in SOP10100
                ' we need to switch them around a bit
           
                Select Case SalesTransactionEntry.TypeTypeID
                    Case 1
                        SOPType = 1  'Quote
                    Case 2
                        SOPType = 2  'Order
                    Case 4
                        SOPType = 3  'Invoice
                    Case 5
                        SOPType = 4  'Return
                    Case 6
                        SOPType = 5  'Back Order
                    Case 3
                        SOPType = 6  'Fulfillment Order
                End Select
                           
                With objCmd
                    .CommandType = adCmdText
                    .CommandText = "EXEC usp_SOPRollDownReqShipDate '" & SOPNumber & "', " & CStr(SOPType) & ", '" & CStr(RequestedShipDate) & "'"
                    .Execute , , adExecuteNoRecords
                End With
           
                Set objCmd = Nothing
           
                ' close and destroy Recordset object
                objRS.Close
                Set objRS = Nothing
           
                ' close and destroy connection object
                objConn.Close
                Set objConn = Nothing
             End If

        End If
    End If
End Sub

7) Go to the Debug menu and select Compile Microsoft Dynamics GP.

8) The above code calls the dbo.usp_SOPRollDownReqShipDate stored procedure which must be created for each company database. Open SQL Server Management Studio and create a New Query. Copy and paste the following code to your query window:

/*
'----------------------------------------------------------------------
' Created by Mariano Gomez
' Maximum Global Business, LLC - http://www.maximumglobalbusiness.com
' (C) 2008
' This code is provided "AS IS" with no warranties expressed or implied
'----------------------------------------------------------------------
*/
USE [YourCompanyDB]
GO

/****** Object:  StoredProcedure [dbo].[usp_SOPRollDownReqShipDate]    Script Date: 12/22/2008 19:34:32 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SOPRollDownReqShipDate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_SOPRollDownReqShipDate]
GO

/****** Object:  StoredProcedure [dbo].[usp_SOPRollDownReqShipDate]    Script Date: 12/22/2008 19:34:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_SOPRollDownReqShipDate]
 @SOPNumber char(31),
 @SOPType smallint,
 @ReqShipDate datetime
AS

IF ISNULL(@SOPNumber, '') = ''
 RETURN

BEGIN TRAN

  -- roll down to all items
  UPDATE SOP10200 WITH (ROWLOCK) SET ReqShipDate = @ReqShipDate
  WHERE SOPNUMBE = @SOPNumber and SOPTYPE = @SOPType

  -- updated the Required Date column for linked POs
  UPDATE A WITH (ROWLOCK) SET A.REQDATE = @ReqShipDate
  FROM POP10100 A INNER JOIN SOP60100 B ON A.PONUMBER = B.PONUMBER
  WHERE (B.SOPNUMBE = @SOPNumber) AND (B.SOPTYPE = @SOPType)

  -- updated the Required Date column for the line items on linked POs
  UPDATE A WITH (ROWLOCK) SET A.REQDATE = @ReqShipDate
  FROM POP10110 A INNER JOIN SOP60100 B ON (A.PONUMBER = B.PONUMBER) AND (A.ORD = B.ORD)
  WHERE (B.SOPNUMBE = @SOPNumber) AND (B.SOPTYPE = @SOPType)

 IF @@ERROR = 0
  COMMIT TRAN
 ELSE
  ROLLBACK TRAN

GO

GRANT EXECUTE ON [dbo].[usp_SOPRollDownReqShipDate] TO DYNGRP
GO

9) Execute the query against each company database to be able to run the VBA customization across more than one company.

Testing the Customization

To test the customization, select an existing sales order. Change the Requested Ship Date. Close the sales order saving any changes. Reopen the order and all line items should now reflect the correct requested ship date.

You can choose to select a sales order that has a linked purchase order to test changes to the Required Date on the purchase order and purchase order line items.

Downloads

You can download the package files and stored procedure for this project below

Click here to download the usp_SOPRollDownReqShipDate stored procedure SQL file.
Click here to download the v10_SOP_Transaction_Entry package file.
Click here to download the v10_SOP_Transaction_Entry_VBA_References package file.

Until next post!

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

Edit:
08/08/2013: Changed If @@Error <> 0 to If @@Error = 0 in stored procedure code. In its previous implementation, the stored procedure would always rollback the changes if there was no error causing requested ship date not to roll down.