Updating SalesOrder Tax Records

Question

Hello - 
 I'm trying to override the tax amount on a Sales Order and I'm getting the following error (Record in use by another task): 
 
 Interestingly enough, the tax amount still seems to save. Any ideas what I'm missing? Here's my code (on table SO_SalesOrderHeader Pre-Write): 
 set TaxSummary = oSession.AsObject(oSession.GetObject("SO_SalesOrderTaxSummary_bus")) orderNo = "" 
 retVal = oBusObj.GetValue("SalesOrderNo$", orderNo) 
 retval = TaxSummary.SetKeyValue("SalesOrderNo$", orderNo) retval = TaxSummary.SetKeyValue("ScheduleSeqNo$", "000001") retval = TaxSummary.SetKeyValue("TaxCode$", "SOVOS") retval = TaxSummary.SetKey() retVal = TaxSummary.SetValue("SalesTaxAmt", 12.20) retval = TaxSummary.Write()

David Speck · Accepted Answer

If your goal is to override the values, the SalesTaxCalcObj gives you a handle to SY_SalesTaxCalculation_bus which will be for the tax summary table for the current task, in this case, SO_SalesOrderTaxSummary. So you can either loop through the tax summary records for the current sales order until you find the one you want to set values on (let's say you want to filter for the tax schedule) or use the SetKeyValue method for each field that makes up the multipart key. 
 https://help-sage100.na.sage.com/2019/FLOR/index.htm#File_Layouts/Sales_Order/SO_SalesOrderTaxSummary.htm 
 Once you are on the tax summary record you want to modify, you can use SetValue on each field followed by Write, just like with your original script but by using the correct object handle. 
 If you really are trying to override the values, then i don't see a reason why you would use any of the built in methods for calculating tax, such as CalculateTax, but i don't know all the details of what you are doing. If you are unsure about a specific method, you can always read the online object reference or post here. 
 https://help-sage100.na.sage.com/2019/FLOR/#Object_Reference/SY/SY_SalesTaxCalculation_bus.html

David Speck · Answer

My advice would be to not override tax values and let the published software handle it (either Sage 100 or another third party, like Avalara) but if you insist, better be sure your calculations are correct so you don't risk receiving a tax audit. 
 For your specific issue, you need to use the object handle that is already in memory for the current tax record, not get a new handle. 
 oBusObj should have an object handle called SalesTaxCalcObj that you can use. Try that and double check that you are on the tax record you need to edit before setting values and writing it. SalesTaxCalcObj points to the SY_SalesTaxCalculation_bus class. 
 https://help-sage100.na.sage.com/2019/FLOR/#Object_Reference/SY/SY_SalesTaxCalculation_bus.html

David Speck · Answer

Sage 100 object handles that are retrieved as a property or through GetObject, NewObject, and GetChildObject are represented as a Long data type, however, to be used with VBScript, they have to be ran through the AsObject method and with VBScript's Set operator. 
 My preferred approach to this is first to check that the target object handle is greater than zero and if it is, then pass it to the AsObject method. See below. 
 ' Example 1: Object handle retrieved as property of another business object.
If oBusObj.SalesTaxCalcObj > 0 Then
 Set oSalesTaxCalcObj = oSession.AsObject(oBusObj.SalesTaxCalcObj)
 
 ' Do stuff here.
 
 Set oSalesTaxCalcObj = Nothing
End If



' Example 2: Object handle retrieved with GetObject.
nAR_Customer_Svc = 0 : nAR_Customer_Svc = oSession.GetObject("AR_Customer_Svc")
If nAR_Customer_Svc > 0 Then
 Set oAR_Customer_Svc = oSession.AsObject(nAR_Customer_Svc)
 
 ' Do stuff here.
 
 Set oAR_Customer_Svc = Nothing
Else
 ' Unable to get object handle, likely a role issue but there are other cases where third party extended solutions will fail due to workstation dependencies not having been installed.
 ' You can display a message or send output to the trace window. I'd advise against using a message in cases where this would repeat and annoy the user.
 
 ' Example of a message box.
 If oSession.UI > 0 Then oSession.AsObject(oSession.UI).MessageBox "", "Unable to get object handle to ""AR_Customer_Svc"" (" & oSession.LastErrorMsg & ")."
 
 ' Example of sending output to the trace window.
 oScript.DebugPrint "Unable to get object handle to ""AR_Customer_Svc"" (" & oSession.LastErrorMsg & ")."
End If



' Example 3: Object handle retrieved with GetChildHandle.
nAR_Customer_Svc = 0 : nAR_Customer_Svc = oBusObj.GetChildHandle("CustomerNo")
If nAR_Customer_Svc > 0 Then
 Set oAR_Customer_Svc = oSession.AsObject(nAR_Customer_Svc)
 
 ' Do stuff here.
 
 Set oAR_Customer_Svc = Nothing
End If

David Speck · Answer

I personally haven't scripted anything for that object so i don't know all of its possible nuances. Here are some points to consider. I checked the file layout for SO_SalesOrderTaxSummary and there is a checkbox field called Overridden$. Perhaps you need to add a SetValue for that field and use "Y" as the value set. Another thought would be to move the script to the post-write event on SO_SalesOrderHeader. Comment out the Write method, the business object might be expecting the tax record to bbe in an editable state and you are affecting that by calling Write. If removing it causes your changes to be discarded, try using SetKey again after your Write to put the record back in an editable state. Easiest way is to store the current padded key into a variable between your first SetKey and the Write, then after the Write, pass the variable to SetKey. The below code is untested but written the way i would do this. Only use the MessageBox during testing unless you want to inform the user that the attempt to update the tax record failed. If oBusObj.SalesTaxCalcObj > 0 Then Set oSalesTaxCalcObj = oSession.AsObject(oBusObj.SalesTaxCalcObj) sMsg = "" sSalesOrderNo = "" : oBusObj.GetValue "SalessSalesOrderNo$", sSalesOrderNo If sSalesOrderNo <> "" And oBusObj.EditState <> 0 Then sScheduleSeqNo = "000001" sTaxCode = "SOVOS" nSalesTaxAmt = 12.34 oSalesTaxCalcObj.SetKeyValue "SalesOrderNo$", sSalesOrderNo oSalesTaxCalcObj.SetKeyValue "ScheduleSeqNo$", sScheduleSeqNo oSalesTaxCalcObj.SetKeyValue "TaxCode$", sTaxCode retVal = 0 : retVal = oSalesTaxCalcObj.SetKey() If retVal <> 0 Then sSalesTaxCalcObj_Currentkey = "" : sSalesTaxCalcObj_Currentkey = oSalesTaxCalcObj.GetKeyPadded() oSalesTaxCalcObj.SetValue "SalesTaxAmt", nSalesTaxAmt retVal = 0 : retVal = oSalesTaxCalcObj.Write() If retVal = 0 Then sMsg = "Unable to write changes for tax summary record. " _ & vbCrLf & "Order No. : " & sSalesOrderNo _ & vbCrLf & "Seq No. : " & sScheduleSeqNo _ & vbCrLf & "Tax Code : " & sTaxCode _ & vbCrLf & "Sales Tax Amt : " & nSalesTaxAmt _ & vbCrLf & "Last Error Msg: " & oSalesTaxCalcObj.LastErrorMsg End If oSalesTaxCalcObj.SetKey sSalesTaxCalcObj_Currentkey Else sMsg = "Unable to set key for tax summary record. " _ & vbCrLf & "Order No. : " & sSalesOrderNo _ & vbCrLf & "Seq No. : " & sScheduleSeqNo _ & vbCrLf & "Tax Code : " & sTaxCode _ & vbCrLf & "Last Error Msg: " & oSalesTaxCalcObj.LastErrorMsg End If If sMsg <> "" Then ' Send sMsg to trace window. Comment out if not needed. oScript.DebugPrint sMsg ' Display sMsg in a Message Box to the user. Comment out if not needed. If oSession.UI > 0 Then oSession.AsObject(oSession.UI).MessageBox "", sMsg End If End If Set oSalesTaxCalcObj = Nothing End If

Sage 100

Updating SalesOrder Tax Records

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING