The e-commerce juggernaut is in full swing, with players from mammoth IBM to tiny ISPs. A look at IBM's e-commerce catalog shows over 100 e-commerce products for the AS/400 alone. It seems that it should be a snap to provide e-capabilities for your systems. Unfortunately, nothing could be further from the truth. While there is indeed an abundance of e-commerce products, they are usually either standalone storefronts or generic database "connectors." In either case, you do not have the integration that is required to make e- commerce part of your overall e-business solution. This article outlines the challenges and opportunities of back-office integration.

What Is Back-office Integration?

When you think of e-commerce, you probably think of the typical electronic storefront, something along the lines of Amazon.com (not the new zShops interface, which is nice, but the original online storefront, which is simply awesome). And while Amazon.com has a truly amazing Web interface—with a functional search engine, something that I cannot overemphasize—the real beauty of the site is its back-end processing. I decided to give Amazon.com a go just before Christmas, and I was truly impressed by how smoothly the operation ran.

I ordered a couple of different items on the 16th of December. (This was very unlike me; normally, I procrastinate.) With a minimum of hassle, I was able to find the items I wanted, order them, put a rush on the shipping, and pay for them via my credit card. Here's where back-office integration came into play. One item was ready for shipment on the 17th, so Amazon.com's shipping department packaged it up and tossed it on a truck. Someone entered the partial shipment, and this information was logged back to the billing department, which then hit my credit card for the price of the item and—here's the cool part—a prorated shipping amount. When I placed the order, there was a single shipping amount for the whole purchase, but the partial shipping process made sure to charge for only that portion that was sent.

I went back online that same day (the 17th) to take a look at my order and saw that one item was on its way (I could even get the tracking information) and one item was still being processed. As it turned out, the second item shipped on the 18th, at which point my card was charged for that item and the remaining shipping charges. Everything ran like

clockwork, and my packages arrived several days before Christmas—something of a minor miracle in my life. Even those e-companies that had problems delivering their products had those problems not because their Web-based order entry systems were bad but more because they were too good—that is, the demand outstripped the supply and the vendors just didn't have enough product to deliver.

Notice the level of integration between the various systems. For example, my keystrokes and button presses at the storefront were translated into a complete order, with customer, order, shipping, and billing information. Master records were added and stored, as this was my first foray into the system. During my next pass through, the system recalled all this information and provided a default for me, which meant that the master records were automatically included as part of the dynamic HTML of the Web interface. (Figure 1 shows this default). The order was cut, and someone on the warehouse floor reviewed the inventory. (I have no idea how integrated this part was, but I would guess that a certain amount of sophisticated inventory management happened here.) Someone made a decision to split the order, and this information was relayed to billing and order processing.

While this is all fairly normal enterprise resource planning (ERP) stuff, the remarkable bit is that the results of these decisions are all available to me online. In fact, I can review my entire order history online if I'd like, meaning that I have access to both real-time transaction data as well as historical information.

What's the Big Deal?

From my little capsule summary, you might be tempted to think, "That's one heck of an impressive e-commerce package Amazon. com is running,. Where can I get a copy?" A little research uncovers the fact that it's not a simple off-the-shelf e-commerce package but an incredibly complex network of systems. By looking at its software technician want ads, I surmised that Amazon.com runs on Microsoft Windows NT, UNIX, and Linux. Its ERP systems run on Oracle, while its Web presence is a blend of systems written in C, C++, Java, and Perl. The e-commerce and middleware component includes BEA Tuxedo, TOP END, and M3. The database is a combination of Oracle and SQL Server.

Why do I belabor this point? Well, first off, looking at this maze of software makes it clear that Amazon.com is not running a turnkey system supported by an occasional phone call to the e-commerce vendor's help desk. In fact, Amazon.com currently has openings for over 50 software support positions. Those are just the openings. One can only guess at the current staff size. So it might be wise to forego any idea of buying Bill and Ted's Excellent E-commerce Software and getting a site with the functionality of Amazon.com.

Second, I'd like to point out that, at least from my cursory inspection, Amazon.com seems to be primarily a UNIX-based environment. This is important because, while IBM is doing an excellent job of positioning the AS/400 as a premier Web serving platform, the current e-commerce offerings tend to be geared toward UNIX and SQL, not exactly the technological stronghold of most AS/400 ERP packages. Hence, the back-end integration strategy for those off-the-shelf packages that have one usually amounts to little more than dumping the entered data into SQL tables and processing it later. This leaves a fairly large hole for your IT staff to fill—namely, that of converting the data from the storefront and entering it into your database.

Where Does That Leave Me?

Well, if you currently have an AS/400-based ERP package, chances are that you're not exactly poised to take advantage of e-commerce. Because most of the storefronts are designed to use SQL as the transaction-processing back-end, there's really no standard interface to the traditional record-based databases of common AS/400 ERP packages such as eBPCS from System Software Associates (SSA) or PRMS from Computer Associates International. Instead, you're going to need some sort of middleware that will integrate the

storefront with your existing business applications. As it turns out, the issue is not so much finding a good e-commerce storefront: There are lots of those. Instead, the real issue is integrating the storefront with your existing line of business applications.

If your current system is primarily SQL-based or you're planning on designing your entire application system from scratch, it's far more likely that you can adapt an offthe-shelf storefront to fit your particular business needs without major work. But if you run an extensive record-based ERP package, either a homegrown system or one from an AS/400 software vendor, then you need to set aside a significant portion of your budget for integration. For example, incorporating order data into an order history display seems straightforward enough, but, if the data is not in the correct format, you're going to need either some very sophisticated SQL calls to access the data correctly or a good data mirroring product. As a very simple but pervasive example, older versions of both PRMS and eBPCS have order dates, but PRMS stores the dates in three different fields—month, year, and day—while eBPCS stores dates as a single YYMMDD field.

The seemingly simple act of calculating the number of days an order line is past due quickly becomes complex in an SQL statement. As soon as you throw in the various Y2K remediation options (e.g., expansion, conversion to L-date, and plus-28/minus-28), the combinations become unmanageable. No off-the-shelf product will be able to support the various possibilities; you'll have to write—and maintain—custom code yourself.

Where Do I Need to Integrate?

That depends on your business. It's unlikely that you have the same type of high-volume, large-customer-base distribution environment that Amazon.com has; in fact, you probably have specific business requirements that are different from theirs and will require different points of integration. Take a look at some possibilities:

• Order entry integration—This is probably the common denominator among all e- commerce integration. You'll need to be able to take the transactions entered online and process them as though they were entered via standard order entry. SQL processing often isn't enough because, while SQL can add records, it won't force the order through the rest of the business processing that may be required.

• Order tracking and history—Another common feature for storefronts is the ability to display open order status as well as historical order data. This sort of feature is well suited to SQL processing because it's primarily a matter of retrieving sets of related data. However, as I've pointed out, it's not always a simple matter of tossing together a quick SQL statement or two; setting up any kind of inquiry requires an in-depth knowledge of the underlying database.

• Order configuration—This capability is often required for make-to-order shops, which tend to build single-unit or small-volume lots. Specific rules and constraints have to be defined, which allows the user to select only those options that make sense together. While there are some examples of fairly complex order entry (for example, take a look at any of the online computer retailers), these systems have fairly simple rules processing. High-end order configuration systems have very sophisticated rules-based processing; you'll need to have some sort of interface to that system.

• Inventory availability—Depending on the sophistication of your ordering process, you may want to check inventory availability prior to taking an order or even provide an estimated delivery date using Available To Promise calculations.

• Promotions and deals—One of the more intriguing aspects of online processing is the ability to tailor the dynamic Web content to spotlight specific items. Have a surplus? Lower

the price and have a banner show up on the customer's screen: "Special Discount! Only Three Days Left!" Also, unlike pure distributors, either catalog or online, manufacturers and wholesalers are far more likely to have specific pricing agreements with their customers, which is usually beyond the capabilities of traditional SQL approaches.

• Work flow integration—While not limited to the e-commerce process, work flow integration is an important consideration in a total e-business solution. You may want to identify certain conditions that will trigger various work flow events. For example, an order from a first-time customer might produce an entry in a special promotional database, while an order from a long-dormant customer might generate a "welcome back" activity ("Be sure to check out our new features...").

How Do I Integrate?

That depends on your situation. For example, for eBPCS Version 6 or later, SSA has included some sophisticated hooks known as semantic message gateways, which allow you to send and receive information from the system. If you have the programming resources, you might want to try integrating these capabilities into an off-the-shelf product.

On the other hand, if you have an older or less "open" system or you have limited programming resources, you might want to try looking to a vendor who specializes in e- commerce solutions for your particular software package. For example, Nexgen Software Technologies (www.nexgeninfo.com), who, I should disclose, is a client of mine, has a product called NexgenCommerce that is specifically designed to provide a storefront interface to various versions of eBPCS and PRMS. NexgenCommerce uses a very thin Lotus Domino user interface that provides the same functions as traditional storefronts. Figure 2 shows a typical shopping screen with navigation, pictures, descriptive text—all the things users expect to see when using a shopping site. Someone with knowledge of Domino can easily modify the site software. However, the product is designed to access the eBPCS or PRMS database. Figure 3 shows how the software reads the shipping information from the eBPCS database and displays it as a default in the shipping form. Similarly, orders automatically run through the appropriate order entry programs, thereby triggering the appropriate eBPCS functions, such as allocations, which further integrate into the material-requirements planning portion of the ERP system. Without this level of integration, both you and your customer base would have to do a lot more work.

No matter what the situation, if you have an established business application system, I recommend that you find a storefront that is highly customizable, preferably one that has a demonstrated ability to interface with traditional AS/400 software. An SQL-only back-end is probably not going to be sufficient, unless you have a lot of programming talent. To use an SQL-only solution, you'll probably end up creating "event" files with database triggers. The SQL interface portion of the storefront software will write records to the event files, and the triggers will then call programs to actually interface with the existing database. The other option is to have a batch interface, which would process the event files and call the appropriate interface programs. In fact, if you have a low-volume, long-lead- time business with simple items, a batch interface is probably a better option. However, if you have a low-volume, long-lead-time business, you probably don't need a Web storefront in the first place.

The Bottom Line

Chances are that if you're reading this article, you'll probably need some sort of storefront processing, if only to keep from falling behind the competition. However, you should not take this decision lightly, especially if you're an AS/400 user. Take a look at the issues outlined in this article and see what solution best fits your particular situation. Do you need a great search engine, or do your customers usually know what they want? Are your customers prone to impulse purchases? Do your research and make an honest assessment

of your IT staff. Above all, remember that e-commerce is not a one-size-fits-all proposition.

Figure 1: As shown here, you can dynamically combine current transaction input with ERP master file data.

Figure 2: NexgenCommerce offers a storefront interface to the eBPCS and PRMS ERP packages.

Figure 3: The eBPCS shipping data integrates with the user's order information.

Joe Pluta is the founder and chief architect of Pluta Brothers Design, Inc. He has been extending the IBM midrange since the days of the IBM System/3. Joe uses WebSphere extensively, especially as the base for PSC/400, the only product that can move your legacy systems to the Web using simple green-screen commands. He has written several books, including Developing Web 2.0 Applications with EGL for IBM i, E-Deployment: The Fastest Path to the Web, Eclipse: Step by Step, and WDSC: Step by Step. Joe performs onsite mentoring and speaks at user groups around the country. You can reach him at This email address is being protected from spambots. You need JavaScript enabled to view it..

---

MC Press books written by Joe Pluta available now on the MC Press Bookstore.

		Developing Web 2.0 Applications with EGL for IBM i Joe Pluta introduces you to EGL Rich UI and IBM’s Rational Developer for the IBM i platform. List Price $39.95 Now On Sale
		WDSC: Step by Step Discover incredibly powerful WDSC with this easy-to-understand yet thorough introduction. List Price $74.95 Now On Sale
		Eclipse: Step by Step Quickly get up to speed and productivity using Eclipse. List Price $59.00 Now On Sale

Also Read

RPG Academy - Database Modernization: Entity Relationship Diagrams

RPG Academy - Database Modernization: A Bit of Database Theory

Talking About Modernization Isn’t Good Enough