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, MIS, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: