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.

Is this what hm.gov.uk thinks is EDI?

Electronic Data transfer in the news. I think it is called pigeon protocol.

No, you can't ridicule it because it is too serious. Unfortunately I suspect this "behind the mailbox" infrastructure is all too common.

I hope the little I.T. guy archives his email.

EDI System Design Part II - Export Mapping

I'm not going to go into the details of creating the text of EDI (Electronic Data Interchange) messages in certain formats. Well not in this post anyway. It is a big subject and the last post was long enough. What I am trying to do is lay out the System Design and APIs.


So you have a routine in your ERP system that created a document or a transaction e.g. a Sales Invoice. The options you have are,

1. Edit the routine to - Check EDI control. Pass the document to a export mapping routine. Store the returned EDI message in a folder for later batched communication.


2. Edit the routine to - Check EDI control. Store the document reference in a folder for later batched export mapping and communication.


3. Create an external routine to - Periodically select all unchecked documents. Check EDI control. Pass the document to a export mapping routine and communicate.

If you don't have access to your existing source code then your only option is 3. If you do have access to the source code you may still want to do option 3. There is less chance of introducing error and, in these days of Sarbanes-Oxley, the administrative work in getting changes approved, in something like an invoicing routine, must be considered. It doesn't mean you can't move to option 1 or 2 at a later date.


For traditional EDI formats (e.g. EDIFACT, TRADACOM etc.) there will be sections of messages that are common to several documents. Consider the senders address. For each document, this will probably be read in from the same place. The format may say addresses must be presented in a certain way. The first line of the address may be limited to a fixed number of characters but our system allows longer lines. We can't chop off the end of lines, so we will have to move the excess to the second line. This isn't that difficult a task but we don't really want to have to type out this code several times, testing and debugging each one. And if a format version change means a code change is required, then a small task will have just turned into a code hunt.

For all these reasons we want a single export mapping routine per format. Things like, senders address, can then be a single local function or subroutine. One of the calling parameters is document type. The next calling parameter will be a list of documents objects, or pointers to documents - probably the key fields in our SQL table. The returning parameter will be a list of formatted EDI documents. The calling program can then take care of writing to the pending folder or passing them to the communication routine.

There is one other less obvious parameter needed by the export mapping routine and that is the Communication Type data. Even though we have designed a system to separate mapping form communication, this flag is still needed. The reason is envelopes and addressing. Especially in traditional EDI formats, the message document is not complete without an envelope wrapper and we will need the communication type.


Part I

EDI System Design Part I - Message Control

Assuming you have an ERP system that processes sales orders, invoices, remittances, stock balances etc. how do add EDI (Electronic Data Interchange) capabilities? The first thing we need is to decide what is going to whom, in what format, by what means.

Let us suppose we want to start sending Sales Invoices to a key customer. The routine that will create the EDI invoice could be called from the ERP routine that does the invoicing, or it could be called from a periodic routine run sometime after. We will need a EDI Sales Invoice Control screen.
Customer:
Message Format:
Protocol:

Suppose we want to start sending Order Acknowledgements. We would have to create a similar screen. To avoid this, we add a Document field. Suppose the standards body releases a latest version of the message format. Some of our partners start to use it. Some stick with the old version. We could add a Format Version field or incorporate the version in the format.
Document:
Customer:
Message Format:
Message Format Version:
Protocol:

Some customers will have multiple branches or delivery points. I warn you from experience, there is a high probability that customer A only has half their branches EDI enabled, while customer B will have all branches enabled. So customer can't be the group account and must be the branch account.

Now a key supplier want us to send our purchase orders, so we add a Supplier field. One of, Customer or Supplier, must be entered. Next the taxman wants our sales tax information. Is he a customer or a supplier? Neither really. With luck you will have a Contacts file that incorporates all these entities and you can use that identity. Otherwise we need a Contact Type field (answer customer/supplier/third party) and Contact ID.
Document:
Contact Type:
Contact ID:
Message Format:
Message Format Version:
Protocol:

Some Document, Format and Protocol combinations will be compatible with immediate delivery e.g. sending purchase orders by email PDF attachment. However some formats are designed for batching up several messages into one. Also a partner might want a steady trickle of messages through the day and prefer one combined message say, once a day or once a week. If you are communicating by Dial-up (facsimile or point-to-point) there will be savings if messages can be combined. To achieve this we need to add a Batched Y/N option. If 'Yes' then the document ID is placed in a pending file to be translated and communicated at a later time.

At last we come to Protocol. We can't just store FTP, HTTP, SMTP, P2P etc. in each case we require more information e.g. URL, user name, password, phone number, alternative phone number. When you have a partner with lots of branches but central communication, this would mean a lot of repeated set up. It would also make batching very difficult because we would have to check every field.

The solution is another level of abstraction. We create a concept of Communication Type and this points to another screen where the additional fields are stored. Then, when Acme Widgets have all their stores documents going through one VAN mailbox, we only have to store the Communication Type identity.

So finally we have...
Document:
Contact Type:
Contact ID:
Message Format:
Message Format Version:
Batched? Y/N:
Communication Type:

Update - I was trying to keep this post as short as possible, but it just keeps on growing. I realised I missed another field. There some other pieces of information that you might need. Examples are where a customer wants EDI invoices but only for orders raised by EDI. Or the customer prohibits amendments to EDI sourced orders. Or the Trading Partner requests special formatting of a reference used in the message. There could be a need for several flags for minor functionality. Rather than clutter this screen with more fields, I tend to add a generic "Options" list on a sub screen.

Part II

EDI Assumptions

I was starting to sketch out my next post. It was going to be on EDI (Electronic Data Interchange) System design and I started with "Assuming you have an ERP system that processes sales orders, invoices, remittances, stock balances etc. how do add EDI capabilities?"

Then I thought wait!!! Important EDI life experience lesson - assume nothing!!!

EDI has a wide reach. It is used in all sorts of areas. While I am most familiar with EDI in ERP systems, it is also used in health care, logistics, military etc. So I thought I'd better at least provide a link to explain the term. This got me thinking about the different types of people who might be interested in these posts, what your situation is and how you came to EDI. And this brought to mind 2 experiences that challenged contrasting assumptions.

1) Assuming Everyone is Big

After many years of being familiar with ERP systems running businesses that employ many people, I had a shock. I came across a software package being used to run a small family service business. It was the sort of thing you can buy off the shelf in PCWorld. It was one of those friend-of-a-friend, help desk situations. I took the opportunity to familiarise myself with it, always looking to learn something new.

My shock came from the fact it had an invoicing function but no orders or quotes. It could purchase but had no stock control. Most of the functionality I thought was needed was missing. How could they run their company with this? The answer was, quite well thank you. You see they did have an order book but the number of orders you could count on your fingers. High value, but low volume. They didn't have a product line, no two jobs were the same. They would produce whatever the customer wanted. If they wanted to know what material stock they had, they looked in the shed.

It made me think. Could I configure my current, all singing, all dancing ERP system to run a similar company? Would it be as easy to use? If they experienced rapid growth, at what point would they need additional functionality?

What if they got an important contract from a big customer who wanted to send orders by EDI and receive invoices by EDI? How would they cope? How would they benefit?

2) Assuming Everyone is Small

Three years later I am summoned to a meeting with our then biggest customer, a major retailer. Lots of suppliers are there as they announce their Electronic Trading imitative. They have done their research and decided that past EDI initiatives have failed because they were too technically complicated and costly for all their small suppliers. They had a solution and from the audience response it went down well.

All suppliers needed was a browser and an Internet connection. They could log on to a website, print off PDF's of orders, confirm acceptance, record supplier order references, alter pricing, update delivery dates, confirm deliveries and even read the latest news from our important customer.
The small suppliers, who had feared warnings of "integrate or die", loved it. Suddenly I could see this was the solution for the small company I had experienced earlier.

The big suppliers sighed. How were we going to explain? You see we already did all this inputting on our computer systems. To repeat it on a web page would dramatically increase our workload. We organised hundreds of deliveries a week to hundreds of stores. Confirming a full order delivery was 1 click, detailing a partial delivery (or split delivery) was click, type, click, type, click, type... Unless we could persuade them to add another option, we were going to have to throw human resource at the problem.

Meeting after meeting followed.They eventually offered an FTP connection, but although most suppliers already outputted information in one of two EDI formats, the customer wouldn't accept them. They invented their own format. Every time we requested more time, we were told we could go live with the browser interface.

After we did go live, the customer provided a telephone help line. When we called we would get nonsense questions about what we had clicked on the web page.

Me: [Sigh, keep calm] We don't use the web page. We communicate server-to-server.

Operator: Eh... I'll just speak to my supervisor.

Lessons Learnt?

Trading partners have differing levels of ability.

Trading partners have many varied business styles and processes.

When volume splits 80/20 and problems split 20/80, 80 x 20 = 20 x 80 (if you see what I mean). Both half's are important. Don't design a solution to one half without consideration of the other.

Maybe Mashups are the future.

Story from the Trenches

I'm working at this company. No one wanted to be responsible for EDI (Electronic Data Interchange). It was seen as a problem (not a solution), so it gets dumped on the new guy - me.

With a lot of patience and time I slowly train myself and begin to understand. I had inherited a third party communication and translation software. It used 1 of only 2 modems in the entire company - it was along time ago. Users hated it. Apart from processing everything twice, it kept breaking down.

Time passes and I slowly improve the system and increase reliability. More customers start to use EDI. We had several customers connected by EDI. 2 different standards. 2 different networks. They were sending us Sales Orders and we were sending the Invoices. Hey! I was proud of what I achieved and everyone else (who didn't want to touch EDI in the first place) saw me as an expert.

Then one day a key supplier suggested we send our Purchase Orders to them by EDI. Hmm EDI with a supplier, not a customer. This was new. So I ring up my contact at our EDI software supplier. I know I need to get the trading partner/document relationship validated on the VAN. Oh, and the software will need some mapping tables for purchase orders. I know they are going to cost.

Me: Can I buy the translation tables for Purchase Orders please?

Contact: According to our records you purchased them some time ago. See your reference XXXXXXX

Me: Let me see (my files are organised). Ah yes, I have it in front of me. No, that was for Sales Orders. I want Purchase Orders.

Contact: What's the difference?

Me: Duh! I see what you mean. Never mind. Click

Overview Part III - Why EDI? (or, What to do with EDI Messages)

How is all this beneficial? How is it better than faxing or emailing a word attachment? In a human sense it isn't. The EDI (Electronic Data Interchange) formats are often not human readable (e.g. Edifact, Tradacom). The point is, it is meant to be machine readable.

Eh? So what? These days we have grown used to unstructured information but it wasn't always the way. The triumph of HTML (and the Internet) is the organising of unstructured information. This brings us on the often over-looked EDI 5th element. The elephant in the room no-one talks about. I left it off the list in Part I because I didn't want to scare you.

So what most people were, and are, doing was putting these messages into an database. For most people this means an RDBMS. For most this means SQL. It is a relatively easy task to design a database schema for a particular type of message (e.g. stock balance, planned delivery etc.) in a particular format (e.g. Edifact, Tradacom). But remember you will have many customers using their own choice of format and their own interpretation of the standard. They all need to go into the same schema.

Other EDI guides will tell you EDI is all about accuracy. Reducing typing errors. This is true. You will now have in your RDBMS an accurate representation of the message. So what are you going to do with it? Report it? Print it out? Change it from electronic data to hard copy? You will be surprised how common this is because if you do, as far as your customer (eh... I mean trading partner) is concerned, you will appear to be "EDI Enabled". You will have ticked the boxes.

EDI becomes really useful when the message is a transaction. If your customer sends you a purchase order and you can create a sales order on your business computer system (sales order / ERP system), without any typing, then there are opportunities for labour cost reduction. So EDI is very popular in high volume situations, like supermarket supply chain.

Unfortunately a transaction is more than just a message or a schema. What if the customer orders a product you don't recognise? Requests a delivery date that doesn't respect your quoted lead time? Asks for a ridiculous price? Each and every field has to be rigorously validated and you have to have a plan to cope when it doesn't validate. After all, who turns down a customer order? Equally you don't want to all that Call Centre labour saving to be swallowed up in increased IT admin costs.

So if you are a small fish supplying a big shark, it is possible you decide to find the 80/20 split by turning all this complicated EDI into a glorified fax machine. It happens.

Part I, Part II

Overview Part II - Communication

Communicating... sending and receiving electronic messages... ummm... sounds like email!

We all understand email and are familiar and comfortable with the concept of email. Then why is EDI (Electronic Data Interchange) more complicated? Well I think part of the answer lies in its age. EDI has been in wide spread use in business longer than email. The other reason may be down to security concerns, but it is certainly technically possible to set up an EDI system using email.

Imagine we didn't have the internet (it wasn't that long ago!). To communicate with another computer I needed either a dedicated cable, or a modem, or a network. If I have 100 trading partners (there's that phrase again) that is a lot of cables or telephoning. So the obvious answer is a network all users communicated with, that forwarded all our messages onwards. Like an out-going postbox and an in-comming mailbox. Yes, this is just like internet email.

It seems so obvious to us now, but back then TCP/IP was not the dominant networking technology. So third party businesses sprung up to operate these networks (called VANs - Value Added Networks). Unfortunately, just like EDI standards, there are lots of EDI networks. So if your customer (eh... I mean trading partner) used a particular one, you were more or less forced to use it, though networks could have their own mailboxs on other networks, to provide network to network communication.

But wait a minute. You still have to communicate with the VAN. Good VANs will offer a choice of ways - modem dial up, secure FTP (File Transfer Protocol), web pages (HTTP - HyperText Transfer Protocol). These days some of these middle-men, third parties, are really just a URL and a server.

Part I, Part III

Overview Part I - Formats

There are 4 elements to EDI (Electronic Data Interchange).

1. Composing a message
2. Sending a message
3. Receiving a message
4. Processing a message

Steps 1 & 4 are sometimes refered to as "translating" or "mapping". Steps 2 & 3 are the communication.

By translating you take a document on your computer system, say an order or an invoice, and convert it into another format. Or you take a document that someone has sent you from their computer system, and convert into a document on your system.

Senders Format --> Another Format --> Receivers Format

Why use this third format? Why not translate directly into the receivers format? Well this is quite a lot of work. Think VHS --> Betamax. Think Paralell port --> USB port. If you have lots of receivers all with their own formats then you are going to have to write a lot of different translation routines. The big idea of EDI was, if we could all agree on one standard format, and we could all write our own translation routine into, and out of, the standard and save a lot of work.

[SAP / Salesforce.com / Oracle] --> Standard --> [Oracle/ Salesforce.com / SAP]

Unfortunately everyone couldn't agree. There are lots of standards. Edifact, Ebiz-XML, Tradacom... and many more. Some are tailored to specific industries. Some are more popular in certain countries. In IT invovation is very fast. People are always thinking up new "improvements".

So when your trading partner tells you they will be using the new "DLite buzzword compliant standard", ignore the "standard" word. Don't try and enter into a conversation/debate about the merits of it and others. Just think of it as their format. Oh did I say "Trading Partner"? That is another term you have to get used to. It means customer or supplier. If they are a customer, you will be accepting their "standard". If they are a supplier, they will be accepting your "standard".

Part II, Part III

Welcome

EDI - Electronic Data Interchange. Do those letters fill you with dread? They seem to frighten a lot of people I know and they really shouldn't. After too long learning much more than I ever wanted, I now realise what I wish I'd known at the start. However the information just wasn't out there. I am going to try and bring things together on this site.I hope you find it informative.