Geodis Wilson is one of the worlds largest freight management companies, employing 5,500 people worldwide from a global network of offices and air/ocean hubs in over 50 countries. Geodis Wilson has extended its COBOL-based freight management solution, Freight/AMS from Transaxiom, with LANSA-based Windows functionality, Microsoft Excel reporting and a Web site to improve productivity and customer service.
Bob Boerma, National Business Development Manager at Geodis Wilson Australia, says, "Internal IT efficiency is crucial to any company. But in our industry, IT is also very much a selling tool. Many agents and customers look at what IT services we can offer and whether that meets their requirements. Our IT capabilities have proven to be endless. Whatever computer system our customer may have, or whatever location they may be, we can offer them sophisticated IT services."
- Preserving the IT Investment
- Windows extensions with Visual LANSA
- Automated Excel Reporting with Visual LANSA and Active X
- Developing New Solutions with Visual LANSA
- System Integration with LANSA Integrator
- Company and System Information
- Coding Example
Preserving the IT Investment
Trevor Gall, IT Manager Australia/New Zealand for Geodis Wilson, explains, "We have been using and customizing our core freight management solution for over 16 years. The functionality has grown to suit our needs, but the user interface has a legacy green-screen look. Users prefer a Windows or browser interface and some managers even refuse to look at the 5250 screens."
"Because the functionality of our core freight management solution is good, we want to build further on our existing infrastructure, but with modern technology and an attractive graphical user interface. We may gradually replace some COBOL logic, but see no need for a total redevelopment."
"We have a very small and productive team of developers who know our business and IT system inside out. We want to keep that knowledge in-house and don't want to introduce separate Windows, Web and integration technologies. We already know how to develop with COBOL and LANSA for iSeries, so using LANSA for Windows and Web development was a small step," says Gall.
"Users get much more excited about a new Visual LANSA application, so it is easier to get the users involved during development. Users are far more accepting of a system they helped to build and the solution is easier to implement," explains Gall.
Windows extensions with Visual LANSA
Gall and his team have developed many Windows add-ons to their freight management solution with Visual LANSA. For example, a commonly used green-screen function for looking at the status of shipments did not provide profit estimates. To provide that information Gall chose to replace the program.
"The new inquiry function took just three hours to develop with Visual LANSA. Now users enjoy not just the extra functionality, but also an attractive and productive Windows interface," says Gall.
And when Geodis Wilson Australia set up a new company to handle sea freight documents, the existing system would have needed pre-printed stationary and an additional iSeries printer. It took six hours to develop a Visual LANSA function that passes the relevant data to an Excel document that has the correct formatting. Now printing is done on the department's PC laser printer. The solution is more flexible and saves money too.
Xan Hatzigeorgiou, Accounts Payable, explains that a LANSA-based extension written in less than a week saves days of work reconciling reports from the Cargo Accounts Settlement Systems (CASS) from the International Air Transport Association. "Every two weeks I used to get a printed CASS report listing hundreds of airway bills for interstate and international exports. I had to go through each line and compare the listed cost with what we were expecting to be charged. It used to take me three days to reconcile the differences and manually create the journal entries."
"Now we receive the report in EDI format by email every two weeks. A Visual LANSA program locates discrepancies, reports them and posts them to the correct job. Now it takes me twenty minutes to get the reconciliation job done," concludes Hatzigeorgiou.
Reconciling used to take me three days every two weeks, now it takes only minutes
Automated Excel Reporting with Visual LANSA and ActiveX
"Our users were not keen on the old fashioned character-based reports," explains Gall. "Excel provides a familiar and cost effective interface and our head office in Sweden had already adopted Excel as their standard for corporate reporting. I wanted to adopt Microsoft Excel as the reporting standard for our Australian operations, but I had to find a way to automatically produce Excel-based reports containing iSeries data."
Visual LANSA programs get the relevant data from the iSeries, do the necessary calculations and call ActiveX with the data parameters to populate the relevant Excel spreadsheet. The Excel spreadsheets are predefined templates, the Visual LANSA application only needs to fill in the cells. Once populated, the Excel reports can be emailed to the relevant party, including customers, head office and internal users. (click here to see a code example)
Gall gives a few tips on ActiveX and Excel reporting. "The easiest way to determine how to structure an ActiveX call in Visual LANSA is to record a macro in Excel and look at the VB script generated. We use the built in features of Excel to do sorts, sub totals and other post-processing functions."
"Once you are ready to implement a report, you need to make sure that you have compatible Microsoft Office versions between the development PC and the users. You need to package the Excel document templates with the application and make sure they are read only."
Renae Harper, Account Supervisor, says, "Automated Excel-based reporting to the head office saves me time. Before I had to go through many AS/400 reports, pick up figures and enter them manually in spreadsheets. Now I just push a couple of buttons and the reports are produced automatically. This used to take me at least three days every month."
Developing New Solutions with Visual LANSA
"We use Visual LANSA for new development as well," explains Gall. "Visual LANSA allows us to give the user a working prototype in a short time for a very low cost. Then we evolve the application as users requirements evolve."
"As we extended our services to include warehouse management, we needed a warehouse management system (WMS). The cost of buying a packaged solution was prohibitive. After evaluating several systems that only partly met our needs, we decided to build it ourselves with Visual LANSA. The initial prototype was developed in 60 hours and enabled the warehouse to operate. As we gained new clients we had another 12 months of ongoing ‘devolution' to enhance the system."
"The solution allows us to run our warehouse services very efficiently with very few staff. Now that the core WMS functionality is more or less complete, we plan to add an attractive graphical product locator."
"The Australian warehouses run the solution on local PCs with a central iSeries database server. The New Zealand operation runs the same warehouse application remotely on a Windows NT Server with a Microsoft SQL Server database. A key benefit is that the same LANSA Repository and source code support deployment on both iSeries and Windows servers."
"We also used Visual LANSA for a Visitor Entry System at its Sydney office. After September 11, the security requirements at all companies involved in handling air cargo were tightened. We had a short time frame to comply with the new arrangements. A programmer that had never used Visual LANSA developed a program to log visitors and issue name labels. The initial version took four hours, then he fine tuned it to a final version in 16 hours."
"The learning curve for Visual LANSA is very short, even for an old COBOL programmer like me. But training does pay off. We developed the first version of our WMS without any training, but we didn't use the full component capability of Visual LANSA. After attending a Visual LANSA training course, developer productivity is even more dramatic."
Visual LANSA allows us to give the user a working prototype in a short time for a very low cost
System Integration with LANSA Integrator
"When we recently purchased a new company, we had a requirement to access an SQL server database from our existing LANSA and COBOL applications. Our LANSA account manager suggested LANSA Integrator, SQL connector, which allows us to efficiently access the SQL database from the iSeries, without needing PC ODBC connectors and their inherent issues."
"One of our key differentiators is the ability to integrate with the applications and systems of our customers. Several customers use EDI to send us transactions while other customers send their orders in Excel spreadsheet format for us to upload. We are now looking to LANSA Integrator to allow for XML and make the exchange of transactions completely real-time and automated."
"LANSA helps us to leverage the investment we have in our existing system and provides a productive and easy to use tool to build new solutions. With LANSA, we can handle iSeries, Windows and Web development with a single set of skills. This is important, as we not only want to create efficient solutions for our users, but want to run a lean and efficient IT department as well," concludes Gall.
Company and System Information
- Geodis Wilson is the result of the merger between Geodis Overseas, the air and sea freight arm of the Geodis Group, and Geodis Wilson, formerly known as Wilson Logistics, a 164-year-old company with Scandinavian roots. With 5,500 people and a global network of offices and air/ocean hubs in over 50 countries, Geodis Wilson is one of the worlds largest freight management companies.
- Turnover in Australasia for 2006 exceeded A$200 Million. The company employs 180 staff at its Australian locations in Sydney, Melbourne, Brisbane, Adelaide and Perth. The New Zealand operation counts 30 staff and has full service sites in Auckland, Wellington & Christchurch.
- Geodis Wilson Australalia uses an System i model 525.
- The LANSA-based WMS solution runs on Windows NT with iSeries database access in Australia, while the New Zealand operation runs the same WMS solution on Windows NT Server with a Microsoft SQL Server database.
- For more information visit: www.geodis.com.au
- Geodis Wilson Australia uses Freight/AMS, a fully integrated system for Air Freight, Sea Freight and Customs Clearance, originally from Powerlan, acquired by Transaxiom (Asia Pacific) Pty Ltd in 2003, and now part of Four Soft (4 S).
Visual LANSA – Active X with Excel, code example
SET_REF COM(#ExcelApp) TO(*CREATE_AS #s_excel.application) invoke method(#Excelapp.workbooks.open) Filename(#LongChar) Open_RETVAL(#Excelwbk) SUBROUTINE NAME(FILLCELLC) PARMS(#XCELL #XVAL) DEFINE FIELD(#XCELL) TYPE(*CHAR) LENGTH(10) DEFINE FIELD(#XVAL) TYPE(*CHAR) LENGTH(30) set #ExcelApp.Range<#XCELL> FormulaR1C1(#XVAL) Endroutine invoke #ExcelApp.Range<#XCELL>.Select invoke #ExcelApp.Range<#XCELL>.sort key1(#ExcelApp.Range<'E10:E10'>) order1 (s_EXCEL.xlAscending) Header(S_EXCEL.xlGuess) OrderCustom(1) Orientation (S_EXCEL.xlSortColumns) DataOption1(S_EXCEL.xlSortNormal) invoke #ExcelApp.Range<#XCELL>.EntireRow.Insert