Visual Integrator and Salesorder 1

[nomus1]

I need to create approx 400 salesorders.
All the same item, same customer, just different ship
to codes.
I have the list of ship to codes in an excel spread sheet.
The ship to codes already exist in Mas90 for this customer.
When I created the import, it runs, but it show 0 records imported, and it displays no data.
Has anyone had any luck importing salesorders, and if so, can you offer me some help.

 

[RichS]

Last year I wrote a VB application that imported sales orders by generating a text file and using VI to import it. Can you give me some additional information about how you are doing the import?

 

[nomus1]

The file is saved as an Excel File.
Most of the information is in the import file itself.

The excel file has
ShipToCode
ShipToName
ShipToAddr1
ShipToAddr2
City
State
Zip
Item Number
Qty Ordered
Qty Shipped
Qty BackOrdered
Unit Price
Extension
Unit Cost
Line Type

Everything else is coded into the import job

Thanks for your help

 

[RichS]

So I take it that you are using Visual Integrator and pointing it to the excel file. I have not tried an Excel file. But you might try the following:

1.Your field mappings in Visual Integrator have to be correct and make sure that all the required fields are populated either with data from excel or a default value.

2. You might try converting the excel file to a csv file and importing that. Many people have done it this way with great results.

3. In the Visual Integrator there is a test routine that will give pretty good error descriptions that you can use for trouble shooting.

4. Our MAS90 representative was a great deal of help in determining what had to be done in order to get things working properly. Contact them to get their advise.

I say the above not knowing what you have tried already so I hope it helps.

Rich

 

[jeanjerome]

Where you able to make it work? I am looking for clues on how to import orders from a website (mySQL) into MAS 90. Can it be done?

RichS what was the source of the data the VB script you created used?

 

[RichS]

It was a custom application. The company processed material for clients. As the material was processed the application automatically recorded what and how much was processed and automatically generated sales orders and stored them in a Microsoft SQL Server 6.5 db. Another custom app ran periodically to create a csv file from the unimported sales orders in SQL. This csv file was imported using VI.

 

[jeanjerome]

RichS:

Thank for your response.

We have an ecommerce site (Perl/MySQL) we have build for a client that now use MAS90 for its accounting. The client want to link the order from the site into MAS90... We are a bit lost on where to start on this project. To make matter worst, it is urgent we find a solution.

Do you know of any solution available to link a web based MySQL database with MAS90? The shopping cart MAS90 suggest is basically featureless when compare to what the client has now...

Thanks again...
Jean

 

[RichS]

I don't know of a commercial solution but I am sure you can develop your own in Perl. Use Perl to create a text file from the MySQL data then set up Visual Integrator to import the file.

 

[Guest_imported]

Rich,
I am interested in this solution that you have created, can you tell me more
Thanks!
Tim Doscher
tdoscher@hotmail.com

 

[RichS]

Sure. I am a consultant and did this job a little over a year ago so there may currently be a better way.

The overall objective:
The client required a way of generating sales orders automatically from information entered by the customer service reps (CSR). The application had to allow for a wide variety of sales order options to please the client's customers. This is why the client did not want to use MAS90 for sales order generation. The issues were mainly the amount of time it took to generate sales orders (from point of service to point of billing) and the number of errors encountered when manually generating sales orders. The custom sales order application allowed data entry by the CSR's as orders where placed by customers. These were validated and passed to a manager or manager's assistant for approval. Once approved, the sales order was available to be imported into MAS 90 by the accounting department. The company was able to reduce employee time by about 40% and turn around on billing from 10 - 15 days to 1 - 5 days and serve the customer better with live access to customer account status information. The information was also much more accurate - errors were almost entirely eliminated due the validation as the customer placed the order.

OK, with that out of the way, here is basically what it took to make this work.

The tools used:
MAS90 Visual Integrator
ODBC
Visual Basic 6.0
Microsoft SQL Server 6.5
Farpoint Spread

The applications:
--Sales order application
Written in Visual Basic 6.0
ODBC connection to SQL 6.5 to store the sales order data
Allowed for the creation, validation, routing and approval of sales orders

--Sales order import application
Written in Visual Basic 6.0
2 ODBC connections - 1 to SQL and 1 (read-only) to MAS90
Allowed an accounting manager to create batches and assign to accounting staff for review. If a sales order had problems it could be "routed" back to the CSR that created it for adjustment.

The SQL database was queried to check for sales orders that were ready (approved by CSR managers) for import. The data for these sales orders was displayed to accounting personel to be reviewed for accuracy. General ledger accounts where assinged by material by default but could be changed by the accounting staff (only). When the accountant was finished reviewing the batch the program created a text file (success using a direct ODBC connection was intermittent and could not be relied on) with all the information necessary for import to MAS90. Each line in the text file was a Sales Order line item. After the text file was generated the accounting manager ran the Visual Integrator routine to import the data from the text file into the MAS90 table. If I remember correctly (check me, it has been a while) the Visual Integrator import routine inserted this data into MAS90 files AR6 and AR7 - the header and detail files for the sales orders. The only problem in the development of all this was knowing just what data MAS90 required. The Data Dictionary was very helpful in this. We were allowed by the MAs90 representative to install MAS90 on a test box and copied live accounting data over to this server so as not to cause problems with the live data. Once the sales orders where imported, the accounting staff created invoices from the sales orders in the usual way. Reports were generated to ensure that the data was the same in MAS90 as in SQL Server.

Some tables had to be identical on the MAS90 and SQL databases. A seperate routine was created to synchronize the customer, inventory item, terms tables and others. This was run anytime a change was made to these tables in MAS90.

On the Sage Software forums (SageTalk) Big Louie (from Houston) and Klaatu (Arizona ?) where very helpful.

The project took about 2 - 3 months and cost the client roughly $60 - $80K however they were able to reduce employee costs in the area of sales order generation and recoup this investment rather quickly while providing better service to their customers.

Rich

Check us out at:

http://www.celerit.com

 

[Guest_imported]

Rich,
How hard was it to do the visual integrator routines, I was hoping to automate this process through the command line via a vb shell.

Do you know of anywhere where I could get some sample import files?
Thanks a bunch, the above information is a great help!

Tim Doscher
timd@providencesystems.com

 

[RichS]

Glad to hear it helped. I did not realize that I had written a novel. I don't know where you could find sample import files; I made my own as needed. The VI routines where not very difficult just tedious - I had to make sure that AR6 and AR7 had all the required fields populated as necessary. A lot of trial and error. Apparently few others had done this before.

The shell command is like:

ChDir "C:\test\MAS90\HOME"

l_lngTaskID = Shell("C:\test\MAS90\HOME\PVXWIN32.EXE ../LAUNCHER/SOTA.INI ../SOA/MAS90 -ARG ../VI/VIWI18 W 0 DIRECT DISPLAY RCI&quot


And building the text file went like:


Do While l_lngCntr < l_lngUbound + 1
If m_udtInvoice(l_lngCntr).Status = &quot;Approved&quot; Then ' not on hold by accounting
' format the date so that MAS 90 will be happy
l_strDate = CStr(Format(m_udtInvoice(l_lngCntr).InvoiceDate, &quot;yyyymmdd&quot)

Write #1, m_udtInvoice(l_lngCntr).InvoiceNum _
, m_udtInvoice(l_lngCntr).InvoiceType _
, m_udtInvoice(l_lngCntr).Territory _
, m_udtInvoice(l_lngCntr).AcctCode _
, l_strDate _
, m_udtInvoice(l_lngCntr).TermsCode _
, m_udtInvoice(l_lngCntr).SalesPersonID _
, m_udtInvoice(l_lngCntr).SalesAcctCD _
, m_udtInvoice(l_lngCntr).CostAcctCD _
, m_udtInvoice(l_lngCntr).InventoryCD _
, m_udtInvoice(l_lngCntr).Quantity _
, m_udtInvoice(l_lngCntr).Price _
, m_udtInvoice(l_lngCntr).Quantity * m_udtInvoice(l_lngCntr).Price _
, m_udtInvoice(l_lngCntr).TotalNonTaxAmount
End If

l_lngCntr = l_lngCntr + 1

Loop


I don't know if this is close to what you are needing but maybe it will provide some direction.

Rich