Mapping Excersizes: EDI Invoice to Open Office Tables (part Two)
Continuing Mapping Exercise
Today we will identify our data source, and begin mapping the source data to the target data. We identified our target format and placed that in the paper map last time. If you didn’t read that post yet, you might want to review it quickly before continuing. (read part One)
The Source
The source is an EDI Invoice. We will pick an X12 4010 810 as our standard, and I am providing you a link to the Standards document here. This is a sample standard and does not contain an exhaustive list of elements, just the ones that we are using. TIE810
We then insert the source elements for the data that we have in this way, starting with the most straight forward data. I am starting at the bottom and going up. IT1_04 is the Unit Price. And it will map directly. So do ProductID and Quantity. Quantity from IT1_02 and Product ID from IT1_07 where IT1_06 is “VC” (I will also look on down the line at other odd numbered elements greater than 7 that have a preceding qualifier of “VC”, this allows the execution of the map to be more flexible if the invoice line item is formatted freely. You may not be able to do this depending on your mapping technology.)
After the easy ones, the direct mapping ones, we get to the concatenated ones. InvoiceID is the concatenation of the ISA_06, ISA_08, ISA_12, GS_06, ST_02 and BIG_02. This is straightforward as well.
Conversion
The status will be pulled from the BIG_09 and we will want to convert this value. Here is where the fun begins. We may say, “Only this subset of values is accepted.” List a set in our usage spec, and fail any that don’t comply. Or we can try to map all of the invoices status that are in the standard to an internal status. But this also leaves us with a possible gap if our Trading Partner doesn’t comply with the standard.
Or we can split the difference. We map status codes that make sense to our system to their corresponding status. And for all others, we map them to “OTHER” and note what they were into the Notes Field. Doing this retains the data for someone to look at later. It prevents failures on this point, and keeps the status from becoming abstracted from the original intent.
Homework time
Go ahead and map the other straight forward data, and document any of the transformation rules that you can. When you are done you can check out the map that I completed up to this point. They don’t have to be exactly the same, and only look at the example after you have given a go at doing it yourself. Next week we will finish the mapping and talk about ways to solve some of the problems faced by the more complex and missing data.
.
Subscribe to "The Integration Engineer" by Email
Find out about the tools and services available at The Integration Engineer's Consulting site.
