Mapping Excersizes: EDI Invoice to Open Office Tables (part Three)
Finish The Paper Map
Today we will finish the three part series mapping exercise. If you haven’t gotten a chance to read the first two posts in this exercise you may want to start with them, or to review them before moving on to the conclusion. (read part One or read part Two)
In last post, we began mapping an EDI Invoice to invoice tables in an Open Office Database. I chose these as Open Office is free and anyone can get a copy to repeat this exercise, and because Invoices are common documents that need to be handled in an e-commerce and supply chain scenario.
Compete Mapping the Invoice Header
At this point we have mapped all of the direct mapping, the data that just moves from one location in the EDI, to another in the Database. We have also done some simple operations such as concatenation to form unique IDs for our invoice records using data in the invoice to preserve referential integrity to the source document. And we have done some conversions for invoice Status. If we though about it, we also indicated that dates were mapped and converted.
Sometimes we will have large conversions, or specific conditions that we want to key a conditional conversion on. We can either code these into the maps, or we can use some API call that the technology we are using provides us. In our example we are going to code them into the map. But you may want to do this in an API or another process in your environment.
Status Conversion
We ask in our specification that all BIG_07 be the value “BS” as “Bill Of Sale” But we don’t want to error out on the invoice if there is any other value. So we export and code a conversion condition into the map. If it is BS, we map BOS to the Status Field, but if it is any other value, we attempt to look that value up in our list of codes, and format a note to be inserted into notes with this text; “Invoice Status of __ as [converted value] please take appropriate action.” And if the Status Code is not in our table we insert, “Invoice Status of __ was unknown.” into the notes.
This way our mapping of this converted value does not rely on the validity of the code, but handles any value presented. And this keeps us from having the people who handle EDI translation exceptions from having to deal with overrides that belong to AP.
Location Codes or Text
Depending on the way you do business, you may only be able to handle account codes for shipping and billing. Or you may want the full billing address for each Invoice in the database. For us, we will do a little of both. When we encounter the N1 loop we will look first at the N1_04. We will match this with an API call to another application to validate that it is a known Billing Account. If it is, we insert the account code into the billing field, and can skip the rest of the data in the N1. If you are unfamiliar with EDI loops, you might want to take a look at this overview of repeating EDI segments.
But if it is new, or not in the system, we will need to have someone in AP verify that we want to remit payment to this location. So we process the N1 lines, and either insert them into the Billing Field, or insert them as a note on the invoice. We will probably need a Billing Account Code that indicates that user action is required if this happens. We are not going to handle this in our mapping. We will let the remittance application handle this process. There are as many ways to handle these situation as there are companies. But in your environment the capabilities of your systems will indicate how to make the most robust integration.
Multiple Mapping Location Options
Some times there will be more than one place where we can get a piece of data. Sometimes this will like creating a total by multiplying all of the line totals. But other times this might be that the Product ID could be in any of the IT1 odd numbered elements from 7-25 and we need to find the one that is qualified with “VC” found on the respective IT1 even numbered elements 6-24. And sometimes we will have a billing term, that might be found in the ITD that is for the whole invoice, but might also be found on a line by line location. (or even more complex, both, with one superseding the other location when found.) Luckily, we have examples in our mapping project for you to become familiar with.
Double Checking Your Paper Map
Walk through this paper map again and see what details have changed along the way. Try to complete the mapping on your own, and then you can take a look at the map that I completed. Because we haven’t actually coded a map to convert the transaction, there will be a few things that we will have over looked. There is nothing wrong with that. We will fill those in when we write the map that we will execute. This is the final step in making a paper map, putting it to use.
So here is the paper map that I completed. I will be creating a conversion for this process and processing some test documents. When that happens, I wil post the transformer, and the sample DB and files. You will be able to look at how that works and tinker with the code to see if your mapping works the same. Until then ask me any questions about the mapping process in the comments section of this post.
Subscribe to "The Integration Engineer" by Email
Find out about the tools and services available at The Integration Engineer's Consulting site.
