Import sales orders from a csv file

Hi Forum,

 

I've trying to have this script working but i guess i need to post it to get help from you guys....

What i am trying to do is import sales orders from a csv file.  some orders have more than 1 line, most of them are single line.

i need to get the Customer Number doing a find by customer Name. Once i get the customer number i build the S/O header and lines. My goal is to have one script... i have one script that creates new customers and new ship to codes.  The S/O code will be appended to the other script, but that's a different story.

i cannot make it loop if the order has more than 1 line.

 

Here is the code... what's left of the code, i've been adding and deleting lines...

 

'Create ProvideX COM Object
Set oScript = CreateObject ("ProvideX.Script")

'The Init method must be the first method called
' Please remember to correct the MAS90\Home path
oScript.Init("C:\Sage Software\MAS 90\VERSION4.4.6\Mas90\home")

'The Session object must be the first MAS 90 object created
Set oSS = oScript.NewObject("SY_Session")

mdate = oSession.sModuleDate
'sdate = oSession.sSystemDate
'ucode = oSession.sUserCode

' Set the user for the Session
r = oSS.nLogon()
If r=0 Then
	' Please remember to correct the username, password
	r = oss.nSetUser("im","metals")
End If

' Set the company, module date and module for the Session
r = oss.nsetcompany("FIT")
r = oSS.nSetDate("S/O",mdate)
r = oSS.nSetModule("S/O")

' Instantiate a Sales Order business object
oSEC = oSS.nSetProgram(oSS.nLookupTask("SO_SalesOrder_ui"))
Set o = oScript.NewObject("SO_SalesOrder_bus", oSS)

' Instantiate a AR_Customer business object
'Set oCust = oScript.NewObject("AR_Customer_bus, oSS)

'Open the CSV file

dim fs,objTextFile
set fs=CreateObject("Scripting.FileSystemObject")
dim arrStr
set objTextFile = fs.OpenTextFile("C:\Akatz\Fitness First\orders.csv")

Do while NOT objTextFile.AtEndOfStream
arrStr = split(objTextFile.ReadLine,",")
strOrderNo = arrstr(0)
strName = arrStr(1)
strPhone = arrStr(2)
strAddress1 = arrStr(12)
strAddress2 = arrStr(13)
strAddress3 = arrStr(14)
'strCity = arrStr(15)
'strState = arrStr(16)
strZip = arrStr(17)
strCountryCode = arrStr(18)
strItem = arrStr(3)
strQty = arrStr(4)
strPrice = arrStr(6)
'strFreight = arrStr(8)
strFreight = strFreight + arrStr(8)

'Read AR_Customer to find if Customer exists

retval = 0
name = ""
custExist = 0
ARDivisionNo = "00"

retval = oCust.nSetIndex("KNAME")
retval = oCust.nFind(strName)   ' this will fail
retval = oCust.nMoveNext()           ' go to next record
if Not(cBool(oCust.nEOF)) then
		retval = oCust.nGetValue("CustomerName$", name)
		if UCase(strName) = UCase(name) then
				custExist = 1
		end if
end if

If custExist = 1 then

' Create a new header record using the next available order number
OrderNo = ""
r = o.nGetNextSalesOrderNo(OrderNo)
r = o.nSetKey(OrderNo)
r = o.nSetValue("ARDivisionNo$", "00")
r = o.nSetValue("CustomerNo$", "0179854")
r = o.nSetValue("CustomerPONo$", "BUSOBJTST")

' Create a new line for the item and write to memory file

r = o.oLines.nAddLine()
r = o.oLines.nSetValue("ItemCode$", strItem)
r = o.oLines.nSetValue("WareHouseCode$", "000")
r = o.oLines.nSetValue("QuantityOrdered", strQty)
r = o.oLines.nSetValue("UnitPrice", strPrice)
r = o.oLines.nWrite()


' Write header and lines to disk
r = o.nSetValue("FreightAmt", strFreight)
r = o.nGetValue("NonTaxableAmt", nontax)
r = o.nSetValue("FreightAmt", freight)
r = o.nSetValue("DepositAmt",nontax+freight)
r = o.nWrite()

strFreight = 0


end if

loop

 

Doing it this way creates and order per line read... there are orders with 2 o more lines, therefore i am creating 2 or 3 orders instead of 1 order with 2 or 3 lines...

 

Any suggestions?

 

Regard,

Manuel

Parents
  • Hi Forum,

     

    I got an example of an script to create SO... i am not seeing anything different compared to what i already have.

    one thing that i noticed it that...:

     

    r = oLines.nAddLine()

     

    the r value is '0' - zero.... it doesn't make any sense.... i am setting the oLines by doing the following..

     

    ' Instantiate a Sales Order business object
    oSEC = oSS.nSetProgram(oSS.nLookupTask("SO_SalesOrder_ui"))
    Set o = oScript.NewObject("SO_SalesOrder_bus", oSS)
    oLines = 0
    Set oLines = oScript.NewObject("SO_SalesOrderDetail_bus", oSS)
    
    ' Instantiate a AR_Customer business object
    oSEC = oSS.nSetProgram(oSS.nLookupTask("AR_Customer_ui"))
    Set oCust = oScript.NewObject("AR_Customer_bus", oSS)

     Am i missing something here???

     

    Regards,

    Manuel Roman

     

Reply
  • Hi Forum,

     

    I got an example of an script to create SO... i am not seeing anything different compared to what i already have.

    one thing that i noticed it that...:

     

    r = oLines.nAddLine()

     

    the r value is '0' - zero.... it doesn't make any sense.... i am setting the oLines by doing the following..

     

    ' Instantiate a Sales Order business object
    oSEC = oSS.nSetProgram(oSS.nLookupTask("SO_SalesOrder_ui"))
    Set o = oScript.NewObject("SO_SalesOrder_bus", oSS)
    oLines = 0
    Set oLines = oScript.NewObject("SO_SalesOrderDetail_bus", oSS)
    
    ' Instantiate a AR_Customer business object
    oSEC = oSS.nSetProgram(oSS.nLookupTask("AR_Customer_ui"))
    Set oCust = oScript.NewObject("AR_Customer_bus", oSS)

     Am i missing something here???

     

    Regards,

    Manuel Roman

     

Children
No Data