BOI Sales Order Example 1

[DC505]

Hello,

I have created a simple Sales Order program that will create a new customer in AR and also create a new Sales Order.

http://www.listech.net/archives/21

I hope this helps some people!

-Adam

 

[DC505]

This is an updated version of that script that does more error checking. It will create a new Zip Code if it finds that it does not exist. Since the comment line in S/O does not hold many characters it will automatically create a new memo for that S/O that has the customers comments. This probably needs more error checking but right now it will handle all of my needs perfectly and hasn't had an error yet.

-Adam

Set oReg=GetObject("winmgmts:\\.\root\default:StdRegProv")
oReg.GetExpandedStringValue &H80000001,"Software\ODBC\ODBC.INI\SOTAMAS90","Directory",PathRoot
PathHome = PathRoot & "\Home"

Set oScript = CreateObject ("ProvideX.Script")
oScript.Init(PathHome)

Set oSS = oScript.NewObject("SY_SESSION")
retVAL = oSS.nlogon()

If retVAL = 0 Then
'User = Trim(InputBox("Enter User Name"))
'Password = Trim(InputBox("Enter Password"))
retVAL = oSS.nSetUser("Adam Listek","comp1234")
End If

If retVAL = 0 Then
MsgBox(oSS.sLastErrorMsg & vbCRLF & "Quiting")
oSS.DropObject()
Set oSS = Nothing
Set oScript = Nothing
WScript.Quit
End If

retVal = oss.nSetCompany("ABC")
If retVAL = 0 Then
MsgBox(oSS.sLastErrorMsg & vbCRLF & "Quiting")
oSS.nCleanup()
oSS.DropObject()
Set oSS = Nothing
WScript.Quit
End If

retVal = oSS.nSetModule("A/R")

retVal = oSS.nSetProgram(oSS.nLookupTask("AR_Customer_ui"))

Set oARCustomerEntry = oScript.NewObject("AR_Customer_bus", oSS)
Set oARCustObject = oScript.NewObject("AR_Customer_svc", oSS)

Dim objExcel
Dim objWkbk
excelFile = Trim(InputBox("Please enter excel file location:"))
Set objExcel = CreateObject("Excel.Application")
Set objWkbk=objExcel.Workbooks.Open(excelFile)

InitRow=2
nextCustomerLine = ""

Do Until objExcel.cells(InitRow,1).Value = ""

tmpBilltoName = objExcel.cells(InitRow,5).Value
tmpComments = objExcel.cells(InitRow,2).Value
tmpEmail = objExcel.cells(InitRow,3).Value
tmpBilltoAddress1 = objExcel.cells(InitRow,6).Value
tmpBilltoAddress2 = objExcel.cells(InitRow,7).Value
tmpBilltoCity = objExcel.cells(InitRow,8).Value
tmpBilltoState = objExcel.cells(InitRow,9).Value
tmpBilltoZip = objExcel.cells(InitRow,10).Value
tmpBilltoPhone = objExcel.cells(InitRow,12).Value
tmpBilltoCountry = objExcel.cells(InitRow,11).Value
tmpShiptoName = objExcel.cells(InitRow,13).Value
tmpShiptoAddress1 = objExcel.cells(InitRow,14).Value
tmpShiptoAddress2 = objExcel.cells(InitRow,15).Value
tmpShiptoCity = objExcel.cells(InitRow,16).Value
tmpShiptoState = objExcel.cells(InitRow,17).Value
tmpShiptoZip = objExcel.cells(InitRow,18).Value
tmpShiptoPhone = objExcel.cells(InitRow,20).Value
tmpShiptoCountry = objExcel.cells(InitRow,19).Value

tmpYear = Year(Now())
tmpMonth = Month(Now())
If tmpMonth < 10 then
tempMonth = tmpMonth
tmpMonth = "0"+CStr(tempMonth)
End If
tmpDay = Day(Now())
currentDate = CStr(tmpYear)+CStr(tmpMonth)+CStr(tmpDay)

nextCustomerNumber = ""
retval = oARCustomerEntry.nGetNextCustomerNo(nextCustomerNumber)

retval = oARCustomerEntry.nSetKeyValue("Ardivisionno$","01")
retval = oARCustomerEntry.nSetKeyValue("Customerno$",nextCustomerNumber)
retVal = oARCustomerEntry.nSetKey()

retVal = oARCustomerEntry.nSetValue("CustomerName$",tmpBilltoName)
retVal = oARCustomerEntry.nSetValue("Addressline1$",tmpBilltoAddress1)
If tmpBilltoAddress2 = "" then
retVal = 0
Else
retVal = oARCustomerEntry.nSetValue("Addressline2$",tmpBilltoAddress2)
End If
retVal = oARCustomerEntry.nSetValue("Zipcode$",CStr(tmpBilltoZip))
If retVal = 0 then
retVal = oSS.nSetModule("S/Y")
retVal = oSS.nSetProgram(oSS.nLookupTask("SY_ZipCode_ui"))
Set oSYZipCode = oScript.NewObject("SY_ZipCode_bus", oSS)
retVal = oSYZipCode.nSetKeyValue("Zipcode$",CStr(tmpBilltoZip))
retVal = oSYZipCode.nSetKey()

retVal = oSYZipCode.nSetValue("Zipcode$",CStr(tmpBilltoZip))
retVal = oSYZipCode.nSetValue("City$",tmpBilltoCity)
retVal = oSYZipCode.nSetValue("Statecode$",tmpBilltoState)
retVal = oSYZipCode.nSetValue("Countrycode$",tmpBilltoCountry)
retVal = oSYZipCode.nWrite()

oSYZipCode.DropObject()
Set oSYZipCode = Nothing

retVal = oARCustomerEntry.nSetValue("Zipcode$",CStr(tmpBilltoZip))
End If

retVal = oARCustomerEntry.nSetValue("Countrycode$",tmpBilltoCountry)
retVal = oARCustomerEntry.nSetValue("Telephoneno$",CStr(tmpBilltoPhone))
retVal = oARCustomerEntry.nSetValue("Emailaddress$",tmpEmail)

customerNumber = nextCustomerNumber

retVal = oARCustomerEntry.nWrite()

retVal = oSS.nSetModule("S/O")
retVal = oSS.nSetProgram(oSS.nLookupTask("SO_SalesOrder_ui"))
Set oSOSalesOrderEntry = oScript.NewObject("SO_SalesOrder_bus", oSS)
Set oSOSalesOrderEntryLines = oSOSalesOrderEntry.oLines
Set oSOSvcObject = oScript.NewObject("SO_SalesOrder_svc", oSS)

nextSalesOrderNo = ""
retVal = oSOSalesOrderEntry.nGetNextSalesOrderNo(nextSalesOrderNo)
retVal = oSOSalesOrderEntry.nSetKeyValue("Salesorderno$",nextSalesOrderNo)
retVal = oSOSalesOrderEntry.nSetKey()

retVal = oSOSalesOrderEntry.nSetValue("Ardivisionno$","01")
retVal = oSOSalesOrderEntry.nSetValue("Customerno$",customerNumber)
If tmpBilltoState = "IL" then
retVal = oSOSalesOrderEntry.nSetValue("Taxschedule$","IL")
Else
retVal = oSOSalesOrderEntry.nSetValue("Taxschedule$","NONTAX")
End If
retVal = oSOSalesOrderEntry.nSetValue("Shiptoname$", tmpShiptoName)
retVal = oSOSalesOrderEntry.nSetValue("Shiptoaddress1$", tmpShiptoAddress1)
If tmpShiptoAddress2 = "" then
retVal = 0
Else
retVal = oSOSalesOrderEntry.nSetValue("Shiptoaddress2$", tmpShiptoAddress2)
End If
retVal = oSOSalesOrderEntry.nSetValue("Shiptocity$", tmpShiptoCity)
retVal = oSOSalesOrderEntry.nSetValue("Shiptostate$", tmpShiptoState)
retVal = oSOSalesOrderEntry.nSetValue("Shiptozipcode$", CStr(tmpShiptoZip))
retVal = oSOSalesOrderEntry.nSetValue("Shiptocountrycode$", tmpShiptoCountry)

If tmpBilltoCountry = "USA" & tmpBilltoState <> "HI" & tmpBilltoState <> "AK" then
retVal = oSOSalesOrderEntry.nSetValue("Shipvia$","SHIPPING")
End If

If tmpComments <> "" then
retVal = oSS.nSetModule("S/O")
Set oSOSalesOrderMemo = oScript.NewObject("SO_SalesOrderMemo_bus", oSS)
retVal = oSOSalesOrderMemo.nSetKeyValue("SalesOrderno$",nextSalesOrderNo)
retVal = oSOSalesOrderMemo.nSetKeyValue("Memocode$","INSTR")
retVal = oSOSalesOrderMemo.nSetKeyValue("Seqno$","000000")
retVal = oSOSalesOrderMemo.nSetKey()

retVal = oSOSalesOrderMemo.nSetValue("Memodesc$","Customer Shipping Instructions")
retVal = oSOSalesOrderMemo.nSetValue("Memodate$",CStr(currentDate))
retVal = oSOSalesOrderMemo.nSetValue("Memotext$",CStr(tmpComments))
retVal = oSOSalesOrderMemo.nWrite()

oSOSalesOrderMemo.DropObject()
Set oSOSalesOrderMemo = Nothing
End If

currentRow = InitRow

Do Until objExcel.cells(currentRow,4).Value <> objExcel.cells(InitRow,4).Value
tmpItem = objExcel.cells(currentRow,21).Value
tmpQuantity = objExcel.cells(currentRow,22).Value
retVal = oSOSalesOrderEntryLines.nAddLine()
retVal = oSOSalesOrderEntryLines.nSetValue("Itemcode$",CStr(tmpItem),"kSALESORDERNO")
retVal = oSOSalesOrderEntryLines.nSetValue("Quantityordered",tmpQuantity)
retVal = oSOSalesOrderEntryLines.nWrite()
currentRow = currentRow + 1
Loop

If (currentRow-1) <> InitRow then
InitRow = currentRow-1
End If

retVal = oSOSalesOrderEntry.nWrite()

InitRow = InitRow + 1

Loop

objWkbk.Saved = true
objExcel.Quit()
Set objWkbk = Nothing
Set objExcel = Nothing

oARCustomerEntry.DropObject()
oARCustObject.DropObject()
oSOSalesOrderEntry.DropObject()
oSOSalesOrderEntryLines.DropObject()
oSS.DropObject()

Set oARCustomerEntry = Nothing
Set oARCustObject = Nothing
Set oSOSalesOrderEntry = Nothing
Set oSOSalesOrderEntryLines = Nothing
Set oss = Nothing
Set oScript = Nothing

MsgBox("Finished!")

 

[sai1234ram]

Dear DC505
I am a new member for this tek-tip. I am also an New Developer in Sage MAS90 Erp. I have started using this Mas90 in vb.net. I am using MAS90 4.2 version. I am using Business Object Interface in developing Mas90. Now I want to bring the datas from the SalesOrder detail table to an Datagrid. I saw the MAS90 Object Interface Book from tek-tips. There i could see one record can be fetched from table to the Datagrid. I want to fetch all the datas into the Datagrid. I am facing the problem in looping the records from the Salesorder Detail table. Can you help me giving sample codings in BOI Regarding data retrival to datagrid
Regards
Ramprasna

 

[DC505]

I ran into something similar when trying to get results out of Mas 90. There is a delimiting character that you need to use to separate out the records. This is what I use to loop through a results set from Customer Records and look for a matching e-mail address.

Dim strResults
Dim strJunk

oARCustomerEntry.nGetResultSets("Emailaddress$", "Customerno$", strResults, strJunk, "", "", "")

Dim strResultsArray = Split(strResults, Chr(138))
Dim strResultsArrayKey = Split(strJunk, Chr(138))

Dim nextCustomerNumber = ""
Dim x = ""
Dim y = 0

For Each x In strResultsArray
If x = tmpEmail Then
nextCustomerNumber = strResultsArrayKey
Exit For
End If
y = y + 1
Next


What I did to figure that out since I still don't have a list of methods and their properties, which if you have by the way and could send me I would be eternally grateful alistek at gmail.com, is use MsgBox commands to print out the data in the nGetResultsSet and figured out what the common delimiter is, the Chr(138), and split that. I get an array then which I am sure you could use to populate the DataGrid.

-Adam

 

[donkndll]

Good example. do you know how to update the payment feilds?

retVAL = oSOOrder.nsetValue("PaymentTypeCategory$", "P")
retVAL = oSOOrder.nsetValue("PaymentType$", pt)

Dim Numb As String = pr.Item("Numb").ToString
retVAL = oSOOrder.nSetValue("EncryptedCreditCardNo$", Numb)

I get an error invalid card number. Maybe there is another object to load or session to set?

 

[dfeller]

Note that you can't pass in an unencrypted card number into the encrypted card number field. You have to use the function to encrypt the card number first into your variable, then write the encrypted card number using the line you already wrote.

Dawn