Script to update SO line pricing


A customer has asked for a way to update pricing on 1000's of SO, for some planned pricing updates they working on.  My idea was to have a button script to loop through all SO, and all lines on each SO, triggering the Sage logic to calculate each line individually (and incrementing a counter with each line change).

What I found was this:

Set oLines = oSession.AsObject(oBusObj.Lines)
retVal = oLines.RecalculateLinePricing()

It's odd that I don't even need to MoveFirst, and it seems to work, but triggers the pricing recalculation for all lines on the SO (ItemType 1 only) with no real control or way of knowing how many lines were actually updated. 

oLines.PriceRecalculation() seems to do the same thing (although I didn't look as closely at that method).

While this should technically do what the customer has asked for, I want to be prepared if they'd like a bit more control over (or feedback on) the line by line updates.

Does anyone know of another way to manually trigger the pricing hierarchy (line by line), without using the "Recalc Price" button?

As another way to get some "what has changed" numbers, before doing the header Write(), is there a record property I can check to see if there are any unsaved changes?

  • 0

    SO_CommonEntryDetail has a method called CalculatePrice.

    SO_SalesOrderDetail has a method called PriceRecalculation.

    Any *_bus objects should have a property called RecordChanged.  So you should be able to start by looping on the sales order header, then loop through each line while calling the detail's PriceRecalculation and then checking if RecordChanged is equal to 1, if it is, write the line, increment you counter, and move to the next line, if any lines were updated, then write the header.  If you intend to output to a log file, you while on each line, you could always get the original price before calling the detail's PriceRecalculation, then if the line was changed, get the new price and output the applicable info to the log.

  • 0 in reply to David Speck


    Thanks for the tip on RecordChanged... I'll use that for sure.

    As I mentioned above, I saw PriceRecalculation, and it works the same way as RecalculateLinePricing (found in SO_ComminEntryDetail).

    No MoveFirst, no loop, no Write(), no line-by-line control...

    Set oLines = oSession.AsObject(oBusObj.Lines)
    retVal = oLines.PriceRecalculation()

    I saw the CalculatePrice method, but it concerns me there is no argument for PriceLevel, like there is in the CI_ItemPrice methods, so I am not sure it would cover the entire pricing hierarchy.

    I'll do some testing though to see if  either of these will work better than RecalculateLinePricing / PriceRecalculation.

  • 0 in reply to Kevin M

    Hmm, I had thought that since those methods were documented under the "detail" objects that they would be for single line use only.  It looks like CalculatePrice is the only method that will actually do what you want and can be used on a line by line basis so you can check the unit price before and after as well as checking RecordChanged.  You experiment with it with known values to see how the price level is used with it.  Something else you could try is save the current price level or quantity into a variable, then force either to a different value, then setting back to the original value, I'm pretty sure both will trigger the detail object to recalculate the price.  You wouldn't be able to use RecordChanged but could compare the original unit price against the unit price after toggling either of them.

  • 0 in reply to David Speck

    I made the same assumption (about the methods in Detail objects), but when I tried (with a GetValue before and after) I didn't get the expected values... then I noticed it was only processing the command once, updating all lines, even when I had a line loop (with multiple lines on the SO) and no Write().  Fun!

    I'll set up an SO with all kinds of pricing (price level, customer override, sales promo, breakpoints...) and test to ensure a scripted loop using CalculatePrice works for them all.

    If not, I'll try the PriceLevel change / change back... idea.

    Last resort: the CI_ItemPrice methods.

  • +1 in reply to Kevin M
    verified answer

    It looks like CalculatePrice will work (even if I override the PL on different lines). 

    Thanks for the feedback David.

    retVal = oBusObj.GetValue("OrderDate$", sOrderDate)
    Set oLines = oSession.AsObject(oBusObj.Lines)
    retVal = oLines.MoveFirst()
    do until oLines.EoF
        sItemCode = "" : nQty = 0 : nBeforeUnitPrice = 0 : nAfterUnitPrice = 0
        retVal = oLines.GetValue("ItemCode$", sItemCode)
        retVal = oLines.GetValue("QuantityOrdered", nQty)
        retVal = oLines.GetValue("UnitPrice", nBeforeUnitPrice)
        retVal = oLines.CalculatePrice(sItemCode,nQty,sOrderDate)
        retVal = oLines.GetValue("UnitPrice", nAfterUnitPrice)
        retVal = oLines.Write()
        'retVal = oSession.AsObject(oSession.UI).MessageBox("","nBeforeUnitPrice " & nBeforeUnitPrice & vbCrlf & "nAfterUnitPrice " & nAfterUnitPrice)
        ' increment counter here, when changed...
        retVal = oLines.MoveNext()
        retVal = oUI.ProgressBar("update")
    loop ' oLines

  • +1 in reply to Kevin M

    I'm trying to do something very similar to what you've described here.  In my case I need to change the Price Level on the line before running CalculatePrice.  Can I simply make the price level change with oLines.SetValue as you've done in your example or does it need to be done with the oUIObj.InvokeChange?  I'm less concerned about the values on the screen while the script is running so long as the SO is correct when refreshed and the invoice is accurate.

  • 0 in reply to ehx

    CalculatePrice does indeed work but the results are only visible after saving, closing and reopening the SO.  How can I get the SO Lines to refresh as part of this script?

  • 0 in reply to ehx

    If in the SO, why not use the Recalc Price button?

    Grid data refreshes are always a problem.  Recently I added a button (on DMAIN) for a client in a similar situation (line data updated by script, but changes were not visible on screen unless clicking on another tab). 

    retVal = 0
    oScript.LinesAdded = 1
    retVal = oScript.LoadGrid("GD_Lines")
    retVal = oUIObj.HandleScriptUI()

    (Credit to Alnoor for posting this previously).

    You'll get a more consistent result by training users to click this button when they want to refresh the grid.  Automating this through invoking the button can work, but it changes the selected row / column, which might be frustrating to users (depending on your script trigger).