Script to check SalesOrder Detail & Update Salesorder Header

Question

Hello,
I am using sage 2018 & am trying to create a script that works alongside scanforce. We received the oSalesOrder.nMBAutoAllocateSalesOrder() command from Scanforce to allocate orders with a VBS, but we're on our own in terms of creating the rest of the script. What I want to have happen is if an order has every line item allocated to the qty that was ordered, I want to update a UDF on the SalesorderHeader to indicate it is fully allocated. If there is any line item that is not fully allocated, I want the header to say "N". The script is working for the most part, line items will allocate, the header will update. The only issue is the salesorder header is not updating correctly. It appears to be changing based upon the last line item of the order being fully allocated or note, rather than every line item. I am not a VBS expert by any stretch so I imagine I overlooked something pretty basic.

I did not include the first half the script that sets up to the connection since I know it is working.



retVAL = oSS.nSetDate("S/O",oSS.sSystemDate)
retVAL = oSS.nSetModule("S/O") 
retVal = oSS.nSetProgram(oSS.nLookupTask("SO_SalesOrder_UI"))
Set oSalesOrder = oScript.NewObject("SO_SalesOrder_BUS", oSS)

oSalesOrder.nMoveFirst()

Do until CBool(oSalesOrder.nEOF)

    sSOOrderNo = "" 
    sSOOrderType = ""   
    sSODItemCode = ""
    sSODItemType = ""   
    sSODDropShip = ""   
    sSOShipExpireDate = ""
    cSODQuantityOrdered = 0
    cSODAllocatedQuantity = 0
    sFullyAllocatedFlag = "Y"
    sFullyAllocated = ""
    sPickingStarted = ""

    retVal = oSalesOrder.nGetValue("SalesOrderNo$",sSOOrderNo)
    retVal = oSalesOrder.nGetValue("OrderType$",sSOOrderType)
    retVal = oSalesOrder.nGetValue("ShipExpireDate$",sSOShipExpireDate)
    retVal = oSalesOrder.nGetValue("UDF_FULLYALLOCATED$",sFullyAllocated)
    retVal = oSalesOrder.nGetValue("D519SF_PickingCompleted$",sPickingStarted)

    tomorrow = DateAdd("d", 1, Date)
    shipDate = sSOShipExpireDate

    YearPart = Left(sSOShipExpireDate, 4)
    MonthPart = Mid(sSOShipExpireDate, 5, 2)
    DayPart = Right(sSOShipExpireDate, 2)
    ConvertedShipDate = CDate(MonthPart & "/" & DayPart & "/" & YearPart)

    If sSOOrderType = "S" and sFullyAllocated = "N" and ConvertedShipDate < tomorrow Then
    Set oLines = oSalesOrder.oLines

    retVal = oLines.nMoveFirst()

    'If all line items quantity ordered = QuantityAllocated, then UDF_FULLYALLOCATED = 'Y'
    Do until CBool(oLines.nEOF)
        retVal = oLines.nGetValue("ItemCode$",sSODItemCode) 
        retVal = oLines.nGetValue("QuantityOrdered",cSODQuantityOrdered)    
        retVal = oLines.nGetValue("AllocatedQuantity",cSODAllocatedQuantity)    
        retVal = oLines.nGetValue("ItemType$",sSODItemType)     
        retVal = oLines.nGetValue("DropShip$",sSODDropShip) 

        If cSODAllocatedQuantity <> cSODQuantityOrdered Then
            retVal = oSalesOrder.nMBAutoAllocateSalesOrder()
            If sSODItemType <> "5" and sSODDropShip <> "Y" Then
            sFullyAllocatedFlag = "N"
            End If
            oSalesOrder.nWrite()            
        End If
        oLines.nMoveNext()
    Loop
    End If
    If sFullyAllocatedFlag = "Y" then
        retVal = oSalesOrder.nSetValue("UDF_FULLYALLOCATED$","Y")   
        oSalesOrder.nWrite()            
    Else
        retVal = oSalesOrder.nSetValue("UDF_FULLYALLOCATED$","N")   
        oSalesOrder.nWrite()                
    End If
    oSalesOrder.nMoveNext()
Loop

oSS.nCleanup()
oSS.DropObject()

Set oSS = Nothing
Set oScript = Nothing

David Speck · Accepted Answer

Have you verified via ODBC or DFDM that a newly created sales order actually has N in the field before processing it with your script? 
 I have seen cases where defaults don't work as expected or when they are expected. 
 Regardless, you should consider either writing to a log at various points of the process including writing out the variables' values so you can verify you are getting expected values and reaching specific logic points in your script. You can go the simple route with just displaying a message box if you are manually executing the script but it'll get real old, real quick if you end up looping over a bunch of sales orders and a bunch of lines which is why i suggested you limit the scope of the loops or temporarily comment out the outer loop on SO_SalesOrderHeader and insert a SetKey with a valid sales order passed as the argument.

daburke · Answer

End If at 119 should be after 126, you should only be writing if this is a order to check the allocated and the allocated is still a Y

David Speck · Answer

I'm not familiar with the MBAutoAllocateSalesOrder method or its potential arguments but since it is called from the header object, are you sure it should be called while in a loop on the lines? 
 In addition to that, i don't see anywhere in your script where, after having checked the lines, you are setting sFullyAllocatedFlag to "Y".

Sage 100

Script to check SalesOrder Detail & Update Salesorder Header

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING