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!