Save order line items on location change


Requirement:

When an order comes in, they are not sure where the order will be shipped from.   Currently, they have an 'ADMIN' site that they use when taking all the orders in.   Once the order is ready to be shipped, they will decide where the order should be shipped from based on quantity available and proximity to the customer site.   At this point, they have to change the site ID for each line on the sales order so that it will properly allocate the inventory at each site and pick up the correct GL accounts.

What they would like is the ability to change the Default Site ID on the header and then have a question pop up that will ask the user if they would like to roll down the change to all the existing line items.   When doing so, it should attempt to reallocate each line, just like if you were manually changing the sites on each line.

Resolution:

On the Sales Transaction Entry form, update the corresponding line items in table SOP10200 when the Default Site ID is changed.  I am also programmatically executing the save to save the header and refresh the form after the line items are saved to the DB.

======================================================================
Important before entering VBA code...

The window and fields must be added for modification
Under Tools, Customize: Add current window to Visual Basic and Add fields to Visual Basic.

Set required references (ADO objects, etc.)
Under Tools, References.
======================================================================

Code:

Dim cn As New ADODB.Connection
Private Sub DefaultSiteID_AfterUserChanged()
    Dim ret As Integer
    ret = MsgBox("Would like to roll down the change to all the existing line items?", vbYesNo, "Roll Down Changes")
   
    If ret = vbYes Then
        Dim rst As New ADODB.Recordset
        Dim strPaymentTerm As String
        Dim strDocNum As String
        Dim cmd As New ADODB.Command
   
        If cn.State = 0 Then openConnection
   
        cmd.ActiveConnection = cn
        cmd.CommandType = adCmdText
        cmd.CommandText = "UPDATE SOP10200 SET LOCNCODE = ? WHERE SOPNUMBE = '" & Me.DocumentNo & "'"
        cmd.Parameters.Append cmd.CreateParameter("@LOCNCODE", adVarChar, adParamInput, 11, Me.DefaultSiteID)
   
        Set rst = cmd.Execute
        strDocNum = Me.DocumentNo
        Me.Save.Value = 1
        Me.DocumentNo = strDocNum
        SendKeys ("{TAB}")
    End If
End Sub
Sub openConnection()
    Set cn = UserInfoGet.CreateADOConnection
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
End Sub



Allocation Process:

Two stored procedures were created.  One to update the line items for the sales order (sopUpdateWarehouse) and the other to update and recalculate the line items totals (dd_IVReallocate) as per the new warehouse/default site ID.

--sopUpdateWarehouse process explanation:

Input Parameters: @SOPTYPE, @SOPNUMBE, @LOCNCODE

A cursor is created to loop through a recordset of distinct items derived from sales items in SOP10100 (Sales Transaction Work) and SOP10200 (Sales Transaction Amounts Work) that does not equal the input parameter: warehouse/default site ID (@LOCNCODE).  Each item found is then updated in SOP10200 with the new warehouse/default site ID.

--dd_IVReallocate process explanation:

Input Parameters: @ITEMNMBR, @LOCNCODE

This stored procedure was created to update the total quantity in the IV00102 (Item Quantity Master) table to reflect the warehouse change.  Two quantity fields (QUANTITY and TRXQTY) are summed from SOP10200 (Sales Transaction Amounts Work) and IV10001 (Inventory Transaction Amounts Work) filtered by ITEMNMBR and LOCNCODE.  This summed value then updates the ATYALLOC column in IV00102 (Item Quantity Master) table.