Scripting

Sage 100 Standard Adding Order script We have the following script that adds orders to the Sales Order tables via the BOI from a web store. What seems to happen is, when the order fails validation the sales order number called is used and then it iterates to the next order to be written. However, the if last order written fails validation it is leaving a dead sales order header (no information) and no line information. Consider the script a gift to the group, if someone could help me determine why we are getting the dead order and what I can do to have my developer fix it!!! ----------------------------------------------------- ' ProcessData Function ' Function Add_Order_to_MAS_Function() ThisStep.LogInfo ""Processing Web Order #"" & ThisStep.RecordSource(""Store_Id"") 'create mas90 formatted date for today Dim tmpYear : tmpYear = Year(Now()) Dim tmpMonth : tmpMonth = Month(Now()) Dim tmpDay : tmpDay = Day(Now()) If tmpMonth < 10 Then tempMonth = tmpMonth tmpMonth = ""0""+CStr(tempMonth) End If If tmpDay < 10 Then tempDay = tmpDay tmpDay = ""0""+CStr(tempDay) End If currentDate = CStr(tmpYear)+CStr(tmpMonth)+CStr(tmpDay) ThisStep.LogInfo ""...Current Date "" & currentDate 'SET FIELDS FOR RUNTIME ThisStep.LogInfo ""...Set Vars"" 'SYS LOGIN' Dim sUser : sUser = System.Variables(""Sage_Test_sUser"") Dim sPassword : sPassword = System.Variables(""Sage_Test_sPassword"") Dim PathHome : PathHome = System.Variables(""Sage_Test_Home_Folder"") Dim MASCompany : MASCompany = System.Variables(""Sage_Test_Company"") ThisStep.LogInfo ""CUSTOMER"" Dim ARDivisionNo : ARDivisionNo = ""00"" 'Variables(""V_ARDivision"") 'MAS CUSTOMER DIVISION' Dim CustomerNo : CustomerNo = ThisStep.RecordSource(""ERP_Customer"") ThisStep.LogInfo ""BILLING"" Dim BillToCompanyName : BillToCompanyName = ThisStep.RecordSource(""Bill_Company"") Dim BillToCustomerName : BillToCustomerName = ThisStep.RecordSource(""Bill_First_Name"") '& "" "" & ThisStep.RecordSource(""Bill_Last_Name"") Dim BillToAddress1 : BillToAddress1 = ThisStep.RecordSource(""Bill_Address"") Dim BillToAddress2 : BillToAddress2 = ThisStep.RecordSource(""Bill_Address2"") Dim BillToCity : BillToCity = ThisStep.RecordSource(""Bill_City"") Dim BillToState : BillToState = ThisStep.RecordSource(""Bill_Province_Code"") Dim BillToZipcode : BillToZipcode = ThisStep.RecordSource(""Bill_Post_Code"") ThisStep.LogInfo ""SHIPPING"" Dim ShipToCompanyName : ShipToCompanyName = ThisStep.RecordSource(""Ship_Company"") Dim ShipToCustomerName : ShipToCustomerName = ThisStep.RecordSource(""Ship_First_Name"") & "" "" & ThisStep.RecordSource(""Ship_Last_Name"") Dim ShipToAddress1 : ShipToAddress1 = ThisStep.RecordSource(""Ship_Address"") Dim ShipToAddress2 : ShipToAddress2 = ThisStep.RecordSource(""Ship_Address2"") Dim ShipToCity : ShipToCity = ThisStep.RecordSource(""Ship_City"") Dim ShipToState : ShipToState = ThisStep.RecordSource(""Ship_Province_Code"") Dim ShipToZipcode : ShipToZipcode = ThisStep.RecordSource(""Ship_Post_Code"") ThisStep.LogInfo ""ORDER And FREIGHT"" Dim nextSalesOrderNo : nextSalesOrderNo = """" Dim Web_Order_ID : Web_Order_ID = ThisStep.RecordSource(""Store_Id"") ThisStep.LogInfo ""Order #"" & Web_Order_ID Dim Web_Order_Comment : Web_Order_Comment = ThisStep.RecordSource(""Note"") Dim ShipVia : ShipVia = ThisStep.RecordSource(""ERP_Ship_Method"") Dim ShipWeightNum : ShipWeightNum = 2 Dim ShipWeightStr : ShipWeightStr = ""2"" Dim OrderTotal : OrderTotal = ThisStep.RecordSource(""Total_Order"") Dim Discount_Amt : Discount_Amt = ThisStep.RecordSource(""Total_Discount"") Dim DiscountPcnt: DiscountPcnt = ThisStep.RecordSource(""Total_Discount_Percent"") Dim Taxable_Amt : Taxable_Amt = ThisStep.RecordSource(""Total_Lines"") Dim NonTaxable_Amt : NonTaxable_Amt = 0.00 Dim SalesTax_Amt : SalesTax_Amt = ThisStep.RecordSource(""Total_Tax_Validated"") Dim FreightAmt : FreightAmt = ThisStep.RecordSource(""Total_Freight"") ThisStep.LogInfo ""PAYMENT INFO"" Const PaymentTypeCategory = ""D"" Const PaymentType = ""WEB"" Const OtherPaymentTypeRefNo = 1234 'Dim PaymentType : PaymentType = Variables(""v_PaymentType"") 'Dim OtherPaymentTypeRefNo : OtherPaymentTypeRefNo = ThisStep.RecordSource(""OrderHeader_CCLast4"") 'Dim OtherPaymentTypeRefNo : OtherPaymentTypeRefNo = Variables(""v_OtherPaymentTypeRefNo"") Dim DepositAmount : DepositAmount = ThisStep.RecordSource(""Pay_Amount"") 'Dim CreditCardAuthorizationNo : CreditCardAuthorizationNo = ThisStep.RecordSource(""OrderHeader_CCAuthorization"") 'Dim CreditCardTransactionID : CreditCardTransactionID = ThisStep.RecordSource(""OrderHeader_CCTransactionNo"") 'Dim CardHolderName : CardHolderName = ThisStep.RecordSource(""OrderHeader_BillToCustomerName"") ' these two CC fields must have an initial value 'Dim CreditCardPreAuthorizationAmt : CreditCardPreAuthorizationAmt = CDbl(ThisStep.RecordSource(""OrderHeader_OrderTotal"")) 'Dim CreditCardPaymentBalanceAmt : CreditCardPaymentBalanceAmt = CDbl(ThisStep.RecordSource(""OrderHeader_OrderTotal"")) 'Dim Last4UnencryptedCreditCardNo : Last4UnencryptedCreditCardNo = ThisStep.RecordSource(""OrderHeader_CCLast4"") 'ORDER LINES' ThisStep.LogInfo ""...Set Order Lines"" Dim OrderLineString : OrderLineString = Variables(""V_StoreOrderLines"") Dim OrderLines : OrderLines = Split(OrderLineString,""!!"")'array of lines' ThisStep.LogInfo ""path:"" & PathHome ThisStep.LogInfo ""Company:"" & PathHome 'initialize script object Set oScript = CreateObject (""ProvideX.Script"") ThisStep.Loginfo ""oScript initialized"" oScript.Init(PathHome) ThisStep.LogInfo ""ProvideX Object Created"" 'create MAS session object Set oSS = oScript.NewObject(""SY_SESSION"") retVAL = oSS.nlogon() ThisStep.LogInfo ""Session Set for "" & sUser &"" ""& sPassword retVal = oSS.nSetUser(sUser,sPassword) ThisStep.LogInfo ""Credentials Set, trying Company: "" & MASCompany If retVAL = 0 Then 'login failed ThisStep.LogInfo ""error: "" & oSS.sLastErrorMsg & "" Quitting"" oSS.DropObject() Set oSS = Nothing Set oScript = Nothing Exit Function End If retVal = oss.nSetCompany(MASCompany) If retVAL = 0 Then ThisStep.LogInfo ""Set Company error: "" & oSS.sLastErrorMsg & "" Quitting"" oSS.nCleanup() oSS.DropObject() Set oSS = Nothing Exit Function End If 'set date, module, task retVal = oss.nSetDate(""S/O"",CurrentDate) retVal = oSS.nSetModule(""S/O"") retVal = oSS.nSetProgram(oSS.nLookupTask(""SO_SalesOrder_ui"")) ThisStep.LogInfo ""initialized with Date"" 'create data entry objects Set oSOOrderEntry = oScript.NewObject(""SO_SalesOrder_bus"", oSS) Set oSOOrderEntryLines = oSOOrderEntry.oLines Set oPOSvcObject = oScript.NewObject(""SO_SalesOrder_svc"", oSS) ThisStep.LogInfo ""Build SO Header"" retVal = oSOOrderEntry.nGetNextSalesOrderNo(nextSalesOrderNo) retVal = oSOOrderEntry.nSetKeyValue(""SalesOrderno$"",nextSalesOrderNo) retVal = oSOOrderEntry.nSetKey() ThisStep.LogInfo ""Build SO Header customer"" retVal = oSOOrderEntry.nSetValue(""ARdivisionno$"",ARDivisionNo) retVal = oSOOrderEntry.nSetValue(""Customerno$"",CustomerNo) ThisStep.LogInfo ""Customer: "" & ARDivisionNo & ""-"" & CustomerNo ThisStep.LogInfo ""Build SO Header Bill to address"" retVal = oSOOrderEntry.nSetValue(""BillToName$"",BillToCustomerName) If retVal = 0 Then ThisStep.LogInfo ""BillToCustomerName error:"" & oSS.sLastErrorMsg & "" Quitting"" Exit Function End If ThisStep.LogInfo ""1"" BillToAddress1 = Left(BillToAddress1,30) retVal = oSOOrderEntry.nSetValue(""BillToAddress1$"",BillToAddress1) If retVal = 0 Then ThisStep.LogInfo ""BillToAddress1 error:"" & oSS.sLastErrorMsg & "" Quitting"" Exit Function End If ThisStep.LogInfo ""2"" 'retVal = oSOOrderEntry.nSetValue(""BillToAddress2$"",BillToAddress2) 'Cannot be null retVal = oSOOrderEntry.nSetValue(""BillToCity$"",BillToCity) If retVal = 0 Then ThisStep.LogInfo ""BillToCity error:"" & oSS.sLastErrorMsg & "" Quitting"" Exit Function End If ThisStep.LogInfo ""3"" retVal = oSOOrderEntry.nSetValue(""BillToState$"",BillToState) If retVal = 0 Then ThisStep.LogInfo ""BillToState error:"" & oSS.sLastErrorMsg & "" Quitting"" Exit Function End If ThisStep.LogInfo ""4"" retVal = oSOOrderEntry.nSetValue(""BillToZipcode$"",BillToZipcode) If retVal = 0 Then ThisStep.LogInfo ""BillToZipcode error:"" & oSS.sLastErrorMsg & "" Quitting"" Exit Function End If ThisStep.LogInfo ""5"" ThisStep.LogInfo ""Build SO Header Ship to address"" retVal = oSOOrderEntry.nSetValue(""ShipToName$"",ShipToCustomerName) retVal = oSOOrderEntry.nSetValue(""ShipToAddress1$"",ShipToAddress1) 'retVal = oSOOrderEntry.nSetValue(""ShipToAddress2$"",ShipToAddress2) 'Cannot be null retVal = oSOOrderEntry.nSetValue(""ShipToCity$"",ShipToCity) retVal = oSOOrderEntry.nSetValue(""ShipToState$"",ShipToState) retVal = oSOOrderEntry.nSetValue(""ShipToZipcode$"",ShipToZipcode) ThisStep.LogInfo ""Build SO Header other info"" 'retVal = oSOOrderEntry.nSetValue(""ShipVia$"",ShipVia) Need to define shipping methods in Sage retVal = oSOOrderEntry.nSetValue(""ShipWeight$"",ShipWeightStr) retVal = oSOOrderEntry.nSetValue(""Weight"",ShipWeightNum) retVal = oSOOrderEntry.nSetValue(""FreightAmt"",FreightAmt) retVal = oSOOrderEntry.nSetValue(""UDF_WEBORDERID"",Web_Order_ID) 'retVal = oSOOrderEntry.nSetValue(""UDF_Web_Order_NO$"",Web_Order_No) 'retVal = oSOOrderEntry.nSetValue(""UDF_Order_Comment$"",Web_Order_Comment) 'retVal = oSOOrderEntry.nSetValue(""OrderDate$"",AuthorizationDate) retVal = oSOOrderEntry.nSetValue(""DiscountAmt"",Discount_Amt) 'retVal = oSOOrderEntry.nSetValue(""DiscountRate"",DiscountPcnt) 'Only need discount rate 'TODO add tax field fOr tax amount 'TODO add tax field for tax code ThisStep.LogInfo ""Build SO Header order date and amounts - Need to get field maps!"" retVal = oSOOrderEntry.nSetValue(""PaymentType$"",PaymentType) 'Values For this come from MAS >> Modules >> AR >> Setup >> Payment Type Maintenance If retVal = 0 Then ThisStep.LogInfo ""PaymentType error:"" & oSS.sLastErrorMsg & "" Quitting"" Exit Function End If 'retVal = oSOOrderEntry.nSetValue(""OtherPaymentTypeRefNo$"",OtherPaymentTypeRefNo) ' If retVal = 0 Then 'ThisStep.LogInfo ""OtherPaymentTypeRefNo error: "" & oSS.sLastErrorMsg & "" Quitting"" 'Exit Function ' End If retVal = oSOOrderEntry.nSetValue(""DepositAmt"",DepositAmount) If retVal = 0 Then ThisStep.LogInfo ""DepositAmt error:"" & oSS.sLastErrorMsg & "" Quitting"" Exit Function End If ThisStep.LogInfo ""...add order lines"" 'add lines Dim tmpQty, tmpPrice, tmpLnTot For Each x in OrderLines ThisStep.LogInfo ""processing>>"" & x & ""<<"" OrderLine = Split(x,""||"") retVal = oSOOrderEntryLines.nAddLine() 'sku = (0)' Dim tmpItemCode : tmpItemCode = OrderLine(0) retVal = oSOOrderEntryLines.nSetValue(""Itemcode$"",tmpItemCode) If retVal = 0 Then ThisStep.LogInfo ""Set Itemcode error: "" & OrderLine(0) &"" ""& oSOOrderEntryLines.sLastErrorMsg & "" Quitting"" oSS.nCleanup() oSS.DropObject() Set oSS = Nothing Exit Function End If 'Dropship Y/N Dim DropShip : DropShip = ""N"" If RTrim(Right(tmpItemCode,2)) = ""-D"" Then DropShip = ""Y"" retVal = oSOOrderEntryLines.nSetValue(""DropShip$"",DropShip) If retVal = 0 Then ThisStep.LogInfo ""Set Dropship error: "" & DropShip &"" ""& oSOOrderEntryLines.sLastErrorMsg & "" Quitting"" oSS.nCleanup() oSS.DropObject() Set oSS = Nothing Exit Function End If 'qty = (1)' tmpQty = CDbl(OrderLine(1)) retVal = oSOOrderEntryLines.nSetValue(""Quantityordered"",tmpQty) If retVal = 0 Then ThisStep.LogInfo ""Set Quantity error: "" & tmpQty &"" ""& oSOOrderEntryLines.sLastErrorMsg & "" Quitting"" oSS.nCleanup() oSS.DropObject() Set oSS = Nothing Exit Function End If 'Price = (2)' tmpPrice = CDbl(OrderLine(2)) retVal = oSOOrderEntryLines.nSetValue(""UnitPrice"",tmpPrice) If retVal = 0 Then ThisStep.LogInfo ""Set UnitPrice error: "" & tmpPrice &"" ""& oSOOrderEntryLines.sLastErrorMsg & "" Quitting"" oSS.nCleanup() oSS.DropObject() Set oSS = Nothing Exit Function End If 'Extension = (3)' tmpLnTot = CDbl(OrderLine(3)) retVal = oSOOrderEntryLines.nSetValue(""ExtensionAmt"",tmpLnTot) If retVal = 0 Then ThisStep.LogInfo ""Set Extension error: "" & tmpLnTot &"" ""& oSOOrderEntryLines.sLastErrorMsg & "" Quitting"" oSS.nCleanup() oSS.DropObject() Set oSS = Nothing Exit Function End If retVal = oSOOrderEntryLines.nWrite() Next ThisStep.LogInfo ""..Processed all lines"" 'write lines for order retVal = oSOOrderEntry.nWrite() If retVAL = 0 Then ThisStep.LogInfo ""error: "" & oSOOrderEntry.sLastErrorMsg & "" Quitting"" 'MsgBox(oSOOrderEntry.sLastErrorMsg & ""Quiting"") oSS.nCleanup() oSS.DropObject() Set oSS = Nothing Exit Function End If ThisStep.LogInfo ""MAS Order entered "" & nextSalesOrderNo Variables(""v_SageSalesOrder"") = nextSalesOrderNo 'cleanup oSOOrderEntry.DropObject() oSOOrderEntryLines.DropObject() oPOSvcObject.DropObject() oSS.DropObject() Set oSOOrderEntryLines = Nothing Set oss = Nothing Set oScript = Nothing ThisStep.LogInfo ""Finished"" End Function

Recommendation: Don't have the script get the next sales order number. Assign the order number. Use the web order number and put a letter prefix (say W) on the order number. This is the way Website Pipeline does it. This is the way I have three of my clients doing it. What you're bumping up against is works as designed. Even when using SO Entry in Sage 100, when the sales order number is auto-generated a record in the header table is created with only the order number. If SO Entry were do die like a script does, you'd see the same condition - a header record with only the order number and no lines.

We would have to come up with a unique prefix per store as the WebOrderIDs are not unique accross stores. Thanks for the guidance!

FYI Magento has order numbers which are significantly larger than the Sales Order number field. We had to concatenate removing middle zero's to get this to work.