Navigation:  Articles > Jun-1998 >

Electronic Commerce, Part 2

Previous pageReturn to chapter overviewNext page

Dave Irvine          
In last month's issue Electronic Commerce, Part 1, Dave provided an introduction to the non-coding issues involved in setting up an EDI system. This month, he gets down to the Access code and queries that make it all work.
In the May 1998 issue, I outlined the issues involved in setting up an EDI system. With that done, I went on to discuss how to export a purchase order from your Access system into EDI and transmit it to your trading partner. But what happens on the receiving end? How can you take an order from one of your customers via EDI and import it into your system? In this article, I'll look at how to import a customer's order -- specifically, how to input the order into an Access-based order entry system.
I've supplied a simple Access application that imports data, flags errors, and allows you to both view the results of the processing and fix errors in the orders (see Figure 1 for the main processing screen). This is far from a complete application, but it's the basic framework for any EDI receiving process. I've used the Sales Order system generated by the Access database wizard as my target system (you'll have to reattach these tables before using the sample database).

199806_di1 Figure 1

199806_di2 Figure 2

Getting the data in
The system flow for information coming from your customer is shown in Figure 2. The customer would transmit the data to you in one of the standard EDI formats. In this case, your customer is sending an 850 Purchase Order, either via point to point FTP (File Transfer Protocol) or via a traditional VAN (Value Added Network) mailbox. Your EDI software will pick up the customer's document, confirm that it's a valid EDI document, send a receipt of acknowledgement back to your trading partner (this would be the ASC X12 997 Functional Acknowledgement), and produce a flat file for your order entry system to process. Since the mechanics of that processing will be different depending on your EDI software, I won't dwell on them here.
However, once you've received the flat file, you need to convert the customer's purchase order into a sales order in your system. First, you need to import the file into a holding table in your Access database. Provided that your EDI software vendor creates a flat file in one of the common formats, this can be done with a simple TransferText action:

DoCmd.TransferText acImportDelim, "SO Inbound", _

  "tblSOInbound", "SO File.txt"

As the first argument indicates, I've assumed that your EDI software has produced a comma-delimited file. The second argument passed to this method allows you to specify an import specification that controls how the data from the file is going to be loaded into the table. Setting this specification up appropriately allows you to exercise a great deal of fine control over the import without writing any code. In Figure 3, you can see the specification I used in the sample database. The spec assumes that the first few fields of the EDI record are fixed (record type, order number, and so forth) but that the rest of the fields (F2, F3) are freeform and vary from record type to record type.

199806_di3 Figure 3
After you've imported the data, you can't consider that you have a sales order yet. It would be better to think of it as a "request" for an order. It's important to remember that, were the order keyed into an online order entry system, you'd apply a series of edits to the data before accepting it. These same edits need to be applied to the inbound EDI data. As a result, an update/edit process is required to verify the customer's data against the data in your system.
This process will either accept the information and produce an entry in your Customer Order table, or it will reject it and produce an exception. In the case of an exception, you need an additional procedure to allow an operator to review the bad orders and edit the information the customer sent in order to clean up any problems. Once the order has been fixed, the order creation process can be rerun and, hopefully, the order can be created. Additionally, once an order has been created, a detailed order acknowledgement (ASC X12 855 PO Acknowledgement) can be created, exported to the EDI software, and transmitted back to the customer. Unlike the earlier Functional Acknowledgement, which merely indicated that the data was received, this Acknowledgement indicates that the data was, in fact, a recognizable purchase order.
Inbound file
So, what does an EDI input file look like? The following example is a sample EDI purchase order file, as it would have been exported from the EDI software. The first field in the records indicates the type of record -- in this case, an Order Header (ORDERHEAD) -- three order detail lines (DETAIL), and a summary record (SUMMARY) that shows the number of detail records in the order.


"DETAIL",1,"Half Inch Copper 90",12,"Ea",0.95

"DETAIL",2,"Half Inch Copper 45",20,"Ea",0.85

"DETAIL",3,"White Basin",1,"Ea",27.35


The ORDERHEAD line holds the customer number, the date of the order, and the customer's purchase order number. The DETAIL lines contain a part number, a part description, the quantity requested, the unit of measure for the quantity, and the expected price. The SUMMARY line provides information for checking that the data was sent correctly -- in this case, that's just a count of the number of detail lines.
This file then needs to be imported into the Access database where it will be held in a holding table (in the sample database, this is the table tblSOinbound). Once the table is loaded with the data sent by the customer, the edit checks are run. The key edits that need to be considered here are:

Can you identify the customer? Does the customer number match a valid, active customer in your customer table?
Is this a duplicate order? Do you have a corresponding order in your system with the same customer number/purchase order number combination?
Can you recognize the products being requested? Can the requested part number be found in your product file? That's actually a little simplistic, since it assumes that the customer is sending only your part number. In many cases, however, the customer will order by their part number, or by the UPC code of the product. Standard EDI practice in distribution and wholesale is for the customer to send all of the above: their part number, your part number, and the UPC. You'll need to create cross-reference tables in your database to cross reference your part to both UPC and customer part numbers (see Table 1). In this way, you're three times more likely to supply the customer with what he or she actually wants. Since the customer's part number might duplicate across different customers, the index on the customer part number includes the customer number. If all else fails, the included part description provides another way to determine what part the customer is requesting.
Table 1. The part number to customer part to UPC number table.





















Do you have sufficient quantity on hand to fill the request? If there's not sufficient stock on hand, and none is expected prior to the ship date, then you should notify the customer that part or all of the requested product is backordered.
Is the pricing correct? If the customer is sending unit price in the order, does this match your unit price, allowing for customer discount/contract pricing? Discrepancies can be handled in at least four ways:
-- In many cases, customers will stipulate that it's the job of the supplier to notify on any price discrepancies and implement an agreed-upon resolution process.
-- Companies might adopt the attitude of, "If our price and your price don't match, we'll pay the lesser of the two."
-- The selling company's price might always apply.
-- The order might be summarily rejected, with the customer having responsibility to take action on it.
Do units of measure agree? This is an important item that's often overlooked, sometimes with unfortunate results. For example, a customer requests 100 feet of copper pipe, but you stock pipe in lengths, not feet (1 Length = 12 Feet). The customer isn't going to be pleased when the resulting truckload (or boatload!) of copper pipe arrives at the job site.
Does the control information match? The last record in each order (the SUMMARY record) contains a control total for the number of detail lines received. If this doesn't match the number of lines in the order, the order is in error.

In the sample databases, I've implemented all of these tests through a series of Access queries. Each query checks for a particular error condition and copies the order to an exception table. There are other edits that can be applied to the order, such as the order putting the customer over their credit limit, and these will differ depending on your system.
Handling problems
Once the system has attempted to create a Sales Order from the customer's data and failed, the system manager must be alerted that the order has been rejected and be provided with information on the nature of the problem (or problems).
Once the error is flagged, the system manager can then use the data correction form (see Figure 4) to review the problems and modify the data. While it might seem perverse to mention this in a Smart Access article, an important point to remember here is that you shouldn't get hung up on using technology to solve all of these problems. If a user orders a product in feet, and you stock it in lengths, a simple phone call to the buyer can clear up the situation fairly quickly. Implementing a mail-based application to resolve these problems is probably overkill.

Figure 4
Nevertheless, I've included a form for reviewing and fixing errors in the customer's data. The form included for fixing bad orders allows the system manager to review the order in error and do one of three things:

delete the order
look up a part and change the order line to the found part
look up a part and change the price on the order to the price of the found part

Once the data is edited, the order can be fed back into the order creation procedure. This edit/update cycle is repeated if necessary (hopefully it won't be) until the order is accepted and a sales order is created.
Replying to the order
In some trading partner situations, the customer will require that the vendor provide a detailed acknowledgement to the purchase order. This not only acknowledges the receipt of the PO, but also provides key information regarding your ability to fill the order. The acknowledgement should include, but isn't limited to, the following:

the vendors sales order number
the expected ship date
the quantities that will be shipped (back orders)
substitution products
discrepancies in pricing

In the sample database, the tables tblSOResponse and tblSOResponseDetail contain the information for this response. These tables contain data that can be exported to the EDI software to create an EDI 855 PO Acknowledgement.
Table tblSOResponse contains the header information for the acknowledgment. Its primary key is the sales order number for the sales order generated from the purchase order, since our sales order numbers should be unique to our system. It's important to notice that, while these orders might have problems, those problems have been resolved in some manner (for instance, we didn't have enough in stock, so we backordered the part).
Table tblSOResponseDetail contains line item detail for the Purchase Order Acknowledgement. In this table, a code field called Line Code contains a code indicating the status of the line. In the sample, I've limited this to the four codes shown in Table 2. As previously noted, your company might want to implement additional tests. Since it's possible for one line item on a PO to generate multiple Response Detail lines (for example, the product is backordered, and there's a price discrepancy), the primary key for this table is the sales order number, the sales order line, and the line code.
Table 2. Valid order detail codes.








Price Discrepancy


Part Substitution

Also included in the acknowledgement are the important items from the line item, along with the results of the order processing. This provides a "you asked for, we did" notification for the orders. For instance, the acknowledgement will have two product IDs in it: ProductID (which holds the part the customer ordered) and ProductIDShipped (which holds the actual part sent). This allows the sender to be notified that the part ordered has been replaced by a different part (presumably of equal or higher quality). Similarly, Quantity is mirrored with Quantity Shipped to indicate that there's a backorder, and UnitPrice is mirrored by Actual UnitPrice to handle price discrepancies.
Once this information has been created in your Access database, it should be exported to a flat file for import into the EDI software and transmission to the customer. The format of this file will vary, depending on the requirements of the EDI software.
As I hope my two articles have pointed out, integrating EDI into your system isn't "black magic" -- nor is it all that difficult. It's important to remember that the key role of EDI is to improve the transaction processing between you and your trading partners. To this end, the most important first step in EDI implementation is to work with a good, solid trading partner -- one that you have a great relationship with. In fact, the best advice I can give you is to try to work with an experienced partner, someone who can show you the ropes and help guide you through any rocky points. In the meantime, you can work with the sample database to see the kind of routines that you'll have to create.
Additionally, a critical success factor in any good EDI system is proper planning. Think about what your goals are. Identify the information that's key to you and your trading partners, and ensure that you can handle it in your application, before trying to roll the EDI layer overtop. Develop a project plan for implementation, and take it in small, manageable stages. Too many EDI implementations have gone astray due to companies trying to do it all, all at once. Remember that there's only one way to eat a french-fried elephant -- one bite at a time!
Read about the download EC2.ZIP on this page
Dave Irvine is the owner of Irvine Data Systems in London, Ontario. He's currently working with Emco Ltd. as EDI coordinator as it implements its EDI system. He is former chairman of the EDI Standards Committee for the Canadian Institute of Plumbing and Heating.

See also
Using FTP from Access Applications