EDI Overview

Part I - Formats
Part II - Communication
Part III - Why

EDI System Design Part IV - Transaction Processing

I have mentioned before that this is the "Elephant in the room" no one likes to talk about. It is hard to talk in abstract terms about something so involved so I am going to take a specific example and use that of a customer sending their Purchase Order. We would want to turn this into a Sales Order.

So we get hold of a copy of the message format definition (Edifact, X12, Tradacom etc.), blow off the dust and start to study it. It might have, say 100 fields - Gulp! Then we look at our Sales Order database tables. These might have 500 fields - Double Gulp! How can we possibly map or translate from one to the other? The answer is we don't, you can't possibly. We have to stop thinking in terms of messages and documents, and think in terms of process.

When our company recieves an order in the mail or by fax, it gets typed into our ERP system. The user does not typically type hundreds of fields. The system should be optimised for speedy entry. A quick list of things that might be entered,

customer account, purchase order reference, delivery address, product code, product quantity, unit of measure, price, delivery date... eh... I've started to run out already... that is only 8.

Then why all those fields on the database tables? Well they do get updated, but they don't all get entered. For example, one of the fields might be Payment Terms, but we don't re-enter it on every order. It gets defaulted. Probably to a value stored on the customer database table. We might have the option of amending it when we type in an order but we lose that option for EDI (Electronic Data Interchange) orders. It doesn't mean it can't be amended sometime after the order was raised. It doesn't mean we can't introduce a rule to flex the setting based on various parameters. The point is we have to remove the human element and we certainly aren't going to let a customer enter the Payment Terms.

We do the analysis of what fields are actually entered. The Import Mapping routine finds where they are in the EDI message, and has stored them in an "pending" database table that looks like a very trimmed down version of the sales order table.

There are some fields that the customer might find problematic to send. Typically our codes for things like unit of measure. We might use EACH, PACK, M, KG. They might use ONE, BOX, MTR, KGS. This is where a universal standard would be useful, but there is no such thing. So we have to set up look-up tables to translate the standards codes or customers codes to ours. Codes are a large subject, but in order not to get bogged down I will just mention that the subject includes, among others, customers branch codes and product codes.

Our processing function will take each input in turn and apply the same field defaulting and validations as our order input screen. If any field fails input validation, the order must be abandoned. At the end we must perform the same update process.

To do this we don't want to re-write the whole order input process. We want to re-use the existing code. We have to hope we have an object-oriented system with methods to call, or structured programming with functions, or at the very least re-useable subroutines. If we have spaghetti code, or no access to source code at all, then we are in trouble.

Any way, when this routine has finished processing an order (wether it was sucessfull or not), we move the "pending" database object to a "processed" or "archived" table. We add any successfull sales order ids or unsucessfull error messages (such as "Order XXXX, Line XXXX : Failed to validate item code XXXX"), so we can produce an audit report. We might go on to develop an edit screen to amend failed order messages and allow them to be unarchieved and processed again.

Most important of all is the entry of price and delivery date. If our existing system allows free entry of these fields, then we need to tighten up it up. We can't allow the customer to enter whatever value they like and anyway, some customers might not send any price or date. We certainly aren't going to send it to them for free! What is a good idea, is to accept any values higher than our system default. If it is less, replace it with our minimum and add a message to the audit trail like "Order XXXX, Line XXXX : Price 11.11 requested, 99.99 used".

One final idea. If the system doesn't already check the customers purchase order reference to make sure that the order isn't a repeat that we have already processed, then we need to add it. I have had batches of hundreds of messages sent twice. I have had customers who insist on sending hard copies in the post and the sales order entry people enter them a second time!

EDI System Design Part III - Import Mapping

OK, I know I've missed something out. Between EDI (Electronic Data Interchange) Exporting and EDI Importing is Communicating. But to understand that, it's best to first have an understanding of both the inputs and outputs. Besides, it's my Blog.
What are we going to do with these EDI messages? Possibilities include,

• Print them out in human readable form
• Covert them into an email alert
• Store them for querying and reporting
• Store them for transaction processing

The first option of printing the message out is surprisingly common. I hope you can spot what is wrong with this approach. You see this sort of configuration has a technical name. It is called a Facsimile machine (or a Fax for short). Joking apart, it would be a shame to have spent a large effort of time and resources to discover we have re-invented the Fax machine (and an expensive one at that).

Now it should be obvious what is wrong with the second approach. Our trading partner can send email alerts and cut out the "EDI" system.
Querying and reporting is fine for some types of data. For example if a customer is updating us on EPOS sales data from their branches. We would probably want to run various analysis reports, maybe comparing against forecast, or projecting future stock replenishment orders. This means getting the information into our database. For most this means SQL compatible tables.

If the message is transactional in nature, and we want to action a transaction on our ERP system, then there are also sound reasons for storing the message in our database first. I don't want to go into too much detail (see Part IV), but one reason is you may have transactions sourced from many different EDI message types/standards, and our database table is a good linga franca to pass to the transaction routine.

Another reason is, we should be phasing in EDI software functionality with little updates often, rather than a long wait, followed by a big bang implementation. We can implement a "glorified fax machine", running reports from our database tables, first. Then move to a more functioning system later. I know I ridiculed this arrangement early on, but the point is this should be a transitional stage. We don't want to leave things like this for long.

So our EDI Import Mapping routine will take each message and loop through each segment updating our object and maybe a list of object details. When we reach a message header marker we write the object to the database. Different message types of the same encoding standard will update different object tables but it is worth all these routines updating an "Envelope" table with message meta data, and putting the id field on the object table. This will enable us at a later date to translate various partner identity codes (products, branches etc.) through a look up with a sender identity.

Once these updates have been done we can delete the EDI message from the "pending" folder and move it to an "archive" folder. There will be times when you get unexpected output (or no output) and we will want to check what our trading partner sent in the raw.