Designing a Flexible Web Storefront Solution for the Oracle DBMS

Executive Summary

SGCO had a client requirement to design and implement a complete Web-based eStore infrastructure and front end for an enterprise Oracle database. The client, a mid-size manufacturing business, used Oracle E-Business Suite to maintain all manufacturing, inventory, and sales data. However, all interactions with customers were manual, through customer service agents. This approach was costly and time-consuming, and had the potential to introduce errors into customer interactions. In addition, a manual approach made it impossible for its customers to place orders, check on the status of orders, and change orders without calling customer service.

After lengthy efforts, the client determined that the Oracle E-Business suite and its iStore sales module were not able to support its requirements for a fully self-serve Web customer portal. In particular, the Oracle application components were not readily adaptable to the unique requirements of this manufacturer’s sales and service model.

SGCO was tasked to design, implement, and deploy an eStore solution that encompassed all of the storefront activities required by the manufacturer. Specifically, the solution had to allow both current and new customers to perform self service across the life cycle of the business relationship. The SGCO and Young Group responded with an innovative software architecture and implementation that delivered on all of the customer requirements in the space of 11 months. That solution is now deployed and in productive use by the customer.

Problem and Background

A mid-sized U.S. manufacturing business that is a leader in the building of automated teller machines (ATMs) on the Gulf Coast a employed a legacy system to manage its sales and customer data. The company began making an investment in the Oracle DBMS in order to modernize this system and permit greater flexibility in managing and conducting business.

In 2004, this company embarked upon a project to provide a Web-based interface for customer interaction with the company – a Web storefront, so to speak. This decision was made at the same time as many other businesses were establishing online stores, and for many of the same reasons – delivering more support options and providing a self-service configuration that could be accessed 24/7 and from any location.

Up until then, all interactions with customers were handled through customer service agents, who received telephone calls, looked up information on the computer, entered relevant purchase, warranty, or similar information, and relayed the results back verbally to the customer. In most cases, those calls are followed up with fax, letter, or email documentation confirming transactions.

Yet the experience of the last decade has been that customers increasingly prefer a purchasing and support experience that enables them to control when, where, and how they interact with the enterprise. And the exclusive use of a human interface, while often useful for its personal touch, can be both frustrating or inconvenient for the customer, and time-consuming and expensive for the manufacturer or service provider. Further, an e-commerce interface improves both responsiveness and accuracy, as the potential for human error is largely eliminated.

The Web storefront project had a dual impact on technology strategy. First, it meant that the project to fully implement the Oracle DBMS took on added importance. Because the legacy systems could not be adapted to the ad hoc query and response format required by an automated Web storefront, it was necessary to make sure that the Oracle DBMS was fully in place and operational, providing the data management and business intelligence functions for the company.

Second, the business need for a Web storefront drove an initial technology decision that proved telling. As the company already had a significant investment in Oracle software, it selected the IStore application to build its e-commerce store, and hired a technology consulting firm to determine requirements, produce specifications, and build the storefront. In theory, it should not have been difficult to implement an Oracle-based solution.

However, after four years and four groups of consultants, the Web storefront solution remained elusive. The Oracle E-Business suite/IStore was not flexible enough to be adapted to meet the unique needs of this manufacturer, despite the use of multiple technology consulting firms over a period of several years. For example, the manufacturer had different warranty lengths and policies, depending whether the item was an entire unit, a component of the unit, or a part within the component.

Exceptions and unique business requirements such as this made it infeasible to adopt an Oracle pre-packaged solution. Nevertheless, over a period of several years, the packaged approach was attempted several times. Because these prior technology-based approaches didn’t appropriately acknowledge the needs of the business, it was little surprise that it was not successful.

Beginning With Business Requirements

Instead of attempting to build a solution around Oracle applications, SGCO and Young took a different approach. It started with a clear and objective understanding of what the manufacturer hoped to accomplish for its business. This enabled it to frame the business requirements into the right technical approach to designing a solution.

First, the business requirements were filtered into a set of e-commerce features. The system should enable the customer to perform the following activities online, in an automated manner:


  • Purchase parts, accessories, and whole units
  • View account status
  • View account histories
  • Create warranty RMA orders with advance replacement shipping
  • Creating RMA orders for both flat rate repair and repair estimates
  • Purchasing Training classes
  • Purchasing software activation license credits
  • Activating software licenses

Other requirements were defined, although these represent the fundamental business operations needed by the company.

Second, SGCO matched requirements with potential technology solutions, without a commitment to any specific one. In doing so, it discovered that the business requirements did not lead well into the use of the Oracle E-Business products, because many of the manufacturer’s unique business requirements could not be successfully modeled by the prepackaged software. While this was apparent in the several failed efforts prior to this time, it required a solutions provider that was willing to look beyond the obvious.

Approach and Architecture

Rather than work with packaged software that wasn’t adequately adaptable, SGCO designed and implemented a system that was centered around business requirements listed above, rather than on fixed technologies. The result was a Web application built specifically for this business, in a far shorter amount of time than possible through adapting prepackaged applications.

Architecturally, the SGCO-designed eStore application used a web application employing PHP as the logic behind the user interface. The application uses the classic ModelView-Controller (MVC) architecture in separating the user interface from the underlying business logic. This ensures that the application would perform well and be readily maintainable across its lifecycle.

For communication, the eStore employs Ajax and JSON (JavaScript Object Notation). Ajax provides for the ability to support user data input and output display without a server page regeneration and redisplay, resulting in Web application performance more comparable to that of a desktop application than Web site. JSON is a lightweight communications protocol that employs JavaScript as the mechanism for transporting data between client and server. The rationale for these technologies is the ability to deliver high performance to the customer, making the application more user-friendly.

This three tier solution incorporates the Oracle environment on the manufacturer’s internal network. It provides for access to that network via an encrypted virtual private network (VPN) tunnel, an Oracle Proxy located on a remote DMZ, and the customer-facing eStore front end.

Minimal logic and data was stored in the customer-facing eStore beyond that required to operate the Ajax interface, and for supporting security and customer privacy objectives. Somewhat more logic and data would be stored on the DMZ, to bring it closer to the user and improve responsiveness. The bulk of the logic and data is stored on servers inside the enterprise network. This would provide both the maximum business continuity in the event of disaster as well as the maximizing control over security.

Interaction with the Oracle database occurs only between the Oracle proxy housed on the DMZ and the Oracle environment located on the manufacturer’s internal network. Interaction between the two was implemented using the Oracle Call Interface OCI8 database driver. Logic within Oracle database consisted of several packaged functions that interact with the eBusiness suite’s OE module.

It’s important to reiterate that this approach was custom-designed and implemented for the customer. This was not an existing product shoehorned into a set of business requirements, but rather a technology solution that was derived from those requirements. Despite the custom approach used by first understanding the business needs and devising a solution that met those needs, SGCO and Young was able to complete the entire project in eleven months.

While it is too early to quantify cost savings or improvements in customer satisfaction resulting from the eStore implementation, the project fulfilled the strategy of the manufacturer to move customer service operations largely online in a self-service storefront. After a wait of several years, this manufacturer now has the opportunity to drive its business based on real time information. It has the ability to provide its customers with a high level of service through a Web application, and keep its business fully informed of customer activity in order to make decisions based on the needs of its customers.

Summary and Conclusions

After several false starts, this manufacturer was finally able to put a fully automated ecommerce storefront into production, freeing customer service staff to be able to work more closely with customers with special or unique requirements. While it is too early to quantify the benefits of this approach, the manufacturer expects to be able to serve customers more flexibly, and at lower costs, than has been possible in the past.

Building an enterprise eStore is a challenging project. Many such projects are abandoned, or fail to meet their original business requirements. That was the initial experience of this manufacturer. A focus on a desired technology solution, rather than on its requirements, made a solution more difficult to achieve than it should have been.

In a larger sense, this project validates the underlying philosophy that the business requirements take precedence over a mandated technical outcome. Predetermined solutions rarely satisfy all important business requirements, because they begin with a focus on the technology. Rather, the appropriate approach is to begin with the business objectives and requirements, and enable the design and implementation of the correct solution to flow from the business. While it may seem to take longer to look objectively at the business needs first, this project demonstrated resoundingly that both business analysis and solution implementation can be done in a reasonable timeframe to benefit the business.

About SGCO

SGCO an Information Technology consulting firm that delivers innovative, scalable business solutions to help our clients reduce costs, increase revenue and gain competitive advantage through technology. Since 1995 the SGCO Group has provided advice, consultancy, design, implementation, training, network support, internal/external business application solutions and digital media communications solutions to a wide range of companies and organizations in more than 12 countries including USA, Canada, United Kingdom, France, India, Australia, South Africa, Serbia, and the Netherlands.