My EDI Qlikview Dashboard

Find this post interesting? Do you like interesting things? Maybe you would like my invention, a connectible candle called a WickBrick!

Get one here http://wickbrick.etsy.com/

WickBrick

In order to get a little better visibility into our world of EDI I thought it would be fun to create a simple EDI Qlikview Dashboard. It sucks in data from a couple of Databases: AS2, TrustedLink, and Adage and shows it in a somewhat linked manner.

The first is the AS2 tab which shows incoming AS2’s, with a fancy bargraph showing how many transmissions per day. It also highlights in red if the AS2 has not yet been processed into the ERP.

It also allows me to click on an transmissions and review the raw EDI.

It also has tabs for incoming orders and outgoing invoices. All and all an interesting start to what will hopefully become a really useful dashboard. In the future I would like to add visibility to outgoing 940’s, 856’s, and perhaps incoming 945’s.

About these ads

Silly me, using the right config file for Altova Mapforce

I’ve been using mapforce for quite a long time and love it. Up until now I have always just used the default built in 00501 configurations for EDI. Well, I ran into an edi doc that needed a few additions. Well, cool I thought. I will just go in and edit the .config file, I love that flexibility! Well little did I know that if I had gone here

http://www.altova.com/components_mapforce.html

I could have just downloaded all of the various flavors from 3040-5030 (about 15 in total I think) and then I could have picked the right one to start out with! How’s that for ease of use!

Notice them all along the top! Oh, yeah and if you press the button that says Download additional EDI collections it will get you to the same place!

So since I’m lazy I just opened up the .mfd file with notepad and replaced the old config for the new config. I had to move around a few things but then wala I’m using that version of the config file. Nifty huh.

Convert Eif invoice to outgoing 880, quick Altova Mapforce Tutorial

Started working on converting an .eif file into an X12 880 using mapforce. One thing I learned was that when using the TransformTool.jar you can just specify a * (asterisk) for the new file name and it will use the original file name with .xml for the transformed file. Very Useful.

So my work flow goes something like this. Create a 880 svn repository. Get an .eif invoice and convert it to .xml using the TransformTool.jar (Manually for now)

Transform Explanation Doc is here http://mellerbeck.googlepages.com/sv_diary_5305664_ERPTEEBarcodingInst.doc

If you are curious the transform definition is

<?xml version=”1.0″?>
<!DOCTYPE InputFile SYSTEM “C:\XML\InputFile.dtd”>
<InputFile>
<Directory>
<DirName>c:\XML\810_Outbound</DirName>
<DirType>.eif</DirType>
<DocTypes>
<DocType>
<DocName>Invoice</DocName>
<DocTransform>c:\XML\Invoice_Outbound.xsl</DocTransform>
<NewFile>
<NewFileType>.xml</NewFileType>
<NewFileName>*</NewFileName>
<NewFileDir>c:\XML\810_Outbound\Processed</NewFileDir>
</NewFile>
<SaveOption>
<SaveOptionType>Save</SaveOptionType>
<SaveOptionDir>c:\XML\Archive</SaveOptionDir>
</SaveOption>
</DocType>
</DocTypes>
</Directory>
</InputFile>

Open up Mapforce and use the provided .dtd from Adage (Invoice_Outbound.dtd) and then provide it the converted .xml file (from above) to use as a reference for the mapping. (clicking the add schema/xml)

Then click the EDI button and add the 880 (under x12 tab)

Next I start studying the vendors EDI specs. I first start with ISA line, and set mapforce to match the requested Data Element Separator, Composite Separator, Repetition Separator, and Segment Terminator. I get to this by right clicking the EDI portion and then select properties. I also click the Extended button to change the Interchange Control Version Number (If needed).

Next I started creating constants and connecting them to the appropriate parts of the ISA. I just learned that Segment Terminator 85 (hex 85) equals a newline. I connect the ID qualifier and the sender/receiver ID.

Next work down to the GS. Connect a constant of GP.

Next I create my lame’o Control number generator. Basically read in two numbers, add one to it, and write out the new numbers to the same file. This is for the GS and ST segment.

I add a ‘now’ from the datetime functions and connect it to the invoice date, and then connect the document number to the invoice number (F76).

Then connect the CustomerPurchaseOrderNumber to the Purchase Order Number and PurhcaseOrderSalesOrderNumber connect to Vendor Order Number.

I connected constants for the Terms of Sale.

I met kindof a nasty snag with the date format of the InvoiceDueDate. From Adage it is output like

26-jun-2008 00:00:00

This isn’t a format that Mapforce Recognizes as a date :( so I had to parse out the day, month, year using a substring-before and replace combo, and then value map the jun into a 6 then connect it all to a datetime-from-parts. There might be an easier way so I posted the question on the Mapforce forum.

Next, started working on the N segments. This was interesting because I had never looked up a bill to name before in Adage. It is stored in the en_bill_tbl so did a select en_bill_name where en_bill_key = ‘whatever’

On further review all that was needed was to send the Remit to which made everything much easier. I connected a RE to the F98_1 and the rest of the name, address info etc…

Next I wanted to get looping information So I connect the Detail01 of my xml to the Loop0300 of the edi spec. Then I connected the price, quantity etc.. to the G17 etc…

To get the total invoice quantity use a sum and connect the InvoiceLineQuantity to it inside of a looping area of code.

I ran into the most annoying problem of not being able to import the generated EDI into TrustedLink. After a lot of work, thinking that it was a problem with the segment it turned out to be a slight bug in Mapforce. Inside of mapforce when I connected a now to the F337 it would truncate the time to 6 chars, but the code it was generating was truncating at 12 chars. Since that is invalid for the F337 the import was not happening successfully. To fix it you connect the now to a time-from-datetime and then to the F337 Time and the generated code truncates correctly.

Mapforce Woes

I love Altova mapforce. But every so often I run into minor blemishes. I used to be able to read in a file, modify it, and then save out to the same file. With with version 2008 rel. 2 this appears broken :( this is my post about it on the forum. http://www.altova.com/forum/default.aspx?g=posts&t=1000001196

Now I have to map some XML to EDI and it needs a segment terminator of <CR><LF> and so far I haven’t figured out how to do it, bummer. http://www.altova.com/forum/default.aspx?g=posts&t=1000001197 Luckily, Altova tech support is excellent and very responsive. So I’m not very worried.

The state of Open Source EDI part II

Do you like interesting things? Maybe you would like my creation, a Handmade connectible candle called a WickBrick!

Get some here http://wickbrick.bigcartel.com/product/20-white-wickbrick-s-with-2-12-wick-s

In my hunt I have stumbled across two more projects. Skylark EDI which appears to be resting from development. I haven’t had a chance to play with it yet. The second is webswell connect I tried to install this software but it took an incredibly long time and then I couldn’t figure out what to do with it. I think it probably does way more than I am looking for.

The state of Open Source EDI

Do you like interesting things? Maybe you would like my creation, a Handmade connectible candle called a WickBrick!

Get some here http://wickbrick.bigcartel.com/product/20-white-wickbrick-s-with-2-12-wick-s

It looks to me like Open Source EDI is finally starting to see the light. First off AS2: Now technically its not really ‘EDI’ but it is an often used method of exchanging EDI documents. To me I think of it as a glorified FTP server. Why oh why then is it so dang expensive? Well lets say you want to use AS2 with Wal*Mart, they require your app to be Drummond Certified. Drummond certification is a wonderful bit of lockin where the people who created the AS2 standard also created the certification and therefore are the only ones that can certify an app. One test from Drummond will cost you $10,000.

Hopefully someday, things will change. Ideally the large companies will realize that AS2 certification isn’t all that important and start letting their suppliers use what ever they want. Second best would be an open source implementation of an AS2 client becoming certified. $10,000 divided across a few companies would start to get economically feasible rather quickly, especially when you consider the cost of say a single Cleo Lexicom AS2 host license of what $1000?

One up-and-coming open source star of AS2 is m-e-c AS2

It seems to be very reliable, and I would be interested in hearing anyone with experience running it in production.

Once your communication channel is setup you need some sort of document translation as well as document management package. One interesting open source package that I have been watching quickly be developed is bots

My dream OS EDI app would be a more expanded version of bots (it would contain a screen to monitor docs, sort by document type, trading partner, sent date, and status (Ack, no ack, or rejected) it would also update the status of the document based on the the 997. Basically a web interface clone of TrustedLink for windows.

Bots would then integrate with mec-as2 and also be aware of the status of the AS2 transmission either straight from the mec-as2 database or through the MDN.

That would be cool.

Section 5: Shipto, depositor, Shipfrom loops, exception, string-join, substring-after, auto-number, replace

And here is section 5 finishing it out.

Section 5: Shipto, depositor, Shipfrom loops, exception, string-join, substring-after, auto-number, replace

Follow

Get every new post delivered to your Inbox.

Join 242 other followers