Extending Your MultiValue ERP with Web Services: Thinking Outside the Box
Why Would We Need to Extend Our ERP?
In many companies the standard or optional modules often don't fit their business model well, and when they do, they can be cost prohibitive. It is not unusual for a company to get modifications from their ERP vendor or a consultant that allows them to customize their data entry screens, reports, or business logic. These modifications can be very effective but also can be very costly. But how often do we think outside the box and create our own add-ons or extensions to our ERP system? In this article we'll discuss a real system that does get outside of the box, the AIX box.
The Problem
What happened to us was as if we had gone to our auto mechanic with a dead battery and the service manager told us, "Your battery is fine, but you need a new alternator which will cost you $325 plus $53.95 each for the mounting bolts, $128.88 for the drive belt, $79.95 for two hours labor, $20 for the garage use fee, and you will only be able to drive in reverse."
I work full time as a developer for a company that looked at the warehousing module offered by their ERP vendor, and it was a completely bad fit. The ERP vendor's solution would have required that we spend a lot of money on specialized devices ( $3000+ each ), device setup ( additional $600 each), and hours of billable professional services time in addition to the basic cost of the module. This might have been acceptable had the resulting process fit into our current processes.
Our company works in an industry where there are many different products from a variety of vendors. These products are interchangeable in many cases. Because of this, our processes are very flexible. With the vendor's wireless warehousing module, we would have needed to have bin movement and lot control. This meant that every product in each of our eight branch warehouses would have needed to be labeled with a barcode and the material codes cataloged and entered in during put-away. Additionally, it would have required the warehouse employee (aka picker) to go to a specific location and find a piece with a specific material code to fulfill an order. It would have also required that each material code be inventoried as well. While this discipline might be good in the sense of knowing exactly what we have on hand, it adds a lot of costs to the process without adding any value.
This would have been a radical departure from our industry norm of picking a suitable substitute product and recording the product used and material code.
Simply put, our company says to the ERP system, "Don't tell us what to do. We'll tell you what we did." The ERP module would have added a considerable amount of labor at both ends of the process.
The Solution
We developed an in-house system that extended the ERP outside the box and outside the green-screen. This system uses a web service that acts as the bridge between the ERP and a client application. It looks something like figure 1.
Fig. 1 Illustration : System Overview
How it Works.
This solution was deployed on inexpensive "Classroom PC" laptops which are physically mounted into each man-lift — a forklift that raises both the picker and the skid of material. See figure 2. Each PC is running a Java application that was developed in-house.
Fig. 2 Man-lift with PC
Here is the process flow.
- The warehouse employee (picker) scans a barcode on the picking ticket.
- The line item information is retrieved and displayed in a form.
- The picker types in data which is validated.
- The picker signals line item completion and the ERP system is updated.
- Process repeats for each line item until the picker signals order completion and again the ERP is updated.
The Picking Operation
When the line item barcode is scanned into the picker application, a web service method is called with a unique identifier and returns a pick item object (class) which contains a combination of fields from the order, customer, ship-to, product, and other files.
This is sent (or serialized) as a response to the web service client where it is de-serialized back into a usable class by the web service client.
As the picker types in quantities, vendor, and material codes these are validated locally or sent to the web service for validation. When the picking process is complete, the data is sent to the web service which calls a custom subroutine. The order, line item, and warehouse records are updated with the picker's data.
"It Took Some Time to Get Used to But ..."
The warehouse pickers were skeptical about the proposed system and thought that it would take far longer to get the job done. After three weeks in full production one of the employees from another branch office visited and asked several of the pickers how they liked the system. The answer was, "It took some time to get used to, but we'd never go back to doing it the old way."
Why? Because they were saving time in the overall process.
In the old system the pick information was written on the paperwork, the product was labeled for the customer, the skid was shrink wrapped, and the paperwork was turned in to the clerks inside who validated the information as they produced the packing slip. If the code was not found, the process stopped and the paperwork was sent back out to the warehouse to be re-checked. The picker would have to cut the shrink wrap open, find the item, and verify the codes. Often it turned out that they had pulled the wrong product from the shelf.
The new system validates the data that the picker enters as he is pulling the item from the shelf. The new system provides immediate feedback that has often caught mistakes as they were being made. It also tells him on the screen what (if any) products can be used as a substitute.
What Did it Cost? Nothing.
The tools and technologies are free, and accounting does not charge my salary to specific projects since, like you, I rarely spend all my time on one project.
This system was completely developed and deployed with and on open source tools. The web service is hosted on Oracle/Sun's Glassfish Application Server running on a virtual Linux server.
The client is a Java application. Both the web service and client application were developed using Oracle/Sun's Netbeans integrated development environment. The subroutines were developed by an in-house programmer in UniBasic.
How Long Did it Take?
Six months from get to go.
Once we decided to move forward with the project, it took an initial 120 hours to develop. We spent the remainder in a live test phase. First we chose one picker to test the application and give us his feedback. It was during this time that we actually went from concept to a user-friendly application. We also realized that our initial approach, a browser based approach, was not going to work. We were also working on the wireless networking to eliminate dead spots. After the first month of testing, we expanded it to our main branch. Within six months we had implemented in full production throughout the company.
This has been in full production for over a year at all of our branch offices.
This web service also includes producing product labels, managing drops (usable leftover pipe from cutting , programming automated saws with length and miter (angle) data, displaying priority tasks on TV screens in our warehouses, and tracking orders through the process.
Let's look under the hood.
Data Transport
The way data is transferred is via an XML dialog between the client and the server as described by a WSDL (Web Services Description Language) file and a Schema file on the web service (fig. 3). The combination of WSDL and Java clients provides the opportunity for the Netbeans IDE to create a web service client automatically. It builds object handlers with methods and properties. This gives the programmer a natural interface to the data and functions as if these resided within the client application itself (see code sample 1). As you will see, we (the programmers) do not ever have to deal with the XML itself.
Fig. 3 Data Transport
The web service method gets the de-serialized object and reformats it into a UniData (UniDynArray) record with attribute 1 set to the list of field names and attribute 2 set to the corresponding data values. It then calls a subroutine through a UniObjects for Java (UOJ) session. The subroutine call returns one parameter that has the same structure. The returned UniDynArray is then converted to the desired object. Code sample 2 shows the conversion routines from and to UniDynArray for the PickItem object. The one parameter in / one parameter out structure allows us to use the same call interface throughout the different methods we are using. The subroutine provides then back end processes that are required to properly update the order.
Overhead
When I first began to use UniObjects, I experienced a common issue. There was a long delay the first time I would connect, open files, and read records. This only happens on the session initialization. Once the session is open, data transfer is very quick. To eliminate this overhead, pre-existing sessions are used and re-used wherever possible. If there are no sessions available, the web service creates one to the host for this and future calls to utilize.
Code samples
Request line item from web service.
LineItem lit = ordPort.getLineItem(scanData);
Convert object from and to UniDynArray (Fig. 4)
public PickItem[] toPickItem(UniDynArray val)
{
ArrayList pArray=new ArrayList();
int itemCount=val.dcount(2,9);
for(int i=1; i<itemCount+1; i++)
{
PickItem pck=new PickItem();
pck.setMode(val.extract(2, 1, i).toString());
pck.setScanData(val.extract(2, 2, i).toString());
pck.setPickerInit(val.extract(2, 3, i).toString());
pck.setProdNum(val.extract(2, 4, i).toString());
pck.setMfgCode(val.extract(2, 5, i).toString());
pck.setHeatCode(val.extract(2, 6, i).toString());
pck.setPickQty(val.extract(2, 7, i).toString());
pck.setContCode(val.extract(2, 8, i).toString());
pck.setTotalPicked(val.extract(2, 9, i).toString());
pck.setPickBin(val.extract(2, 10, i).toString());
pck.setPcsQty(val.extract(2, 10, i).toString());
pArray.add(pck);
}
return (PickItem[]) pArray.toArray(new PickItem[pArray.size()]);
}
public UniDynArray toUniDynArray()
{
String[] argNames =
{
"MODE", "SCAN.DATA", "PICKER.NUM", "PROD.NUM", "MFG.CODE", "HEAT.CODE", "PICK.QTY", "CONTAINER", "TOT.PCK", "PICK.BIN", "PCS.QTY"
};
UniDynArray val=new UniDynArray();
int i=1;
for(String name : argNames)
{
val.replace(1, i++, name);
}
safeRepl(val, 2, 1, -1, this.getMode());
safeRepl(val, 2, 2, -1, this.getScanData());
safeRepl(val, 2, 3, -1, this.getPickerInit());
safeRepl(val, 2, 4, -1, this.getProdNum());
safeRepl(val, 2, 5, -1, this.getMfgCode());
safeRepl(val, 2, 6, -1, this.getHeatCode());
safeRepl(val, 2, 7, -1, this.getPickQty());
safeRepl(val, 2, 8, -1, this.getContCode());
safeRepl(val, 2, 9, -1, this.getTotalPicked());
safeRepl(val, 2, 10, -1, this.getPickBin());
safeRepl(val, 2, 11, -1, this.getPcsQty());
return val;
}
private void safeRepl(UniDynArray ar, int attr, int val, int subval, String src)
{
if(src != null)
{
if(subval > -1)
ar.replace(attr, val, subval, src);
else
ar.replace(attr, val, src);
}
}
Fig. 4
"I Don't Like Java ... What Other Options Are There?"
You may not want to develop in Java. The good news is that there are other ways to do this. Web services don't have to use WSDL, and they don't have to be written in Java. JSON (Java Script Object Notation) is another data type that can be used to connect the data to clients in other system. JSON is very commonly used in the auto-suggestion controls on web sites. There are many JSON encoders and parsers available for almost every programming language. For example you can pass JSON data from ASP services to browser-based clients using Ajax (Asynchronous JavaScript and XML). There is a UniObjects COM object that works with ASP and Visual Basic.
Here is a list of platforms and technologies that you can choose from.
- Web Servers
- Apache
- Microsoft IIS
- Oracle / Sun Glassfish
- Tomcat
- Web service platforms
- Java on Tomcat
- ASP on IIS
- PHP on Apache or IIS
- Client platforms
- C++ with WSDL or JSON
- Java with WSDL or JSON
- Visual Basic with JSON
- Web based with JavaScript
Your web service server choices include IIS, Tomcat, or Sun's Glassfish server. Tomcat and Glassfish are free downloads available for Mac, Windows, and Linux. IIS comes with the Window 2000 series servers.
Your client choices are Java, C++, Visual Basic (Windows only), or even browser based using Javascript.
Our choice has been Java clients on Windows and Linux devices connecting to a Glassfish web application server running on a Linux virtual machine.
Final Thoughts
This article described a system created as a process improvement project. The end result provided our company with a system that frees our employees to focus on more customer-centric activities rather than duplicating data recording and moving paperwork. More work is flowing more efficiently.
Where neither the standard tools built into the ERP nor the optional modules were able meet our needs, extending the ERP system allowed us to achieve our goals.
